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