Wednesday, August 16, 2006
10gR2: OBJECT_NAME missing from V$SQL_PLAN for INSERT statement
I'd written a query the other day to try and identify SQL statements that had been run which wanted a certain degree of parallelism (DOP) and had either run with less than the requested amount of PX slaves or had run serially - in order that we could identify whether the (poor) performance of a query was potentially due to it not getting the resources (PX) we expected it to.
I posted the query on this entry here but have updated it since and it's on this link now...in case I change it again. It needs more work as it currently doesn't fully understand how many PX slaves will be used for a given piece of SQL, e.g. Sorts and Set operations (UNION etc...) all add to the requirements...I'm trying to determine the rules for this so I can improve the query in this respect.
Anyway, it seemed to work reasonably well - particularly at finding things that were supposed to run parallel but actually ran serially.
I did, however, notice that even on certain (INSERT) statements that didn't involve Sorts/Set operations it was showing records where the required DOP (what I thought would be the maximum DOP it was going to request) was actually less than the achieved DOP - this didn't make sense until I delved deeper and found that the OBJECT_NAME column on the V$SQL_PLAN seems to be NULL on the line relating to the INSERT operation in the plan - running an EXPLAIN PLAN for the statement showed the OBJECT_NAME correctly but only when it's executed and the V$SQL_PLAN is checked did I find that this column appears to be, unexpectedly, NULL.
With the OBJECT_NAME being NULL it meant that my script was not including the DOP on the target table when determining the object with the highest DOP thereby sometimes getting an incorrect value and resulting in rows where the Required DOP is less than the Achieved DOP.
I created the obligatory test case to illustrate the point before contacting support to raise an SR (5686332.993). It turns out that this is all down to bug 5204810 which is (allegedly) fixed in 11g. The bug is a little unclear in that it only talks about this issue when dealing with conventional inserts and not Direct Path inserts which were those where I'd experienced the problem.
The bug suggests that 11g will add a new line to the execution plans such that it will look like this:
The ID 1 line will be visible in 11g the bug suggests and it will then start to show the OBJECT_NAME (TABLE_X in this instance) correctly on this new line rather than a NULL.
Oracle support suggested that although it wasn't directly mentioned in this bug, it was still the same issue causing the problem for my Direct Path inserts...although I'm not entirely convinced since Direct Path inserts already have an extra line (LOAD AS SELECT) under the INSERT STATEMENT line in their plans...perhaps the fix, whilst including the line for LOAD TABLE CONVENTIONAL for conventional inserts, has also ensured that the OBJECT_NAME is correctly displayed when it's a Direct Path INSERT and the Operation is LOAD AS SELECT...I'll try to remember to test it when 11g becomes available.
I posted the query on this entry here but have updated it since and it's on this link now...in case I change it again. It needs more work as it currently doesn't fully understand how many PX slaves will be used for a given piece of SQL, e.g. Sorts and Set operations (UNION etc...) all add to the requirements...I'm trying to determine the rules for this so I can improve the query in this respect.
Anyway, it seemed to work reasonably well - particularly at finding things that were supposed to run parallel but actually ran serially.
I did, however, notice that even on certain (INSERT) statements that didn't involve Sorts/Set operations it was showing records where the required DOP (what I thought would be the maximum DOP it was going to request) was actually less than the achieved DOP - this didn't make sense until I delved deeper and found that the OBJECT_NAME column on the V$SQL_PLAN seems to be NULL on the line relating to the INSERT operation in the plan - running an EXPLAIN PLAN for the statement showed the OBJECT_NAME correctly but only when it's executed and the V$SQL_PLAN is checked did I find that this column appears to be, unexpectedly, NULL.
With the OBJECT_NAME being NULL it meant that my script was not including the DOP on the target table when determining the object with the highest DOP thereby sometimes getting an incorrect value and resulting in rows where the Required DOP is less than the Achieved DOP.
I created the obligatory test case to illustrate the point before contacting support to raise an SR (5686332.993). It turns out that this is all down to bug 5204810 which is (allegedly) fixed in 11g. The bug is a little unclear in that it only talks about this issue when dealing with conventional inserts and not Direct Path inserts which were those where I'd experienced the problem.
The bug suggests that 11g will add a new line to the execution plans such that it will look like this:
-----------------------------------------
Id Operation Name
-----------------------------------------
0 INSERT STATEMENT
1 LOAD TABLE CONVENTIONAL TABLE_X
-----------------------------------------
The ID 1 line will be visible in 11g the bug suggests and it will then start to show the OBJECT_NAME (TABLE_X in this instance) correctly on this new line rather than a NULL.
Oracle support suggested that although it wasn't directly mentioned in this bug, it was still the same issue causing the problem for my Direct Path inserts...although I'm not entirely convinced since Direct Path inserts already have an extra line (LOAD AS SELECT) under the INSERT STATEMENT line in their plans...perhaps the fix, whilst including the line for LOAD TABLE CONVENTIONAL for conventional inserts, has also ensured that the OBJECT_NAME is correctly displayed when it's a Direct Path INSERT and the Operation is LOAD AS SELECT...I'll try to remember to test it when 11g becomes available.
Subscribe to Posts [Atom]