create table test0605 as select * from dba_objects;select t1.owner,t1.object_name,t1.object_id from test0605 t1 where t1.object_id=3344;select t1.SQL_ID,t1.HASH_VALUE,t1.SQL_TEXT,t1.SQL_FULLTEXT,to_char(t1.LAST_ACTIVE_TIME,'yyyy-mm-dd hh24:mi:ss') LAST_ACTIVE_TIME ,to_char(t1.LAST_LOAD_TIME,'yyyy-mm-dd hh24:mi:ss') LAST_LOAD_TIME from v$sqlarea t1 where t1.SQL_FULLTEXT like '%test0605 t1 where t1.object_id=3344%' order by LAST_ACTIVE_TIME desc,LAST_LOAD_TIME desc;/*select * from v$sql_plan t1 where t1.;select * from v$sql_plan_statistics;select * from v$sql_workarea;*/select * from v$sql_plan_statistics_all t1 where t1.SQL_ID='f21sgfyqvkz9m';select * from table(dbms_xplan.display_cursor('f21sgfyqvkz9m',0,'advanced'));select * from table(dbms_xplan.display_awr('f21sgfyqvkz9m'));--1008关于一定时期内显著的执行计划变动、资源消耗变动 ，oracle提供了awrsqrpt.sql 、spresql.sql两个脚本--10211030包含执行计划的表中的字段基本字段字段 描述id 每一个操作的标识符。如果数字前面带有星号，意味着该行有谓词信息operation 该行的操作，也叫 row sourcename 操作的对象查询优化器评估类rows(e-rows) 评估中操作返回的记录总数bytes(e-bytes) 评估中操作返回的字节数tempspc 评估中操作使用的临时空间大小cost(%cpu) 评估中操作的开销。括号中是开销的百分比。注意该值是通过执行计划计算过来的。即：父操作的开销包含子操作的开销time 评估中执行操作需要的时间分区类pstart 访问的第一个分区。如果解析时不知道是哪个分区就设为key、key(i) 、key(mc)、 key(or)、 key(sq)pstop 访问的最后一个分区。如果解析时不知道是哪个分区就设为key、key(i) 、key(mc)、 key(or)、 key(sq)运行时统计starts 指定操作执行的次数a-rows 指定操作返回的真实记录数a-time 指定操作执行的真实时间io统计类buffers 操作期间进行的逻辑读数量reads 操作期间进行的物理读数量writes 操作期间进行的物理写数量内存使用统计类0mem 最优执行所需内存的评估值1mem 一次性通过(one-pass)执行所需内存的评估值used-mem 最后一次操作时使用的内存量user-tmp 最后一次操作时使用的临时空间大小--1439 display函数/*format参数可用的基本值basic 仅显示最少的信息。基本上之报错操作和操作的对象typical 显示大部分相关内容，基本上包含除了别名，提纲和字段投影外的所有信息 serial 和typical类似，只是没有并行的相关信息all 显示除了提纲外的所有信息advanced 显示所有信息alias 空值包含查询块名和对象别名那一部分的显示bytes 控制执行计划表中字段bytes的显示cost 控制执行计划表中字段cost的显示note 控制包含注意信息note那一部分的显示outline 控制包含提纲outline那一部分的显示parallel 空值并行处理信息的显示，尤其是，执行计划表中字段TQ、IN-OUT、PQ distrib的显示partition 控制分区信息的显示，尤其是pstart和pstop的显示peek_binds 控制包含被窥测的绑定变量那一部分的显示。predicate 控制包含谓词filter和access那一部分的显示projection 控制包含字段投影信息那一部分的显示remote 控制远程执行的sql语句的显示rows 控制执行计划表中字段rows的显示*/--1055--case 1SQL> CREATE TABLE t AS SELECT * FROM o;SQL> execute dbms_stats.gather_table_stats(user, 't')SQL> EXPLAIN PLAN FOR CREATE TABLE t AS SELECT * FROM o;SQL> SQL> SELECT * FROM table(dbms_xplan.display);PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------Plan hash value: 1827057699 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | 1000 | 84000 | 5 (0)| 00:00:01 | | 1 | LOAD AS SELECT | T | | | | | | 2 | TABLE ACCESS FULL | O | 1000 | 84000 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------- --case2SQL> CREATE INDEX i ON t (object_id);SQL> SQL> EXPLAIN PLAN FOR ALTER INDEX i REBUILD;SQL> SQL> SELECT * FROM table(dbms_xplan.display);PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------Plan hash value: 1634214326 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 1000 | 4000 | 2 (0)| 00:00:01 | | 1 | INDEX BUILD NON UNIQUE| I | | | | | | 2 | SORT CREATE INDEX | | 1000 | 4000 | | | | 3 | INDEX FAST FULL SCAN| I | 1000 | 4000 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------- --case3SQL> EXPLAIN PLAN FOR SELECT * FROM t;SQL> SQL> SELECT * FROM table(dbms_xplan.display);PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 84000 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| T | 1000 | 84000 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- --case4SQL> EXPLAIN PLAN FOR INSERT INTO t SELECT * FROM o WHERE rownum = 1;SQL> SQL> SELECT * FROM table(dbms_xplan.display);PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------Plan hash value: 1110922135 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 84 | 3 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | T | | | | | |* 2 | COUNT STOPKEY | | | | | | | 3 | TABLE ACCESS FULL | O | 1000 | 84000 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(ROWNUM=1) --case5SQL> EXPLAIN PLAN FOR UPDATE t SET subobject_name = object_name;SQL> SQL> SELECT * FROM table(dbms_xplan.display);PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------Plan hash value: 931696821 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1000 | 33000 | 3 (0)| 00:00:01 | | 1 | UPDATE | T | | | | | | 2 | TABLE ACCESS FULL| T | 1000 | 33000 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- --case6SQL> EXPLAIN PLAN FOR MERGE INTO t 2 USING (SELECT * FROM o) o 3 ON (t.object_id = o.object_id) 4 WHEN MATCHED THEN UPDATE SET t.subobject_name = o.subobject_name 5 WHEN NOT MATCHED THEN INSERT (owner, object_name, object_id, created, last_ddl_time) 6 VALUES (o.owner, o.object_name, o.object_id, o.created, o.last_ddl_time);SQL> SQL> SELECT * FROM table(dbms_xplan.display);PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------Plan hash value: 4087175684 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1000 | 162K| 7 (15)| 00:00:01 | | 1 | MERGE | T | | | | | | 2 | VIEW | | | | | | |* 3 | HASH JOIN OUTER | | 1000 | 164K| 7 (15)| 00:00:01 | | 4 | TABLE ACCESS FULL| O | 1000 | 84000 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL| T | 1000 | 84000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T"."OBJECT_ID"(+)="O"."OBJECT_ID") --case7SQL> EXPLAIN PLAN FOR DELETE FROM t;SQL> SQL> SELECT * FROM table(dbms_xplan.display);PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------Plan hash value: 3335594643 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 1000 | 4000 | 3 (0)| 00:00:01 | | 1 | DELETE | T | | | | | | 2 | TABLE ACCESS FULL| T | 1000 | 4000 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- --1519dbms_xplan.display_cursor/*format可用的修饰符 allstats* 是iostats memstats的快捷方式iostat* 控制I/0统计的显示last* 默认，显示所有执行计划过的统计。如果指定此值，只显示最后一次执行的统计信息memstats* 控制pga相关统计的显示runstats_last 和iostats last 相同，适用于 oracle10g r1runstats_tot 和iostats相同。适用于 oracle10g r1以上附带*表示从oracle10g r2 起可以使用*/case1 SQL> UPDATE t 2 SET val = (SELECT /*+ index(t) */ max(val) FROM t WHERE id BETWEEN 6 AND 19), 3 pad = (SELECT pad FROM t WHERE id = 6) 4 WHERE id IN (SELECT id FROM t WHERE id BETWEEN 6 AND 19);SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'basic'));PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------EXPLAINED SQL STATEMENT:------------------------UPDATE t SET val = (SELECT /*+ index(t) */ max(val) FROM t WHERE idBETWEEN 6 AND 19), pad = (SELECT pad FROM t WHERE id = 6) WHERE idIN (SELECT id FROM t WHERE id BETWEEN 6 AND 19)Plan hash value: 2674170182----------------------------------------------| Id | Operation | Name |----------------------------------------------| 0 | UPDATE STATEMENT | || 1 | UPDATE | T || 2 | NESTED LOOPS | || 3 | TABLE ACCESS FULL | T || 4 | INDEX UNIQUE SCAN | T_PK || 5 | SORT AGGREGATE | || 6 | TABLE ACCESS BY INDEX ROWID| T || 7 | INDEX FULL SCAN | I || 8 | TABLE ACCESS BY INDEX ROWID | T || 9 | INDEX UNIQUE SCAN | T_PK |------------------------------------------------case2SQL> SELECT deptno, count(*) 2 FROM emp 3 WHERE job = 'CLERK' AND sal < 1200 4 GROUP BY deptno;SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------SQL_ID 5k3rhmx9hprh7, child number 0-------------------------------------SELECT deptno, count(*) FROM emp WHERE job = 'CLERK' AND sal < 1200GROUP BY deptnoPlan hash value: 4067220884--------------------------------------------| Id | Operation | Name | E-Rows |--------------------------------------------| 0 | SELECT STATEMENT | | || 1 | HASH GROUP BY | | 1 ||* 2 | TABLE ACCESS FULL| EMP | 1 |--------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter(("JOB"='CLERK' AND "SAL"<1200))Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level--case3SQL> SELECT * 2 FROM emp 3 WHERE rownum <= 10;SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------SQL_ID ajp501ttxv1vn, child number 0-------------------------------------SELECT * FROM emp WHERE rownum <= 10Plan hash value: 1973284518--------------------------------------------| Id | Operation | Name | E-Rows |--------------------------------------------| 0 | SELECT STATEMENT | | ||* 1 | COUNT STOPKEY | | || 2 | TABLE ACCESS FULL| EMP | 1 |--------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=10)Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level--case4SQL> SELECT * 2 FROM ( 3 SELECT * 4 FROM emp 5 ORDER BY sal DESC 6 ) 7 WHERE rownum <= 10;SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------SQL_ID 7jpchs5c5bstu, child number 0-------------------------------------SELECT * FROM ( SELECT * FROM emp ORDER BY sal DESC ) WHERErownum <= 10Plan hash value: 1744961472-------------------------------------------------| Id | Operation | Name | E-Rows |-------------------------------------------------| 0 | SELECT STATEMENT | | ||* 1 | COUNT STOPKEY | | || 2 | VIEW | | 1 ||* 3 | SORT ORDER BY STOPKEY| | 1 || 4 | TABLE ACCESS FULL | EMP | 1 |-------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=10) 3 - filter(ROWNUM<=10)Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --case5SQL> SELECT * 2 FROM emp 3 WHERE job = 'CLERK' 4 AND 1=2;SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------SQL_ID 7vajq952vnrbk, child number 0-------------------------------------SELECT * FROM emp WHERE job = 'CLERK' AND 1=2Plan hash value: 3896240783--------------------------------------------| Id | Operation | Name | E-Rows |--------------------------------------------| 0 | SELECT STATEMENT | | ||* 1 | FILTER | | ||* 2 | TABLE ACCESS FULL| EMP | 1 |--------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(NULL IS NOT NULL) 2 - filter("JOB"='CLERK')Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --case 6SQL> SELECT ename FROM emp 2 UNION ALL 3 SELECT dname FROM dept 4 UNION ALL 5 SELECT '%' FROM dual;ENAME----------ACCOUNTINGRESEARCHSALESOPERATIONS%SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------SQL_ID 781xq971h0y2p, child number 0-------------------------------------SELECT ename FROM emp UNION ALL SELECT dname FROM dept UNION ALL SELECT'%' FROM dualPlan hash value: 4181933179--------------------------------------------| Id | Operation | Name | E-Rows |--------------------------------------------| 0 | SELECT STATEMENT | | || 1 | UNION-ALL | | || 2 | TABLE ACCESS FULL| EMP | 1 || 3 | TABLE ACCESS FULL| DEPT | 4 || 4 | FAST DUAL | | 1 |--------------------------------------------Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --case7SQL> SELECT /*+ ordered use_nl(dept) index(dept) */ * 2 FROM emp, dept 3 WHERE emp.deptno = dept.deptno 4 AND emp.comm IS NULL 5 AND dept.dname != 'SALES';SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------SQL_ID 3ux4g340c933p, child number 0-------------------------------------SELECT /*+ ordered use_nl(dept) index(dept) */ * FROM emp, dept WHEREemp.deptno = dept.deptno AND emp.comm IS NULL AND dept.dname != 'SALES'Plan hash value: 2694310824---------------------------------------------------------| Id | Operation | Name | E-Rows |---------------------------------------------------------| 0 | SELECT STATEMENT | | || 1 | NESTED LOOPS | | || 2 | NESTED LOOPS | | 1 ||* 3 | TABLE ACCESS FULL | EMP | 1 ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | 1 ||* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |---------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("EMP"."COMM" IS NULL) 4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") 5 - filter("DEPT"."DNAME"<>'SALES')Note----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --case 8SQL> SELECT * 2 FROM emp 3 WHERE NOT EXISTS (SELECT /*+ no_unnest */ 0 4 FROM dept 5 WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno) 6 AND NOT EXISTS (SELECT /*+ no_unnest */ 0 7 FROM bonus 8 WHERE bonus.ename = emp.ename); FROM bonus *第 7 行出现错误:ORA-00942: ？？？？？？？SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------SQL_ID 2tty67sw0nz6c, child number 1-------------------------------------SELECT * FROM table(dbms_xplan.display_cursor(null,null,'iostats last'))Plan hash value: 3713220770---------------------------------------------------------------------| Id | Operation | Name | E-Rows |---------------------------------------------------------------------| 0 | SELECT STATEMENT | | || 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 52 |---------------------------------------------------------------------Note----- - cardinality feedback used for this statement - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --1555SQL> CREATE TABLE t 2 PARTITION BY HASH (id) 3 PARTITIONS 2 4 AS 5 SELECT rownum AS id, rownum AS n, lpad('*',1000,'*') AS pad 6 FROM dual 7 CONNECT BY level <= 1000;SQL>SQL> PAUSESQL>SQL> REM Display information about partitioning (columns Pstart and Pstop) andSQL> REM temporary space (column TempSpc)SQL>SQL> SELECT /*+ gather_plan_statistics */ count(pad) 2 FROM (SELECT /* parallel(t,2) */ rownum AS rn, pad 3 FROM t 4 ORDER BY n) 5 WHERE rn = 1;COUNT(PAD)---------- 1SQL> /COUNT(PAD)---------- 1SQL> /COUNT(PAD)---------- 1SQL> /COUNT(PAD)---------- 1SQL>SQL> SELECT * FROM table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------SQL_ID 6rwckvk4d8xjb, child number 0-------------------------------------SELECT /*+ gather_plan_statistics */ count(pad) FROM (SELECT /*parallel(t,2) */ rownum AS rn, pad FROM t ORDER BY n) WHERErn = 1Plan hash value: 2056542717-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | | 171 (100)| | | || 1 | SORT AGGREGATE | | 1 | 515 | | | | | ||* 2 | VIEW | | 1177 | 591K| | 171 (2)| 00:00:02 | | || 3 | SORT ORDER BY | | 1177 | 591K| 640K| 171 (2)| 00:00:02 | | || 4 | COUNT | | | | | | | | || 5 | PARTITION HASH ALL| | 1177 | 591K| | 19 (0)| 00:00:01 | 1 | 2 || 6 | TABLE ACCESS FULL| T | 1177 | 591K| | 19 (0)| 00:00:01 | 1 | 2 |---------------------------------------------------------------------------------------------------------1744SQL> alter session set statistics_level=all;Session altered.执行这步之后，执行计划中才会显示 startsselect /*+ gather_plan_statistics */ * from data_skew;select * from table(dbms_xplan.display_cursor(null,null,'advanced'));select * from data_skew where rownum < 101;--- 4dy2ffhhyud3c 569193580 0 select * from data_skew where rownum <100 1 2017/6/6 14:21:36 2017/6/6 14:21:35SELECT * FROM table(dbms_xplan.display_cursor('6rwckvk4d8xjb',0,'iostats last'));--case 2 advancedSELECT * FROM table(dbms_xplan.display_cursor('6rwckvk4d8xjb',0,'advanced'));--case 3 typicalSELECT * FROM table(dbms_xplan.display_cursor('6rwckvk4d8xjb',0,'typical'));--case 4 allSELECT * FROM table(dbms_xplan.display_cursor('6rwckvk4d8xjb',0,'all'));--case 5 memstats lastSELECT * FROM table(dbms_xplan.display_cursor('6rwckvk4d8xjb',0,'memstats last'));SELECT * FROM table(dbms_xplan.display_cursor('6rwckvk4d8xjb',0,'iostats last'));SELECT /*+ gather_plan_statistics */ count(pad)FROM (SELECT /* parallel(t,2) */ rownum AS rn, pad FROM t ORDER BY n)WHERE rn = 1;grant execute on dbms_hpof to ownerdbms_profiler. alter procedure sys.dbms_profiler.perfect_triangles compile debug;grant all on dbms_profiler to public;dbms_output.put_lineselect t1.object_name,t1.object_id,t1.object_type,t1.owner from dba_objects t1 where t1.object_name='PERFECT_TRIANGLES';SELECT s.line, round(ratio_to_report(p.total_time) OVER ()*100,1) AS time, total_occur, s.textFROM all_source s, (SELECT u.unit_owner, u.unit_name, u.unit_type, d.line#, d.total_time, d.total_occur FROM plsql_profiler_units u, plsql_profiler_data d WHERE u.runid = &runid AND d.runid = u.runid AND d.unit_number = u.unit_number) pWHERE s.owner = p.unit_owner (+)AND s.name = p.unit_name (+)AND s.type = p.unit_type (+)AND s.line = p.line# (+)AND s.owner = userAND s.name = 'SLEEP'ORDER BY s.line;dbms_sql.explain plan for select * from data_skew where rownum <2000;exec dbms_stats.gather_table_stats(user,'DATA_SKEW')；select sys_context('userenv','sid') from dual;select * from v$sql_plan_statistics ;select /*+ gather_plan_statistics*/ * from t where n2=19;select sps.LAST_OUTPUT_ROWS, sps.LAST_CR_BUFFER_GETS, sps.LAST_CU_BUFFER_GETS from v$session s, v$sql_plan_statistics sps where s.prev_sql_id = sps.sql_id and s.PREV_CHILD_NUMBER = sps.CHILD_NUMBER and s.sid = sys_context('userenv', 'sid') and sps.OPERATION_ID = 1;dbms_stats.ObjectTabselect * from test0605;alter table test0605 add (tmp_obj as (substr(object_name,1,3)));SELECT partition_name, partition_position, num_rowsFROM user_tab_partitionsWHERE table_name = 'T'ORDER BY partition_position;select p.partition_position,p.partition_name,s.subpartition_position,s.subpartition_namefrom user_tab_partitions p, user_tab_subpartitions swhere p.table_name='T' and s.table_name=p.table_name and s.partition_name=p.partition_nameorder by p.partition_position,s.subpartition_position;select * from test0605;exec dbms_stats.gather_table_stats(user,'TEST0605');exec dbms_stats.gather_index_stats(user,'IDX_OBJECT_ID');select /*+ index(test0605 IDX_OBJECT_ID) */ object_name from test0605 where object_id =3389;explain plan for select /*+ index_ffs(test0605 IDX_OBJECT_ID) */ object_name from test0605 where object_id is not null;select extract(hg from sql_id) from v$sql_cs_statistics;select owner,object_id,extract('SYS' from O) from test;select * /*,extract(YEAR FROM CREATED)*/ from test;select * from dba_enabled_traces;select * from v$diag_info where name='';select * from tvd$xtat ;select t1.pname,t1.pval1,t1.pval2 from sys.aux_stats$ t1where t1.sname='SYSSTATS_MAIN';select sum(t1.SINGLEBLKRDS) as count,sum(t1.SINGLEBLKRDTIM) time_ms from v$filestat t1;create table t as select rownum as id,round(dbms_random.normal*1000) as val1,100+round(ln(rownum/3.25+2 )) as val2,100+round(ln(rownum/3.25+2 )) as val3,dbms_random.string('p',250) as pad from all_objects where rownum<=1000order by dbms_random.value;update t set val1=null where val1<0;alter table t add constraints t_pk primary key(id);create index t_val1_i on t(val1);create index t_val2_i on t(val2);begin dbms_stats.gather_table_stats( ownname=>user, tabname=>'T', estimate_percent=>100, method_opt =>'for all columns size skewonly', cascade=>true); end;select t1.NUM_ROWS,t1.BLOCKS,t1.EMPTY_BLOCKS,t1.AVG_SPACE,t1.CHAIN_CNT,t1.AVG_ROW_LEN from user_tab_statistics t1where t1.TABLE_NAME='T'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN---------- ---------- ------------ ---------- ---------- ----------- 1000 44 0 0 0 266 /*NUM_ROWS 表中数据的行数BLOCKS 高水位线以下的数据块个数EMPTY_BLOCKS 高水位线以上的数据块个数AVG_SPACE 表中数据块的平均空闲空间（单位：字节）CHAIN_CNT 涉及行迁移、行链接的总行数AVG_ROW_LEN 表中平均每个记录的长度（单位：字节）*/select t1.column_name name,t1.num_distinct "#dst",t1.low_value,t1.high_value,t1.density dens,t1.num_nulls "#null",t1.avg_col_len avglen,t1.HISTOGRAM,t1.num_buckets "#bkt" from user_tab_col_statistics t1where t1.table_name='T';NAME #dst LOW_VALUE HIGH_VALUE DENS #null AVGLEN HISTOGRAM #bkt------------------------------ ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ---------- --------------- ----------ID 1000 C102 C20B .001 0 4 NONE 1VAL1 457 C105 C22160 .002353264 481 3 HEIGHT BALANCED 254VAL2 6 C20202 C20207 .0005 0 4 FREQUENCY 6VAL3 6 C20202 C20207 .0005 0 4 FREQUENCY 6PAD 1000 203F2C5D523371586E695D456775533C565A522A4F3128234F43502F77353026 7E7E7438735D7A464E77205B7B79454D517E384069784521344735457E2F2120 .001 0 251 HEIGHT BALANCED 254select utl_raw.cast_to_number(low_value) as low_value,utl_raw.cast_to_number(high_value) as high_valuefrom user_tab_col_statisticswhere table_name='T'and column_name='VAL1';/* LOW_VALUE HIGH_VALUE---------- ---------- 4 3295*/begin dbms_stats.gather_index_stats(ownname =>user, indname =>'T_VAL1_I',estimate_percent=>100); end; select t1.INDEX_NAME,t1.blevel,t1.LEAF_BLOCKS,t1.DISTINCT_KEYS,t1.NUM_ROWS,t1.CLUSTERING_FACTOR,t1.AVG_LEAF_BLOCKS_PER_KEY,t1.AVG_DATA_BLOCKS_PER_KEYfrom user_ind_statistics t1where t1.TABLE_NAME='T';/*INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY------------------------------ ---------- ----------- ------------- ---------- ----------------- ----------------------- -----------------------T_PK 1 2 1000 1000 972 1 1T_VAL1_I 1 2 457 519 508 1 1T_VAL2_I 1 3 6 1000 174 1 29BLEVEL 访问叶子快而需要读取的分支块的数量，包括根块LEAF_BLOCKS 索引中的叶子块数量DISTINCT_KEYS 索引中唯一键值总数NUM_ROWS 索引中的键值数，对于主键来说 等同于 DISTINCT_KEYSCLUSTERING_FACTOR 表明有多少临近的索引条目知道不同的数据块。如果表中数据和索引的排序是相似的，那么 聚簇因子就小。最小值是表中非空数据的数据块总数如果表中数据和索引的排序迥异，聚簇因子就非常大。最大值是索引中的键值数*/select i.index_name,i.clustering_factor,clustering_factor(user,i.table_name,ic.COLUMN_NAME) as my_clstffrom user_indexes i,user_ind_columns ic where i.table_name='T' and i.index_name=ic.INDEX_NAME;select * from v$fixed_table;select * from user_tab_pending_stats;201705310907select dbms_stats.get_stats_history_retention() from dual;select extract(hg from sql_id) from v$sql_cs_statistics;select owner,object_id,extract('SYS' from O) from test;select * /*,extract(YEAR FROM CREATED)*/ from test;select * from dba_enabled_traces;select * from v$diag_info where name='';select * from tvd$xtat ;select t1.pname,t1.pval1,t1.pval2 from sys.aux_stats$ t1where t1.sname='SYSSTATS_MAIN';select sum(t1.SINGLEBLKRDS) as count,sum(t1.SINGLEBLKRDTIM) time_ms from v$filestat t1;create table t as select rownum as id,round(dbms_random.normal*1000) as val1,100+round(ln(rownum/3.25+2 )) as val2,100+round(ln(rownum/3.25+2 )) as val3,dbms_random.string('p',250) as pad from all_objects where rownum<=1000order by dbms_random.value;update t set val1=null where val1<0;alter table t add constraints t_pk primary key(id);create index t_val1_i on t(val1);create index t_val2_i on t(val2);begin dbms_stats.gather_table_stats( ownname=>user, tabname=>'T', estimate_percent=>100, method_opt =>'for all columns size skewonly', cascade=>true); end;select t1.NUM_ROWS,t1.BLOCKS,t1.EMPTY_BLOCKS,t1.AVG_SPACE,t1.CHAIN_CNT,t1.AVG_ROW_LEN from user_tab_statistics t1where t1.TABLE_NAME='T'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN---------- ---------- ------------ ---------- ---------- ----------- 1000 44 0 0 0 266 /*NUM_ROWS 表中数据的行数BLOCKS 高水位线以下的数据块个数EMPTY_BLOCKS 高水位线以上的数据块个数AVG_SPACE 表中数据块的平均空闲空间（单位：字节）CHAIN_CNT 涉及行迁移、行链接的总行数AVG_ROW_LEN 表中平均每个记录的长度（单位：字节）*/select t1.column_name name,t1.num_distinct "#dst",t1.low_value,t1.high_value,t1.density dens,t1.num_nulls "#null",t1.avg_col_len avglen,t1.HISTOGRAM,t1.num_buckets "#bkt" from user_tab_col_statistics t1where t1.table_name='T';NAME #dst LOW_VALUE HIGH_VALUE DENS #null AVGLEN HISTOGRAM #bkt------------------------------ ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ---------- --------------- ----------ID 1000 C102 C20B .001 0 4 NONE 1VAL1 457 C105 C22160 .002353264 481 3 HEIGHT BALANCED 254VAL2 6 C20202 C20207 .0005 0 4 FREQUENCY 6VAL3 6 C20202 C20207 .0005 0 4 FREQUENCY 6PAD 1000 203F2C5D523371586E695D456775533C565A522A4F3128234F43502F77353026 7E7E7438735D7A464E77205B7B79454D517E384069784521344735457E2F2120 .001 0 251 HEIGHT BALANCED 254select utl_raw.cast_to_number(low_value) as low_value,utl_raw.cast_to_number(high_value) as high_valuefrom user_tab_col_statisticswhere table_name='T'and column_name='VAL1';/* LOW_VALUE HIGH_VALUE---------- ---------- 4 3295*/begin dbms_stats.gather_index_stats(ownname =>user, indname =>'T_VAL1_I',estimate_percent=>100); end; select t1.INDEX_NAME,t1.blevel,t1.LEAF_BLOCKS,t1.DISTINCT_KEYS,t1.NUM_ROWS,t1.CLUSTERING_FACTOR,t1.AVG_LEAF_BLOCKS_PER_KEY,t1.AVG_DATA_BLOCKS_PER_KEYfrom user_ind_statistics t1where t1.TABLE_NAME='T';/*INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY------------------------------ ---------- ----------- ------------- ---------- ----------------- ----------------------- -----------------------T_PK 1 2 1000 1000 972 1 1T_VAL1_I 1 2 457 519 508 1 1T_VAL2_I 1 3 6 1000 174 1 29BLEVEL 访问叶子快而需要读取的分支块的数量，包括根块LEAF_BLOCKS 索引中的叶子块数量DISTINCT_KEYS 索引中唯一键值总数NUM_ROWS 索引中的键值数，对于主键来说 等同于 DISTINCT_KEYSCLUSTERING_FACTOR 表明有多少临近的索引条目知道不同的数据块。如果表中数据和索引的排序是相似的，那么 聚簇因子就小。最小值是表中非空数据的数据块总数如果表中数据和索引的排序迥异，聚簇因子就非常大。最大值是索引中的键值数*/select i.index_name,i.clustering_factor,clustering_factor(user,i.table_name,ic.COLUMN_NAME) as my_clstffrom user_indexes i,user_ind_columns ic where i.table_name='T' and i.index_name=ic.INDEX_NAME;select * from v$fixed_table;select * from user_tab_pending_stats;201705310907--统计信息保留窗口select dbms_stats.get_stats_history_retention() from dual;--2.0 修改保留窗口exec dbms_stats.alter_stats_history_retention(retention=>14);--3.0 删除14t天之前的统计信息dbms_stats.purge_stats(before_timestamp=>systimestamp-14);--执行 2.0 3.0 需要权限 analyze any dictionary--4.0 确认统计信息是否变动过select t1.OWNER,t1.TABLE_NAME，to_char(t1.STATS_UPDATE_TIME,'yyyy-mm-dd hh24:mi:ss') stats_time from dba_tab_stats_history t1 where t1.owner='OWNER' and t1.TABLE_NAME='T1' order by stats_time desc;--5.0 恢复单张表的统计信息dbms_stats.restore_table_stats(ownname=>,tabname=>,as_of_timestamp=>,force=>)--6.0 修改表的统计信息exec dbms_stats.gather_table_stats(ownname=>'OWNER',tabname=>'T1'); select wri.rowcnt,wri.blkcnt,wri.avgrln,wri.analyzetime from sys.wri$_optstat_tab_history wri,dba_objects obj where wri.obj#=obj.data_object_id and obj.object_name='T1' order by wri.analyzetime desc nulls last,obj.subobject_name asc nulls last, obj.data_object_id desc nulls last;select count(1) from test t1 ;--71988select t1.owner,t1.object_name from test t1 where t1.object_id =3389;SQL> exec dbms_stats.set_table_stats( tabname=>'T1',ownname=>'OWNER',numrows => 2);PL/SQL procedure successfully completed.select t1.operation,to_char(t1.START_TIME,'yyyy-mm-dd hh24:mi:ss'), (t1.end_time-t1.start_time) day(1) to second(0) as durationfrom dba_optstat_operations t1order by t1.start_time desc;--1528 去人该参数maximum PGA allocated 如果 远远大于 aggregate PGA target parameter ，那么初始化参数 pga_aggregate_target 值 也许不合适了select name ,value/1024/1024 ,unit from v$pgastat where name in ('aggregate PGA target parameter','maximum PGA allocated')--1559select * from v$sql_plan t1;select * from v$sql_plan_statistics t1;select * from v$sql_workarea t1;select * from v$sql_plan_statistics_all;--10053 eventsalter session set tracefile_identifier='10053';alter session set events '10053 trace name context forever,level 1';select * from test;alter session set events '10053 trace name context off';--10132 eventsalter session set tracefile_identifier='10132';alter session set events '10132 trace name context forever,level 1';select * from test;alter session set events '10132 trace name context off';--1609 历史执行计划select * from table(dbms_xplan.display_awr('sql_id'))--1639为了使用 display_cursor函数，调用者必须对以下几张动态性能视图有 select权限： V$SESSION 、v$sql、v$sql_plan、v$sql_plan_statistics_all20170601 select * from scott.emp where deptno=10 order by ename;select * from table(dbms_xplan.display(null,null,'advanced'));select * from table(dbms_xplan.display(null,null,'advanced +iostats last'));/*1 Plan hash value: 1503919072 3 ---------------------------------------------------------------------------4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |5 ---------------------------------------------------------------------------6 | 0 | SELECT STATEMENT | | 5 | 190 | 4 (25)| 00:00:01 |7 | 1 | SORT ORDER BY | | 5 | 190 | 4 (25)| 00:00:01 |8 |* 2 | TABLE ACCESS FULL| EMP | 5 | 190 | 3 (0)| 00:00:01 |9 ---------------------------------------------------------------------------10 11 Predicate Information (identified by operation id):12 ---------------------------------------------------13 14 2 - filter("DEPTNO"=10)*/1030包含执行计划的表中的字段基本字段字段 描述id 每一个操作的标识符。如果数字前面带有星号，意味着该行有谓词信息operation 该行的操作，也叫 row sourcename 操作的对象查询优化器评估类rows(e-rows) 评估中操作返回的记录总数bytes(e-bytes) 评估中操作返回的字节数tempspc 评估中操作使用的临时空间大小cost(%cpu) 评估中操作的开销。括号中是开销的百分比。注意该值是通过执行计划计算过来的。即：福操作的开销包含子操作的开销time 评估中执行操作需要的时间分区类pstart 访问的第一个分区。如果解析时不知道是哪个分区就设为key、key(i) 、key(mc)、 key(or)、 key(sq)pstop 访问的最后一个分区。如果解析时不知道是哪个分区就设为key、key(i) 、key(mc)、 key(or)、 key(sq)并行和分布式处理类starts 指定操作执行的次数a-rows 指定操作返回的真实记录数a-time 指定操作执行的真实时间io统计类buffers 操作期间进行的逻辑读数量reads 操作期间进行的物理读数量writes 操作期间进行的物理写数量内存使用统计类omem 最有执行所需内存的评估值imem 一次性通过(one-pass)执行所需内存的评估值used-mem 最后一次操作时使用的内存量user-tmp 最后一次操作时使用的临时空间大小select * from scott.emp where rownum <5;select * from table(dbms_xplan.display(null,null,'advanced +allstats'));select * from table(dbms_xplan.display(null,null,'advanced +iostats'));select * from table(dbms_xplan.display(null,null,'advanced +last'));select * from table(dbms_xplan.display(null,null,'advanced +memstats'));select * from table(dbms_xplan.display(null,null,'advanced +runstats_last'));select * from (select * from scott.emp order by sal desc) where rownum <6;select * from table(dbms_xplan.display(null,null,'advanced'));select * from scott.emp,scott.dept where emp.deptno=dept.deptno and emp.comm is null and dept.dname!='SALeES';select t1.INDEX_NAME,t1.blevel,t1.LEAF_BLOCKS,t1.DISTINCT_KEYS,t1.NUM_ROWS,t1.CLUSTERING_FACTOR,t1.AVG_LEAF_BLOCKS_PER_KEY,t1.AVG_DATA_BLOCKS_PER_KEYfrom user_ind_statistics t1where t1.TABLE_NAME='TEST'; SELECT index_name, clustering_factor(user, table_name, column_name) AS clust_factor FROM user_ind_columns WHERE table_name = 'T';select * from sys.aux_stats_history; --SQL> REM Display information about execution statistics (columns Starts, E-Rows,--SQL> REM A-Rows and A-Time) and I/O operations (columns Buffers, Reads and Writes)--1. 确认该参数statistics_level 的值SQL> show parameter statistics_level;NAME TYPE VALUE------------------------------------ ----------- ------------------------------statistics_level string TYPICAL --该参数statistics_level默认值 typical --2. 修改参数值为 allSQL> alter session set statistics_level=all;Session altered.--3. 再次确认该参数值SQL> show parameter statistics_level;NAME TYPE VALUE------------------------------------ ----------- ------------------------------statistics_level string ALL/*执行计划中 Starts 、 E-Rows 、A-Rows 、 A-Time 、Buffers 、 Reads 、 Writes 等参数只在 statistics_level=all的情况下才可以看到值。*/--以下开始测试select * from data_skew;以下分为 case1、case2、case3、case4、case5--case 1 iostats lastSELECT * FROM table(dbms_xplan.display_cursor('4y4a4kxntcmsf',0,'iostats last'));/*1 SQL_ID 6rwckvk4d8xjb, child number 02 -------------------------------------3 SELECT \*+ gather_plan_statistics *\ count(pad) FROM (SELECT \* 4 parallel(t,2) *\ rownum AS rn, pad FROM t ORDER BY n) WHERE 5 rn = 16 7 Plan hash value: 20565427178 9 -----------------------------------------------------------------------------------------------------------10 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes |11 -----------------------------------------------------------------------------------------------------------12 | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 153 | 126 | 126 |13 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 153 | 126 | 126 |14 |* 2 | VIEW | | 1 | 894 | 1 |00:00:00.03 | 153 | 126 | 126 |15 | 3 | SORT ORDER BY | | 1 | 894 | 1000 |00:00:00.04 | 153 | 126 | 126 |16 | 4 | COUNT | | 1 | | 1000 |00:00:00.01 | 150 | 0 | 0 |17 | 5 | PARTITION HASH ALL| | 1 | 894 | 1000 |00:00:00.01 | 150 | 0 | 0 |18 | 6 | TABLE ACCESS FULL| T | 2 | 894 | 1000 |00:00:00.01 | 150 | 0 | 0 |19 -----------------------------------------------------------------------------------------------------------20 21 Predicate Information (identified by operation id):22 ---------------------------------------------------23 24 2 - filter("RN"=1)25 26 Note27 -----28 - dynamic sampling used for this statement (level=2)29 */--case 2 advancedSELECT * FROM table(dbms_xplan.display_cursor('cahbf3stcpj2g',0,'advanced'));/*1 SQL_ID 6rwckvk4d8xjb, child number 02 -------------------------------------3 SELECT \*+ gather_plan_statistics *\ count(pad) FROM (SELECT \* 4 parallel(t,2) *\ rownum AS rn, pad FROM t ORDER BY n) WHERE 5 rn = 16 7 Plan hash value: 20565427178 9 -------------------------------------------------------------------------------------------------------10 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |11 -------------------------------------------------------------------------------------------------------12 | 0 | SELECT STATEMENT | | | | | 139 (100)| | | |13 | 1 | SORT AGGREGATE | | 1 | 515 | | | | | |14 |* 2 | VIEW | | 894 | 449K| | 139 (2)| 00:00:01 | | |15 | 3 | SORT ORDER BY | | 894 | 449K| 488K| 139 (2)| 00:00:01 | | |16 | 4 | COUNT | | | | | | | | |17 | 5 | PARTITION HASH ALL| | 894 | 449K| | 19 (0)| 00:00:01 | 1 | 2 |18 | 6 | TABLE ACCESS FULL| T | 894 | 449K| | 19 (0)| 00:00:01 | 1 | 2 |19 -------------------------------------------------------------------------------------------------------20 21 Query Block Name / Object Alias (identified by operation id):22 -------------------------------------------------------------23 24 1 - SEL$1*/--case 3 typicalSELECT * FROM table(dbms_xplan.display_cursor('cahbf3stcpj2g',0,'typical'));/*1 SQL_ID 6rwckvk4d8xjb, child number 02 -------------------------------------3 SELECT \*+ gather_plan_statistics *\ count(pad) FROM (SELECT \* 4 parallel(t,2) *\ rownum AS rn, pad FROM t ORDER BY n) WHERE 5 rn = 16 7 Plan hash value: 20565427178 9 -------------------------------------------------------------------------------------------------------10 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |11 -------------------------------------------------------------------------------------------------------12 | 0 | SELECT STATEMENT | | | | | 139 (100)| | | |13 | 1 | SORT AGGREGATE | | 1 | 515 | | | | | |14 |* 2 | VIEW | | 894 | 449K| | 139 (2)| 00:00:01 | | |15 | 3 | SORT ORDER BY | | 894 | 449K| 488K| 139 (2)| 00:00:01 | | |16 | 4 | COUNT | | | | | | | | |17 | 5 | PARTITION HASH ALL| | 894 | 449K| | 19 (0)| 00:00:01 | 1 | 2 |18 | 6 | TABLE ACCESS FULL| T | 894 | 449K| | 19 (0)| 00:00:01 | 1 | 2 |19 -------------------------------------------------------------------------------------------------------20 21 Predicate Information (identified by operation id):22 ---------------------------------------------------23 24 2 - filter("RN"=1)25 26 Note27 -----28 - dynamic sampling used for this statement (level=2)29 */--case 4 allSELECT * FROM table(dbms_xplan.display_cursor('cahbf3stcpj2g',0,'all'));/*1 SQL_ID 6rwckvk4d8xjb, child number 02 -------------------------------------3 SELECT \*+ gather_plan_statistics *\ count(pad) FROM (SELECT \* 4 parallel(t,2) *\ rownum AS rn, pad FROM t ORDER BY n) WHERE 5 rn = 16 7 Plan hash value: 20565427178 9 -------------------------------------------------------------------------------------------------------10 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |11 -------------------------------------------------------------------------------------------------------12 | 0 | SELECT STATEMENT | | | | | 139 (100)| | | |13 | 1 | SORT AGGREGATE | | 1 | 515 | | | | | |14 |* 2 | VIEW | | 894 | 449K| | 139 (2)| 00:00:01 | | |15 | 3 | SORT ORDER BY | | 894 | 449K| 488K| 139 (2)| 00:00:01 | | |16 | 4 | COUNT | | | | | | | | |17 | 5 | PARTITION HASH ALL| | 894 | 449K| | 19 (0)| 00:00:01 | 1 | 2 |18 | 6 | TABLE ACCESS FULL| T | 894 | 449K| | 19 (0)| 00:00:01 | 1 | 2 |19 -------------------------------------------------------------------------------------------------------20 21 Query Block Name / Object Alias (identified by operation id):22 -------------------------------------------------------------23 24 1 - SEL$125 2 - SEL$2 / from$_subquery$_001@SEL$126 3 - SEL$227 6 - SEL$2 / T@SEL$228 29 Predicate Information (identified by operation id):30 ---------------------------------------------------31 32 2 - filter("RN"=1)33 34 Column Projection Information (identified by operation id):35 -----------------------------------------------------------36 37 1 - (#keys=0) COUNT("PAD")[22]38 2 - "RN"[NUMBER,22], "PAD"[VARCHAR2,1000]39 3 - (#keys=1) "N"[NUMBER,22], ROWNUM[22], "PAD"[VARCHAR2,1000]40 4 - "N"[NUMBER,22], "PAD"[VARCHAR2,1000], ROWNUM[4]41 5 - "N"[NUMBER,22], "PAD"[VARCHAR2,1000]42 6 - "N"[NUMBER,22], "PAD"[VARCHAR2,1000]43 44 Note45 -----46 - dynamic sampling used for this statement (level=2)47 */--case 5 memstats lastSELECT * FROM table(dbms_xplan.display_cursor('cahbf3stcpj2g',0,'memstats last'));/*1 SQL_ID 6rwckvk4d8xjb, child number 02 -------------------------------------3 SELECT \*+ gather_plan_statistics *\ count(pad) FROM (SELECT \* 4 parallel(t,2) *\ rownum AS rn, pad FROM t ORDER BY n) WHERE 5 rn = 16 7 Plan hash value: 20565427178 9 --------------------------------------------------------------------------------------------------------------------10 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem | Used-Tmp|11 --------------------------------------------------------------------------------------------------------------------12 | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | | | | |13 | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | | | | |14 |* 2 | VIEW | | 1 | 894 | 1 |00:00:00.03 | | | | |15 | 3 | SORT ORDER BY | | 1 | 894 | 1000 |00:00:00.04 | 1134K| 560K|74752 (1)| 1024 |16 | 4 | COUNT | | 1 | | 1000 |00:00:00.01 | | | | |17 | 5 | PARTITION HASH ALL| | 1 | 894 | 1000 |00:00:00.01 | | | | |18 | 6 | TABLE ACCESS FULL| T | 2 | 894 | 1000 |00:00:00.01 | | | | |19 --------------------------------------------------------------------------------------------------------------------20 21 Predicate Information (identified by operation id):22 ---------------------------------------------------23 24 2 - filter("RN"=1)25 26 Note27 -----28 - dynamic sampling used for this statement (level=2)29 *//*添加 hint /*+ gather_plan_statistics */SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last')); 以 runstats_last的方式查看执行计划 同样可以返回 execution statistics (columns Starts, E-Rows,SQL> REM A-Rows and A-Time) and I/O operations (columns Buffers, Reads and Writes)*/select /*+ gather_plan_statistics */ object_name from test0605;select * from table(dbms_xplan.display_cursor(null,null,'advanced'));select * from test0605;
转载于:https://www.cnblogs.com/iyoume2008/p/7041282.html
