oracle性能调优学习0621

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 id = parent_id
 START WITH id = 0 ;

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('bdfmh55d9vy9y',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

--------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   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 id):
---------------------------------------------------

   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

----------------------------------------------------------
| Id  | Operation       | Name   |
----------------------------------------------------------
|   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 id):
---------------------------------------------------

   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

-------------------------------------------------------------------------
| Id  | Operation      | Name    | Cost (%CPU)|
-------------------------------------------------------------------------
|   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> alter session set "_use_nosegment_indexes"=TRUE;

Session altered.

SQL> create index sh.sales_vi1 on sh.sales(quantity_sold) nosegment;

Index created.

跟踪oracle执行：

SQL> alter session set sql_trace=true;

Session altered.

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> alter session set tracefile_identifier=GUY;

Session altered.

[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');