oracle性能调优学习0621
1.PLAN_tablecolumn 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
2dbms_session.session_trace_enable(waits=>true,binds=>false,plan_stat=>'all_executions');
3end;
4/
PL/SQL procedure successfully completed.
识别跟踪文件：
SQL>>
Session> $ 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');
页:
[1]