Danisment - isn't it always "alter system flush shard_pool"? I thought so...
Danisment Gazi Unal (Unal Bilisim) wrote: >Hello, > >I did 2 tests for PL/SQL and SQL statements. This is the test for >SQL. > >Here are the steps: > >SQL > alter session flush shared_pool; >SQL > alter session set sql_trace=true; >SQL > insert into test select * from test; >SQL > alter session set sql_trace=false; > > >FROM DICTIONARY: > >SVRMGR> select >SQL_TEXT,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,COMMAND_TYPE from v$sql >where sql_text like 'insert into test select * from test%'; > >SQL_TEXT DISK_READS BUFFER_GET ROWS_PROCE >COMMAND_TY >------------------------------------- ---------- ---------- ---------- >---------- >insert into test select * from test 345 1014 >8192 2 >1 row selected. > > >FROM RAW TRACE FILE: > >PARSING IN CURSOR #1 len=36 dep=0 uid=5 oct=2 lid=5 tim=2795932206 >hv=895761708 ad='5083d50c' >insert into test select * from test >END OF STMT >PARSE #1:c=7,e=28,p=17,cr=42,cu=2,mis=1,r=0,dep=0,og=4,tim=2795932206 >. >other recursive statements. >. >. near end of file >EXEC >#1:c=28,e=258,p=328,cr=597,cu=373,mis=0,r=8192,dep=0,og=4,tim=2795932464 > > > >BUFFER GETS IN RAW TRACE FILE: >cr: 597 + 42 = 639 >cu: 373 + 2 = 375 > >Buffer gets = 639 + 375 = 1014, which is same as v$sql.BUFFER_GETS > > >DISK_READS IN RAW TRACE FILE: > >p: 17 + 328 = 345, which is same as v$sql.DISK_READS. > >According to these test, results in dictionary and raw trace files are >same. But tkprof formats as below: > > >insert into test select * from test > > >call count cpu elapsed disk query >current rows >------- ------ -------- ---------- ---------- ---------- ---------- >---------- >Parse 1 0.00 0.02 17 42 >1 0 >Execute 1 0.12 1.86 91 126 >356 8192 >Fetch 0 0.00 0.00 0 0 >0 0 >------- ------ -------- ---------- ---------- ---------- ---------- >---------- >total 2 0.12 1.88 108 168 >357 8192 > > >DISK_READS = 108 >BUFFER GETS = 168 + 357 = 525 > >Question: > >Which one is correct ? Dictionary/raw trace file or tkprof results ? > >My comment: > >I guess, tkprof substructs child recursive statements from parent user >statement ? Why ? This is not a PL/SQL statement ? So, statistics are >already not included in parent statement ? I guess statistics in raw >trace files are inclusive statistics which include statistics of their >child statements according to call orders of kernel calls. But is this >expected behavior. > >Thanks in advance... > >-- >Danisment Gazi Unal >http://www.unal-bilisim.com > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?ISO-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
