跟踪 优化 SQL

2. 启动跟踪功能
SQL> exec sys.dbms_system.set_sql_trace_in_session(141,982, true);

3. 运行141,982对应session下的sql语句

4. 关闭跟踪功能
SQL> exec sys.dbms_system.set_sql_trace_in_session(141,982,false);

5. 格式化跟踪数据
> tkprof dgtest_ora_3420.trc dgtest_ora_3420.out sys=no explain=hr/hr

> tkprof dgtest_ora_3420.trc dgtest_ora_3420.out sys=no explain=hr/hr SORT=(EXECPU,FCHCPU)

============主体部分===================
update t set owner = 'OOO'
WHERE
OWNER = 'SYS'

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.02          0          4          0           0
Execute      1      0.42       1.85        682      13417      24725       23236
Fetch        0      0.00       0.00          0          0          0           0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        2      0.42       1.88        682      13421      24725       23236

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56 (HR)

Rows     Row Source Operation
------- ---------------------------------------------------
0 UPDATE T (cr=13461 pr=680 pw=0 time=1816660 us)
47174   TABLE ACCESS FULL T (cr=13403 pr=680 pw=0 time=94492 us)

Rows     Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT   MODE: ALL_ROWS
0   UPDATE OF 'T'
47174    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

解释：
1) sql语句的统计信息
我们把select语句的执行过程分成3个阶段(分析