Re: How to find the parallelism degree of a query that has been finished?(on 10.2)
- From: H.Tonguç YILMAZ <tonguc.yilmaz@xxxxxxxxx>
- To: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>, niall.litchfield@xxxxxxxxx
- Date: Fri, 20 Feb 2009 08:35:56 +0200
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_cnt
4 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@xxxxxxxxx>
> 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@xxxxxxxxx>
> *To:* oracle-l@xxxxxxxxxxxxx
> *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 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/
Other related posts:
