Home » RDBMS Server » Performance Tuning » How to Change execution plan of Currently Executing Statement? (Oracle 10.2.4.0 on RHEL)
| How to Change execution plan of Currently Executing Statement? [message #493500] |
Tue, 08 February 2011 08:32  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello
Please refer following sql statements and code
Session 1
create table tab1 as select * from dba_objects where object_id is not null;
alter session set events '10046 trace name context forever, level 12';
declare
x number;
begin
for i in 1..4
loop
select count(1) into x from tab1 where object_id=2331;
--dbms_output.put_line(i);
insert into tab2 values(i);
commit;
dbms_lock.sleep(6);
end loop;
end;
/
Session 2
after "starting" the above pl/sql block from Session 1, I keep on querying tab2 from Session 2
And as soon as 2 records are inserted in tab2, I create index from Session 2
select * from tab2;
select * from tab2;
select * from tab2;
N
----------
1
2
create index i on tab1(object_id);
As I have tested from a single session (just before this test) such index is used for the sql statement
select count(1) into x from tab1 where object_id=2331;
However when I checked the trace file I am not geeting results as expected
I am expecting 4 execution plans - 2 FTS and 2 Index Access scans
and for this I am issuing following command
tkprof dst1_ora_7369.trc dst1_ora_7369.txt aggregate=no sys=no
But unfortunately I am getting following output
SELECT COUNT(1)
FROM
TAB1 WHERE OBJECT_ID=2331
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 4 0.00 0.00 0 2 0 0
Fetch 4 0.03 0.03 0 1646 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.03 0.03 0 1649 0 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 SORT AGGREGATE (cr=4 pr=0 pw=0 time=73 us)
2 INDEX RANGE SCAN I (cr=4 pr=0 pw=0 time=47 us)(object id 4673976)
Note : Complete trace file is attached with this post
My questions are
1) Why I am unable to see 4 execution plans - 2 with FTS and 2 with Index access when I mentioned 'aggregate=no'?
2) Whether the index i will be used for last 2 iterations after first 2 iterations of FTS?
If answer to above question 2) is 'No'
By which method I can force an ongoing sql statement in loop to take different execution path?
Of course I can't hard parse sql in 'that' current session
Will flushing Shared pool help in above case?
Thanks and Regards,
OraKaran
|
|
|
|
| Re: How to Change execution plan of Currently Executing Statement? [message #493511 is a reply to message #493500] |
Tue, 08 February 2011 09:06  |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
The Row Source Operations shows that the Index was used -- at least in the fourth execution if not the third one as well.
Unfortunately, the SQL statistics are for all 4 exectuions (and that is why consistent gets shows up as 1,646 blocks while the Index Read plan did only 4 blocks). So the first 2 (or 3 ?) executions did do a FullTableScan while the last 2 or 1 did do an IndexRangeScan.
Apparently, "aggregate=NO" did not kick in at all.
Can you retry the tkprof with "aggregate=NO' one more time ?
Also, if you can, paste the raw trace file.
Hemant K Chitale
|
|
|
|
| Re: How to Change execution plan of Currently Executing Statement? [message #493522 is a reply to message #493500] |
Tue, 08 February 2011 09:41 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Hemant and Michel
Thanks for your replies
Michel
I will try the tweak you have suggested. Though I am confident it will work as expected it will not be real case scenario
I am trying to find out how I can come out of the problematic situation if the query has picked up wrong plan and will continue further for many iterations
Hemant
In fact I tried 'aggregate=no' twice
First it did not gave desired results so I thought it would be TYPO
Again checked tkprof parameters
And though I found I issued proper command jut for the benefit of doubt again tried the command
I have attached the complete trace of the session now (by changing file extension from .trc to .ora)
Quote:
at least in the fourth execution if not the third one as well
BTW how you concluded above? I was trying to see that but could not found
Is it from?
Misses in library cache during parse
Regards,
OraKaran
|
|
|
|
| Re: How to Change execution plan of Currently Executing Statement? [message #493544 is a reply to message #493522] |
Tue, 08 February 2011 10:20 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
The tkprof already shows me two different Dynamic Sampling executions, one without an index on the table and one with the index present.
From the raw trace, I see
PARSING IN CURSOR #2 len=46 dep=1 uid=5 oct=3 lid=5 tim=1266767177985754 hv=3933124688 ad='5ec065a0'
SELECT COUNT(1) FROM TAB1 WHERE OBJECT_ID=2331
so that statement is CURSOR #2 and will remain #2 until and unless it is closed.
Then, I see the first execution which read 821 blocks :
EXEC #2:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1266767177985864
FETCH #2:c=27996,e=27768,p=0,cr=821,cu=0,mis=0,r=1,dep=1,og=1,tim=1266767178013660
and the second execution which also read 821 blocks :
EXEC #2:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1266767183884988
FETCH #2:c=6999,e=7353,p=0,cr=821,cu=0,mis=0,r=1,dep=1,og=1,tim=1266767183892363
a third execution which read 2 blocks (also notice the "mis=1" meaning that it had to be re-parsed at this execution)
EXEC #2:c=7999,e=7775,p=1,cr=74,cu=0,mis=1,r=0,dep=1,og=1,tim=1266767189761668
FETCH #2:c=0,e=28,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1266767189761718
and the fourth execution which also read 2 blocks :
EXEC #2:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1266767195622918
FETCH #2:c=0,e=32,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=1266767195622971
for a total of 1646 blocks.
However, only one Parse entry was captured. (dep=1 is because it was a "child" of the parent PLSQL block (CURSOR #1) that is at dep=0)
PARSING IN CURSOR #2 len=46 dep=1 uid=5 oct=3 lid=5 tim=1266767177985754 hv=3933124688 ad='5ec065a0'
while Row Source was captured at the end, but appears to be for 2 executions, returning 2 rows (cnt=2 is 2 rows)
STAT #2 id=1 cnt=2 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=4 pr=0 pw=0 time=73 us)'
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=4673976 op='INDEX RANGE SCAN I (cr=4 pr=0 pw=0 time=47 us)'
Instead of running a PLSQL loop, you could manually issue the statement 4 times as an SQL SELECT (creating the index from another session after the first 2 executions).
Also ensure that you EXIT the session before you run a tkprof on the trace file -- else the cursor closure and RowSourceOperations are not captured properly in 10.2. (Of course, alternatively running a completed unrelated SQL like "SELECT 'X' FROM DUAL;" should also force a closure of the cursor).
(11.2 has improved capturing of the STAT# (RowSourceOperations) calls.)
Hemant K Chitale
|
|
|
|
| Re: How to Change execution plan of Currently Executing Statement? [message #493551 is a reply to message #493500] |
Tue, 08 February 2011 10:45 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Hemant
First of all thanks for sharing your observations and conclusion after reading the trace file.
Regarding existing the trace session, Yes. I exited it to close cursors but as you have mentioned, I should have executed dummy 'select x from dual' as a precaution.
I have executed the statements in a loop to simulate a real time scenario.
Once we had some 'Credit Note Preparation' batch process which use to run for 30-40 minutes during daytime.
And may be because of 'bind variable peeking', if it picked the wrong plan, support team had no options but to wait and watch it (and pray) till it finished
Though now I can think we should have checked cursor_sharing setting, using hard coded if possible with histogram, that time it was not possible and it made things worst on few occasions
So as a bottom-line can we say that if a sql is executing in a loop we can't force to change it's execution path externally (from other session)?
Regards,
OraKaran
|
|
|
|
| Re: How to Change execution plan of Currently Executing Statement? [message #493589 is a reply to message #493551] |
Tue, 08 February 2011 19:30 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
>So as a bottom-line can we say that if a sql is executing in a loop we can't force to change it's execution path externally (from other session)?
Rubbish. You have proven that you CAN change the execution plan. See the differences between the trace file statistics first 2 and the last 2 execution in your PLSQL loop.
>I have executed the statements in a loop to simulate a real time scenario.
I really don't know if your "real world" program uses a PLSQL loop in this manner. I have seen ETL jobs stupidly use PLSQL loops to insert 1 row at a time but I don't know your real job.
>And may be because of 'bind variable peeking', if it picked the wrong plan, support team had no options but to wait and watch it (and pray) till it finished
Nonsense. They have options that can be executed from other sessions
1. Gather Statistics (with, preferably, no_invalidate=>FALSE)
2. Dummy DDL (e.g. COMMENT ON TABLE ...) to force invalidation of SQLs
3. Adding Indexes
Hemant K Chitale
|
|
|
|
| Re: How to Change execution plan of Currently Executing Statement? [message #493637 is a reply to message #493500] |
Wed, 09 February 2011 03:21 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Hemant
Quote:
>So as a bottom-line can we say that if a sql is executing in a loop we can't force to change it's execution path externally (from other session)?
Rubbish. You have proven that you CAN change the execution plan. See the differences between the trace file statistics first 2 and the last 2 execution in your PLSQL loop.
I apologize. I don't know what made me write that, while I read your earlier reply clearly stating change in plan. I realized my mistake.
Quote:
>I have executed the statements in a loop to simulate a real time scenario.
I really don't know if your "real world" program uses a PLSQL loop in this manner. I have seen ETL jobs stupidly use PLSQL loops to insert 1 row at a time but I don't know your real job.
I agree that sometimes it is mistake of writing pl/sql but other times it is just a requirement.
Quote:
>And may be because of 'bind variable peeking', if it picked the wrong plan, support team had no options but to wait and watch it (and pray) till it finished
Nonsense. They have options that can be executed from other sessions
1. Gather Statistics (with, preferably, no_invalidate=>FALSE)
2. Dummy DDL (e.g. COMMENT ON TABLE ...) to force invalidation of SQLs
3. Adding Indexes
2nd option I never thought of. Thanks for the suggestion. I will try it.
Regarding Option 1, I tried it few days back on Oracle 9i and did not got the desired results. I will try it again
For Option 3, you have proved the change took place exactly when it was supposed to. (for 3rd and 4th execution of the statement)
Once again I am sorry if I have annoyed you with my earlier reply
Thanks and Regards,
OraKaran
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 23:16:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
