tag:blogger.com,1999:blog-22570622435129340222024-03-08T02:46:47.860+05:30Sachin Arora's BlogSachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.comBlogger43125tag:blogger.com,1999:blog-2257062243512934022.post-71748213768129793902009-02-09T12:01:00.005+05:302009-02-09T12:06:52.860+05:30Index range Scan Vs Nested loop in IN-LIST ?<div>Which one is better over the above? This question has been the one of the latest topic in our performance team when using a viable solution for varying In-List management.</div><div><br /></div><div><br /></div><div><div>Background:</div><div><br /></div><div>We all know Oracle's shared pool is sensitive to the way sql is written - whitespace, uppercase/lowercase difference, number of bind variables in IN-Clause.</div><div>To avoid shared pool being bombarded with thousands of sqls of same type, we currently are using buckets for IN-clause.</div><div><br /></div><div><div>Ex:</div><div><br /></div><div>select * from emp where empno in (:B1,:B2,:B3,......,:B16) -- this is bucket size 16.</div><div><br /></div><div>So, if user passes only one bind value, the rest of bind values will go as null. </div><div>And if user passes two bind values, the rest of bind values will go as null. </div><div><br /></div><div>This way we control, the number of sqls which needs to be maintained in shared pool.</div><div><br /></div><div>We implement different size of buckets: 16, 32, 64, 128, 256, 512, 768, 1000</div><div><br /></div><div>Upon searching i came across Tomy kyte's solution mentioned here (http://tkyte.blogspot.com/2006/06/varying-in-lists.html).</div><div>Initially i used his 8i solution and results were somehow unexpected:</div><div><div><blockquote></blockquote>Here is the testcase:</div><div><span class="Apple-style-span" style="font-family:'courier new';"><br /></span></div><div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">SQL> create or replace type stringTableType as table of varchar2(4000);</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 2 /</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Type created.</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">SQL> SQL> 2 3 4</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 5 v_str long default p_str || ',';</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 6</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 7 v_n number;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 8</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 9 v_data stringTableType := stringTableType();</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 10</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 11 begin</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 12</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 13 loop</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 14</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 15 v_n := instr( v_str, ',' );</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 16</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 17 exit when (nvl(v_n,0) = 0);</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 18</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 19 v_data.extend;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 20</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 21 v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 22</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 23 v_str := substr( v_str, v_n+1 );</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 24</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 25 end loop;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 26</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 27 return v_data;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 28</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 29 end;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 30</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> 31 /</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Function created.</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">SQL> create table test as select rownum r from dual connect by level<=100000;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Table created.</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">SQL> create index test_ux on test(r);</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Index created.</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">PL/SQL procedure successfully completed.</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Now running a NORMAL in-list with 10 inlist values:</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">SQL> declare</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">a number;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">begin</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">for i in 1..10000 loop</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">select count(*) into a from test where r in (1,2,3,4,5,6,7,8,9,10);</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">end loop;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">end;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">/</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> </span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">PL/SQL procedure successfully completed.</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Elapsed: 00:00:01.23</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Note the "Elapsed" time. It is 1.5 secs.</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Now using the Collection method:</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">SQL> SQL> declare</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">a number;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">begin</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">for i in 1..10000 loop</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">select count(*) into a from test</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">where r in ( select /*+ cardinality(10) */ * from THE ( select cast( convert2Table( '1,2,3,4,5,6,7,8,9,10' ) as stringTableType ) from dual ) );</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">end loop;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">end;</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">/</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"> </span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">PL/SQL procedure successfully completed.</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size:small;">Elapsed: 00:00:22.41</span></span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><div><span class="Apple-style-span" style="font-size:medium;">Note the "Elapsed" time is almost 15x more than normal inlist.</span></div><div><span class="Apple-style-span" style="font-size:medium;"><br /></span></div><div><span class="Apple-style-span" style="font-size:medium;">This was a bit surprising.</span></div><div><span class="Apple-style-span" style="font-size:medium;"><br /></span></div><div><span class="Apple-style-span" style="font-size:medium;">I did the same test under 10046 + tkprof eyes.</span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">SELECT COUNT(*)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">FROM</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> TEST WHERE R IN (1,2,3,4,5,6,7,8,9,10)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">call count cpu elapsed disk query current rows</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">------- ------ -------- ---------- ---------- ---------- ---------- ----------</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">Parse 1 0.00 0.00 0 0 0 0</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">Execute 10000 0.46 0.42 0 0 0 0</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">Fetch 10000 1.99 2.00 0 200000 0 10000</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">------- ------ -------- ---------- ---------- ---------- ---------- ----------</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">total 20001 2.46 2.43 0 200000 0 10000</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">Rows Row Source Operation</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">------- ---------------------------------------------------</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 10000 SORT AGGREGATE (cr=200000 pr=0 pw=0 time=2117708 us)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 100000 INLIST ITERATOR (cr=200000 pr=0 pw=0 time=2271474 us)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 100000 INDEX RANGE SCAN TEST_UX (cr=200000 pr=0 pw=0 time=1354629 us)(object id 60859168)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> </span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">SELECT COUNT(*)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">FROM</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> TEST WHERE R IN ( SELECT /*+ cardinality(10) */ * FROM THE ( SELECT CAST(</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> CONVERT2TABLE( '1,2,3,4,5,6,7,8,9,10' ) AS STRINGTABLETYPE ) FROM DUAL ) )</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">call count cpu elapsed disk query current rows</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">------- ------ -------- ---------- ---------- ---------- ---------- ----------</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">Parse 1 0.01 0.01 0 48 0 0</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">Execute 10000 5.40 5.42 0 0 0 0</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">Fetch 10000 9.57 9.65 0 120000 0 10000</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">------- ------ -------- ---------- ---------- ---------- ---------- ----------</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">total 20001 14.99 15.09 0 120048 0 10000</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">Rows Row Source Operation</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;">------- ---------------------------------------------------</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 10000 SORT AGGREGATE (cr=120000 pr=0 pw=0 time=13856299 us)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 100000 NESTED LOOPS (cr=120000 pr=0 pw=0 time=16447839 us)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 100000 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=11596021 us)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 100000 HASH UNIQUE (cr=0 pr=0 pw=0 time=11085381 us)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 100000 COLLECTION ITERATOR PICKLER FETCH CONVERT2TABLE (cr=0 pr=0 pw=0 time=3527457 us)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 20000 FAST DUAL (cr=0 pr=0 pw=0 time=151992 us)</span></span></div><div><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style="font-size: x-small;"> 100000 INDEX RANGE SCAN TEST_UX (cr=120000 pr=0 pw=0 time=2182846 us)(object id 60859168)</span></span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><div><br /></div><div>The difference between 2 is sea. Although the later did almost 1/2 LIO yet the total time spent on CPU was huge.</div><div><br /></div><div>It seems to me that NL over INLIST ITERATOR is not a good choice ATLEAST for 10 values.</div><div><br /></div><div>Now, I tried with 1000 values -- i didnt capture the screen shots. But here is what i saw.</div><div><br /></div><div>- for loop that runs 1000 times -- the "R in (1,2,3...1000)" took 54 secs. And using the Tom Kyte's suggested methood - it took 16 minutes.</div><div><br /></div><div>Am i missing something?</div><div><br /></div><div><br /></div></div></div></div></div></div></div></div></div>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com21tag:blogger.com,1999:blog-2257062243512934022.post-10208276955518658922008-12-19T15:42:00.000+05:302008-12-27T12:58:37.943+05:30Partition stats usage with Bind peeking disabled<div>Last week, I was working on a package, which is used to collect stats.</div><div>I realized we collect stats of all partioned tables,indexes at both partition and global level.</div><div><br /></div><div><div>While working on that package, i was pondering when will we actually use partition/local level stats (over global stats) in real-world scenario as most of our "where" clause </div><div><br /></div><div>values are passed using bind variables. How will optimizer will come to know which partition (stats) to use because value passed will checked run time except in the case when it </div><div><br /></div><div>is run for the first time and Bind peek happens.</div><div><br /></div><div>A bit confused with this I performed a test, where my test case was inspired by <a href="http://oraclesponge.wordpress.com/2006/10/28/partition-pruning-and-bind-variables/">David Aldridge's blog post </a>(however i conducted a different set of tests).</div><div><br /></div><div>I first checked the difference in 10053 trace in sqls where we pass literals Vs bind variables - when bind peek is enabled (default - enabled, but not in our case).</div><div><br /></div><div><br /></div><div><div>Test case:</div><div>============</div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">drop table test_par</span></div><div><span class="Apple-style-span" style="font-size:x-small;">/</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">create table test_par</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> (</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> col1 number not null,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> col2 number not null</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> )</span></div><div><span class="Apple-style-span" style="font-size:x-small;">nologging compress pctfree 0</span></div><div><span class="Apple-style-span" style="font-size:x-small;">partition by range (col1)</span></div><div><span class="Apple-style-span" style="font-size:x-small;">(partition p1_to_4 values less than (5),</span></div><div><span class="Apple-style-span" style="font-size:x-small;">partition p5 values less than (6)</span></div><div><span class="Apple-style-span" style="font-size:x-small;">)</span></div><div><span class="Apple-style-span" style="font-size:x-small;">/</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">insert /*+ append */ into test_par</span></div><div><span class="Apple-style-span" style="font-size:x-small;">select mod(rownum,4)+1,rownum</span></div><div><span class="Apple-style-span" style="font-size:x-small;">from dual</span></div><div><span class="Apple-style-span" style="font-size:x-small;">connect by level <= 100000</span></div><div><span class="Apple-style-span" style="font-size:x-small;">/</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">insert into test_par values(5,10);</span></div><div><span class="Apple-style-span" style="font-size:x-small;">commit;</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">create index idx02_test_par</span></div><div><span class="Apple-style-span" style="font-size:x-small;">on test_par (col2)</span></div><div><span class="Apple-style-span" style="font-size:x-small;">local nologging</span></div><div><span class="Apple-style-span" style="font-size:x-small;">/</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">begin</span></div><div><span class="Apple-style-span" style="font-size:x-small;">DBMS_STATS.GATHER_TABLE_STATS (</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> ownname => user,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> tabname => 'test_par',</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> partname => null,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> estimate_percent => 100,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> block_sample => false,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> method_opt => 'for all columns size 1',</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> degree => null,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> granularity => 'ALL',</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> cascade => true,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> stattab => NULL, </span></div><div><span class="Apple-style-span" style="font-size:x-small;"> statid => NULL,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> statown => NULL,</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> no_invalidate => FALSE);</span></div><div><span class="Apple-style-span" style="font-size:x-small;">end;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">/</span></div><div><br /></div><div>So - i have 75000 rows in partition p1_to_4 and 1 row in p5 </div><div><br /></div><div><br /></div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> select count(*),col1 from test_par partition(p1_to_4) group by col1;</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> COUNT(*) COL1</span></div><div><span class="Apple-style-span" style="font-size:x-small;">---------- ----------</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> 25000 1</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> 25000 2</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> 25000 4</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> 25000 3</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> select count(*),col1 from test_par partition(p5) group by col1;</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> COUNT(*) COL1</span></div><div><span class="Apple-style-span" style="font-size:x-small;">---------- ----------</span></div><div><span class="Apple-style-span" style="font-size:x-small;"> 1 5</span></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div>Now we have the set up ready, we have to enable the trace and check which stats are used when.</div><div><br /></div><div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">Case 1: - Bind peek enable. I use literal value to query table and see which stats are used.</span></span></div><div><br /></div><div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set "_optim_peek_user_binds"=true;</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set events '10053 trace name context forever, level 2';</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> select * from test_par where col1=2 and col2=5;</span></div><div><br /></div><div><br /></div><div>Now checking the trace file, we see:</div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">BASE STATISTICAL INFORMATION</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***********************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Table Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Table: TEST_PAR Alias: TEST_PAR Partition [0] --<span class="Apple-style-span" style="font-weight: bold;"> <span class="Apple-style-span" style="font-style: italic;">Do you see Partition stats getting used?</span></span></span></div><div><span class="Apple-style-span" style="font-style: italic; font-weight: bold;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 100000 #Blks: 149 AvgRowLen: 7.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 100000 #Blks: 149 AvgRowLen: 7.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Index Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Index: IDX02_TEST_PAR Col#: 2 PARTITION [0]</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 1 #LB: 222 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 149.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 1 #LB: 222 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 149.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><br /></div></div></div><div><div>So, with Bind peek enabled - we use partition stat (when we should) when literals are used</div><div><br /></div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">Case 2: Bind peek enable. I use Bind value to query table and see which stats are used.</span></span></div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set "_optim_peek_user_binds"=true;</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set events '10053 trace name context forever, level 2';</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> variable lcol1 number;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> variable lcol2 number;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> execute :lcol1:=2;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> execute :lcol2:=5;</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;</span></div><div><br /></div><div>Trace file:</div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">BASE STATISTICAL INFORMATION</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***********************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Table Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Table: TEST_PAR Alias: TEST_PAR Partition [0] <span class="Apple-style-span" style="font-style: italic; font-weight: bold;">-- Here again, partition stats getting used</span></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 100000 #Blks: 149 AvgRowLen: 7.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 100000 #Blks: 149 AvgRowLen: 7.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Index Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Index: IDX02_TEST_PAR Col#: 2 PARTITION [0]</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 1 #LB: 222 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 149.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 1 #LB: 222 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 149.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><br /></div><div>Since this was the first time, we used this sql, Bind peek had to happen.</div><div><br /></div><div>This is confirmed by following section of trace file:</div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">*******************************************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Peeked values of the binds in SQL statement</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">*******************************************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">kkscoacd</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Bind#0</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> kxsbbbfp=2a973589d8 bln=22 avl=02 flg=05</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> value=2</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Bind#1</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> kxsbbbfp=2a973589f0 bln=22 avl=02 flg=01</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> value=5</span></div><div><br /></div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">CASE 3: Bind peek enabled - I use Bind value to query table and see which stats are used. </span></span>-- This will be 2nd time - so this time i dont expect peeking to happen.</div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set "_optim_peek_user_binds"=true;</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set events '10053 trace name context forever, level 2';</span></div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> variable lcol1 number;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> variable lcol2 number;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> execute :lcol1:=5;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> execute :lcol2:=10;</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;</span></div><div><br /></div><div><br /></div><div>Trace file:</div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">BASE STATISTICAL INFORMATION</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***********************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Table Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Table: TEST_PAR Alias: TEST_PAR Partition [0] <span class="Apple-style-span" style="font-style: italic; font-weight: bold;">-- Part Stats .. again</span></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 100000 #Blks: 149 AvgRowLen: 7.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 100000 #Blks: 149 AvgRowLen: 7.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Index Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Index: IDX02_TEST_PAR Col#: 2 PARTITION [0]</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 1 #LB: 222 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 149.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 1 #LB: 222 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 149.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><br /></div><div><br /></div><div>In this case - we still use old execution path and old stats - which is agony of 10g. </div><div>We had only one row for this partition and we ended up using INDEX access when direct access would be really fast.</div><div>I heard about Adaptive cursor sharing in 11g will avoid this, but still to run tests on it.</div><div><br /></div><div><br /></div><div><br /></div><div>Now we will run the same tests by keeping the bind peeking disabled (as in my environment)</div><div><br /></div><div><br /></div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">CASE 4: Bind peek disabled - we use literals</span></span></div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter system flush shared_pool;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set "_optim_peek_user_binds"=false;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set events '10053 trace name context forever, level 2';</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> select * from test_par where col1=5 and col2=10;</span></div><div><br /></div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">BASE STATISTICAL INFORMATION</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***********************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Table Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Table: TEST_PAR Alias: TEST_PAR Partition [1] <span class="Apple-style-span" style="font-style: italic; font-weight: bold;">-- Picked the right stats of right partition</span></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 1 #Blks: 1 AvgRowLen: 6.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 1 #Blks: 1 AvgRowLen: 6.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Index Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Index: IDX02_TEST_PAR Col#: 2 PARTITION [1]</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 0 #LB: 1 #DK: 1 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 0 #LB: 1 #DK: 1 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><br /></div><div>Literals - as seen knows where to go.</div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">CASE 5: Bind peek disabled - we use binds</span></span></div><div><br /></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> variable lcol1 number;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> variable lcol2 number;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> execute :lcol1:=5;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> execute :lcol2:=10;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set "_optim_peek_user_binds"=false;</span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> alter session set events '10053 trace name context forever, level 2';</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;</span></div><div><br /></div><div>Trace file:</div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">BASE STATISTICAL INFORMATION</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***********************</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Table Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Table: TEST_PAR Alias: TEST_PAR (Using composite stats) <span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-style: italic;"> -- These are global stats </span></span></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> #Rows: 100001 #Blks: 75 AvgRowLen: 7.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">Index Stats::</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> Index: IDX02_TEST_PAR Col#: 2</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> USING COMPOSITE STATS</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;"> LVLS: 1 #LB: 223 #DK: 100000 LB/K: 1.00 DB/K: 1.00 CLUF: 150.00</span></div><div><span class="Apple-style-span" style="font-size:x-small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:x-small;">***************************************</span></div><div><br /></div><div><br /></div><div>So - the conclusion is :</div><div><br /></div><div>When we have BIND peeeking disabled, and with usage of bind values - we are most likely (any one of you could come up where above doesnt hold good) NOT using partiton stats.</div><div><br /></div></div></div></div>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com3tag:blogger.com,1999:blog-2257062243512934022.post-85462017043587621282008-11-13T14:36:00.000+05:302008-11-13T14:42:13.236+05:30Oracle Bitmap indexes - implementation<p class="MsoNormal">Oracle uses BBC (Byte aligned Bitmap Code) 1-sided algorithm to store Bitmap indexes.</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><span class="Apple-style-span" style="font-style: italic;">What is BBC?</span></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Thinking of Bitmap indexes, most of us recall a simple Bitmap Index such as the one mention below:</p><p class="MsoNormal"></p><table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt; mso-yfti-tbllook:480;mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-border-insideh: .5pt solid windowtext;mso-border-insidev:.5pt solid windowtext"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td width="59" valign="top" style="width:44.25pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Columns<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.25pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 1<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.25pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 2<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.25pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 3<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.3pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 4<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.3pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 5<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.3pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 6<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.3pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 7<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.3pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 8<o:p></o:p></p> </td> <td width="59" valign="top" style="width:44.3pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal">Row 9<o:p></o:p></p> </td> </tr> <tr style="mso-yfti-irow:1"> <td width="59" valign="top" style="width:44.25pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">Red<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:2"> <td width="59" valign="top" style="width:44.25pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">Green <o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> </tr> <tr style="mso-yfti-irow:3;mso-yfti-lastrow:yes"> <td width="59" valign="top" style="width:44.25pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">Blue<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.25pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">1<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> <td width="59" valign="top" style="width:44.3pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt"> <p class="MsoNormal"><span style="font-family:Courier;font-size:10.0pt;">0<o:p></o:p></span></p> </td> </tr></tbody></table><br /><p></p><p class="MsoNormal"></p><p class="MsoNormal">Fig: 1 - Table having 9 rows with data distribution shown as 0 and 1.</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">In the above style of storing bits is uncompressed i.e. all 0s and 1s are stored as it is. So, while doing any bitwise operation (and/or/xor), we use the uncompressed version of bitmaps. </p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><span class="Apple-style-span" style="font-style: italic;"><span class="Apple-style-span" style="text-decoration: underline;">Potential drawbacks of using this technique:</span></span></p> <ol style="margin-top:0in" start="1" type="1"> <li class="MsoNormal" style="mso-list:l0 level1 lfo1;tab-stops:list .5in">high storage cost</li> <li class="MsoNormal" style="mso-list:l0 level1 lfo1;tab-stops:list .5in">high query cost</li> </ol> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><i style="mso-bidi-font-style:normal"><span class="Apple-style-span" style="text-decoration: underline;">Can compression be achieved?</span><o:p></o:p></i></p> <p class="MsoNormal">Yes, we can compress 0s and 1s which are consecutive. Think of compressing seven 0s as one with a special header saying that this 0 is equivalent to seven 0s.<span style="mso-spacerun:yes"> </span></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">But this has its own disadvantage. For any bit-wise operation, I have to <i style="mso-bidi-font-style:normal">first decompress</i> bitmap indexes in question and do the “and”/”or” operation, which <i style="mso-bidi-font-style:normal">outweighs any savings in storage costs.<o:p></o:p></i></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">So, the need was for a compress bitmap technique using which bit-wise operations could be done on the compressed state.</p> <p class="MsoNormal"><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-style: italic;">One such example of such techniques is BBC (used by Oracle) – called Byte aligned bitmap code.</span></span></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">This technique of bitmap compression is based on run-length encoding. Using this encoding BITMAPs are aligned along the BYTE boundaries. Each aligned BYTE is called a gap-byte (GBYTE) if all the bits of that BYTE store the same logical value (0 or 1). If it’s a mix of bits having different logical values, its called a non-gap-byte or map-byte (MBYTE). Adjacent bytes of the same class are grouped and controlled by a control byte (CBYTE), which keeps the information whether the bytes following it are MBYTE or GBYTE and if its GBYTE what is the length of bytes? And if it’s a MBYTE, which type of MBYTE (is it an offset or not? – offset is a MBYTE having only single bit different from other bits in that byte)</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">In the above explanation, a gap-byte could be for 0 or for 1. so, it is called <span class="Apple-style-span" style="font-style: italic;">2-sided BBC.</span></p> <p class="MsoNormal"><span class="Apple-style-span" style="font-style: italic;">Oracle uses 1-sided BBC</span>, in which only 0s are candidate for GBYTE not 1s. All 1s will still make an MBYTE in Oracle. I’m not sure why Oracle uses 1-sided BBC technique. It could be due to integration complexities with other part of RDBMS-engine. </p> <p class="MsoNormal">But going back on the question, why do we need Byte alignment when I can compress without it?</p> <ol style="margin-top:0in" start="1" type="1"> <li class="MsoNormal" style="mso-list:l1 level1 lfo2;tab-stops:list .5in">Computers are happy dealing with BYTEs than BITS. </li> <li class="MsoNormal" style="mso-list:l1 level1 lfo2;tab-stops:list .5in">I can do BIT-WISE operations as efficiently (in fact better) as I can do in decompress form.</li> <li class="MsoNormal" style="mso-list:l1 level1 lfo2;tab-stops:list .5in">Control Bytes (CBYTES) are very handy in decoding the compress BITMAP.</li> </ol> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><span class="Apple-style-span" style="font-style: italic;"><span class="Apple-style-span" style="font-weight: bold;">Can this BITMAP compression and query response get better?</span></span><br /><br /></p> <p class="MsoNormal">From what I see after doing research on net, the answer is <span class="Apple-style-span" style="font-weight: bold;">Yes.</span></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><b style="mso-bidi-font-weight:normal"><i style="mso-bidi-font-style: normal">The technique of handling the bits BYTEs way has gotten better by handling the WORD way.<o:p></o:p></i></b></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">The concepts are similar to the ones followed in BBC, the major difference is aligning the bits using “word” rather than “byte”.<span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-style: italic;"> Computers today are more friendly dealing in words (multiple bytes) than on byte.</span></span></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">I will write more on word aligned hybrid compression technique in perspective of oracle in coming days.</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Thanks for reading!</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Inputs: </p> <p class="MsoNormal">Patent# 5363098 – by <span class="apple-style-span"><span style="font-family:Arial;font-size:9.0pt;color:#333333;">Antoshenkov, Gennady</span></span><span class="apple-converted-space"><span style=" ;font-family:Arial;font-size:9.0pt;color:#333333;"> </span> (Oracle corp).<o:p></o:p></span></p><p></p><p class="MsoNormal"><br /></p><p class="MsoNormal"><br /></p>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com1tag:blogger.com,1999:blog-2257062243512934022.post-40942634398488464122008-05-09T14:29:00.000+05:302008-05-09T14:52:15.787+05:30ORA-14097 - when using materialized view and partition tableThis one was an interesting issue which came up few days back. I spent quite sometime before I solved it.<br /><br />Issue was - A developer came upto me and told that he is getting "ORA-14097 - : column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" while exchanging partitions.<br /><br />This guy is working on a warehouse design in which huge data loads will happen on base tables through out the day and there are mviews based on which it mines and these mviews' data should be moved fact tables at midnight 12.<br /><br />Now we strategize in a way that at midnight when base tables will be truncated for fresh load of next day, we exchange the partitions of these mviews with temporary partitioned tables and use "Insert /*+ append */" to move it to final fact tables. We could not have directly exchanged partitions of fact table as they might not be partitioned by day.<br /><br />Now the above is all background. While investigating the issue, I was perplexed for some time when he showed me the issue. He created a temporary partitioned table using "create table as select .. the mview where 1=2" and while doing exchange partition he was getting ORA-14097.<br /><br />Let me give you a simple test case:<br /><br /><span style="font-size:85%;">SQL> create table test(a number);<br /><br />Table created.<br /><br />SQL> create materialized view log on test with rowid including new values;<br /><br />Materialized view log created.<br /><br />SQL> create materialized view test_mv refresh fast on demand with rowid as select * from test;<br /><br />Materialized view created.<br /><br />SQL> insert into test values(1);<br /><br />1 row created.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> exec dbms_mview.refresh('test_mv','F')<br /><br />PL/SQL procedure successfully completed.</span><br /><br />Now both table and mview have on erow each.<br /><br />Let's try and exchange partition of this mview with another table.<br /><br /><span style="font-size:85%;">SQL> select partition_name from user_tab_partitions where table_name='TEST_PART';<br /><br />PARTITION_NAME<br />------------------------------<br />SYS_P3446<br /><br />SQL> alter table test_part exchange partition SYS_P3446 with table test_mv;<br />alter table test_part exchange partition SYS_P3446 with table test_mv<br /> *<br />ERROR at line 1:<br />ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION<br /><br /></span><br />I used CTAS, there is no question abt data type and column order.<br /><br /><span style="font-size:85%;">SQL> desc test_part<br />Name Null? Type<br />----------------------------------------- -------- ----------------------------<br />A NUMBER<br /><br />SQL> desc test_mv<br />Name Null? Type<br />----------------------------------------- -------- ----------------------------<br />A NUMBER<br /></span><br /><br />After doing some research, i got through this metalink article: <a href="https://metalink.oracle.com/metalink/plsql/f?p=130:14:1483823492787880407::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,72332.1,1,1,1,helvetica"><span style="font-family:helvetica;"><strong>72332.1</strong></span></a><br /><br />According to that : "If a table has a FUNCTIONAL index on it there is an extra hidden column in COL$ which will cause an EXCHANGE to fail. Comparing USER_TAB_COLUMNS will not show this, nor will USER_UNUSED_COL_TABS but COL$ will show the difference."<br /><br /><span style="font-size:85%;">SQL> select col#, name<br />2 from sys.col$<br />3 where obj# in<br />4 (select object_id from user_objects where object_name = 'TEST_MV');<br /><br /> COL# NAME<br />---------- ------------------------------<br /> 0 M_ROW$$<br /> 1 A</span><br /><br />Now there you go - this M_ROW$$ was creating the problem for me.<br /><br />Old saying - identifying is problem is 80% of tak that entails solving it.<br /><br />Now - i created test_mv using primary key (not using rowid) and the whole exchange process worked fine!<br /><br />For those who donot have PK in their tables can consider having a separate column which can be seeded using a sequence and treat that as PK to combat this issue.<br /><br />Though it was trivial issue and solution. it kept me thinking for some time!<br /><div style="text-align: left;"><pre style="font-family: arial;"><span><br /><br /></span></pre></div>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com0tag:blogger.com,1999:blog-2257062243512934022.post-42611727923614777232008-05-08T23:14:00.000+05:302008-05-09T00:53:26.825+05:30Query result cache in 11g<span style="font-family:arial;">In 11g its possible to store the result of the SQL in a special section of shared pool called RESULT CACHE.<br /><br />As the name suggests, this cache stores results. This makes subsequent SQLs run damn fast resulting in better performance.<br /><br />To me, this features sounds like Materialized view - because those also when introduced boosted the performance of queries which used to take a lot of time while computing the result. The resultant data of mview was stored in table (i.e. segment/disk). And this case the resultant data is stored in memory. Though memory not being persistent, this feature still helps in subsequent runs of the same query.<br /><br />The feature is broadly governed by init.ora parameter - RESULT_CACHE_MODE - which can be changed using "alter system" or "alter session". The default value of this parameter is MANUAL. You can switch it to FORCE (not recommended).<br /><br />Now after this parameter has taken effect, you can modify your SQL to see its affect.<br /><br />SQL> select /*+ result_cache */ deptno,sum(sal) from emp group by deptno;<br /><br /> DEPTNO SUM(SAL)<br />---------- ----------<br /> 30 9400<br /> 20 10875<br /> 10 8750<br /><br />NOw run this query again - with hint<br />and this time with autotrace traconly explain option:<br /><br />SQL> set autotrace traceonly exp<br />SQL> /<br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 4067220884<br /><br />--------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |<br />| 1 | RESULT CACHE | bw8bd7rpb6h0sg1rcrwyx3rz0x | | | | |<br />| 2 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |<br />| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |<br />--------------------------------------------------------------------------------------------------<br /><br />Here you see data being fetched from result cache.<br /><br />There are some dynamic views which can give more insight.<br /><br />SQL> select table_name from dict where lower(table_name) like '%result%';<br /><br />TABLE_NAME<br />------------------------------<br />DBA_METHOD_RESULTS<br />USER_METHOD_RESULTS<br />ALL_METHOD_RESULTS<br />V$CLIENT_RESULT_CACHE_STATS<br />GV$RESULT_CACHE_DEPENDENCY<br />GV$RESULT_CACHE_MEMORY<br />GV$RESULT_CACHE_OBJECTS<br />GV$RESULT_CACHE_STATISTICS<br />V$RESULT_CACHE_DEPENDENCY<br />V$RESULT_CACHE_MEMORY<br />V$RESULT_CACHE_OBJECTS<br /><br />TABLE_NAME<br />------------------------------<br />V$RESULT_CACHE_STATISTICS<br />GV$CLIENT_RESULT_CACHE_STATS<br />CLIENT_RESULT_CACHE_STATS$<br /><br /><br /><br />I used V$RESULT_CACHE_OBJECTS:<br /><br />1* select id,status,block_count,name from V$RESULT_CACHE_OBJECTS<br />SQL> /<br /><br /> ID STATUS BLOCK_COUNT<br />---------- --------- -----------<br />NAME<br />----------------------------------------------------------------------------<br /><br /> 0 Published 1<br />SCOTT.EMP<br /><br /> 1 Published 1<br />select /*+ result_cache */ deptno,sum(sal) from emp group by deptno<br /><br /><br />Similar to the hint "result_cache" - there is another hint - "no_result_cache"<br /><br />>> no_result_cache is to be used when you have set database parameter: RESULT_CACHE_MODE to FORCE - which means oracle will try to cache the result of all queries.<br /><br />Ex: select /*+ no_result_cache */ deptno,sum(sal) from emp group by deptno<br /><br />To see the memory utlization - there are few dynamic views, database functions available.<br /><br />SQL> set serverout on<br />SQL> exec dbms_result_cache.memory_report<br />R e s u l t C a c h e M e m o r y R e p o r t<br />[Parameters]<br />Block Size = 1K bytes<br />Maximum Cache Size = 3488K bytes (3488 blocks)<br />Maximum Result Size = 174K bytes (174 blocks)<br />[Memory]<br />Total Memory = 136340 bytes [0.064% of the Shared Pool]<br />... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]<br />... Dynamic Memory = 131200 bytes [0.061% of the Shared Pool]<br />....... Overhead = 65664 bytes<br />....... Cache Memory = 64K bytes (64 blocks)<br />........... Unused Memory = 29 blocks<br />........... Used Memory = 35 blocks<br />............... Dependencies = 1 blocks (1 count)<br />............... Results = 34 blocks<br />................... SQL = 34 blocks (34 count)<br /><br />PL/SQL procedure successfully completed.<br /><br />There is again an init.ora parameter governing the size of max result cache size i.e result_cache_max_size<br /><br />This value is also seen in dbms_result_cache.memory_report output in "Maximum Cache Size " header.<br /><br />Conclusion: SQL Result cache seems to be a great feature which can immensly improve the sql performance of many applications SQLs. I wish there are no major bugs in this :)<br /><br />Note: Its just the begining that I have started looking into these features. If you happen to research something which I may have missed, please add to the note.<br /><br /></span>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com0tag:blogger.com,1999:blog-2257062243512934022.post-3017223453585271812008-03-28T15:02:00.000+05:302008-03-28T16:12:58.501+05:30Password is case sensitive now - Oracle 11gGood news for people who always wanted oracle schema passwords to be case sensitive.<br />Oracle has done this in 11g.<br /><br />Just to show an example:<br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">C:\Documents and Settings\sachin.arora>sqlplus "/ as sysdba"</span><br /><br /><span style="font-family:courier new;">SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 28 15:02:56 2008</span><br /><br /><span style="font-family:courier new;">Copyright (c) 1982, 2007, Oracle. All rights reserved.</span><br /><br /><br /><span style="font-family:courier new;">Connected to:</span><br /><span style="font-family:courier new;">Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production</span><br /><span style="font-family:courier new;">With the Partitioning, OLAP, Data Mining and Real Application Testing options</span><br /><br /><span style="font-family:courier new;">SQL> create user test identified by AroRa default tablespace users;</span><br /><br /><span style="font-family:courier new;">User created.</span><br /><br /><span style="font-family:courier new;">SQL> grant create session to test;</span><br /><br /><span style="font-family:courier new;">Grant succeeded.</span><br /><br />SQL> connect test/arora<br />ERROR:<br />ORA-01017: invalid username/password; logon denied<br /><br />SQL> connect test/AroRa<br />Connected.<br /></span><br />With 11g, Oracle has made significant changes in password maintenence algorithm.<br />The password verifier is based on 160 bit SHA-1 hashing algorithm. This empowers Oracle to not only allow case sensitive passwords, but also enable the users to keep a strong password that can include special characters.<br /><br />For pre-11g users, when they migrate to 11g, their password still behave password insensitive. However, i feel its good to take the advantage of this useful feature and change the password.<br /><br />For applications which cant afford to change their passwords for xyzee purpose, Oracle has supplied an init.ora i.e. SEC_CASE_SENSITIVE_LOGON that can be set to true/false.<br />Default value of this parameter is true which means passwords should be case sensitive.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com1tag:blogger.com,1999:blog-2257062243512934022.post-15738867904389243632008-03-27T16:36:00.000+05:302008-03-28T16:12:58.501+05:30Memory parameters in 11g - MEMORY_TARGET and MEMORY_MAX_TARGETFew days back, I was checking metalink and came across few good notes on 11g memory management.<br />Though we are not using 11g in our development environment, i have downloaded a copy of 11g rel1 on my windows machine.<br /><br />Testing out these new paarmeters gave me an insight on how Oracle has improvised its memory management.<br /><br />Oracle has introduced 2 new parameters (along with many others!) - MEMORY_TARGET and MEMORY_MAX_TARGET<br /><br />Using these parameters, you can manage SGA and PGA together rather than managing them separately (using SGA_TARGET, SGA_MAX_SIZE , PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY in 10g)<br /><br />If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_SIZE to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.<br /><br />For instance :<br />If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.<br /><br />If MEMORY_TARGET is set to non zero value:<br /><br /><ul><li>SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.</li><li>SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.</li><li>SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).</li><li>PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).</li></ul><br />With this version, oracle has become smart as in exchanging memory between SGA and PGAs. This is a huge achievement.<br />When starting up, Oracle takes up memory equal to MEMORY_TARGET (or MEMORY_MAX_TARGET if mentioned) from Operating System RAM and manage its reqources within itself.<br />This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.<br /><br /><br />I tested out few of these experiments mentioned above. Here are the results:<br /><br /><span style="font-weight: bold;">Test 1: When Memory_target is 1G</span><br /><br />init.ora parameter<br />> memory_target=1073741824<br />> sga_max_size=0<br /><br /><span style="font-size:85%;">SQL> startup<br />ORACLE instance started.<br /><br />Total System Global Area 640303104 bytes<br />Fixed Size 1335024 bytes<br />Variable Size 226492688 bytes<br />Database Buffers 406847488 bytes<br />Redo Buffers 5627904 bytes<br />Database mounted.<br />Database opened.<br /></span><br />>> It seems around 600M is used for SGA. The decesion of using 600 MB of SGA is of Oracle itself.<br /><br /><span style="font-size:85%;">SQL> sho parameter sga<br /><br />NAME TYPE VALUE<br />------------------------------------ ----------- -------------------<br />lock_sga boolean FALSE<br />pre_page_sga boolean FALSE<br />sga_max_size big integer 612M<br />sga_target big integer 0<br /></span><br /><span style="font-weight: bold;">Test 2: When MEMORY_TARGET is 2g (with no change in any other parameter)</span><br /><br /><span style="font-size:85%;">SQL> alter system set memory_target=2048m scope=spfile;<br /><br />System altered.<br /><br />SQL> startup force<br />ORACLE instance started.<br /><br />Total System Global Area 1288978432 bytes<br />Fixed Size 1339344 bytes<br />Variable Size 226492464 bytes<br />Database Buffers 1052770304 bytes<br />Redo Buffers 8376320 bytes<br />Database mounted.<br />Database opened.<br /><br />SQL> sho parameter sga<br /><br />NAME TYPE VALUE<br />------------------------------------ ----------- ---------------------<br />lock_sga boolean FALSE<br />pre_page_sga boolean FALSE<br />sga_max_size big integer 1232M<br />sga_target big integer 0<br /></span><br /><span style="font-weight: bold;">Test 3: When sga_max_size/sga_target=700M and memory_target=1G</span><br /><br /><span style="font-size:85%;">SQL> startup<br />ORACLE instance started.<br /><br />Total System Global Area 732352512 bytes<br />Fixed Size 1335696 bytes<br />Variable Size 192941680 bytes<br />Database Buffers 532676608 bytes<br />Redo Buffers 5398528 bytes<br />Database mounted.<br />Database opened.<br />SQL> sho parameter sga<br /><br />NAME TYPE VALUE<br />------------------------------------ ----------- ---------------------<br />lock_sga boolean FALSE<br />pre_page_sga boolean FALSE<br />sga_max_size big integer 700M<br />sga_target big integer 700M<br />SQL><br />SQL> sho parameter memory<br /><br />NAME TYPE VALUE<br />------------------------------------ ----------- ---------------------<br />hi_shared_memory_address integer 0<br />memory_max_target big integer 1G<br />memory_target big integer 1G<br />shared_memory_address integer 0<br /></span><br />This time, oracle has acknowledged the SGA_MAX_SIZE parameter.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com12tag:blogger.com,1999:blog-2257062243512934022.post-3950779581179618592008-03-14T14:04:00.000+05:302008-03-14T14:35:03.894+05:30Cost, selectivity and cardinality - all changes in 10g rel2 10053 trace fileFew days back o one of the google groups, there was <a href="http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/f0371d471a3fb776/091f38e9f40e1033?hl=en&lnk=gst&q=index+selectivity#091f38e9f40e1033">issue </a>posted by a gentleman about "index selectivity". Per him the "index selectivity" should be different than what is being shown by the optimizer. He was inquiring whether the procedure he followed to calculate "index selectivity" is correct or not.<br /><br />Here is what he had to say:<br /><br />I don't know how the optimizer has calculated ix_sel of the index<br />IDT_FCMO_TMS.<br />Oracle version: 10.2.0.3.0<br />Any idea?<br /><br /><span style="font-weight: bold;">SQL Statement: </span><br /><span style="font-size:85%;"><span style="font-family:courier new;"> select /* PRUEBA */ count(*) FROM TDTC_CAPACIDAD_MODALIDAD WHERE </span><br /><span style="font-family:courier new;"> CMO_CODTSG = 'HT' AND CMO_CODTMS = 'DBL</span></span><br /><br /><span style="font-weight: bold;">10053 Trace:</span><br /><br /><span style="font-size:85%;"><span style="font-family:courier new;">***************************************</span><br /><span style="font-family:courier new;">BASE STATISTICAL INFORMATION</span><br /><span style="font-family:courier new;">***********************</span><br /><span style="font-family:courier new;">Table Stats::</span><br /><span style="font-family:courier new;"> Table: TDTC_CAPACIDAD_MODALIDAD Alias: TDTC_CAPACIDAD_MODALIDAD</span><br /><span style="font-family:courier new;"> #Rows: 82240 #Blks: 1132 AvgRowLen: 82.00</span><br /><span style="font-family:courier new;">Index Stats::</span><br /><span style="font-family:courier new;"> Index: IDT_FCMO_CHA Col#: 5</span><br /><span style="font-family:courier new;"> LVLS: 1 #LB: 170 #DK: 939 LB/K: 1.00 DB/K: 14.00 CLUF:13425.00</span><br /><span style="font-family:courier new;"> Index: IDT_FCMO_TMS Col#: 34 <----- the index</span><br /><span style="font-family:courier new;"> LVLS: 1 #LB: 214 #DK: 471 LB/K: 1.00 DB/K: 26.00 CLUF:12648.00 <----- the index</span><br /><span style="font-family:courier new;"> Index: IDT_FCMO_TSU Col#: 31</span><br /><span style="font-family:courier new;"> LVLS: 0 #LB: 1 #DK: 58 LB/K: 1.00 DB/K: 6.00 CLUF: 370.00</span><br /><span style="font-family:courier new;"> Index: RDT_PCMO Col#: 1 2</span><br /><span style="font-family:courier new;"> LVLS: 1 #LB: 206 #DK: 82061 LB/K: 1.00 DB/K: 1.00 CLUF:28928.00</span><br /><span style="font-family:courier new;">***************************************</span><br /><span style="font-family:courier new;">SINGLE TABLE ACCESS PATH</span><br /><span style="font-family:courier new;"> Column (#3): CMO_CODTSG(VARCHAR2)</span><br /><span style="font-family:courier new;"> AvgLen: 3.00 NDV: 21 Nulls: 0 Density: 0.047619 <---------- first column of the index</span><br /><span style="font-family:courier new;"> Column (#4): CMO_CODTMS(VARCHAR2)</span><br /><span style="font-family:courier new;"> AvgLen: 5.00 NDV: 387 Nulls: 0 Density: 0.002584 <---------- second column of the index</span><br /><span style="font-family:courier new;"> Table: TDTC_CAPACIDAD_MODALIDAD Alias:</span><br /><span style="font-family:courier new;">TDTC_CAPACIDAD_MODALIDAD</span><br /><span style="font-family:courier new;"> Card: Original: 82240 Rounded: 10 Computed: 10.12 Non Adjusted:</span><br /><span style="font-family:courier new;">10.12</span><br /><span style="font-family:courier new;"> Access Path: TableScan</span><br /><span style="font-family:courier new;"> Cost: 184.60 Resp: 184.60 Degree: 0</span><br /><span style="font-family:courier new;"> Cost_io: 181.00 Cost_cpu: 29454495</span><br /><span style="font-family:courier new;"> Resp_io: 181.00 Resp_cpu: 29454495</span><br /><span style="font-family:courier new;"> Access Path: index (index (FFS))</span><br /><span style="font-family:courier new;"> Index: IDT_FCMO_TMS</span><br /><span style="font-family:courier new;"> resc_io: 36.00 resc_cpu: 15484960</span><br /><span style="font-family:courier new;"> ix_sel: 0.0000e+00 ix_sel_with_filters: 1</span><br /><span style="font-family:courier new;"> Access Path: index (FFS)</span><br /><span style="font-family:courier new;"> Cost: 37.89 Resp: 37.89 Degree: 1</span><br /><span style="font-family:courier new;"> Cost_io: 36.00 Cost_cpu: 15484960</span><br /><span style="font-family:courier new;"> Resp_io: 36.00 Resp_cpu: 15484960</span><br /><span style="font-family:courier new;"> Access Path: index (AllEqRange)</span><br /><span style="font-family:courier new;"> Index: IDT_FCMO_TMS</span><br /><span style="font-family:courier new;"> resc_io: 1.00 resc_cpu: 42971</span><br /><span style="font-family:courier new;"> ix_sel: 0.0021231 ix_sel_with_filters: 0.0021231 <-------------------- wrong ix_sel?</span><br /><span style="font-family:courier new;"> Cost: 1.01 Resp: 1.01 Degree: 1</span><br /><span style="font-family:courier new;"> Best:: AccessPath: IndexRange Index: IDT_FCMO_TMS</span><br /><span style="font-family:courier new;"> Cost: 1.01 Degree: 1 Resp: 1.01 Card: 10.12 Bytes: 0</span><br /><span style="font-family:courier new;">*************************************** </span></span><br /><br />IX_SEL should be 0.047619 * 0.002584 (which is far from what optimizer showed i.e 0.0021231)<br /><br />According to Jonathan lewis book "Cost-Based Oracle Fundamentals", it is well explain in Chapter-4 P 66,67 that in case of select using multiple column index, index selectivity is calculated using the multiplication of individual selectivities of columns.<br />And that is precisely what the person seeking support showed.<br /><br />While testing this result on 10.2.0.2, I found the index selectivity never gets multiplied in case of multicolumn index. In fact optimizer calculates index selectivity based on "distinct keys" of index.In this case (per the trace shown above) it is 471.<br />So the index selectivity comes out to be 1/(DK of index)=1/471=0.0021231, which is what is shown by optimizer.<br /><br />I think, by the time Jonathan's book was published, not all the changes were tested. In fact I visited his blog and some <a href="http://jonathanlewis.wordpress.com/2008/03/11/everything-changes/">couple of finding similar to this posted there</a>.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com0tag:blogger.com,1999:blog-2257062243512934022.post-14187253388718430272008-03-13T11:43:00.000+05:302008-03-13T15:58:31.906+05:30ORA-01115 and ORA-27067 during crash recoveryYesterday, one of our development server got a power outage because of which all our databases running on that server crashed.<br /><br />After the server was brought up, I started these databases manually. One of the db refused to startup with normal "startup" command.<br /><br />Here was the front-end error.<br /><br /><span style="font-size:78%;">SQL> startup<br />ORACLE instance started.<br /><br />Total System Global Area 432013312 bytes<br />Fixed Size 2169944 bytes<br />Variable Size 356115368 bytes<br />Database Buffers 62914560 bytes<br />Redo Buffers 10813440 bytes<br />Database mounted.<br />ORA-01115: IO error reading block from file 2 (block # 575701)<br />ORA-01110: data file 2: '/mnt/user/oracle/data/initdb/undotbs01.dbf'<br />ORA-27067: size of I/O buffer is invalid<br />Additional information: 2<br />Additional information: 1572864<br /></span><br />And here is what I saw in alert.log<br /><br /><span style="font-size:78%;"><span style="font-family:verdana;">ALTER DATABASE OPEN</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:14 2008</span><br /><span style="font-family:verdana;">Beginning crash recovery of 1 threads</span><br /><span style="font-family:verdana;"> parallel recovery started with 2 processes</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:15 2008</span><br /><span style="font-family:verdana;">Started redo scan</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:16 2008</span><br /><span style="font-family:verdana;">Completed redo scan</span><br /><span style="font-family:verdana;"> 130874 redo blocks read, 760 data blocks need recovery</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:16 2008</span><br /><span style="font-family:verdana;">Started redo application at</span><br /><span style="font-family:verdana;"> Thread 1: logseq 49756, block 4723</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:16 2008</span><br /><span style="font-family:verdana;">Recovery of Online Redo Log: Thread 1 Group 1 Seq 49756 Reading mem 0</span><br /><span style="font-family:verdana;"> Mem# 0 errs 0: /mnt/filer15adapp/data/adap_dev/redo1.log</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:16 2008</span><br /><span style="font-family:verdana;">Errors in file /mnt/oracle-dev1/admin/adap_dev/bdump/adap_dev_p001_4721.trc:</span><br /><span style="font-family:verdana;">ORA-01115: IO error reading block from file 2 (block # 575701)</span><br /><span style="font-family:verdana;">ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'</span><br /><span style="font-family:verdana;">ORA-27067: size of I/O buffer is invalid</span><br /><span style="font-family:verdana;">Additional information: 2</span><br /><span style="font-family:verdana;">Additional information: 1409024</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:19 2008</span><br /><span style="font-family:verdana;">Aborting crash recovery due to slave death, attempting serial crash recovery</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:19 2008</span><br /><span style="font-family:verdana;">Beginning crash recovery of 1 threads</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:19 2008</span><br /><span style="font-family:verdana;">Started redo scan</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:20 2008</span><br /><span style="font-family:verdana;">Completed redo scan</span><br /><span style="font-family:verdana;"> 130874 redo blocks read, 760 data blocks need recovery</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:25 2008</span><br /><span style="font-family:verdana;">Aborting crash recovery due to error 1115</span><br /><span style="font-family:verdana;">Wed Mar 12 10:01:25 2008</span><br /><span style="font-family:verdana;">Errors in file /mnt/oracle-dev1/admin/adap_dev/udump/adap_dev_ora_4717.trc:</span><br /><span style="font-family:verdana;">ORA-01115: IO error reading block from file 2 (block # 575701)</span><br /><span style="font-family:verdana;">ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'</span><br /><span style="font-family:verdana;">ORA-27067: size of I/O buffer is invalid</span><br /><span style="font-family:verdana;">Additional information: 2</span><br /><span style="font-family:verdana;">Additional information: 1572864</span><br /><span style="font-family:verdana;">ORA-1115 signalled during: ALTER DATABASE OPEN...</span><br /><span style="font-family:verdana;">Wed Mar 12 10:02:37 2008</span><br /><span style="font-family:verdana;">Shutting down instance: further logons disabled </span><br /><br /><br /><span style="font-size:100%;">This was the first time i got this error in my experience. Db being running in noarchive log mode (this being dev server) just added to my worries.<br /><br />Googling didnt help. After some research work, i encountered a note on metalink <a href="https://metalink.oracle.com/metalink/plsql/f?p=130:14:7668690220961778056::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,423128.1,1,1,1,helvetica"><span style="font-family:helvetica;"><strong>Note:423128.1.</strong></span></a><br /><br />Though they didnt explain the cause of the problem, but suggested the workaround.<br /><br />i.e.<br /><br />1) Mount the database<br />2) Issue "recover database" command<br />-- Wait for "Media recovery complete"<br />3) alter database open<br /><br />I'm s not sure about the cause as this Note on metalink refers to a <a href="https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=5362935">Bug 5362935</a> - "CRASH RECOVERY FAILS WITH ORA-27067 ERROR", which is closed with status "</span><span style="font-family:helvetica;"><span style="font-size:100%;">Suspended, Req'd Info not Avail" and lots of useful information is hidden so people like us, who are not working in Oracle Corp cant see it.</span><br /></span></span>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com1tag:blogger.com,1999:blog-2257062243512934022.post-59282905760763776132008-03-05T17:29:00.000+05:302008-03-05T17:35:32.742+05:30How to pass a value from Oracle to Unix shell environment variable?Though its too trivial, but I thought its not a bad idea to put it here to help the newbies.<br />Following information can be of help when you want to store a table record or some computation in a shell variable.<br /><br />env_variable=`sqlplus username}/{password} <<><span style="font-weight: bold;"><br />set echo off verify off feed off termout off pages 0</span><br />{{SELECT STMT}};<br />exit<br />EOF`<br /><br /><br />For example:<br /><br />-bash-3.00$ DB_USED=`sqlplus -s "/ as sysdba" << EOF<br />> set echo off verify off feed off termout off pages 0<br />> select sum(tot.bytes/1024-sum(nvl(fre.bytes,0))/1024)/3.8<br />> from dba_free_space fre,<br />> (select tablespace_name, sum(bytes) bytes<br />> from dba_data_files<br />> group by tablespace_name) tot,<br />> dba_tablespaces tbs<br />> where tot.tablespace_name = tbs.tablespace_name<br />> and fre.tablespace_name(+) = tbs.tablespace_name<br />> group by tbs.tablespace_name, tot.bytes/1024, tot.bytes<br />> /<br />> exit<br />> EOF`<br /><br />-bash-3.00$ echo $DB_USED<br />4189795.79<br /><br />This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.<br /><br /><br /></eof></eof>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com2tag:blogger.com,1999:blog-2257062243512934022.post-31995865796637137602008-02-22T12:02:00.000+05:302008-02-22T12:48:50.502+05:30How to rename Materialized view? ORA-32318Now - this is what you call a long break .. almost a year. Well - The personal life has been rocking all during last year and I have good news to share, I'm a proud father of a cute little girl. We are still searching a name for her. That is some relief in Hindu religion that we can take up to some days before finalizing the name of baby.<br /><br />I hope to be more punctual in writing my experiences here on the blog.<br />Today, I have something on materialized views. How to rename them?<br /><br />Last week, I had this requirement to rename a materialized view and initial search on how to the same landed me on to <a href="http://ora-32318.ora-code.com/">ORA-32318</a>.<br /><br />ORA-32318: cannot rename a materialized view<br />Cause: Renaming a materialized view or its base table is not supported.<br />Action: Do not rename the base table of a materialized view.<br /><br />Now, this is what I dont expect Oracle to do. when rename of table is possible, then why not rename of mat view possible? If any one knows a logical reason, please share.<br /><br />Anyways, I gave it a further thought on how to accomplish this and an idea struck me.<br /><br />I'm giving the information of our system but with changed names.<br />DB1 - where base table exists - 10.2.0.2<br />DB2 - where mview exists - 10.2.0.2<br />T1 - name of the table on DB1<br />M1 - name of the mview - Fast/Incremental refresh on demand<br /><br />Now as discussed the requirement is to convert M1 to M2, with least down-time (at least for selects).<br /><br />Here is some test setup scripts:<br /><br /><span style="font-weight: bold;">On DB1:</span><br /><br />SQL> create table t1 as select * from all_objects;<br /><br />Table created.<br /><br />SQL> alter table t1 add primary key (object_id);<br /><br />Table altered.<br /><br />SQL> create materialized view log on t1;<br /><br />Materialized view log created.<br /><br /><span style="font-weight: bold;">On DB2:</span><br /><br /><br />SQL> create materialized view m1 refresh fast on demand as select * from t1@db1; - db1 is database link<br /><br />Materialized view created.<br /><br />SQL> select count(*) from m1;<br /><br />COUNT(*)<br />----------<br /> 62551<br /><br />Now steps to change the name:<br /><br />Step1: On DB1<br />- Stop DML/DDL activities on the base table T1<br /><br />Step2: On DB2<br />- Refresh the M1 mview to ensure that there are no changes in there, so we can plan to change the name.<br />- Create another table with new name. This new name should be the name of mview intended.<br /><br />SQL> create table m2 as select * from m1;<br /><br />Table created.<br /><br />>> Well this can help you in moving non-partition base-table/mview to partitioned base-table/mview<br /><br />Step3: On DB2<br /><br />Create new materialized view using "prebuilt" option.<br /><br />SQL> create materialized view m2 ON PREBUILT TABLE REFRESH fast on demand as select * from t1@db1;<br /><br />Materialized view created.<br /><br />Step4: On DB2<br /><br />Drop the old materialized view.<br /><br />SQL> drop materialized view m1;<br /><br />Materialized view dropped.<br /><br />Thats it!<br /><br />Now to check if thats working.<br /><br />Delete few rows on t1@db1<br /><br />SQL> delete from t1 where object_id in (2,3);<br /><br />2 rows deleted.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />>> And now refresh the new mview.<br /><br />SQL> exec dbms_mview.refresh('M2')<br /><br />PL/SQL procedure successfully completed.<br /><br />>> Check the no of rows:<br />SQL> select count(*) from m2;<br /><br />COUNT(*)<br />----------<br /> 62549<br /><br />Finally check the way oracle refreshed this mview.<br /><br /><br />SQL> select LAST_REFRESH_TYPE from user_mviews where MVIEW_NAME='M2';<br /><br />LAST_REF<br />--------<br />FAST<br /><br /><br />Well that's just a confirmation :)<br /><br />Note: I havent tested other functionalities of this test. So please do a thorough testing of this code before pushing into production database system.<br />BTW - we are doing the same.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com3tag:blogger.com,1999:blog-2257062243512934022.post-23495433638893241242007-03-23T14:31:00.000+05:302007-03-23T15:48:03.263+05:30Db file sequential read while doing full table scan?<p class="MsoNormal">These days, we are working on data warehouse in which we have a master table which will have 1.5m (approx) rows inserted every half hour and we have few fast refresh materialized view based on it. These mviews have some aggregate functions on it, which makes it a bit complex. </p> <p class="MsoNormal"><o:p> </o:p><br />To start the experiment, each mview refreshes used to take some 18-20 mins, which is totally against the business requirement. Then we tried to figure out on why the mview refresh is taking so much time, in spite of dropping all the bitmap indexes on the mview (generally b-map indexes are not good for inserts/updates).<br /></p> <p class="MsoNormal"> </p> <p class="MsoNormal">The 10046 trace (level 12) highlighted that there were many <i style="">“db file sequential reads”</i> on mview because of optimizer using “I_SNAP$_mview” to fetch the rows from mview and merge the rows with that of master table to make the aggregated data for the mview.</p> <p class="MsoNormal">Good part of the story is access to master table was quite fast because we used direct load (using sqlldr direct=y) to insert the data in it. When you use direct load to insert the data, oracle maintains the list of rowids added to table in a view called “SYS.ALL_SUMDELTA”. So while doing fast mview refresh, news rows inserted are picked directly from table using the rowids given from ALL_SUMDELTA view and not from Mview log, so this saves time.</p> <p class="MsoNormal">Concerned part was still Oracle using I_SNAP$ index while fetching the data from mview and there were many “db file sequential read” waits and it was clearly visible that Oracle waited on sequential read the most. We figured it out that full table scan (which uses scattered read, and multi block read count) was very fast in comparison to index access by running simple test against table. Also the tables are dependent mviews are only for the day. End of the day the master table and mview’s data will be pushed to historical tables and master table and mviews will be empty post midnight.</p> <p class="MsoNormal">I gathered the stats of mview and then re-ran the mview refresh, and traced the session, and this time optimizer didn’t use the index which was good news.</p> <p class="MsoNormal">Now the challenge was to run the mview stats gathering job every half an hour or induce wrong stats to table/index to ensure mview refresh never uses index access or may be to lock the stats using DBMS_STATS.LOCK_TABLE_STATS. </p> <p class="MsoNormal">But we found another solution by creating the mview with “USING NO INDEX” clause. This way “I_SNAP$” index is not created with “CREATE MATERIALIZED VIEW’ command. As per Oracle the “I_SNAP$” index is good for fast refresh but it proved to be reverse for us because our environment is different and the data changes is quite frequent.</p> <p class="MsoNormal">Now, we ran the tests again, we loaded 48 slices of data (24 hrs x 2 times within hour) and the results were above expectations. We could load the data with max 3 mins per load of data.</p> <p class="MsoNormal">This is not the end of story. In the trace we could see the mview refresh using “MERGE” command and using full table scan access to mview (which we wanted) and rowid range access to master table.</p> <p class="MsoNormal">The explain plan looks like:</p><br /><p>Rows Row Source Operation<br /> ------- ---------------------------------------------------<br /> 2 MERGE SF_ENV_DATA_MV (cr=4598 pr=5376 pw=0 time=47493463 us)<br /> 263052 VIEW (cr=3703 pr=3488 pw=0 time=24390284 us)<br /> 263052 HASH JOIN OUTER (cr=3703 pr=3488 pw=0 time=24127224 us)<br /> 263052 VIEW (cr=1800 pr=1790 pw=0 time=14731732 us)<br /> 263052 SORT GROUP BY (cr=1800 pr=1790 pw=0 time=14205624 us)<br /> 784862 VIEW (cr=1800 pr=1790 pw=0 time=3953958 us)<br /> 784862 NESTED LOOPS (cr=1800 pr=1790 pw=0 time=3169093 us)<br /> 1 VIEW ALL_SUMDELTA (cr=9 pr=0 pw=0 time=468 us)<br /> 1 FILTER (cr=9 pr=0 pw=0 time=464 us)<br /> 1 MERGE JOIN CARTESIAN (cr=9 pr=0 pw=0 time=459 us)<br /> 1 NESTED LOOPS (cr=6 pr=0 pw=0 time=99 us)<br /> 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=56 us)<br /> 1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=23 us)(object id 36)<br /> 1 TABLE ACCESS CLUSTER USER$ (cr=3 pr=0 pw=0 time=40 us)<br /> 1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=7 us)(object id 11)<br /> 1 BUFFER SORT (cr=3 pr=0 pw=0 time=354 us)<br /> 1 INDEX RANGE SCAN I_SUMDELTA$ (cr=3 pr=0 pw=0 time=243 us)(object id 158)<br /> 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)<br /> 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)<br /> 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)<br /> 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)<br /> 784862 TABLE ACCESS BY ROWID RANGE SF_ENV_SLICE_DATA (cr=1791 pr=1790 pw=0 time=2383760 us)<br /> 708905 MAT_VIEW ACCESS FULL SF_ENV_DATA_MV (cr=1903 pr=1698 pw=0 time=6387829 us)</p><br /><br /><span style="font-size:85%;"><span style="font-size:78%;"></span></span><br /><p class="MsoNormal">You can see the access pattern as above.</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Interesting twist in the story is when I saw the wait events in trace file.</p><br /><br /><p> Event waited on Times Max. Wait Total Waited<br /> ---------------------------------------- Waited ---------- ------------<br /> db file sequential read 2253 0.74 7.73<br /> db file scattered read 240 1.05 6.77<br /> log file switch completion 6 0.98 3.16<br /> log file switch 8 0.98 2.47<br /> rdbms ipc reply 6 0.00 0.00<br /> log buffer space 3 0.42 0.61</p><br /></p><br /><p class="MsoNormal">Again, even when we are doing full table scan, there are “db file sequential reads”?</p> <p class="MsoNormal">To confirm I opened the raw trace file (before tkprof), and checked the obj# on sequential read wait event, it was the mview (SF_ENV_DATA_MV) !! and there were many. To further investigate I checked if there were any scattered reads to mview or not. I found there were scattered reads but there were many sequential reads also on which Oracle waited more than that of scattered read which did most of the data fetching.</p> <p class="MsoNormal">After giving some thought, I realized that we created the mviews without storage clause, which means Oracle created the mview with default storage clause.</p> <p class="MsoNormal"><o:p> </o:p> </p> <p class="MsoNormal">So assuming there are 17 blocks in an mview (container table) extent and Multi block read count is 16, Oracle will use scattered read mechanism (multiple blocks) to read the first 16 blocks and for the rest 1 it will use sequential read mechanism (one block), so you will find many sequential reads wait events sandwiched between scattered reads.<span style="font-weight: bold;"> </span><i style=""><span style="font-weight: bold;">To overcome this we created the mview with larger extent sizes and also multiple of MBCR (multi block read count).</span><o:p></o:p></i></p> <p class="MsoNormal">Also, another cause of sequential read is chained or migrated rows, if your mview (or table) rows are migrated, the pointer to the next row is maintained in old (original) block, which will always<span style=""> </span>be read by a single i/o call i.e. by sequential read.You can check the count of chained rows using DBA_TABLES.CHAIN_CNT after analysing the table .<span style="font-weight: bold;"> </span><i style=""><span style="font-weight: bold;">So to overcome this, we created the mview with genuine pctfree so when the merge runs (as a part of mview refresh) and updates few rows, the rows are not moved to a different block and hence avoiding sequential read.</span><o:p></o:p></i></p> <p class="MsoNormal">Conclusion: </p> <ol style="margin-top: 0in;" start="1" type="1"> <li class="MsoNormal" style="">Mview creation with “USING NO INDEX” does not create “I_SNAP$” index which sometimes help in fast refresh when the data changes are quite frequent and you cannot afford to collect stats after every few mins.</li><li class="MsoNormal" style="">Create mview with storage clause suiting to your environment. Default extent sizes may not be always good. </li><li class="MsoNormal" style="">PCTFREE can be quite handy to avoid sequential reads and extra block read.</li> </ol> <p class="MsoNormal"><o:p> </o:p></p>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com5tag:blogger.com,1999:blog-2257062243512934022.post-63340859562226883802007-03-03T16:43:00.000+05:302007-03-08T13:57:56.969+05:30Optimizer_mode – ALL_ROWS or FIRST_ROWS?<p class="MsoNormal">Out of all Oracle RDBMS modules, optimizer code is actually the most complicated code and different optimizer modes seem like jack while lifting your car in case of a puncture.</p> <p class="MsoNormal">This paper focuses on how optimizer behaves differently when you have optimizer mode set to ALL_ROWS or FIRST_ROWS.</p> <p class="MsoNormal">Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]</p> <p class="MsoNormal">By default, the value of optimizer_mode is CHOOSE which basically means ALL_ROWS (if statistics on underlying tables exist) else RULE (if there are no statistics on underlying tables). So it is very important to have statistics collected on your tables on regular intervals or else you are living in Stone Age.</p> <p class="MsoNormal">FIRST_ROWS and ALL_ROWS are both cost based optimizer features. You may use them according to their requirement.</p> <p class="MsoNormal"><b style=""><i style="">FIRST_ROWS/ FIRST_ROWS[n<u>]<o:p></o:p></u></i></b></p> <p class="MsoNormal">In simple terms it ensures best response time of first few rows (n rows).</p> <p class="MsoNormal">This mode is good for interactive client-server environment where server serves first few rows and by the time user scroll down for more rows, it fetches other. So user feels that he has been served the data he requested, but in reality the request is still pending and query is still fetching the data in background.</p> <p class="MsoNormal">Best example for this is toad, if you click on data tab, it instantaneously start showing you data and you feel toad is faster than sqlplus, but the fact is if you scroll down, you will see the query is still running.</p> <p class="MsoNormal">Ok, let us simulate this on SQLPLUS</p> <p class="MsoNormal"><i style=""><u>Create a table and index over it:<o:p></o:p></u></i></p><br />SQL> create table test as select * from all_objects;<br /><br />Table created.<br /><br />SQL> create index test_in on test(object_type);<br /><br />Index created.<br /><br />SQL> exec dbms_stats.gather_table_stats(‘SAC’,'TEST')<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select count(*) from test;<br /><br />COUNT(*)<br />----------<br />37944<br /><br />SQL> select count(*) from test where object_type='JAVA CLASS';<br /><br />COUNT(*)<br />----------<br />14927<br /><br /><p class="MsoNormal">You see out of almost 38k records, 15k are of JAVA class. And now if you select the rows having object_type=’JAVA_CLASS’, it should not use index as almost half of the rows are JAVA_CLASS. It will be foolish of optimizer to read the index first and then go to table.</p> <p class="MsoNormal"><i style=""><u>Check out the Explain plans<o:p></o:p></u></i></p><br /><br />SQL> set autotrace traceonly exp<br />SQL> select * from test where object_type='JAVA CLASS';<br /><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 1357081020<br /><br />--------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1001 | 94094 | 10 (0)| 00:00:01 |<br />|* 1 | TABLE ACCESS FULL| TEST | 1001 | 94094 | 10 (0)| 00:00:01 |<br />--------------------------------------------------------------------------<br /><br /><p class="MsoNormal">As you see above, optimizer has not used Index we created on this table.</p> <p class="MsoNormal"><i style=""><u>Now use FIRST_ROWS hint:<o:p></o:p></u></i></p><br />SQL> select /*+ FIRST_ROWS*/ * from test where object_type='JAVA CLASS';<br /><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 3548301374<br /><br />---------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />---------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 14662 | 1345K| 536 (1)| 00:00:07 |<br />| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 14662 | 1345K| 536 (1)| 00:00:07 |<br />|* 2 | INDEX RANGE SCAN | TEST_IN | 14662 | | 43 (3)| 00:00:01 |<br />---------------------------------------------------------------------------------------<br /><br /><p class="MsoNormal">In this case, optimizer has used the index. </p> <p class="MsoNormal">Q> Why?<br /></p> <p class="MsoNormal">Ans> Because you wanted to see first few rows quickly. So, following your instructions oracle delivered you first few rows quickly using index and later delivering the rest.</p> <p class="MsoNormal"> </p> <p class="MsoNormal">See the difference in cost, although the response time (partial) of second query was faster but resource consumption was high.</p> <p class="MsoNormal">But that does not mean that this optimizer mode is bad. As I said this mode may be good for interactive client-server model. In most of OLTP systems, where users want to see data fast on their screen, this mode of optimizer is very handy.</p> <p style="font-style: italic;" class="MsoNormal">Important facts about FIRST_ROWS<br /></p> <p style="font-style: italic;" class="MsoNormal"> </p> <ol> <li>It gives preference to Index scan Vs Full scan (even when index scan is not good).</li> <li>It prefers nested loop over hash joins because nested loop returns data as selected (& compared), but hash join hashes one first input in hash table which takes time.</li> <li>Cost of the query is not the only criteria for choosing the execution plan. It chooses plan which helps in fetching first rows fast.</li> <li>It may be a good option to use this in an OLTP environment where user wants to see data as early as possible.</li> </ol> <p class="MsoNormal"> </p> <p class="MsoNormal"><b style=""><i style=""><br />ALL_ROWS<o:p></o:p></i></b></p> <p class="MsoNormal">In simple terms, it means better throughput</p> <p class="MsoNormal">While FIRST_ROWS may be good in returning first few rows, ALL_ROWS ensures the optimum resource consumption and throughput of the query. In other words, ALL_ROWS is better to retrieve the last row first.</p> <p class="MsoNormal">In above example while explaining FIRST_ROWS, you have already seen how efficient ALL_ROWS is.</p> <p style="font-weight: bold; font-style: italic;" class="MsoNormal">Important facts about ALL_ROWS<br /></p> <p style="font-weight: bold; font-style: italic;" class="MsoNormal"> </p> <ol> <li>ALL_ROWS considers both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example ‘where employee_code=7712’), but if selectivity of column is quite high ('where deptno=10'), optimizer may consider doing Full table scan. With ALL_ROWS, optimizer has more freedom to its job at its best.</li> <li>Good for OLAP system, where work happens in batches/procedures. (While some of the report may still use FIRST_ROWS depending upon the anxiety level of report reviewers)</li> <li>Likes hash joins over nested loop for larger data sets. </li> </ol><br /><span style="font-weight: bold;">Conclusion<br /><br /></span>Cost based optimizer gives you flexibility to choose response time or throughput. So use them based on your business requirement.<span style="font-weight: bold;"><br /></span>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com24tag:blogger.com,1999:blog-2257062243512934022.post-59854188382887650882007-03-02T20:17:00.000+05:302007-03-03T17:03:53.397+05:30Nested loops, Hash join and Sort Merge joins – difference?<p class="MsoNormal"><b style=""><u>Nested loop (loop over loop)<o:p></o:p></u></b></p> <p class="MsoNormal"><o:p></o:p>In this algorithm, an outer loop is formed which consists of few entries and then for each entry, and inner loop is processed.</p> <p class="MsoNormal"><o:p></o:p>Ex:</p> <p class="MsoNormal">Select tab1.*, tab2.* from tabl, tab2 where tabl.col1=tab2.col2;</p> <p class="MsoNormal"><o:p> </o:p>It is processed like:</p> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;"><o:p></o:p>For i in (select * from tab1) loop<br />For j in (select * from tab2 where col2=i.col1) loop<br />Display results;<br />End loop;<br />End loop;<br /></p> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;">The Steps involved in doing nested loop are:<span style=""><br /></span></p> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;"><span style="">a)<span style=""> </span></span><!--[endif]-->Identify outer (driving) table</p> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;"><!--[if !supportLists]--><span style=""> b)<span style=""> </span></span><!--[endif]-->Assign inner (driven) table to outer table.</p> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;"><!--[if !supportLists]--><span style=""> c)<span style=""> </span></span><!--[endif]-->For every row of outer table, access the rows of inner table.</p> <p class="MsoNormal">In execution plan it is seen like this:</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">NESTED LOOPS<br />outer_loop<br />inner_loop</p> <p style="font-style: italic;" class="MsoNormal">When optimizer uses nested loops?</p> <p class="MsoNormal">Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving condition. It is important to have an index on column of inner join table as this table is probed every time for a new value from outer table.</p> <p class="MsoNormal"><o:p> </o:p>Optimizer may not use nested loop in case:<span style=""><br /></span></p><span style=""><span style=""></span></span><!--[endif]--> <ol> <li>No of rows of both the table is quite high</li> <li>Inner query always results in same set of records</li> <li>The access path of inner table is independent of data coming from outer table.</li> </ol> <!--[endif]--> <p class="MsoNormal"><i style=""><u>Note</u>: You will see more use of nested loop when using FIRST_ROWS optimizer mode as it works on model of showing instantaneous results to user as they are fetched. There is no need for selecting caching any data before it is returned to user. In case of hash join it is needed and is explained below.<br /></i></p> <p class="MsoNormal"> </p> <p class="MsoNormal"><b style=""><u>Hash join<o:p></o:p></u></b></p> <p class="MsoNormal">Hash joins are used when the joining large tables.<span style=""> </span>The optimizer uses smaller of the 2 tables to build a hash table in memory and the scans the large tables and compares the hash value (of rows from large table) with this hash table to find the joined rows.</p> <p class="MsoNormal">The algorithm of hash join is divided in two parts</p><!--[if !supportLists]--><!--[endif]--> <ol> <li><b style="">Build</b> a in-memory hash table on smaller of the two tables.</li> <li><b style="">Probe</b> this hash table with hash value for each row second table</li> </ol> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;"><!--[endif]--></p> <p class="MsoNormal"> </p> <p class="MsoNormal">In simpler terms it works like</p> <p class="MsoNormal"><i style=""><u>Build phase<o:p></o:p></u></i></p> <p class="MsoNormal">For each row RW1 in small (left/build) table loop<br />Calculate hash value on RW1 join key<br />Insert RW1 in appropriate hash bucket.<br />End loop;<br /></p> <p class="MsoNormal"> </p> <p class="MsoNormal"><i style=""><u>Probe Phase<o:p></o:p></u></i></p> <p class="MsoNormal">For each row RW2 in big (right/probe) table loop<br /><span style=""> </span> Calculate the hash value on RW2 join key<br /><span style=""> </span> For each row RW1 in hash table loop<br /> If RW1 joins with RW2<br /> Return RW1, RW2<br />End loop;<br />End loop;<br /></p> <p class="MsoNormal"> </p> <p style="font-style: italic;" class="MsoNormal">When optimizer uses hash join?<br /></p> <p style="font-style: italic;" class="MsoNormal"> </p> <p class="MsoNormal">Optimizer uses has join while joining big tables or big fraction of small tables.</p> <p class="MsoNormal">Unlike nested loop, the output of hash join result is not instantaneous as hash joining is blocked on building up hash table.<br /></p> <p class="MsoNormal"> </p> <p class="MsoNormal"><i style=""><u>Note: </u>You may see more hash joins used with ALL_ROWS optimizer mode, because it works on model of showing results after all the rows of at least one of the tables are hashed in hash table.<o:p></o:p></i></p> <p class="MsoNormal"><b style=""><u>Sort merge join<o:p></o:p></u></b></p> <p class="MsoNormal">Sort merge join is used to join two independent data sources. They perform better than nested loop when the volume of data is big in tables but not as good as hash joins in general.</p> <p class="MsoNormal">They perform better than hash join when the join condition columns are already sorted or there is no sorting required.</p> <p class="MsoNormal">The full operation is done in two parts:</p> <ul> <li><!--[if !supportLists]--><i style="">Sort join operation<o:p></o:p></i></li> </ul> <p class="MsoNormal" style="margin-left: 0.5in;">get first row RW1 from input1<br />get first row RW2 from input2.<br /><i style=""><span style=""></span></i></p> <p class="MsoNormal" style="margin-left: 0.5in;"><i style=""><br /></i></p> <ul> <li><i style="">Merge join operation<o:p></o:p></i></li> </ul> <p class="MsoNormal" style="margin-left: 0.5in;"><o:p></o:p>while not at the end of either input loop<br /><span style=""> </span> if RW1 joins with RW2<br /><span style=""> </span> get next row R2 from input 2<br /><span style=""> </span> return (RW1, RW2)<br /><span style=""> </span> else if RW1 < style=""> get next row RW1 from input 1<br /><span style=""> </span> else<br />get next row RW2 from input 2<br /><span style=""> </span>end loop</p> <p class="MsoNormal">Note: If the data is already sorted, first step is avoided.</p> <p class="MsoNormal">Important point to understand is, unlike nested loop where driven (inner) table is read as many number of times as the input from outer table, in sort merge join each of the tables involved are accessed at most once. So they prove to be better than nested loop when the data set is large.</p> <p style="font-style: italic;" class="MsoNormal">When optimizer uses Sort merge join?</p> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;"><!--[if !supportLists]--><span style="">a)<span style=""> </span></span><!--[endif]-->When the join condition is an inequality condition (like <, <=, >=). This is because hash join cannot be used for inequality conditions and if the data set is large, nested loop is definitely not an option.</p> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;"><!--[if !supportLists]--><span style="">b)<span style=""> </span></span><!--[endif]-->If sorting is anyways required due to some other attribute (other than join) like “order by”, optimizer prefers sort merge join over hash join as it is cheaper.<br /></p> <p class="MsoNormal" style="margin-left: 0.5in; text-indent: -0.25in;"> </p> <p class="MsoNormal"><i style="">Note: Sort merge join can be seen with both ALL_ROWS and FIRST_ROWS optimizer hint because it works on a model of first sorting both the data sources and then start returning the results. So if the data set is large and you have FIRST_ROWS as optimizer goal, optimizer may prefer sort merge join over nested loop because of large data. And if you have ALL_ROWS as optimizer goal and if any inequality condition is used the SQL, optimizer may use sort-merge join over hash join<o:p></o:p></i></p> <p class="MsoNormal"> </p>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com42tag:blogger.com,1999:blog-2257062243512934022.post-84221506750729152762007-02-27T12:28:00.000+05:302007-02-27T12:38:03.950+05:3010g new background processesWith 10g many new background processes were introduced.<br />This note highlights those.<br /><br /> <p class="MsoNormal"><u>MMAN</u><br /><br />Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.</p> <p class="MsoNormal"><br /> <!--[if !supportLineBreakNewLine]--><br /> <!--[endif]--></p> <p class="MsoNormal"><u>RVWR<br /></u><br />Process responsible for writing flashback logs which stores pre-image of data-blocks.</p> <p class="MsoNormal">These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><br /><br /><u>CTWR <o:p></o:p></u></p> <p class="MsoNormal">Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.</p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><u>MMNL</u></p> <p class="MsoNormal"><u> </u>The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)<u><o:p></o:p></u></p> <p class="MsoNormal"><br /><br /><br /><u>MMON<o:p></o:p></u></p> <p class="MsoNormal">The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.<u><br /></u><br /><br /><u>M000</u></p> <p class="MsoNormal">MMON background slave (m000) processes. <br /> <!--[if !supportLineBreakNewLine]--><br /> <!--[endif]--></p> <p class="MsoNormal"><br /><u>RBAL<br /></u><br />RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.<br /><br /><u>ARBx<br /></u><br />These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.<br /><br /><u>ASMB<br /></u><br />The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.<u><o:p></o:p></u></p> <br />For more detailed info check following note on metalink:<br /><br /><small><a href="https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=48681.1">Server Architecture on UNIX and NT</a><br /></small>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com1tag:blogger.com,1999:blog-2257062243512934022.post-88524260624400546882007-02-15T17:14:00.000+05:302007-02-19T14:05:32.353+05:30Size your Undo tablespaceWhat should be the size of UNDO tablespace? Generally this question is vaguely answered saying that sizing comes with experience (of DBA) or looking at load on server or sometimes even by the number of ORA-1555 or out of space errors.<br /><br />This paper is to help DBA’s in calculating the size of UNDO tablespace by using a simple formula.<br /><br />While designing an application, generally it is tough to know about the number of transactions and subsequently number of rows changed per second.<br />So I suggest having a “big undo tablespace” to start with and based on load, after doing some calculations and resize your UNDO tablespace.<br /><br />In my case one of the applications was going to production (live), and I had no idea that how many transactions will happen against this database. All what I was told that there will be optimum (transactional) activity on this database. The word “optimum” itself is vague.<br /><br />So I started with UNDO tablespace with size of 2GB and datafiles with autoextend “on” .<br /><br />Note:<br />In production, you must be very careful in using this (autoextend on) as the space may grow to inifinity very fast. So my advice is either dont use this option, or use with "maxsize" or continuously monitor space (which is tough).<br /><br />I month later, I noticed the activity from V$undostat.<br /><br />Here is the step by step approach:<br /><br /><strong>Step 1: Longest running query.<br /></strong><br />SQL> select max(maxquerylen) from v$undostat;<br /><br /><br />MAX(MAXQUERYLEN)<br />----------------<br />1793<br /><br />This gives you ideal value for UNDO_RETENTION. To be on the safer size you should add few more seconds to get the right value. So in my case, the size of undo retention should be say 2000 secs.<br /><br /><strong>Step 2: Size of UNDO tablespace.</strong><br /><br />Size of UNDO needed = UNDO_RETENTION x [UNDO block Generation per sec x DB_BLOCK_SIZE] + Overhead(30xDB_BLOCK_SIZE)<br /><br />Out of these we know UNDO_RETENTION and DB_BLOCK_SIZE<br /><br />All we need is to find out “UNDO Blocks per second”<br /><br />Which can be easily fetched from v$undostat<br /><br />SQL> SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 24*60*60) "UPS"<br />2 FROM v$undostat;<br /><br /><br />UPS<br />------------------------------<br />8.11985583<br /><br />V$undostat stores data for every 10 mins and begin/end times are start/end time of those intervals. We multiplied it with 24*60*60 because the difference between two dates will be in days and to get to seconds, we need it to multiply with 24hrs*60mins*60secs<br /><br />So now we have all the values needed.<br /><br /><strong>Undo size needed = [8.12 x 2000 x 8192] + [30 x 8192] = 133283840 bytes = 127.11 MB</strong>Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com9tag:blogger.com,1999:blog-2257062243512934022.post-73872738320458291402007-02-07T13:42:00.000+05:302007-02-07T14:47:37.545+05:30Identify and fix table fragmentation in Oracle 10g - how?<strong>Table fragmentation – when?</strong><br /><br />If a table is only subject to inserts, there will not be any fragmentation.<br />Fragmentation comes with when we update/delete data in table.<br />The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.<br /><br />To understand it more clearly, we need to be clear on how oracle manages space for tables.<br /><br />“High water mark” of table actually defines the border line between <em>(ever)</em> used and unused <em>(never)</em> space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.<br /><br />Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.<br /><br />SQL> create table test as select * from dba_tables; -- <strong>Create a table</strong><br /><br />Table created.<br /><br />SQL> analyze table test compute statistics; -- <strong>Analyze it<br /></strong><br />Table analyzed.<br /><br />SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"<br />2 from user_tables where table_name='TEST'; -- <strong>The number of blocks used/free<br /></strong><br />Ever Used Never Used Total rows<br />---------- ---------- ----------<br />49 6 1680<br /><br />SQL> delete from test where owner='SYS'; --- <strong>Im deleting almost half the number of rows.<br /></strong><br />764 rows deleted.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> analyze table test compute statistics; -- <strong>Analyze it again</strong><br /><br />Table analyzed.<br /><br />SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"<br /><br />2 from user_tables where table_name='TEST'; -- <strong>No difference in blocks usage<br /></strong><br />Ever Used Never Used Total rows<br />---------- ---------- ----------<br />49 6 916<br /><br />PL/SQL procedure successfully completed.<br /><br />Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.<br /><br /><br /><strong><u>Reasons to reorganization</u></strong><br /><strong><br /></strong>a) Slower response time (from that table)<br />b) High number of chained (actually migrated) rows. <br />c) Table has grown many folds and the old space is not getting reused.<br /><br />Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.<br /><br /><u><strong>How to reorganize?</strong> </u><br /><br />Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table<br />a) Export, drop, and import.<br />b) Alter table <name>move (to another tablespace, or same tablespace).<br /><br /><em>Oracle 10g provides us a new way of reorganizing the data.</em><br /><br />Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.<br /><br />Before using this command, you should have row movement enabled.<br /><br />SQL> alter table test enable row movement;<br /><br />Table altered.<br /><br />There are 2 ways of using this command.<br /><br /><strong><u>1. Break in two parts</u></strong>: In first part rearrange rows and in second part reset the HWM.<br /><br /><em>Part 1:</em> Rearrange (All DML's can happen during this time)<br /><br />SQL> alter table test shrink space compact;<br /><br />Table altered.<br /><br /><em>Part 2:</em> Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)<br /><br />SQL> alter table sa shrink space;<br /><br />Table altered.<br /><br /><strong><u>2. Do it in one go:</u></strong><br /><strong><u></u></strong><br />SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)<br /><br />Table altered.<br /><br /><em><strong>Few advantages over the conventional methods</strong></em><br /><br />1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.<br /><br />2. Its an online operation, So you dont need downtime to do this reorg.<br /><br />3. It doesnot require any extra space for the process to complete.<br /><br /><br /><strong><u>Conclusion</u></strong><br /><strong><u></u></strong><br />Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com7tag:blogger.com,1999:blog-2257062243512934022.post-88284827167369403872007-01-31T14:17:00.000+05:302007-02-01T16:56:54.793+05:30Running shell script from OracleIt’s simple!!<br /><br />Especially with 10g, it is even more simpler.<br /><br />Here is an example:<br /><br /><strong><u>Step 1: Create a shell script and grant execute priv to oracle user. I created a simple one:</u></strong><br /><br />bash-3.00$ cat /tmp/test.sh<br />#!/usr/bin/ksh<br />echo "Test succeeded `date`" >> /tmp/test.log<br /><br />Note: Do not forget to start your shell script with the shell you are using.<br />>> #!/usr/bin/ksh should be present in your shell script otherwise, the job will fail with ORA-27369 job of type EXECUTABLE failed with <exit><br /><br />bash-3.00$ chmod 755 /tmp/test.sh<br /><br /><strong><u>Step 2: Create the job:</u></strong><br />Remember user doing the following should have “CREATE JOB” privilege.<br /><br /><br />SQL> BEGIN<br />2 DBMS_SCHEDULER.CREATE_JOB (<br />3 job_name => 'TEST_SHELL',<br />4 job_type => 'EXECUTABLE',<br />5 job_action => '/tmp/test.sh',<br />6 start_date => sysdate,<br />7 repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', /* every one hour */<br />8 enabled => TRUE,<br />9 comments => 'Testing shell script from Oracle');<br />10 END;<br />11 /<br /><br />PL/SQL procedure successfully completed.<br /><br /><strong><u>Step 3: Check if the job is running:</u></strong><br /><br />bash-3.00$ cat /tmp/test.log<br />Test succeeded Wed Jan 31 01:02:23 PST 2007<br />Test succeeded Wed Jan 31 01:03:23 PST 2007<br />Test succeeded Wed Jan 31 01:04:23 PST 2007Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com1tag:blogger.com,1999:blog-2257062243512934022.post-87808935250247199692007-01-30T12:39:00.000+05:302007-01-31T10:46:48.602+05:30Index skip Scan – how?With Oracle 9i, CBO is equipped with many more features, one of them is “Index skip scan” .This means even if you have a composite index on more than one column and you use the non-prefix column alone in your SQL, it may still use index.<br /><br />I said “may” because CBO will calculate the cost of using the index and if it is more than that of full table scan, then it may not use index.<br /><br />Index skip scan works differently from a normal index (range) scan.<br />A normal range scan works from top to bottom first and then move horizontal.<br />But a Skip scan includes several range scans in it. Since the query lacks the leading column it will rewrite the query into smaller queries and each doing a range scan.<br /><br />Ex: <br />SQL> create table test (a number, b number, c number);<br /><br />Table created.<br /><br />SQL> create index test_i on test(a,b);<br /><br />Index created.<br /><br />SQL> begin<br /> 2 for i in 1 .. 100000<br /> 3 loop<br /> 4 insert into test values(mod(i, 5), i, 100);<br /> 5 end loop;<br /> 6 commit;<br /> 7 end;<br /> 8 /<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />SQL> exec dbms_stats.gather_table_stats(ownname => 'SAC', tabname => 'test', cascade => true);<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> set autotrace trace exp<br />SQL> select * from test where b=95267;<br /><br />Execution Plan<br />----------------------------------------------------------<br /> 0<br />SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22 Card=1 Bytes=10)<br /><br /><br /> 1 0<br /> TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=22 Card=1 Bytes=10)<br /><br /><br /> 2 1<br /> INDEX (SKIP SCAN) OF 'TEST_I' (INDEX) (Cost=21 Card=1)<br /><br /><br />I above example, “select * from test where b=95267” was broken down to several small range scan queries. It was effectively equivalent to following <br /><br />Select * from test where a=0 and b=95267<br />Union<br />Select * from test where a=1 and b=95267<br />Union<br />Select * from test where a=2 and b=95267<br />Union<br />Select * from test where a=3 and b=95267<br />Union<br />Select * from test where a=4 and b=95267;<br /><br />In concrete, saying that skip scan is not as efficient as normal “single range scan” is correct. But yet saves some disk space and overhead of maintaining another index.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com2tag:blogger.com,1999:blog-2257062243512934022.post-51585038789619200392007-01-24T12:08:00.000+05:302007-01-24T13:49:14.367+05:30External Tables - 10gUntil 9i external tables could only be used for read purposes.<br />But 10g allows you to populate external tables.<br /><br />here is how it does:<br /><br />Step 1: - Create a directory<br /><br />SQL> create directory my_dir as '/usr/test'; --- Make sure Oracle OS user has<br />privilege to write on this directory.<br /><br />Step 2: - Grant permission to user SCOTT<br /><br />SQL> grant read,write on directory my_dir to scott;<br /><br />Step 3: - Create the External Table: -<br /><br />SQL> CREATE TABLE scott.external_emp_dept<br /> ORGANIZATION EXTERNAL<br /> (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir<br /> LOCATION ('emp_dept.exp')<br /> ) <br /> reject limit unlimited<br /> AS <br /> select e.ename,d.dname from emp e, dept d where <br /> e.deptno=d.deptno;<br /><br />SQL> select * from scott.external_emp_dept;<br /><br />ENAME DNAME<br />---------- --------------<br />SMITH RESEARCH<br />ALLEN SALES<br />WARD SALES<br />JONES RESEARCH<br />MARTIN SALES<br />BLAKE SALES<br />CLARK ACCOUNTING<br />SCOTT RESEARCH<br />KING ACCOUNTING<br />TURNER SALES<br />ADAMS RESEARCH<br /><br />ENAME DNAME<br />---------- --------------<br />JAMES SALES<br />FORD RESEARCH<br />MILLER ACCOUNTING<br /><br />And you will find a file generated i.e. 'emp_dept.exp' in /usr/test directory.<br /><br />Now you can take this file to the target database and create an external table <br />and associate the file with that table.<br /><br />Step 4: - to be executed on the source database<br />SQL> set long 2000<br />SQL> Select dbms_metadata.get_ddl('TABLE','EXTERNAL_EMP_DEPT') from dual;<br /><br />The above command will give you the DDL command to create the table at target <br />database.<br /><br />Similar note is also available on metalink <a href=https://metalink.oracle.com/metalink/plsql/f?p=130:14:1228087276867018346::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,249321.1,1,1,1,helvetica>249321.1</a> which was also written by me when I was with Oracle Support.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com2tag:blogger.com,1999:blog-2257062243512934022.post-4789199819385059842007-01-12T14:58:00.000+05:302007-01-12T15:45:33.081+05:30SESSION_CACHED_CURSORS Vs CURSOR_SPACE_FOR_TIME - which, when and why?They 2 considered to be the most important parameter for shared pool tuning, but I guess most of us generally don’t use them or sometimes use them incorrectly.<br /><br />The idea to put them here to understand “what they do?”, “when to use them?”, “how to use them?” and finally “see the impact”.<br /><br /><strong>SESSION_CACHED_CURSORS<br /></strong><br />In most of the environments, there are many SQL’s which are re-fired many a times within a session, and every time they are issued, the session searches the shared pool for the parsed state, if it doesn’t get the parsed version, it will “hard parse” it, and if it exists in shared pool, it will still do a “soft parse”.<br /><br />As we know “hard parse” is a costly operation, even a “soft parse” requires library cache latch and CPU overhead, which if aggregated is a significant number.<br /><br />This parameter if set to a non-zero value (default is 50), improves the “soft parse” performance by doing a softer soft parse.<br /><br /><strong><u>How?</u></strong><br /><br />If enabled, oracle maintains a local session cache which stores recently closed cursors of a session.<br /><br />To avoid this space getting misused or overused, oracle maintains the cursors for which there have been 3 parsed calls in the past, so all the SQL’s issued by a session are not here. Remember each cursor if pinned here, is not freeable and hence you may require more shared pool area.<br /><br />A normal cursor in shared pool is sum of 2 components:<br />a) Heap 0 – size 1KB<br />b) SQL Area – size multiple of 4k<br /><br />When we use session_cached_cursors only first component of cursor which is HEAP 0 is pinned in local session cache and if there is a call for re-parse for a statement, Oracle first checks the existence of the cursor in local cache and if found, it gets the address of the rest of the cursor which is in SQL Area (assuming if it is not aged out), so hereby saving CPU overhead and library cache latch contention.<br /><br /><br /><em>How much it is getting used ?</em><br /><br />SQL> select max(value) from v$sesstat<br />2 where STATISTIC# in (select STATISTIC# from v$statname where name='session cursor cache count');<br /><br />MAX(VALUE)<br />----------<br />100<br /><br />This shows maximum value for session_cached_cursors in past. If this value= “session_cached_cursors” (init.ora parameter), you should consider increasing it.<br /><br /><em>If you want to see how is your session cache doing?</em><br /><br />SQL> select cache/tot*100 "Session cursor cache%"<br />2 from<br />3 (select value tot from v$sysstat where name='parse count (total)'),<br />4 ( select value cache from sys.v_$sysstat where name = 'session cursor cache hits' );<br /><br />Session cursor cache%<br />---------------------<br />106.635284<br /><br />A value near 100 is considered very good. But you may still consider increasing this parameter if MAX(VALUE) in query one shows you equal number of cached cursor which you have set.<br /><br /><br /><u>Conclusion</u>: In an OLTP application, where the same set of SQL is issued number of times, one must configure this parameter more than its default value (50).<br />Also increasing this parameter will mean extra memory required for shared pool, so you must increase your shared pool when you use this parameter.<br /><br /><strong>CURSOR_SPACE_FOR_TIME</strong><br /><strong></strong><br />SQL can be aged out of shared pool in 2 cases:<br />a) <u>When the cursor is closed</u>: When the cursors are closed by application, they can still be in shared pool, until there comes a request for a new cursor and oracle needs to use LRU algorithm. SESSION_CACHED_CURSORS helps you in pinning (partial because it only pins HEAP 0) when the cursors are closed.<br /><br />b) <u>When the cursor is open</u>: Oracle requires parsed state of SQL at PARSE and EXECUTE phase. If oracle parses (soft or hard) a statement, there is a likely hood that Oracle may age out your SQL out of shared pool after PARSE state if it requires to accommodate a new SQL coming its way. So in the EXECUTE state, there is a possibility that parsed information is lost and oracle parse it again.<br /><br />CURSOR_SPACE_FOR_TIME if set to TRUE, ensures that SQL is not aged out before the cursor is closed, so in EXECUTE phase, you will have the PARSE information.<br /><br />But this is generally a rare case and happens in a very highly active environment because to accommodate a new SQL, Oracle first check the free space and if it doesn’t get, it checks the closed cursors and see if any cursor can be aged out and when there is no space which can be reclaimed, Oracle comes to open cursors which are not EXECUTED.<br />This generally happens when the space of shared pool is too less.<br /><br />I don’t suggest setting this parameter to TRUE in most of the cases.<br /><br />There are some other serious tradeoffs also.<br />When a cursor is pinned, it cant be aged out and related memory cannot be freed for any new SQL and if you set this parameter to TRUE, you are telling Oracle to keep all the open cursors pinned and not freeable.<br />If you use this parameter, you are pinning the whole cursor not just the HEAP 0 which is 1k, you are pinning HEAP 0 (1k) + SQL Area (multiple of 4k) which makes shared pool life tough because of space issues.<br /><br /><u>Conclusion:</u><br /><br />As I said, I don’t suggest setting this parameter to TRUE in most of the cases. An alternative to set this parameter is to increase shared pool size or/and check your code on how many numbers of cursors you are opening/closing. That will be a better approach. Setting this parameter is like taking paracetamol without knowing the cause of fever.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com9tag:blogger.com,1999:blog-2257062243512934022.post-54320357658506450522007-01-06T13:17:00.000+05:302008-12-10T10:13:56.253+05:30Should I rebuild or coalesce my indexes ?<p>Should I rebuild or coalesce my indexes ?<br /><br /><br /><br />My last 2 notes on this blogs were specific to rebuilding (when and to which) indexes.<br /><br />There is one more critical aspect which I wanted to discuss is the cost/effort related to rebuilding indexes.<br /><br />Rebuilding an index is quite a costly operation and you must evaluate the benefit Vs effort before rebuilding an index.<br /><br />Rebuilding (online) an index requires additional resources like space, cpu usage, time.<br /><br />Here is one more option, which is less used or probably less popular “coalesce”.</p><br /><br /><lets>Rebuild Vs Coalesce </strong><br /><br /><br /><u><strong>Rebuild</strong> </u><br /><ul><br /><li>Can move an index to a different tablespace</li><br /><li>Resource consuming process</li><br /><li>Takes more time</li><br /><li>Creates a new tree</li><br /><li>Shortens the height of an index if it was increased due to DML activities</li><br /><li>Rebuilding can affect future DML’s because index becomes compact and for future DML’s index has to be extend dynamically.</li></ul><br /><strong><u>Coalesce</u></strong><br /><ul><br /><li>Cannot move an index to a different tablespace</li><br /><li>Comparatively less resource consuming</li><br /><li>Takes relatively less time</li><br /><li>Only merge the adjacent free space between leaf blocks within a branch</li><br /><li>Doesn’t shorten height of index</li><br /><li>Since coalesce doesn’t effect the total size and only frees up the unused space, it doesn’t affect future DML’s</li></ul><br /><p></p><br /><br /><a href="http://4.bp.blogspot.com/_UMUqPNYoxX4/RaNM4I3eL-I/AAAAAAAAAAM/HId0mNjUnGI/s1600-h/coalesce.gif"><img id="BLOGGER_PHOTO_ID_5017938937157398498" style="CURSOR: hand" alt="" src="http://4.bp.blogspot.com/_UMUqPNYoxX4/RaNM4I3eL-I/AAAAAAAAAAM/HId0mNjUnGI/s320/coalesce.gif" border="0" /></a><br /><br /><em>Image Source: Oracle doc</em><br />Coalescing the index, frees up space of adjacent leaf blocks within a branch block. This way the number of blocks or extents which an index is using will not change but there will be more number of free blocks which can be used for future inserts or updates.<br /><br />In an OLTP environment, where data is highly volatile, coalesce is better because it doesn’t shrink the index and the free space remains with the index segment.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com1tag:blogger.com,1999:blog-2257062243512934022.post-4865437055461939522007-01-04T17:29:00.000+05:302007-01-12T15:28:41.165+05:30How to find indexes which are candidate for rebuild?I'm writing this in response to Harvinder's comment.<br /><br />I know it’s a very arguable question, but let me put my words and then you can comment.<br /><br /><em>I follow different approaches to find out indexes for rebuild</em><br /><br />- Find out indexes having height(blevel+1) > 4 i.e. Indexes having BLEVEL > 3<br /><u>How:</u><br />SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3<br /><br />- Analyze indexes and find out ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20<br /><u>How:</u><br />First "Analyze the index with validate structure option" and then,<br /><br />SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;<br /><br /><br /><em>But (a big but), the reason to rebuild should be because of poor performance of your queries using indexes. You should/must not rebuild indexes if you find both the above reason true for index if it is not coupled with poor SQL performance.<br /></em><br />See this example:<br /><br />SQL> analyze index TEST_INDX validate structure; -- First analyze the suspect index<br /><br />Index analyzed.<br /><br />SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;<br /><br />NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS<br />------------ ---------- ---------- ----------- -------------<br />TEST_INDX 8 938752 29575 73342<br /><br /><strong>You can see height of the index is 8 and also high number of DEL_LF_ROWS<br /></strong><br />SQL> set autotrace on<br />SQL> set timing on<br />SQL><br />SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';<br /><br />COUNT(*)<br />----------<br />39700<br /><strong>Elapsed: 00:00:27.25<br /></strong><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 870163320<br /><br /><br />Id Operation Name Rows Bytes Cost (%CPU) Time<br /><br /><br />0 SELECT STATEMENT 1 117 <strong>10 (0)</strong> 00:00:01<br /><br />1 SORT AGGREGATE 1 117<br /><br />*2 INDEX RANGE SCAN TEST_INDX 115 13455 10 (0) 00:00:01<br /><br /><br /><br /><br />Statistics<br />----------------------------------------------------------<br />1 recursive calls<br />0 db block gets<br /><strong>764 consistent gets<br />757 physical reads</strong><br />0 redo size<br />516 bytes sent via SQL*Net to client<br />468 bytes received via SQL*Net from client<br />2 SQL*Net roundtrips to/from client<br />0 sorts (memory)<br />0 sorts (disk)<br />1 rows processed<br /><br />Now you rebuild the indexes<br /><br />SQL> alter index TEST_INDX rebuild;<br /><br />Index altered.<br /><br />SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';<br /><br />COUNT(*)<br />----------<br />39700<br /><br /><strong>Elapsed: 00:00:06.18<br /></strong><br />Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 870163320 - <em><strong>See here although it is using the same plan but still it is faster</strong></em><br /><br /><br />Id Operation Name Rows Bytes Cost (%CPU) Time<br /><br />----------------------------------------------------------------------------------<br /><br />0 SELECT STATEMENT 1 117 <strong>6 (0)</strong> 00:00:01<br /><br />1 SORT AGGREGATE 1 117<br /><br />* 2 INDEX RANGE SCAN TEST_INDX 115 13455 6 (0) 00:00:01<br /><br /><br />Statistics<br />----------------------------------------------------------<br />15 recursive calls<br />0 db block gets<br /><strong>592 consistent gets<br />588 physical reads<br /></strong>0 redo size<br />516 bytes sent via SQL*Net to client<br />468 bytes received via SQL*Net from client<br />2 SQL*Net roundtrips to/from client<br />0 sorts (memory)<br />0 sorts (disk)<br /><br /><br />SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;<br /><br />NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS<br />------------------------------ ---------- ---------- ---------- -----------<br />TEST_INDX 4 865410 15434 0<br /><br /><br /><strong>This clearly indicates rebuild helped my query performance. The height of index is reduced to 4 and DEL_LF_ROWS is 0</strong><br /><strong></strong><br /><strong></strong><br />Now coming to second of part of Harvinder's comment.<br /><br />Possible ways of Rebuilding.<br /><br />- Online/Offline.<br /><br /><strong><u>ONLINE Rebuild (8i onwards)</u></strong><br /><br />SQL> Alter index <index_name> rebuild online;<br /><br />This allows parallel DML to go on while Index is getting rebuild. Remember, online index requires more space, as it creates a new index before dropping the old one.<br /><br />Index Rebuild is primarily a 3 step process<br /><br /><strong><u>Prepare phase</u></strong>: Oracle locks the table for a fraction of second (actually not felt) to build index structure and populate data-dictionary.<br /><br /><strong><u>Build phase: </u></strong>Most of the work is done in this phase. Oracle engine populates the index using the table and allows parallel DML's, parallel DML's uses a temporary journal table (b tree index like structure) to host the entries while the new index is getting populated.<br /><br /><strong><u>Merge phase: </u></strong>Now the final stage, Oracle merges the new index with the journal table and drops the old index. Even during the merge phase, any changes to the table are recorded in the journal table and they get merged towards end of this phase.<br /><br />9i onwards, online index rebuild feature includes Reverse Key, Function Based and Key Compressed indexes.<br /><br /><strong><u>Offline Index rebuild.</u></strong><br /><br />SQL> Alter index <index_name> rebuild;<br /><br />This is conventional rebuild which was used(still available) before 8i. In this rebuild process, oracle drops the old index and creates a new one. In this process, no extra space is required, but parallel dml's are not supported.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com0tag:blogger.com,1999:blog-2257062243512934022.post-19629659920943778682007-01-02T18:03:00.000+05:302007-01-12T15:29:03.736+05:30When should I rebuild my indexes?Need is necessary for any change. I hope all agree to this. So why many DBA’s (not all) rebuilds indexes on periodical basis without knowing the impact of it?<br /><br />Let’s revisit the facts stated by many Oracle experts:<br /><br />- B Tree indexes can never be unbalanced<br />- The distance of any leaf block (which contains the index value) from the root block is always same.<br />- B Tree performance is good for both small and large tables and does not degrade with the growth of table<br /><br />When will rebuild help?<br /><br />• When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based.<br /><br />If your SQL’s use “=” predicate, rebuilding of index may not help you at all unless the DML’s (updates/deletes) on the table are cause of increasing the height of index. Due to heavy updates/deletes on a table, the space in the index can be left unused (unless it is reused) due to which block splits and if the splitting goes beyond the level of accomodation, the height of index increases by 1.<br />In simpler terms, unless you reduce the height (BLEVEL) of index, the time taken to reach the leaf block from root block will not change.<br /><br />As explained above, the height of index can be decreased by rebuild of an index(only if height was increased to updates/deletes).<br /><br />Only in case of range scan, rebuild (consider coalesce option also) may help.<br /><br />Select * from emp where empno between 1000 and 2000;<br /><br />In above case there could be lot of deleted/updated data which will also be read while reading between 1000 to 2000. Rebuilding will reclaim any unused space and hence the select could be faster.<br /><br />• Index fast full scan/ Index full scan. (rare case)<br />If your SQL’s are designed in a way that mostly data is selected from index (it doesn’t goes to table). In this case the whole index is read for data not for redirecting to table.<br /><br />Ex: <br />Select count(empno) from emp; -- you have an index in empno<br />Select empno from emp -- you have an index in empno<br /><br />Both the above SELECTs will fetch the data from Index only. Since the whole index is getting read, the space used by deleted rows is also read at the time of SELECT operationSachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com10tag:blogger.com,1999:blog-2257062243512934022.post-72312822818755336052007-01-02T10:50:00.000+05:302007-01-02T11:07:05.600+05:30Happy New Year - 2007<a href=http://www.icq.com/img/friendship/static/card_16961_rs.swf>I wish everyone a very happy, prosperous, peaceful, joyful new year 2007.</a><br /><br />With this, I want to thank you for the superb response to this blob.<br />I wanted to mention this earlier, but I was waiting for applauding mails to pause, so I could publish them in one go.<br /><br /><br /><br />jadharmadhikari no_reply@xxxx.com<br />Great, Its really great, Thanks.<br /><br />Ajay Kumar ajaykumar21@xxxxx.com<br />Nice work I must say.... Well Done and BIG WOW<br /><br />Mushtaque Syed mushtaquesyed@xxxxx.com<br />Thanks <br /> <br />Very nice and interesting, knowledgeable,<br />I appreciate<br /><br />rAvi... ravirxz@xxxxx.com <br />Hi Sachin,<br /> <br />This certainly is a great blog and an excellent idea initiated. Just wanted to appreciate you of your hard work.<br /> <br />Regards,<br />~rAvi...<br /><br /> vinodh kumar vinodh_thi@xxxxx.com <br />Hi Sachin,<br /> <br />What u have tried is a valuable thing.Thanks for taking time to prepare this blog.<br />I appreciate if u share ur knowledge more in tuning the SQL statements.But still what u have done is an excellent job.Keep going.<br /> <br />Regards,<br />Vinodhkumar V.Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.com1