Re: Parallel Threads

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Wed, 11 Mar 2009 09:48:22 -0500
Message-ID: <203315c10903110748w60a89cb3u4d8f23c08d0a774c_at_mail.gmail.com>

Hi Ken
  What version of Oracle ? If you have access to ASH data, v$active_session_history has three columns qc_instance_id, qc_session_id and qc_session_serial# can be used to see how many slaves were used by a query at a specific point in time.

select to_char(sample_time, 'DD-MON-YYYY HH:MI:SS') tm, QC_INSTANCE_ID, QC_SESSION_ID,count(*) from v$active_session_history where QC_SESSION_ID is not null and session_id != qc_session_id group by to_char(sample_time, 'DD-MON-YYYY HH:MI:SS'),QC_SESSION_ID, QC_INSTANCE_ID
order by 1
/
TO_CHAR(SAMPLE_TIME,'DD-MO QC_INSTANCE_ID QC_SESSION_ID COUNT(*)

-------------------------- -------------- ------------- ----------
11-MAR-2009 02:47:34                    1          1187          4
11-MAR-2009 02:47:34                    1          1574          1
11-MAR-2009 02:47:35                    1          1574          2
11-MAR-2009 02:47:36                    1          1187         12
11-MAR-2009 02:47:37                    1          1187         12
11-MAR-2009 02:47:37                    1          1574         12
11-MAR-2009 02:47:40                    1          1187          1
11-MAR-2009 02:47:40                    1          1574          2
11-MAR-2009 02:47:42                    1          1187          3
11-MAR-2009 02:47:42                    1          1574          3
11-MAR-2009 02:47:43                    1          1187          3

....

-- 
Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals -  http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com

On Wed, Mar 11, 2009 at 8:35 AM, Kenneth Naim <kennaim_at_gmail.com> wrote:



>  How can I tell how many parallel threads were spawned by a parallel query

> after the query completed. I have a query that normally takes a couple hours

> but ran for 24 hours over the weekend. My theory is that the 40 parallel

> threads were reduced by the optimizer for some reason so I’d like to know

> how many threads it used.

>

>

>

> Thanks,

>

> Ken

>


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 11 2009 - 09:48:22 CDT