通过GRANT将执行计划踢出共享池

1.scott用户查询SPID

scott@PROD>select distinct(sid) from v$mystat;

       SID
----------
     45

scott@PROD>select p.spid,p.pid from v$process p,v$session s where s.paddr=p.addr and s.sid=45;

SPID                    PID
------------------------ ----------
10216                    36

2.SYS用户打开10046事件

sys@PROD>oradebug setospid 10216
Oracle pid: 36, Unix process pid: 10216, image: oracle@gc1 (TNS V1-V3)
sys@PROD>oradebug event 10046 trace name context forever,level 12;
Statement processed.

3.SCOTT用户做查询

scott@PROD>select count(*) from t1; --硬解析

  COUNT(*)
----------
     50218

scott@PROD>select count(*) from t1;--软解析

  COUNT(*)
----------
     50218

scott@PROD>grant select on t1 to tom;--对表t1做DDL操作

Grant succeeded.

scott@PROD>select count(*) from t1; --这次应该是硬解析

  COUNT(*)
----------
     50218

scott@PROD>select count(*) from t1; 

  COUNT(*)
----------
     50218

4.结束10046，查出trace文件路径

sys@PROD>oradebug event 10046 trace name context off;
Statement processed.
sys@PROD>oradebug tracefile_name
/u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace/PROD_ora_10216.trc

[oracle@gc1:/u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace]$  tkprof PROD_ora_10216.trc PROD_ora_10216.trc.tk1  aggregate=no sys=no --相同的SQL不合并

TKPROF: Release 11.2.0.1.0 - Development on Wed Jan 28 21:58:31 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


[oracle@gc1:/u01/app/oracle/oradata/PROD/dump/diag/rdbms/prod/PROD/trace]$ less PROD_ora_10216.trc.tk1

TKPROF: Release 11.2.0.1.0 - Development on Wed Jan 28 21:58:31 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Trace file: PROD_ora_10216.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
--------------------------------------------------------------------------------

*** SESSION ID:(45.515) 2015-01-28 21:51:28.679

********************************************************************************

SQL ID: 5bc0v4my7dvr5
Plan Hash: 3724264953
select count(*)
from
t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0         39          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        206          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.04          0        245          0           1

Misses in library cache during parse:  1 --第一次为硬解析
Optimizer mode: ALL_ROWS
Parsing user id: 33 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=206 pr=0 pw=0 time=0 us)
  50218   TABLE ACCESS FULL T1 (cr=206 pr=0 pw=0 time=26194 us cost=56 size=0 card=49953)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message from client                     2        1.33          1.33
********************************************************************************

SQL ID: 5bc0v4my7dvr5
Plan Hash: 3724264953
select count(*)
from
t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        206          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        206          0           1

Misses in library cache during parse:  0   --软解析
Optimizer mode: ALL_ROWS
Parsing user id: 33 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=206 pr=0 pw=0 time=0 us)
  50218   TABLE ACCESS FULL T1 (cr=206 pr=0 pw=0 time=36544 us cost=56 size=0 card=49953)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       22.33         22.33
********************************************************************************

SQL ID: g93b08rgfknc5
Plan Hash: 0
grant select on t1 to tom


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02       0.02          0         71          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0         71          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   1        0.01          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        2.23          2.23
********************************************************************************

SQL ID: 5bc0v4my7dvr5
Plan Hash: 3724264953
select count(*)
from
t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        206          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        206          0           1

Misses in library cache during parse:  1 --grant后的这次执行重新进行了解析
Optimizer mode: ALL_ROWS
Parsing user id: 33 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=206 pr=0 pw=0 time=0 us)
  50218   TABLE ACCESS FULL T1 (cr=206 pr=0 pw=0 time=47022 us cost=56 size=0 card=49953)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        7.62          7.62
********************************************************************************

SQL ID: 5bc0v4my7dvr5
Plan Hash: 3724264953
select count(*)
from
t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        206          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        206          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 33 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=206 pr=0 pw=0 time=0 us)
  50218   TABLE ACCESS FULL T1 (cr=206 pr=0 pw=0 time=41144 us cost=56 size=0 card=49953)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

由此我们可以看出对sql中的对象做DDL操作会把该执行计划踢出共享池

生产中最安全的就是GRANT这种方式了