oracle效能調優學習0621

標籤：oracle

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(‘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

--------------------------------------------------------------------------------------
| 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.

[[email protected] 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‘);

oracle效能調優學習0621