_insert DATE
,cursor_num NUMBER
,depth NUMBER
,user_id NUMBER
,parse_cnt NUMBER
,parse_cpu NUMBER
,parse_elap NUMBER
,parse_disk NUMBER
,parse_query NUMBER
,parse_current NUMBER
,parse_miss NUMBER
,exe_count NUMBER
,exe_cpu NUMBER
,exe_elap NUMBER
,exe_disk NUMBER
,exe_query NUMBER
,exe_current NUMBER
,exe_miss NUMBER
,exe_rows NUMBER
,fetch_count NUMBER
,fetch_cpu NUMBER
,fetch_elap NUMBER
,fetch_disk NUMBER
,fetch_query NUMBER
,fetch_current NUMBER
,fetch_rows NUMBER
,ticks NUMBER
,sql_statement LONG
);
set sqlterminator off
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 0, 83, 1, 1000, 1321, 0, 0, 0, 1
, 1, 0, 40, 140733193388032, 0, 206158430208, 140733193388032, 140733193388032
, 2, 0, 20, 0, 0, 0, 1, 625
, 'select user from dual
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 83, 1, 0, 658, 0, 0, 0, 1
, 1, 2000, 1288, 140733193388032, 0, 0, 1, 0
, 1, 1000, 1824, 0, 69, 0, 1, 18354695
, 'SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(''parallel_execution_enabled'', ''false'') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TAB1") FULL("TAB1") NO_PARALLEL_INDEX("TAB1") */ :"SYS_B_2" AS C1, CASE WHEN "TAB1"."OBJECT_ID"<:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "SCOTT"."TAB1" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "TAB1") SAMPLESUB
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 83, 1, 0, 624, 0, 0, 0, 1
, 1, 2000, 1436, 140733193388032, 0, 0, 1, 0
, 1, 0, 73, 0, 2, 0, 1, 2487
, 'SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param(''parallel_execution_enabled'', ''false'') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), NVL(SUM(C3),:"SYS_B_2") FROM (SELECT /*+ NO_PARALLEL("TAB1") INDEX("TAB1" IND1) NO_PARALLEL_INDEX("TAB1") */ :"SYS_B_3" AS C1, :"SYS_B_4" AS C2, :"SYS_B_5" AS C3 FROM "SCOTT"."TAB1" "TAB1" WHERE "TAB1"."OBJECT_ID"<:"SYS_B_6" AND ROWNUM <= :"SYS_B_7") SAMPLESUB
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 0, 83, 1, 3998, 2275, 0, 2, 0, 1
, 1, 0, 25, 140733193388032, 0, 0, 0, 0
, 3, 0, 83, 0, 7, 0, 18, 1697
, 'select object_id ,object_name from tab1 where object_id < 20
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 2, 1, 83, 1, 1000, 614, 0, 0, 0, 1
, 1, 1000, 1204, 140733193388032, 0, 0, 1, 0
, 1, 1000, 1447, 0, 69, 0, 1, 8979687
, 'SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(''parallel_execution_enabled'', ''false'') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("TAB1") FULL("TAB1") NO_PARALLEL_INDEX("TAB1") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "SCOTT"."TAB1" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "TAB1") SAMPLESUB
')
/
INSERT INTO tkprof_table VALUES
(
SYSDATE, 1, 0, 83, 1, 2000, 1703, 0, 2, 0, 1
, 1, 0, 35, 140733193388032, 0, 0, 0, 0
, 2, 12998, 12565, 159, 169, 0, 1, 13861
, 'select count(*) from tab1
')
/
set sqlterminator on
[oracle@oadata trace]$ cat record.sql
select user from dual ;
select object_id ,object_name from tab1 where object_id < 20 ;
select count(*) from tab1 ;
[oracle@oadata trace]$
使用tkprof和sql trace需要知识和经验的结合，现摘录官方文档中的部分内容，对你我或许有一定的启示作用：
Avoiding the Argument Trap
If you are not aware of the values being bound at run time, then it is possible to fall into the argument trap. EXPLAIN PLAN cannot determine the type of a bind variable from the text of SQL statements, and it always assumes that the type is varchar. If the bind variable is actually a number or a date, then TKPROF can cause implicit data conversions, which can cause inefficient plans to be executed. To avoid this situation, experiment with different data types in the query.
To avoid this problem, perform the conversion yourself.
Avoiding the Read Consistency Trap
The next example illustrates the read consistency trap. Without knowing that an uncommitted transaction had made a series of updates to the NAME column, it is very difficult to see why so many block visits would be incurred.
Cases like this are not normally repeatable: if the process were run again, it is unlikely that another transaction would interact with it in the same way.
SELECT name_id
FROM cq_names
WHERE name = 'FLOOR';
call count cpu elapsed disk query current rows
---- ----- --- ------- ---- ----- ------- ----
Parse 1 0.10 0.18 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.11 0.21 2 101 0 1
Misses in library cache during parse: 1
Parsing user id: 01 (USER1)
Rows Execution Plan
---- --------- ----
0 SELECT STATEMENT
1 TABLE ACCESS (BY ROWID) OF 'CQ_NAMES'
2 INDEX (RANGE SCAN) OF 'CQ_NAMES_NAME' (NON_UNIQUE)
Avoiding the Schema Trap
This example shows an extreme (and thus easily detected) example of the schema trap. At first, it is difficult to see why such an apparently straightforward indexed query needs to look at so many database blocks, or why it should access any blocks at all in current mode.
SELECT name_id
FROM cq_names
WHERE name = 'FLOOR';
call count cpu elapsed disk query current rows
-------- ------- -------- --------- ------- ------ ------- ----
Parse 1 0.06 0.10 0 0 0 0
Execute 1 0.02 0.02 0 0 0 0
Fetch 1 0.23 0.30 31 31 3 1
Misses in library cache during parse: 0
Parsing user id: 02 |