Re: How to find the parallelism degree of a query that has been finished?(on 10.2)

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Thu, 19 Feb 2009 10:21:49 -0800 (PST)
Message-ID: <384962.29188.qm_at_web52806.mail.re2.yahoo.com>


You could try this (not sure if it's accurate tho):

select parsing_schema_name, sql_id, max(px_servers_execs_total) px_cnt
from dba_hist_sqlstat
where px_servers_execs_total > 0
group by parsing_schema_name, sql_id

You could join it to dba_ash to get the time.

-Deepak




________________________________
From: H.TonguÁ YILMAZ <tonguc.yilmaz_at_gmail.com>
To: oracle-l_at_freelists.org
Sent: Thursday, February 19, 2009 11:19:18 AM
Subject: How to find the parallelism degree of a query that has been  finished?(on 10.2)

One of my DBA colleague asked me this question today but I couldn't come up with a dictionary view for this kind of a need. Since he was on 10.2 and has AWR as a cost option we worked on this SQL;

SELECT qc_session_id,
       sql_id,
       to_char(sample_time,
               'dd.mm.yyyy hh24:mi:ss'),
       COUNT(*) potantial_dop
  FROM dba_hist_active_sess_history
 GROUP BY qc_session_id,
          sql_id,
          to_char(sample_time,
                  'dd.mm.yyyy hh24:mi:ss')
 ORDER BY 4 DESC;

Depending on the type of SQL this query sould be equal to DOP+1 or DOP*2+1, assuming that you have the timing and the sql_id of the query you are after this may help but I wanted to have your comments and ideas on this topic.

Thank you.

-- 
Best Regards,
H.TonguÁ YILMAZ

http://vimeo.com/user983017/videos
http://tonguc.yilmaz.googlepages.com/



      
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 19 2009 - 12:21:49 CST

Original text of this message