Saturday, January 30, 2010

How reliable SQL_TRUE it is ?

nix process pid: 214, image: oracle@odsdev01 (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2010-01-18 17:47:42.549
*** SESSION ID:(274.21788) 2010-01-18 17:47:42.549
...

*** 2010-01-18 20:44:17.998
FETCH #1:c=0,e=176,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408287274
FETCH #1:c=0,e=157,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408295527
FETCH #1:c=0,e=188,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408303670
FETCH #1:c=0,e=307,p=1,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408311955
FETCH #1:c=0,e=185,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408318782
FETCH #1:c=0,e=188,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=33407408326769

The sql running for few hours with "alter session set sql_trace=true ", but from tkprof , it only records 41 minutes, omitting the rest timing -- returning 50 millions of records to client (wait event is "SQL*Net message from client" )

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.07 0.07 2 2 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 1650667 1103.09 2444.07 640737 304709 151 24759989
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1650672 1103.16 2444.14 640739 304711 151 24759989

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Maybe there is trace level different in between sql_trace=true and event 10046 , caused the idle event is not shown in the trace info. ?

No comments:

Post a Comment