Re: How to find the parallelism degree of a query that has been finished?(on 10.2)
From: H.Tonguç YILMAZ <tonguc.yilmaz_at_gmail.com>
Date: Fri, 20 Feb 2009 08:35:56 +0200
Message-ID: <f8e9eb670902192235t411154d4l67d0a56c0b15b2f6_at_mail.gmail.com>
Deepak, Nial thanks for the ideas.
SQL_ID cxk63fp44mwyt
select /*+ PARALLEL(abcdefg,32) */ id ...
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
5 WHERE px_servers_execs_total > 0 and sql_id = 'cxk63fp44mwyt' 6 GROUP BY parsing_schema_name,
7 sql_id;
6 FROM dba_hist_active_sess_history 7 WHERE sql_id = 'cxk63fp44mwyt'
8 AND to_char(sample_time,'dd.mm.yyyy hh24:mi:ss') = '13.02.2009 10:33:47'
9 GROUP BY qc_session_id,
So here the guy who ran this query insists that it took too long since he couldn't get the DOP he requested that morning, and he thinks this is DBA's fault since he configured the Resource Manager not appropriate.
> 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
> *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;
> 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 should return 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/
>
>
Date: Fri, 20 Feb 2009 08:35:56 +0200
Message-ID: <f8e9eb670902192235t411154d4l67d0a56c0b15b2f6_at_mail.gmail.com>
Deepak, Nial thanks for the ideas.
Let's look at the below example, this query is hinted for DOP 32 and it is a simple SELECT without any subquery, ORDER nor GROUP BY;
*SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('cxk63fp44mwyt'));
PLAN_TABLE_OUTPUT
SQL_ID cxk63fp44mwyt
select /*+ PARALLEL(abcdefg,32) */ id ...
Plan hash value: 3358689559
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| 0 | SELECT STATEMENT | | | | 51304 (100)| | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 2546K| 310M| 51304 (1)| 00:10:16 | 3 | PX BLOCK ITERATOR | | 2546K| 310M| 51304 (1)| 00:10:16 | 4 | TABLE ACCESS FULL| ABCDEFG | 2546K| 310M| 51304 (1)|00:10:16
17 rows selected
SQL> SELECT parsing_schema_name,
2 sql_id, 3 MAX(px_servers_execs_total) px_cnt4 FROM dba_hist_sqlstat
5 WHERE px_servers_execs_total > 0 and sql_id = 'cxk63fp44mwyt' 6 GROUP BY parsing_schema_name,
7 sql_id;
PARSING_SCHEMA_NAME SQL_ID PX_CNT
------------------------------ ------------- ---------- TED0042 cxk63fp44mwyt 64 SQL> SELECT qc_session_id, 2 sql_id, 3 to_char(sample_time, 4 'dd.mm.yyyy hh24:mi:ss'), 5 COUNT(*) potantial_dop
6 FROM dba_hist_active_sess_history 7 WHERE sql_id = 'cxk63fp44mwyt'
8 AND to_char(sample_time,'dd.mm.yyyy hh24:mi:ss') = '13.02.2009 10:33:47'
9 GROUP BY qc_session_id,
10 sql_id, 11 to_char(sample_time, 12 'dd.mm.yyyy hh24:mi:ss')13 ORDER BY 4 DESC; QC_SESSION_ID SQL_ID TO_CHAR(SAMPLE_TIME,'DD.MM.YYY POTANTIAL_DOP
------------- ------------- ------------------------------ ------------- 482 cxk63fp44mwyt 13.02.2009 10:33:47 32*
So here the guy who ran this query insists that it took too long since he couldn't get the DOP he requested that morning, and he thinks this is DBA's fault since he configured the Resource Manager not appropriate.
As a result my DBA colleague wants to prove that he really got the DOP what he requested and he needs an accurate method to show this on 10.2.
2009/2/19 Deepak Sharma <sharmakdeep_oracle_at_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
>> *Sent:* Thursday, February 19, 2009 11:19:18 AM
> ------------------------------
> *From:* H.Tonguç YILMAZ <tonguc.yilmaz_at_gmail.com>
> *To:* oracle-l_at_freelists.org
> *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;
>> FROM dba_hist_active_sess_history
> *SELECT qc_session_id,
> sql_id,
> to_char(sample_time,
> 'dd.mm.yyyy hh24:mi:ss'),
> COUNT(*) potantial_dop
> 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 should return 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/
>
>
-- Best Regards, H.Tonguç YILMAZ http://vimeo.com/user983017/videos http://tonguc.yilmaz.googlepages.com/ -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 20 2009 - 00:35:56 CST