Main Page

I've been working on trying to determine where long running queries are at and came across a problem today with V$SQL_PLAN on HP-UX 10gR1
(10.1.0.4.0) that I thought I'd share with you…

Essentially, I was trying to go into V$SQL_PLAN for the problem query, joining up the PARENT_ID and ID columns using CONNECT BY when I discovered that it wasn't joining things up correctly. Further investigation showed that some of the entries in the table had PARENT_ID values which did not relate to any ID values and could therefore not be CONNECTed BY any means...I've since raised a TAR - sorry, Service Request (SR) on the super new whizzy Metalink and the response from Oracle was that it was indeed a bug.

I did some searching on Metalink and the forums and found that there have been problems with V$SQL_PLAN before where the support recommendation was that the problem was fixed in later releases and that a workaround was to just use EXPLAIN PLAN or:

select * from table(dbms_xplan.display_cursor('&sql_id'))
/

For the given SQL_ID - if the plan is still in memory.

It's always wise before going to Support with a problem to come up with a test case in my view...they will end up asking for one anyway and it’s perfectly reasonable to do so. Here's the one that I logged with them:

drop table test1;
drop table test2;
create table test1(col1 number, col2 number,col3 number,col4 varchar2(20)); insert into test1 values(1000,2000,3000,'AAA'); insert into test1 values(1001,2000,3001,'BBB'); insert into test1 values(1002,2001,3000,'CCC'); create table test2(col5 number, col6 varchar2(20)); insert into test2 values(2000,'DDD'); insert into test2 values(2001,'EEE'); commit;

WITH i1 AS
(
select /*+ no_merge materialize */ t1.col1
,t1.col2
,t1.col3
,t1.col4
from test1 t1
, test2 t2
where t1.col2 = t2.col5
and t1.col3 = 3000
)
, i2 AS
(
select /*+ no_merge materialize */ t1.col1
,t1.col2
,t1.col3
,t1.col4
from test1 t1
, test2 t2
where t1.col2 = t2.col5
and t1.col3 = 3001
)
select i1.col1
, i1.col2
, i1.col3
, i1.col4
, i2.col4
from i1
, i2
where i1.col2 = i2.col2
/

select operation,id,parent_id,depth,position from v$sql_plan where sql_id='fj03rbvgpqrp6' order by id /

On 10gR1 on HP-UX I'm getting duff entries in the output:




OPERATION ID PARENT_ID DEPTH POSITION
SELECT STATEMENT 0 0 127
TEMP TABLE TRANSFORMATION 1 0 1 1
HASH JOIN 10 1 2 1
VIEW 11 10 3 1
TABLE ACCESS 12 11 4 1
VIEW 13 10 3 2
TABLE ACCESS 14 13 4 1
LOAD AS SELECT 15 1 2 2
HASH JOIN 16 2 3 1
TABLE ACCESS 17 3 4 1
TABLE ACCESS 18 3 4 2
LOAD AS SELECT 19 1 2 3
HASH JOIN 20 6 3 1
TABLE ACCESS 21 7 4 1
TABLE ACCESS 22 7 4 2



Notice that there are no ID's 2,3,6 or 7 and therefore the PARENT_ID's with those values are plain wrong.

Interestingly, on my 10gR2 on XP at home this worked fine so they probably fixed it in going to 10gR2 me thinks:




OPERATION ID PARENT_ID DEPTH POSITION

SELECT STATEMENT 0 0 14
TEMP TABLE TRANSFORMATION 1 0 1 1
LOAD AS SELECT 2 1 2 1
HASH JOIN 3 2 3 1
TABLE ACCESS 4 3 4 1
TABLE ACCESS 5 3 4 2
LOAD AS SELECT 6 1 2 2
HASH JOIN 7 6 3 1
TABLE ACCESS 8 7 4 1
TABLE ACCESS 9 7 4 2
HASH JOIN 10 1 2 3
VIEW 11 10 3 1
TABLE ACCESS 12 11 4 1
VIEW 13 10 3 2
TABLE ACCESS 14 13 4 1

The PARENT_ID and ID's CONNECT up no problem now.

This is a bit of a pain cos I need it to work really for my "where is my query at" efforts....grrrr! Given it only fails in this way infrequently and for certain types of query (in my example where there are numerous TEMP TABLE TRANSFORMATIONs) I think I’ll just live with it as a feature in my script.

Labels: bugs, tuning