Re: Parallel Query & Shared Pool SQL TEXT

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Mon, 10 Oct 2005 20:41:05 +0200
Message-ID: <0d5501c5cdca$2df891c0$3c02a8c0@JARAWIN>

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 Seerangan To: Oracle-L Freelists 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.

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

Received on Mon Oct 10 2005 - 13:48:14 CDT