![]() Which one should we use to determine the explain plan used at run-time.The Proving Grounds (Swampy Pit - Flayer Jungle)Ī mighty siege weapon whose atrocities can be traced back to the Eternal Conflict, where it annihilated countless angelic forces of the Heavenly Host, turning the tide of several wars. What is the difference between the explain plans in the "row source operation" and the "explain plan section". How come your sample displays more info in the row source section than what I get ? And what do those numbers mean ?Īlso: How come I don't see the "row source" section for the last sql statement ? I have a few Questions w.r.t to the explain plans generated by the tkprof. T x_was_NOT_PCT where object_name like :xĠ TABLE ACCESS BY INDEX ROWID OBJ#(35635) (cr=2 r=0 w=0 time=70 us)Ġ INDEX RANGE SCAN OBJ#(35636) (cr=2 r=0 w=0 time=64 us)(object id 35636)īut since you never parse with :x := '%' and the index should be used here, this bind variable "peeking" works to your advantage T x_was_percent where object_name like :xģ0046 TABLE ACCESS FULL T (cr=2391 r=182 w=0 time=189486 us) Select * from t x_was_NOT_PCT where object_name like :x Select * from t x_was_percent where object_name like :x Consider:įor table for all indexes for all indexed columns Here, the optimizer will parseĪs if :x had a value, was a literal. Your application always uses LIKE without leading '%' - meaning an index would be helpful generally. Well, suppose you use LIKE lots - this could be very beneficial. You start up next monday and the first query you submit using a bind of :N := 99 - index range scan plan that week). Just think, you start up on monday morning and the first query you submit uses a bind of :N := 1, full scan plan that week. So where does this come into play, when is this a "good thing" (this example shows it can be a confusing thing. Same here - now the index range scan isn't appropriate Here the bind variable is in fact 99 if you remember from the test case - we can see that by the very number of rows. But watch out:ġ TABLE ACCESS FULL T (cr=428 r=124 w=0 time=19046 us) That is the plan you expect if the bind is 99 - index range scan. On the other hand:ġ TABLE ACCESS BY INDEX ROWID T (cr=3 r=0 w=0 time=116 us)ġ INDEX RANGE SCAN T_IDX (cr=2 r=0 w=0 time=73 us)(object id 35628) That is what you expect when the bind was set to 1 during the hard parse. Now, I ran tkprof with AGGREGATE=NO so we would see each of the 2 queries 2 times:ģ0045 TABLE ACCESS FULL T (cr=2404 r=124 w=0 time=174004 us) the one taggedt with N_WAS_99 is hard parsed with the bind set to the value of 99Īnd now we execute (using soft parses) the SAME queries - but with the inputs flipped flopped: the one tagged N_WAS_1 is hard parsed with the bind set to the value of 1. Note that we run 2 queries - and each one twice. So, we run these queries against this data. index on t(id) should be used if id is NOT 1. Stress the word hard there, it is not every parse, only on the hard parse. They were soft parses but a parse is a parse and something to be avoided when possible.īasically - it means when the query is first hard parsed, the optimizer will peek at the binds in order to determine how to optimize the query. ![]() at least we bound - but the price we paid for parsing is extremely high. ALL PLSQL VARIABLES IN SQL ARE BINDS!!! Look at the parse to execute numbers there - parse once, execute many, use binds. INSERT /* STATIC SQL */ into t values ( :b1 )Ĭall count cpu elapsed disk query current rows Now that is interesting but TKPROF is fascinating: ![]() Table set timing alter session set sql_trace=true Ĥ insert /* STATIC SQL */ into t values ( i ) ĥ 'insert /* DYNAMIC SQL */ into t values ( :i )' let me demonstrate with your example using sql_trace and tkprof. ![]() The beauty, they sheer beauty of PLSQL is that the things you have to be concerned with in other languages like What you have just proven is that static SQL in plsql is much much more efficient then using dynamic sql to do the SAME!!! Why? Because BOTH are using bind variables. ![]()
0 Comments
Leave a Reply. |