针对某个特定的SQL语句开启10046跟踪

1. 介绍

在11g中,Oracle可以针对某个SQLID开启SQL跟踪,这对于想要跟踪SQL在不同会话执行情况,比较有用。

2.跟踪样例

检查要跟踪的SQL的SQLID

SQL> alter system flush shared_pool;

System altered.
SQL> var  B1 number;
SQL> exec :B1 := 371;

PL/SQL procedure successfully completed.

SQL> select count(1) from t1 where object_id = :B1;

  COUNT(1)
----------
         1
SQL> set linesize 180
SQL> column sql_text format a60
SQL> select sql_text, sql_id, child_number, executions, s.PLAN_HASH_VALUE
  2    from v$sql s
  3   where s.SQL_TEXT like 'select count(1) from t1%'
  4     and s.SQL_TEXT not like '%v$sql%';

SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE
------------------------------------------------------------ ------------- ------------ ---------- ---------------
select count(1) from t1 where object_id = :B1                a94d6c2m6js7c            0          1      3724264953

SQL> alter system flush shared_pool;

System altered.

开启SQL跟踪

SQL> alter system set events 'sql_trace [sql: a94d6c2m6js7c] level 12';

在不同的会话执行相关SQL

SQL> var  B1 number;
SQL> exec :B1 := 371;

PL/SQL procedure successfully completed.

SQL> select count(1) from t1 where object_id = :B1;

  COUNT(1)
----------
         1

关闭SQL跟踪

SQL> alter system set events 'sql_trace [sql: a94d6c2m6js7c] off';

3. 分析SQL

对SQL的跟踪,每次执行SQL都会产生到trace文件中,所以如果多个会话在执行该SQL,SQL的跟踪情况会散落到不同的trc文件中。
如果SQL某次执行问题比较大,可以采用aggregate=no,对SQL的每次执行进行统计,而不是SQL的整个执行情况进行统计

$ tkprof t12c_ora_9498.trc t12c_ora_9498.trc.rpt aggregate=no sys=no

TKPROF: Release 11.2.0.4.0 - Development on Mon Apr 10 08:27:52 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

执行情况1:

SQL ID: a94d6c2m6js7c Plan Hash: 3724264953

select count(1)
from
 t1 where object_id = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.03          0          0          0           0
Fetch        2      0.01       0.02          0        992          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.02       0.05          0        992          0           1

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=992 pr=0 pw=0 time=20126 us)
         1          1          1   TABLE ACCESS FULL T1 (cr=992 pr=0 pw=0 time=20103 us cost=277 size=143 card=11)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

执行情况2:

SQL ID: a94d6c2m6js7c Plan Hash: 3427986586

select count(1)
from
 t1 where object_id = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.02       0.03          1         76          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.02       0.03          1         78          0           1

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=2 pr=0 pw=0 time=39 us)
         1          1          1   INDEX RANGE SCAN IDX_OBJ_ID (cr=2 pr=0 pw=0 time=23 us cost=1 size=13 card=1)(object id 88068)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00

关于紫砂壶

感悟技术人生
此条目发表在工具分类目录,贴了标签。将固定链接加入收藏夹。