Re: Statspack 9.2

From: delcenserie christophe <tof_at_debian.linux>
Date: Thu, 24 Aug 2006 20:29:06 +0200
Message-ID: <pan.2006.08.24.18.29.06.186473@debian.linux>

Le Thu, 24 Aug 2006 20:00:17 +0200, delcenserie christophe a écrit :

> Le Thu, 24 Aug 2006 08:36:15 +0000, Richard Foote a écrit :
> 

>> "delcenserie christophe" <tof_at_debian.linux> wrote in message
>> news:pan.2006.08.24.05.00.17.443301_at_debian.linux...

>>> Le Wed, 23 Aug 2006 21:35:48 +0000, Richard Foote a écrit :
>>>
>>>> "delcenserie christophe" <tof_at_debian.linux> wrote in message
>>>> news:pan.2006.08.23.17.39.08.742119_at_debian.linux...
>>>>> Le Wed, 23 Aug 2006 02:45:46 -0700, rajesh_choprauk a écrit :
>>>>>
>>>>>> Does anyone know if it is possible to exclude SYS/Statspack SQL from
>>>>>> statspack report?
>>>>>
>>>>> show tkprof sys=no
>>>>>
>>>>
>>>> Hi Christophe
>>>>
>>>> You know how to tkprof a statspack report !!
>>>>
>>>> Hey that's really cool, imagine having a nicely formatted statspack 
>>>> report
>>>> !!
>>>>
>>>> Care to share to us all how you perform this technological feat ?
>>>>
>>>> Cheers
>>>>
>>>> Richard
>>>
>>> Richard
>>>
>>> No whith statspack.
>>> alter session set sql_trace=true or package
>>> DBMS.SYSTEM.SET_SQL_TRACE_IN_SESSION
>>>

>>
>> Hi Christophe
>>
>> So it doesn't really help the OP much does it, since he specifically asked
>> about eliminating recursive SQL from statspack (thread title rather gives it
>> away).
>>
>> I would also recommend DBMS_SUPPORT (or DBMS_MONITOR with 10g) rather than
>> the DBMS_SYSTEM for tracing purposes. See metalink notes 377204.1 and
>> 62294.1 for details.
>>
>> Cheers
>>
>> Richard

> 
> I did not know. I will look at that. Thank you for your help.
> 
> Christophe

I test it now, and i see the diffence between.

Test with alter session set sql_trace = true

i have juste take select user from dual;

I see it:
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from
 objauth$ where obj#=:1 and col# is not null group by privilege#, col#,   grantee# order by col#, grantee#

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0      0 

... select
grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from  objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by   grantee#
...
select user
from
 dual

i try it with execute sys.dbms_support.start_trace (true,true);

select user
from
 dual

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        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0      

    1

AND 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        0.01          0.01
********************************************************************************

i try it with exec

.session_trace_enable(142);

SQL> select sid, serial#, username
from v$session; 2

       SID SERIAL# USERNAME

---------- ---------- ------------------------------
       142       7071 SYSTEM
       144          1
       145          1

...

SQL> exec dbms_monitor.session_trace_enable(142);

Procédure PL/SQL terminée avec succès.

SQL> select user from dual;

USER

---

SYSTEM SQL> exec dbms_monitor.session_trace_disable;

Procédure PL/SQL terminée avec succès.

SQL> I have the same result as dbms_monitor

Thanks Richard. Received on Thu Aug 24 2006 - 13:29:06 CDT