Re: trace output of a paralell query

On 1 Sep 2004 07:24:44 -0700, hopehope_123_at_yahoo.com (utkanbir) wrote:

>Hi gurus ,
>
>Below is the trace output of a paralell query .
>In this output , i dont understand why the execute values are non zero
>although the statement is a select . I expect to see non zero values
>in fetch columns .
>
>select /*+full(t)*/
>count(*)
>from tuketici t
>
>call count cpu elapsed disk query current rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 1 0.00 0.00 0 0 0 0
>Execute 1 1.82 90.12 12552 16736 0 0
>Fetch 1 1.32 112.54 0 0 0 1
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 3 3.15 202.67 12552 16736 0 1
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 46
>
>Elapsed times include waiting on following events:
>Event waited on Times Max. Wait Total Waited
>---------------------------------------- Waited ----------
>------------
>rdbms ipc reply 2 0.00 0.00
>db file sequential read 11997 0.32 83.23
>global cache cr request 6291 0.09 1.40
>db file parallel read 84 0.28 4.14
>When i issue the same statement by using noparallel option , the trace
>file contains:
>select /*+noparallel(t) full(t)*/
>count(*)
>from tuketici t
>
>call count cpu elapsed disk query current rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 1 0.00 0.00 0 0 0 0
>Execute 1 0.00 0.00 0 0 0 0
>Fetch 1 18.44 333.56 195538 199807 0 1
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 3 18.44 333.56 195538 199807 0 1
>
>Misses in library cache during parse: 0
>Optimizer goal: CHOOSE
>Parsing user id: 46
>
>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 2 881.71 881.73
>global cache cr request 97958 0.09 2.87
>db file scattered read 12983 0.31 233.78
>db file sequential read 13437 0.08 80.43
>
>
>This time i can see nonzero fetch values.
>
>
>
>Kind Regards,
>hope

The query will be executed by a parallel query slave (ora_sid_p<n>), the original session acting as a coordinator only.

--
Sybrand Bakker, Senior Oracle DBA