oracle性能调优学习0621

Acfe

　　1.PLAN_table
　　column query_plan format a55
　　column cardinality format 99999
　　column cost format 99999
　　delete from plan_table;
　　set lines 100
　　set pages 100
　　set echo on
　　EXPLAIN PLAN FOR
　　SELECT *
　　FROM hr.employees JOIN hr.departments USING (department_id);
　　SELECT RTRIM (LPAD (' ', 2 * LEVEL) ||
　　RTRIM (operation) || ' ' ||
　　RTRIM (options) || ' ' ||
　　object_name) query_plan,
　　cost, cardinality
　　FROM plan_table

　　CONNECT BY PRIOR>
　　START WITH>　　SELECT * FROM TABLE(dbms_xplan.display());
　　2.查询总消耗时间最多的前10条sql语句：
　　SELECT sql_id, child_number, sql_text, elapsed_time
　　FROM (SELECT sql_id,
　　child_number,
　　sql_text,
　　elapsed_time,
　　cpu_time,
　　disk_reads,
　　rank() over(ORDER BY elapsed_time DESC) AS elapsed_rank
　　FROM v$sql)
　　WHERE elapsed_rank < 10;
　　通过sql_id得到执行计划：SQL>  select * from table(dbms_xplan.display_cursor('bdfmh45d9vy9y',0,'TYPICAL'));
　　DBMS_XPLAN.display函数展示了PLAN_TABLE中的执行计划，而DISPLAY_CURSOR则展示了在v$sql_plan中缓存的执行计划的信息。
　　explain plan for SELECT department_name, last_name, job_title
　　FROM hr.employees
　　JOIN hr.departments
　　USING (department_id)
　　JOIN hr.jobs
　　USING (job_id)
　　7   ORDER BY department_name, job_title;
　　Explained.
　　SQL> select * from table(dbms_xplan.display(null,null,'TYPICAL -BYTES'));
　　PLAN_TABLE_OUTPUT
　　----------------------------------------------------------------------------------------------------
　　Plan hash value: 3301068746
　　--------------------------------------------------------------------------------------

　　|>　　--------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT        |      |  106 |   10  (20)| 00:00:01 |
　　|   1 |  SORT ORDER BY         |      |  106 |   10  (20)| 00:00:01 |
　　|*  2 |   HASH JOIN         |      |  106 |    9  (12)| 00:00:01 |
　　|   3 |    MERGE JOIN         |      |  106 |    6  (17)| 00:00:01 |
　　|   4 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |   27 |    2   (0)| 00:00:01 |
　　|   5 |      INDEX FULL SCAN        | DEPT_ID_PK  |   27 |    1   (0)| 00:00:01 |
　　|*  6 |     SORT JOIN         |      |  107 |    4  (25)| 00:00:01 |
　　|   7 |      TABLE ACCESS FULL        | EMPLOYEES   |  107 |    3   (0)| 00:00:01 |
　　|   8 |    TABLE ACCESS FULL        | JOBS      |   20 |    3   (0)| 00:00:01 |
　　--------------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
　　6 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
　　filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
　　22 rows selected.
　　SQL> explain plan for select department_name,last_name from hr.employees join hr.departments using(department_id);
　　Explained.
　　SQL> select * from table(dbms_xplan.display(null,null,'BASIC +PREDICATE'));
　　PLAN_TABLE_OUTPUT
　　----------------------------------------------------------------------------------------------------
　　Plan hash value: 1473400139
　　----------------------------------------------------------

　　|>　　----------------------------------------------------------
　　|   0 | SELECT STATEMENT      |    |
　　|   1 |  MERGE JOIN       |    |
　　|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |
　　|   3 |    INDEX FULL SCAN      | DEPT_ID_PK  |
　　|*  4 |   SORT JOIN       |    |
　　|   5 |    VIEW        | index$_join$_001  |
　　|*  6 |     HASH JOIN       |    |
　　|   7 |      INDEX FAST FULL SCAN    | EMP_DEPARTMENT_IX |
　　|   8 |      INDEX FAST FULL SCAN    | EMP_NAME_IX  |
　　----------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
　　filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
　　6 - access(ROWID=ROWID)
　　22 rows selected.
　　虚拟索引：
　　虚拟索引是指没有创建对应的物理实体的索引。虚拟索引的目的，是在不必消耗时间，耗cpu，耗IO已经消耗大量的存储空间去实际创建索引的情况下，来判读一个索引是否能够对SQL优化起到作用。
　　SQL> explain plan for select * from sh.sales where quantity_sold>10000;
　　Explained.
　　SQL> select * from table(dbms_xplan.display(null,null,'BASIC +COST'));
　　PLAN_TABLE_OUTPUT
　　----------------------------------------------------------------------------------------------------
　　Plan hash value: 1744557519
　　-------------------------------------------------------------------------

　　|>　　-------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT     |     |  4   (0)|
　　|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| SALES    |  4   (0)|
　　|   2 |   INDEX RANGE SCAN     | INDEX_QU_SOLD |  3   (0)|
　　-------------------------------------------------------------------------
　　9 rows selected

　　SQL>>
　　Session>　　SQL> create index sh.sales_vi1 on sh.sales(quantity_sold) nosegment;
　　Index created.
　　跟踪oracle执行：

　　SQL>>
　　Session>　　SQL> begin
　　2  dbms_session.session_trace_enable(waits=>true,binds=>false,plan_stat=>'all_executions');
　　3  end;
　　4  /
　　PL/SQL procedure successfully completed.
　　识别跟踪文件：

　　SQL>>
　　Session>　　[oracle@node2 trace]$ ls -l *GUY*
　　-rw-r----- 1 oracle asmadmin 36056 Jun 21 14:54 MECBS2_ora_24731_GUY.trc
　　-rw-r----- 1 oracle asmadmin   328 Jun 21 14:54 MECBS2_ora_24731_GUY.trm
　　获取跟踪文件的状态：
　　SELECT s.sql_trace,
　　s.sql_trace_waits,
　　s.sql_trace_binds,
　　traceid,
　　tracefile
　　FROM v$session s
　　JOIN v$process p
　　ON (p.addr = s.paddr)
　　WHERE audsid = userenv('SESSIONID');