RE: Parallel Query & Shared Pool SQL TEXT
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <dba.orcl@xxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Oct 2005 20:58:54 +0200
indeed -- they are names of so-called table queues,
and they show that you apparently have "PRODUCER" slave processes (populating
the table queues with their intermediate results)
and "CONSUMER" slave processes, using the results of the producers.
SQL*Plus AUTOTRACE gives you a nice overview of the complete execution plan;
otherwise, you might want to use the utlxplp.sql script
($ORACLE_HOME/rdbms/admin).
kind regards,
Lex.
------------------------------------------------------------------
Steve Adams Seminar <http://www.naturaljoin.nl/events/seminars.html>
http://www.naturaljoin.nl/events/seminars.html
------------------------------------------------------------------
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of jaromir nemec
Sent: Monday, October 10, 2005 20:41
To: dba.orcl@xxxxxxxxx; Oracle-L Freelists
Subject: Re: Parallel Query & Shared Pool SQL TEXT
Hi Sami,
> I am sure this is a parallel query.
right, this is a parallel slave query
>All the tablenames are converted into :QXXXX.
:Qnnnnnnn is actually not a table name. If parallel slave query access a table,
you will see something like this:
SELECT /*+ NO_EXPAND ROWID(A1) */ . . . . . FROM "<owner>"."<table_name>"
PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)
The :Qnnnnnnn is a row set generated by previous slave query. To see the query
producing it you must find the original query (i.e. the query of the
coordinator).
If still running use v$px_session, alternatively search V$SQL_PLAN using column
object_node. In the execution plan for the query coordinator you will find your
original query in the column OTHER. Some other line of execution plan contains
in the column OTHER the slave query generating the row set. This could see
something like this:
SELECT /*+ Qnnnnnn NO_EXPAND ROWID(A1) */
You must examine the whole execution plan to get the context.
HTH
Jaromir D.B. Nemec
----- Original Message -----
From: Sami <mailto:dba.orcl@xxxxxxxxx> Seerangan
To: Oracle-L Freelists <mailto:oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, October 05, 2005 9:34 PM
Subject: Parallel Query & Shared Pool SQL TEXT
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
...
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.
BEGIN:VCARD
VERSION:2.1
N:de Haan;Lex
FN:Lex de Haan
ORG:Natural Join B.V.
TEL;WORK;VOICE:+31.30.2515022
TEL;HOME;VOICE:+31.30.2518795
TEL;CELL;VOICE:+31.62.2955714
TEL;WORK;FAX:+31.30.2523366
ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Pieter Breughelstraat 10=0D=0AUtrecht 3583
SK=0D=0ANetherlands
URL;WORK:http://www.naturaljoin.nl
EMAIL;PREF;INTERNET:lex.de.haan@xxxxxxxxxxxxxx
REV:20040224T160439Z
END:VCARD
Other related posts:
