[bbk2301] 第44集 - Chapter 11-SQL Statement Tuning(03)
Generate the Execution Plan
- Can be used without tracing
- Needs the plan_table table utlxplan.sql
- Create the explain plan:
SQL> explain plan for 2 select * from my_all_objects; Explained.
Query plan_table Table
Query plan_table to display the execution plans:
- Query plan_table directly.
- Use script utlxpls.sql(hide Paralle Query information)
- Use script utlxplp.sql(show Parallel Query information)
- Use the dbms_xplan package.(recommend method)
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 4167016233 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27 | 567 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 567 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------- 8 rows selected.
execution plan解读:先执行里面的TABLE ACCESS FULL,在执行外面的SELECT STATEMENT;看缩进,也就是说先执行缩进里面的,在执行往外的.如果同级别的,先执行上面的,在执行下面的.
SQL> @?/rdbms/admin/utlxpls.sql PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 4167016233 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27 | 567 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 567 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------- 8 rows selected.
注意:在产生一个新的plan之前,最好把表中存在的数据给清空. (后期版本的oracle可能自动清空之前的plan)
推荐书籍<<Effective Oracle by Design>>
Using SQL Trace & TKPROF
To use SQL trace and TKPROF
- Set the initialization parameters.
SQL>ALTER SESSION SET sql_trace = True;
- Run the application
SQL>ALTER SESSION SET sql_trace = False;
- Format the trace file with TKPROF
- Interpret the output.
一般情况下,只是session级别,不会是system级别;如果是system级别,会产生大量的文件,造成系统负担.
Enable & Disable SQL Trace
At he instance level:SQL_TRACE = {True|False}
At the session level:
SQL>ALTER SESSION SET SQL_TRACE = {True|False} SQL>EXECUTE dbms_session.set_sql_trace({True|False}); SQL>EXECUTE dbms_system.set_sql_trace_in_session(session_id,serial_id,{True|False})
Formatting the Trace File
$tkprof tracefile.trc output.txt [options]
TKPROF Statistics
- Count:Number of execution calls
- CPU:CPU seconds used
- Elapsed:Total elapsed time
- Disk:Physical reads
- Query:Logical reads for consistent read
- Current:Logical reads in current mode
- Rows:Rows processed
SQL*Plus Autotrace
- Create the plan_table table.
- Create and grant the plustrace role.
SQL>@ORACLE_HOME/sqlplus/admin/plustrce.sql SQL>GRANT plustrace TO scott;
- Autotract syntax:
SET AUTOTRACE [Off|On|Traceonly] [Explain|statistics]
Summary
In this lesson,you should have learned how to:
- Describe how the optimizer is used.
- Describe how hints are used
- Explain the concept of plan stability
- Explain the use of stored outlines
- Use SQL Trace and TKPROF