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.

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_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
>

> ------------------------------
> *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 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-l
Received on Fri Feb 20 2009 - 00:35:56 CST

Original text of this message