RE: Parallel Threads

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Wed, 11 Mar 2009 11:25:15 -0400
Message-ID: <9E716A1722DE4200B3F99E1F03336D96_at_KenPC>

Seem my v$active_session_history view only has data for the last two hours. Is this the default config, if so how can I change it?  

Thanks,

Ken  

---

From: Riyaj Shamsudeen [mailto:riyaj.shamsudeen_at_gmail.com] Sent: Wednesday, March 11, 2009 10:48 AM To: kennaim_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Parallel Threads  

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 - 10:25:15 CDT