Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Parallel Query & Shared Pool SQL TEXT

Re: Parallel Query & Shared Pool SQL TEXT

From: zhu chao <zhuchao_at_gmail.com>
Date: Thu, 6 Oct 2005 11:28:20 +0800
Message-ID: <962cf44b0510052028t679b8018mf503b7df5fb18998@mail.gmail.com>


Your problem is similar to mine.

Try check v$sql_plan see if you can find the SQLs running in parallel. And compare them.
 If the SQL is still running, you can find it from v$pq_xxx views. find the parent session, and you can find the actual SQL.

Also
On 10/6/05, Sami Seerangan <dba.orcl_at_gmail.com> wrote:
>
> This is the output from v$sql.sql_text:
> I am sure this is a parallel query. All the tablenames are converted into
> :QXXXX. How to identify the real table name. I have 1000+ such queries are
> in my shared pool and most of the queries elapsed time is more than 30 mins.
> I want to find out the actual tablename these queries are running against.
> SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3,A1.C4 C4,A1.C5 C5,A1.C6 C6,
> A1.C7 C7 F
> ROM (SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3,A1.C4 C4,A1.C5 C5,A1.C6C6,
> A1.C7
> C7 FROM (SELECT /*+ ORDERED NO_EXPAND USE_HASH(A3) */ A2.C6 C0,A2.C7 C1,
> A2.C0 C
> 2,A2.C5 C3,A2.C11 C4,A2.C4 C5,A2.C9 C6,TO_CHAR(A2.C6,'DD-Mon-YYYY') C7
> FROM
> (SELECT /*+ ORDERED NO_EXPAND USE_HASH(A5) */ A5.C0 C0,A4.C0 C1,A5.C9 C2,
> A4.C1 C3,A5
> .C1 C4,A5.C2 C5,A5.C3 C6,A5.C4 C7,A5.C5 C8,A5.C6 C9,A5.C7 C10,A5.C8 C11
> FROM :Q44491002 A4,:Q44491000 A5
> WHERE A5.C0=A4.C0) A2,:Q44491001 A3
> WHERE A3.C0=A2.C0)
> A1 ORDER BY A1.C0 DESC,A1.C1 DESC,A1.C2 DESC) A1
> WHERE ROWNUM<=15
> How to identify the table name here.
>

--
Regards
Zhu Chao
www.cnoug.org <http://www.cnoug.org>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 05 2005 - 22:31:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US