tag:blogger.com,1999:blog-36479658770695344892023-11-16T03:52:45.450-08:00Merav Kedem's BlogOracle Performance Tuning TipsMerav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-3647965877069534489.post-86364749138419245262013-12-06T02:19:00.001-08:002013-12-06T11:44:38.157-08:00Exadata Storage Index solving an endless updateThe following simple update was running every evening, endlessly, on a production Exadata machine, Oracle version 11.2.0.3 (well, not truly endlessly, but for about an 30-60 minutes, which is considered to be quite endless in my standards...).<br />
<br />
<em>UPDATE T1 <br />SET EXP_DATE = SYSDATE <br />WHERE P_TIME IS NOT NULL <br />AND EXP_DATE IS NULL</em><br />
<em></em><br />
The update was running on a 20M rows table, only using two NULL/NOT NULL filtering criteria, and updates around 10-20K rows.<br />
Since it run for about an hour, using a FULL TABLE SCAN, in this hour other updates that were running simultaneously on the same table (with different criteria) were suffering from row locks on a daily basis, and this was simply not acceptable. <br />
Analyzing this intrusive update indicated that the update was spending most of its time on IO Waits, as many unneeded blocks were returned by the Exadata storage tier to the Exadata database tier, and only then database tied was able to start applying the null filters ( i.e "<em>P_TIME IS NOT NULL AND EXP_DATE IS NULL</em>").<br />
<br />
Index was not an option here. So what is?<br />
<br />
Since nulls are used here, Exadata <strong><span style="color: red;">Storage Index</span></strong> (which is part of Exadata offloading/smart scan features) could be the solution, if we just let Oracle use it.<br />
<br />
For those of you who are yet unfamiliar with Exadata storage indexes, here is a brief explanation:<br />
<br />
Storage Indexes is a very powerful Exadata feature, when used. Don't mix it with Oracle’s traditional B-Tree or bitmapped indexes as they are not stored in the database. Instead, its a feature (in-memory structures) of the storage server software that is designed to eliminate disk I/O. They identify locations where the requested records are not, instead of the other way around.<br />
They store minimum and maximum column values for disk storage units. Because SQL predicates are passed to the storage servers when Smart Scans are performed, the storage software can check the predicates against the Storage Index metadata (maximum and minimum values) before doing the requested I/O. Any storage region that cannot possibly have a matching row is skipped. <br />
In addition to the minimum and maximum values, there is a flag indicating whether any of the records in a storage region contain nulls.<br />
You can read more about exadata storage index in the following link<br />
<a href="http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31exadata-354069.html">http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31exadata-354069.html</a> <br />
<br />
Anyway, before we drill into how storage indexes tremendously helped in this case, here are some basic info on the update: its text, execution plan, and runtime statistics over the last two weeks: <br />
<br />
<em>SQL> select sql_text from dba_hist_sqltext where sql_id='bxcvv372pkr7m';</em><br />
<em> </em><br />
<em>UPDATE T1 SET EXP_DATE = SYSDATE WHERE P_TIME IS NOT NULL AND EXP_DATE IS NULL</em><br />
<em> </em><br />
<em>SQL> select * from table ( DBMS_XPLAN.DISPLAY_AWR('bxcvv372pkr7m',3865564104) );</em><br />
<em> </em><br />
<em>PLAN_TABLE_OUTPUT<br />---------------------------------------------------<br />SQL_ID bxcvv372pkr7m<br />--------------------<br />UPDATE T1 SET EXP_DATE = SYSDATE WHERE P_TIME<br />IS NOT NULL AND EXP_DATE IS NULL</em><br />
<em> </em><br />
<em>Plan hash value: 3865564104</em><br />
<em>--------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | <br />--------------------------------------------------------------------------------------------------<br />| 0 | UPDATE STATEMENT | | </em><br />
<em>| 1 | UPDATE | T1 | </em><br />
<em>| 2 | <strong><span style="color: red;">TABLE ACCESS STORAGE FULL</span></strong> | T1 | </em><br />
<em>--------------------------------------------------------------------------------------------------</em><br />
<em> </em><br />
This above plan (to achieve an easier display, I have removed several fields from it) is showing us that full table scan is being used while running the update, and that smart scan can be used here, theoretically. <br />
Selecting basic statistics on Table T1 shows that it has almost 20M rows over 1M blocks (block size is 8k).<br />
<em></em><br />
<em>SQL> select num_rows,blocks,last_analyzed from dba_tables a where table_name='T1';</em><br />
<em> </em><br />
<em> NUM_ROWS BLOCKS LAST_ANAL<br />------------------- ------------ -----------------<br /> 19309303 1096332 28-NOV-13</em><br />
<br />
And here are some statistics on its execution taken from dba_hist_sqlstat (b.t.w., dba_hist_sqlstat is an excellent place to grab overtime statistics information on sql executions):<br />
<br />
<em>select trunc(b.begin_interval_time) btime ,<br /> a.plan_hash_value plan_hs,<br /> sum(a.executions_delta) execs,<br /> sum(trunc(a.elapsed_time_delta/ 1000000/decode(a.executions_delta,0,1,a.executions_delta) ,3) ) tm_sec,<br /> sum(trunc(a.rows_processed_delta/decode(a.executions_delta,0,1,a.executions_delta) ,2) ) nrows, <br /> sum(trunc( a.buffer_gets_delta/decode(a.executions_delta,0,1,a.executions_delta) ,2)) buff_avg,<br /> sum(a.PHYSICAL_READ_BYTES_DELTA) pysrd,<br /> sum(a.PHYSICAL_READ_REQUESTS_DELTA) pysrq,<br /> sum(a.DISK_READS_DELTA) dkrd,<br /> sum(a.<strong><span style="color: red;">IO_INTERCONNECT_BYTES_DELTA</span></strong>) intercn,<br /> sum(a.<strong><span style="color: red;">IO_OFFLOAD_RETURN_BYTES_DELTA</span></strong>) </em><em>offld</em><em>, <br /> sum(a.IO_OFFLOAD_ELIG_BYTES_DELTA) elig<br />from dba_hist_sqlstat a , dba_hist_snapshot b <br />where sql_id='bxcvv372pkr7m'<br />and a.snap_id=b.snap_id <br />and b.begin_interval_time > sysdate -14<br />group by trunc(begin_interval_time), plan_hash_value<br />order by trunc(begin_interval_time) desc;</em><br />
<br />
<em>btime plan_hs execs tm_sec nrows buff_avg pysrd pysrq dkrd intercn offld </em><em>elig</em><br />
<em>------ ------ ------ ------ ------ ------ ------ ------ ------ </em><em>------ </em><br />
01-DEC-2013 3865564104 1 1751 16752 4327717 32936501248 104370 4020569 <strong>32936501248 0</strong> 0<br />
28-NOV-2013 3865564104 1 2511 0 3873633 30624784384 59219 3738377 <strong>30624784384 0</strong> 0<br />
27-NOV-2013 3865564104 1 3042 21454 5210873 41140527104 74379 5022037 <strong>41140527104 0</strong> 0<br />
26-NOV-2013 3865564104 1 1504 23270 3220407 25268518912 50146 3084536 <strong>25268518912 0</strong> 0<br />
25-NOV-2013 3865564104 1 1730 0 1954352 15447539712 27075 1885686 <strong>15447539712 0</strong> 0<br />
24-NOV-2013 3865564104 1 1712 17479 4329434 33956782080 77921 4145115 <strong>33956782080 0</strong> 0<br />
21-NOV-2013 3865564104 1 2492 16112 4303976 34013880320 71177 4152085 <strong>34013880320 0</strong> 0<br />
20-NOV-2013 3865564104 1 2749 14181 4352718 33895473152 81683 4137631 <strong>33895473152 0</strong> 0<br />
19-NOV-2013 3865564104 1 2591 18819 5365660 42522451968 76118 5190729 <strong>42522451968 0</strong> 0<br />
<br />
As indicated by IO_INTERCONNECT_BYTES_DELTA, exadata storage tier had to pass huge amount of IO to the database tier, and as indicate by the <strong>zero values</strong> of IO_OFFLOAD_RETURN_BYTES_DELTA, although plan is showing "TABLE ACCESS STORAGE FULL", there was no exadata smart scan/offloading here.<br />
<br />
So why is that? Why does Oracle not using Exadata powerful offloading abilities? <br />
<br />
This is because in order for smart scan to happen, few basic prerequisites need to be fulfilled:<br />
<br />
1) There must be a <strong><span style="color: red;">full scan</span></strong> of an object (and indeed there is)<br />
2) The object must be <strong><span style="color: red;">stored on Exadata storage</span></strong> (and indeed it is)<br />
3) The scan must use Oracle’s <strong><span style="color: red;">Direct Path Read</span></strong> mechanism (<strong>and it does not</strong>) => So this unfulfilled prerequisite disables the use of smart scan here!!!!<br />
<br />
How do I know direct path read was not used?<br />
Lets look at DBA_HIST_ACTIVE_SESS_HISTORY (b.t.w, its another great place to historically analyze what was going on while SQL was executed). It will show us what Oracle was doing while running this update over the last two weeks:<br />
<br />
<em>SQL> select nvl(event,'CPU') event,count(*)<br />from DBA_HIST_ACTIVE_SESS_HISTORY<br />where sql_id='bxcvv372pkr7m'<br />and sample_time > sysdate - 14 <br />group by event<br />order by count(*) desc; </em><br />
<em> </em><br />
<em>EVENT COUNT(*)<br />---------------------------------------------------------------- ----------<br /><strong><span style="color: red;">cell multiblock physical read</span></strong> 2018<br />CPU 94<br />cell single block physical read 37<br />read by other session 1</em><br />
<br />
And indeed, what we see here is that most of the time Oracle was waiting on "cell multiblock physical read", which is the regular full scan of exadata (also known as "db file scattered read" in non exeadata machines). <br />
If a smart scan would have been used here I would expect to see the "<strong><span style="color: red;"><em>cell smart table scan</em></span></strong>" wait event.<br />
<br />
OK, so lets undo the third restriction. Lets force Oracle use direct path read. <br />
How do we force it? By telling oracle, using hints for example, to run the update on parallel:<br />
<br />
<em>SQL> select sql_text from dba_hist_sqltext where sql_id='88s54njj4n3mj';</em><br />
<em> </em><br />
<em>UPDATE <strong>/*+ <span style="color: red;">full(A) parallel(A,8)</span> */</strong> T1 A SET EXP_DATE = SYSDATE WHERE P_TIME IS NOT NULL AND EXP_DATE IS NULL</em><br />
<br />
Lets run it with the new hints and see what Oracle will do this time:<br />
When Oracle is using Storage Index, the only way to verify it is by checking the statistics "<strong><em><span style="color: red;">cell physical IO bytes saved by storage index</span></em>" </strong>is increasing.<br />
<br />
<em>SQL> select name,value<br />from v$mystat s, v$statname n<br />where s.statistic# = n.statistic#<br />and n.name in ('cell physical IO bytes saved by storage index',<br /> 'cell physical IO interconnect bytes returned by smart scan');<br /><br />NAME VALUE<br />---------------------------------------------------------------- ----------<br /><strong><span style="color: red;">cell physical IO bytes saved by storage index</span></strong> 0<br />cell physical IO interconnect bytes returned by smart scan 0</em><br />
<br />
The statistics is showing us that up till now, no storage index offload was used by this session.<br />
Now, lets run the update and see whether this statistic will increase as expected.<br />
<br />
<em>SQL> UPDATE /*+ full(A) parallel(A,8) */ T1 A SET EXP_DATE = SYSDATE WHERE P_TIME IS NOT NULL AND EXP_DATE IS NULL;</em><br />
<br />
<em>SQL> select name,value<br />from v$mystat s, v$statname n<br />where s.statistic# = n.statistic#<br />and n.name in ('cell physical IO bytes saved by storage index',<br /> 'cell physical IO interconnect bytes returned by smart scan');</em><br />
<br />
<em>NAME VALUE<br />---------------------------------------------------------------- ----------<br /><span style="color: red;"><strong>cell physical IO bytes saved by storage index</strong> <strong>3106807808</strong></span><br />cell physical IO interconnect bytes returned by smart scan 4052928</em><br />
<br />
Yes! the above update was using storage index. It is indicating that 3,106,807,808 bytes were saved by storage index. Wow!!! That's a lot!!! <br />
And see other statistics. Now, after storage index was used, only <em>4052928 </em>bytes were returned from the storage tier to the database tier, through the interconnect. <br />
This is a dramatic saving. <br />
<br />
The update has finished in <strong>8 seconds</strong>!!! I will say that again folks, as you may not hear me right.<br />
Updating 15k rows in 20M rows table now took only <strong>8 second</strong>!!! <br />
No more 1 hour update. Big wow! I call this a huge saving, a tremendous improvement, great relief.<br />
<br />
Lets view that execution plan this time:<br />
<br />
<em>SQL> select * from table ( DBMS_XPLAN.DISPLAY_cursor) ;</em><br />
<em>PLAN_TABLE_OUTPUT<br />------------------------------------<br />SQL_ID 43x29vnth9z4x, child number 0<br />-------------------------------------<br />UPDATE /*+ full(A) parallel(A,8) */ T1 A SET<br />EXP_DATE = SYSDATE WHERE P_TIME IS NOT NULL AND EXP_DATE IS NULL</em><br />
<em></em><br />
<em>Plan hash value: 4279346641</em><br />
<em>--------------------------------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |<br />--------------------------------------------------------------------------------------------------------------------------------------<br />| 0 | UPDATE STATEMENT | | <br />| 1 | UPDATE | T1 |</em><br />
<em>| 2 | PX COORDINATOR </em><br />
<em>| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |<br />| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |<br />|* 5 | TABLE ACCESS STORAGE FULL| T1 | Q1,00 | PCWP | |<br />--------------------------------------------------------------------------------------------------------------------------------------</em><br />
<em>Predicate Information (identified by operation id):<br />---------------------------------------------------</em><br />
<em> 5 - <strong>storage(:Z>=:Z AND :Z<=:Z AND ("EXP_DATE" IS NULL AND "P_TIME" IS NOT NULL))</strong><br /> filter(("EXP_DATE" IS NULL AND "P_TIME" IS NOT NULL))</em><br />
<br />
The plan is showing us that parallel execution did happen, and that Smart Scan is possible now, as the predicate information is displaying the storage line.<br />
<br />
And finally, lets observe which wait events occurred this time while running the update: <br />
<br />
<em>SQL> select nvl(event,'CPU') event,count(*)<br />from v$active_session_history<br />where sql_id='43x29vnth9z4x'<br />and sample_time > sysdate - 1/24<br />group by event<br />order by count(*) desc; </em><br />
<em> </em><br />
<em>EVENT COUNT(*)<br />---------------------------------------------------------------- ----------<br /><strong><span style="color: red;">cell smart table scan</span></strong> 8</em><br />
<br />
As expected, we now see the wait event "<strong><em>cell smart table scan</em></strong>", which proves that Smart Scan was used now.<br />
<br />
Needless to say that after this change locking situation stopped occuring. <br />
Mission completed. Well done :)<br />
<br />
That's all for now. Hope you've enjoyed this post. As always, comments are most welcomed.<br />
<br />
Merav Kedem,<br />
<div dir="ltr" style="text-align: left;">
Founder of</div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">DB OPTimize</span></div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">Oracle Performance Tuning & DBA Consulting</span></div>
<div dir="ltr" style="text-align: left;">
<a href="http://www.dboptimize.co.il/"><span style="color: #249fa3;">www.dboptimize.co.il</span></a></div>
<div dir="ltr" style="text-align: left;">
<a href="http://meravkedem.blogspot.com/">meravkedem.blogspot.com</a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3;">merav@dboptimize.co.il</span></a></div>
<br />Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com2tag:blogger.com,1999:blog-3647965877069534489.post-46104403261758966532013-05-02T02:45:00.003-07:002013-05-03T08:34:28.785-07:00Exadata, Oracle and its Optimizer - The Dark Effect of Wrong StatisticsExadata or not, sometimes Oracle is still Oracle, optimizer is still optimizer, and math is still math.<br />
<br />
Here is the story: Instance is Oracle 11.2.0.3, running on an Exadata machine (X2-2 half Rack).<br />
User is complaining query is taking too long to finish (about 5 minutes).<br />
User has also said that last time he run that query, the range scanned was much wider (about a year), nevertheless no performance problems had occurred. Query had finished rather fast the previous time it run... (this is a point to remember for later on).<br />
<br />
Let's look at the query. For the sake of this post, I have simplified it a bit but the idea remains the same.<br />
Basically it is just a simple select, with 4 months range predicates on a date column (<em>SCAN_DATE</em>), and another predicates on some non unique id column (<em>POLICY_NUM</em>).<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em> SELECT COUNT(SCAN_DATE)</em></span><br />
<span style="font-size: x-small;"><em> FROM my_user.my_table a</em></span><br />
<span style="font-size: x-small;"><em> WHERE scan_date >= TO_DATE('01/01/2013 0:0:0', 'DD/MM/YYYY HH24:MI:SS')</em></span><br />
<span style="font-size: x-small;"><em> AND scan_date < TO_DATE('01/05/2013 0:0:0', 'DD/MM/YYYY HH24:MI:SS')</em></span><br />
<span style="font-size: x-small;"><em> AND ( policy_num ='183' OR policy_num='000000183');</em></span><br />
<span style="font-size: x-small;"></span><br />
Let's run it and see what happens:<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>09:51:12 SQL> SELECT COUNT(SCAN_DATE)</em></span><br />
<span style="font-size: x-small;"><em> FROM my_user.my_table A</em></span><br />
<span style="font-size: x-small;"><em>WHERE SCAN_DATE >= TO_DATE('01/01/2013 0:0:0', 'DD/MM/YYYY HH24:MI:SS')</em></span><br />
<span style="font-size: x-small;"><em>AND SCAN_DATE < TO_DATE('01/05/2013 0:0:0', 'DD/MM/YYYY HH24:MI:SS')</em></span><br />
<span style="font-size: x-small;"><em>and ( POLICY_NUM ='183' OR policy_num='000000183');</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>COUNT(SCAN_DATE)</em></span><br />
<span style="font-size: x-small;"><em>----------------</em></span><br />
<span style="font-size: x-small;"><em> 0</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em><strong>Elapsed: 00:04:43.87 => NOTICE THE HIGH ELAPSED TIME </strong></em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>Execution Plan</em></span><br />
<span style="font-size: x-small;"><em>----------------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em>Plan hash value: 877559244</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>----------------------------------------------------------------------------------------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em>| Id | Operation | Name | Rows| Bytes | Cost (%CPU)|Time |</em></span><br />
<span style="font-size: x-small;"><em>----------------------------------------------------------------------------------------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em>| 0 | SELECT STATEMENT | | 1 | 16 | 7 (0)| 00:00:01 |</em></span><br />
<span style="font-size: x-small;"><em>| 1 | SORT AGGREGATE | | 1 | 16 | | |</em></span><br />
<span style="font-size: x-small;"><em>|* 2 | TABLE ACCESS BY INDEX ROWID | MY_TABLE | 1 | 16 | 7 (0)| 00:00:01 |</em></span><br />
<span style="font-size: x-small;"><em><strong>|* 3 | INDEX RANGE SCAN | SCAN_DATE_INDX | 9 | | 3 (0)| 00:00:01 |</strong></em></span><br />
<span style="font-size: x-small;"><em>----------------------------------------------------------------------------------------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>Predicate Information (identified by operation id):</em></span><br />
<span style="font-size: x-small;"><em>---------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em> 2 - filter("POLICY_NUM"='000000183' OR "POLICY_NUM"='183')</em></span><br />
<span style="font-size: x-small;"><em> 3 - access("SCAN_DATE">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')</em></span><br />
<span style="font-size: x-small;"><em> AND "SCAN_DATE"<TO_DATE(' 2013-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>Statistics</em></span><br />
<span style="font-size: x-small;"><em>----------------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em> 1 recursive calls</em></span><br />
<span style="font-size: x-small;"><em> 0 db block gets</em></span><br />
<span style="font-size: x-small;"><em> 4622469 consistent gets <strong>=> NOTICE THE HIGH CONSISTENT GETS </strong></em></span><br />
<span style="font-size: x-small;"><em> 375620 physical reads => <strong>AND HIGH PHYSICAL READS</strong></em></span><br />
<span style="font-size: x-small;"><em> 8320 redo size</em></span><br />
<span style="font-size: x-small;"><em> 533 bytes sent via SQL*Net to client</em></span><br />
<span style="font-size: x-small;"><em> 520 bytes received via SQL*Net from client</em></span><br />
<span style="font-size: x-small;"><em> 2 SQL*Net roundtrips to/from client</em></span><br />
<span style="font-size: x-small;"><em> 0 sorts (memory)</em></span><br />
<span style="font-size: x-small;"><em> 0 sorts (disk)</em></span><br />
<span style="font-size: x-small;"><em> 1 rows processed</em></span><br />
<div>
<span style="font-size: x-small;"></span><br /></div>
<span style="font-size: x-small;"><span style="font-size: small;">The above execution plan is showing index range scan is used (bolded line). It is also stated in the plan that <strong>optimizer thinks only 9 rows</strong> will be returned by the index. This looked suspicious to me, since table has millions of rows.<br /> <br />Let's view the execution in OEM SQL Monitoring (this is a great and most recommended feature!!!).</span> </span><br />
<span style="font-size: x-small;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8eHpJtO9TPGTNPfiNM3CL47Iu0ksvFg4W4Sfx3FJ7_0nRfHlhTFMQQaYKsZoRAKaGtod2YWpk5dH6_lvvjjgr-GpPcdKgEzvxfp5B0Ljv-zGoaVS-8WlbJBRDi5nYv_z6pON2hyphenhyphenwPB-jE/s1600/wrong_stats.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8eHpJtO9TPGTNPfiNM3CL47Iu0ksvFg4W4Sfx3FJ7_0nRfHlhTFMQQaYKsZoRAKaGtod2YWpk5dH6_lvvjjgr-GpPcdKgEzvxfp5B0Ljv-zGoaVS-8WlbJBRDi5nYv_z6pON2hyphenhyphenwPB-jE/s640/wrong_stats.jpg" width="640" /></a></div>
<span style="font-size: x-small;"></span><br />
Indeed it shows that although estimated rows is 9 (and this is what making the optimizer wrongly choose his decision), the actual returned rows are 10M.<br />
Exadata or not Exadata, optimizer is still optimizer, and math is still math. <br />
Scanning 10M rows through an index is obviously not the best thing to do (and this is an understatement).<br />
Just think about it. Even if IO latency is very very good, lets say 0.5ms (which is considered to be very good latency for random reads), multiplying 0.5ms by 10M seeks will still take forever.<br />
<br />
So what is making Oracle choose a wrong decision?<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>10:21:32 SQL> select num_rows,last_analyzed<br />from dba_tables<br />where owner='MY_OWNER'<br />and table_name='MY_TABLE';</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>NUM_ROWS LAST_ANAL<br />------------------ -----------------<br />168,562,490 26-OCT-12</em></span><br />
<span style="font-size: x-small;"></span><br />
Table was last analyzed on 26.10.2012? Six months ago? This doesn't sound promising. But let's continue and look what are the statistics of those two relevant columns, especially low value and high value..<br />
(btw, I have simplified the select on the low/high_value to make the query below more readable and short. To be able to see the real values on these two column you need to use some conversion on it.)<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>10:28:29 SQL> SELECT column_name,data_type,num_distinct,density,last_analyzed,low_value,high_value<br />10:28:29 18 from DBA_TAB_COLUMNS a<br />10:28:29 19 where table_name='MY_TABLE' <br />10:28:29 20 and column_name in ('POLICY_NUM','SCAN_DATE');</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY LAST_ANAL LOW_V HI_V<br />--------------- ---------- ------------ ---------- --------- ---------- -------------------- --------------------<br />POLICY_NUM VARCHAR2 3922917 5.1066E-06 26-OCT-12 8888<br /><strong>SCAN_DATE</strong> DATE 19276420 .00002758 26-OCT-12 1981-1-1 0:0:0 <strong>2012-10-22</strong> 2:51:48</em></span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><span style="font-size: small;">Look what we found. The <strong>highest value</strong> of SCAN_DATE column is <strong>22.10.2012</strong>. Obviously this is because last time table was analyzed was in 26.10.2012. No wonder Oracle thought there won't be many rows returned for the requested predicate. The requested predicate is requesting rows when scan date between 1.1.2013 - 1.5.2013. This date range is higher thna the high_value of <em>scan_date</em> column:</span> </span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em> WHERE SCAN_DATE >= TO_DATE(<strong>'01/01/2013</strong> 0:0:0', 'DD/MM/YYYY HH24:MI:SS') <br /> AND SCAN_DATE < TO_DATE(<strong>'01/05/2013</strong> 0:0:0', 'DD/MM/YYYY HH24:MI:SS')</em><br /><br /><span style="font-size: small;">Now let's see how many rows are actually in the table. Notice the hints I have used, and how fast Oracle returned the result. Those hints are forcing Oracle to use index fast full scan with parallel query (degree 4), which is much more appropriate in this case than a simple index range scan, since table has millions of rows (Note: <strong>Exadata offloading</strong> is also relevant here, and I have added a special section for that at the end of this post).</span><br /><br /><em>10:04:49 SQL> SELECT /*+ <strong>index_ffs (a,scan_date_indx) parallel_index(a,scan_date_indx,4)</strong> */ </em></span><br />
<span style="font-size: x-small;"><em>COUNT(SCAN_DATE) </em></span><br />
<span style="font-size: x-small;"><em>FROM my_owner.my_table A;</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>COUNT(SCAN_DATE)<br />----------------<br /> 186,863,326</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>Elapsed: 00:00:05.44</em></span><br />
<span style="font-size: x-small;"></span><br />
Table has 186M rows. Now let's see how many rows will be returned for the requested dates:<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>10:05:11 SQL> SELECT /*+ index_ffs (a,scan_date_indx) parallel_index(a,scan_date_indx,4) */ COUNT(SCAN_DATE)<br /> FROM my_owner.my_table A<br /> WHERE SCAN_DATE >= TO_DATE('01/01/2013 0:0:0', 'DD/MM/YYYY HH24:MI:SS')<br /> and scan_date < to_date('01/05/2013 0:0:0', 'DD/MM/YYYY HH24:MI:SS');</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>COUNT(SCAN_DATE)<br />----------------<br /> 10,213,081</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>Elapsed: 00:00:04.95</em></span><br />
<span style="font-size: x-small;"></span><br />
Indeed, 10M rows are returned here. This is not a surprise, since we have already seen the huge gap between estimated rows and actual rows in OEM's SQL Monitoring screen.<br />
<br />
Here is the time to say that after finding there is a statistics problem here, I have explored the issue a bit more with the customer, to figure out what was the case when query run much faster (remember this was part of the complaint?). It appeared that query run much faster when the rows were selected from year 2012. Customer has informed me that since they started running the query on year 2013, query was taking forever..<br />
And now we know why..<br />
Let's explore it a bit more, and see how many rows are returned for 2012:<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>10:06:07 SQL> SELECT /*+ index_ffs (a,scan_date_indx) parallel_index(a,scan_date_indx,4) */ COUNT(SCAN_DATE)<br />10:06:07 2 FROM my_owner.my_table A<br />10:06:07 3 WHERE SCAN_DATE >= TO_DATE('01/01/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS')<br />10:06:07 4 and scan_date < to_date('01/05/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS');</em></span><br />
<br />
<span style="font-size: x-small;"><em>COUNT(SCAN_DATE)<br />----------------<br /> 10,381,170</em></span><br />
<br />
<span style="font-size: x-small;"><em>Elapsed: 00:00:04.24</em></span><br />
<span style="font-size: x-small;"></span><br />
Let's see what Oracle thinks about the cardinality of this predicate:<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>10:07:16 SQL> SELECT /*+ index_ffs (a,scan_date_indx) parallel_index(a,scan_date_indx,4) */ COUNT(SCAN_DATE)<br />10:07:16 2 FROM my_owner.my_table A<br />10:07:16 3 WHERE SCAN_DATE >= TO_DATE('01/01/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS')<br />10:07:16 4 and scan_date < to_date('01/05/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS');</em></span><br />
<br />
<span style="font-size: x-small;"><em>COUNT(SCAN_DATE)<br />----------------<br /> 10381170</em></span><br />
<br />
<span style="font-size: x-small;"><em>Elapsed: 00:00:04.01</em></span><br />
<br />
<span style="font-size: x-small;"><em>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 1948420453</em></span><br />
<span style="font-size: x-small;"><em>---------------------------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |<br />---------------------------------------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 7 | 155K (1)| 00:31:05 | | | |<br />| 1 | SORT AGGREGATE | | 1 | 7 | | | | | |<br />| 2 | PX COORDINATOR | | | | | | | | |<br />| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 7 | | | Q1,00 | P->S | QC (RAND) |<br />| 4 | SORT AGGREGATE | | 1 | 7 | | | Q1,00 | PCWP | |<br />| 5 | PX BLOCK ITERATOR | | 9473K| 63M| 155K (1)| 00:31:05 | Q1,00 |PCWC||<br />|* 6 | INDEX STORAGE FAST FULL SCAN| SCAN_DATE_INDX | <strong>9473K</strong>| 63M| 155K (1)| 00:31:05 | Q1,00 | PCWP | |<br />---------------------------------------------------------------------------------------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em>Predicate Information (identified by operation id):<br />---------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em> 6 - storage("SCAN_DATE">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SCAN_DATE"<TO_DATE('<br /> 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<br /> filter("SCAN_DATE">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SCAN_DATE"<TO_DATE('<br /> 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))</em></span><br />
<em></em><br />
<span style="font-size: x-small;"><em>Statistics<br />----------------------------------------------------------<br /> 12 recursive calls<br /> 108 db block gets<br /> 588111 consistent gets<br /> 585699 physical reads<br /> 0 redo size<br /> 537 bytes sent via SQL*Net to client<br /> 520 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</em> </span><br />
<span style="font-size: x-small;"> </span><br />
<span style="font-size: small;">As expected, when the given date range is for 2012 (i.e <em>SCAN_DATE >= TO_DATE('01/01/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS') and SCAN_DATE < to_date('01/05/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS')</em> ),<br />the estimated returned rows Oracle is showing (9473K) is much more accurate.</span><br />
<span style="font-size: x-small;"><span style="font-size: small;">And why does the query returns much faster when the given date range was for 2012?<br />Because Oracle knows scanning this index will bring 10M rows, so it smartly decides not to use this index. Instead, it uses the index on POLICY_NUM, as shown below.<br />Notice also the monitor hint. This will enable us to view the execution in SQL Monitoring, since by default, only long running queries (above 5 seconds) or parallel queries are shown.</span> </span><br />
<br />
<span style="font-size: x-small;"><em>09:49:11 SQL> set autotrace on<br />09:49:34 SQL><br />09:49:46 SQL> SELECT /*+ <strong>monitor</strong> */ COUNT(SCAN_DATE)<br />09:49:47 2 FROM my_owner.my_table A<br />09:49:47 3 WHERE SCAN_DATE >= TO_DATE('01/01/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS')<br />09:49:47 4 AND SCAN_DATE < TO_DATE('01/05/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS')<br />09:49:47 5 and ( POLICY_NUM ='183' OR policy_num='000000183');</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>COUNT(SCAN_DATE)<br />----------------<br /> 0</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>Elapsed: 00:00:00.01</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 1090044264</em></span><br />
<span style="font-size: x-small;"><em>-------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 16 | 62 (0)| 00:00:01 |<br />| 1 | SORT AGGREGATE | | 1 | 16 | | |<br />| 2 | INLIST ITERATOR | | | | | |<br />|* 3 | TABLE ACCESS BY INDEX ROWID| MY_TABLE | 4 | 64 | 62 (0)| 00:00:01 |<br />|* 4 | INDEX RANGE SCAN | INDX_POLICY_NUM | <strong>79</strong> | | 4 (0)| 00:00:01 |<br />-------------------------------------------------------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em>Predicate Information (identified by operation id):<br />---------------------------------------------------</em></span><br />
<span style="font-size: x-small;"><em> 3 - filter("SCAN_DATE">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND<br /> "SCAN_DATE"<TO_DATE(' 2012-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<br /> 4 - access("POLICY_NUM"='000000183' OR "POLICY_NUM"='183')</em></span><br />
<em></em><br />
<span style="font-size: x-small;"><em>Statistics<br />----------------------------------------------------------<br /> 1 recursive calls<br /> 0 db block gets<br /> 8 consistent gets<br /> 3 physical reads<br /> 0 redo size<br /> 533 bytes sent via SQL*Net to client<br /> 520 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</em></span><br />
<span style="font-size: x-small;"></span><br />
So the only question remains open is why does statistics are not updated, as this is an Oracle 11.2.0.3 instance, and statistics gathering through dbms_stats should be done automatically.<br />
I suspected locked statistics. Let's check that:<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>10:08:41 SQL> select owner, table_name, stattype_locked<br />from dba_tab_statistics<br />where table_name='MY_TABLE' ; </em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>OWNER TABLE_NAME STATTYPE_LOCKED<br />---------- --------------- -------------------------<br />IMAGE MY_TABLE ALL</em></span><br />
<span style="font-size: x-small;"></span><br />
As suspected, statistics are locked!!! No wonder last time table was analyzed was 6 months ago. For date fields, this is a bad thing. You can lock statistics if and only if it has no effect the optimizer decision. And clearly this is not the case here. Optimizer decision was effected becuase high_value on the date column had a wrong value (much lower value than it should).<br />
Resolution here is simple. Unlock statistics, re-gather statistics, and... <span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: arial, helvetica, clean, sans-serif; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 16px; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">Voilà!! </span>. Query "suddenly" runs much faster...<br />
<br />
Let's see what happens to "high_value" statistics, after unlocking and re-gathering statistics on MY_TABLE:<br />
<br />
<em><span style="font-size: x-small;">12:41:32 SQL> SELECT owner,column_name,data_type,num_distinct,density,last_analyzed,num_nulls,<br />low_value,high_value</span></em><br />
<em><span style="font-size: x-small;"> from DBA_TAB_COLUMNS a<br /> where table_name='MY_TABLE'</span></em><br />
<em><span style="font-size: x-small;"> and column_name in ('POLICY_NUM','SCAN_DATE');</span></em><br />
<span style="font-size: x-small;"><br /></span><span style="font-size: x-small;"> </span><em><span style="font-size: x-small;">COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY LAST_ANAL NUM_NULLS LOW_V HI_V<br />--------------- ---------- ------------ ---------- --------- ---------- -------------------- --------------------<br />POLICY_NUM VARCHAR2 5634560 9.9753E-06 28-APR-13 0 8888<br />SCAN_DATE DATE 5644288 0 .000045952 28-APR-13 0 1981-1-1 0:0:0 <strong>2013-4-28</strong> 12:1:31</span></em><br />
<span style="font-size: x-small;"><br /></span>Brilliant. HIGH_VALUE now has the correct value, so let's re-run the original query and see how Oracle execute it this time:<br />
<span style="font-size: x-small;"><br /></span><em><span style="font-size: x-small;">12:42:21 SQL> SELECT COUNT(SCAN_DATE)<br /> FROM my_owner.my_table A<br />WHERE SCAN_DATE >= TO_DATE('01/01/2013 0:0:0', 'DD/MM/YYYY HH24:MI:SS')<br />AND SCAN_DATE < TO_DATE('01/05/2013 0:0:0', 'DD/MM/YYYY HH24:MI:SS')<br />and ( POLICY_NUM ='183' OR policy_num='000000183');</span></em><br />
<span style="font-size: x-small;"><br /></span><em><span style="font-size: x-small;">COUNT(SCAN_DATE)<br />----------------<br /> 0</span></em><br />
<span style="font-size: x-small;"><br /></span><em><span style="font-size: x-small;">Elapsed: 00:00:00.01</span></em><br />
<span style="font-size: x-small;"><br /></span><em><span style="font-size: x-small;">Execution Plan<br />----------------------------------------------------------<br />Plan hash value: 1090044264</span></em><br />
<em><span style="font-size: x-small;">-------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />-------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 18 | 52 (0)| 00:00:01 |<br />| 1 | SORT AGGREGATE | | 1 | 18 | | |<br />| 2 | INLIST ITERATOR | | | | | |<br />|* 3 | TABLE ACCESS BY INDEX ROWID | MY_TABLE | 3 | 54 | 52 (0) | 00:00:01 |<br />|* 4 | INDEX RANGE SCAN | <strong>INDX_POLICY_NUM </strong> | 61 | | 5 (0) | 00:00:01 |<br />-------------------------------------------------------------------------------------------------</span></em><br />
<em><span style="font-size: x-small;">Predicate Information (identified by operation id):<br />---------------------------------------------------</span></em><br />
<em><span style="font-size: x-small;"> 3 - filter("SCAN_DATE">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND<br /> "SCAN_DATE"<TO_DATE(' 2013-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<br /> 4 - access("POLICY_NUM"='000000183' OR "POLICY_NUM"='183')</span></em><br />
<br />
<span style="font-size: x-small;"><em>Statistics<br />----------------------------------------------------------<br /> 0 recursive calls<br /> 0 db block gets<br /> 8 consistent gets<br /> 0 physical reads<br /> 0 redo size<br /> 533 bytes sent via SQL*Net to client<br /> 520 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</em><br /> </span><br />
Excellent. As expected, after Oracle sees the right statistics, it now knows the highest value for SCAN_DATE is 28.4.13 and not 26.10.12, therefore it can smartly choose much more suitable execution plan. This time Oracle decides not to range scan the index <em>SCAN_DATE_INDX </em>(since it knows such range will be wide), but instead, it chooses to range scan<em> INDX_POLICY_NUM.</em><br />
This time the result is returned in no time. <br />
<em> </em><br />
Oracle is no mystery. Just need to understand it, and know how it works.<br />
:)<br />
<br />
And now, if you can just bare with me a bit longer, here is some cool exadata staff to notice: <br />
Since we forced an index fast full scan with parallel, Oracle was able to use Exadata smart scan offloading (btw, to enable the possibility of smart scan use, full scan and direct read are a must). Since the only column used in this query is scan_date, offloading is extremely effective here: more than 4GB were offloaded (i.e database didn't need to process those 4G as most of them were already filtered by the storage). This can also be seen in OEM, as <em>"cell offload efficiency: 97%":</em><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6YljPOYONChyphenhyphenPI0pnZ1cR7hx-F3l5SeszsuZheQSxdf8ZOFyLAHA6ncKxT_kneP-IFbKPYa53JgnAVIrLZqN0iTumXUptXap6_rTSDTJ0N_mGnlEcuxJPfMnca-zI3vpuz9A-jKT8hkMb/s1600/wrong_stats-exa-1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="256" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6YljPOYONChyphenhyphenPI0pnZ1cR7hx-F3l5SeszsuZheQSxdf8ZOFyLAHA6ncKxT_kneP-IFbKPYa53JgnAVIrLZqN0iTumXUptXap6_rTSDTJ0N_mGnlEcuxJPfMnca-zI3vpuz9A-jKT8hkMb/s640/wrong_stats-exa-1.jpg" width="640" /></a></div>
This smart scan can be also verified through v$sesstat:<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>SQL> select b.name,a.value from v$sesstat a, v$statname b, v$session c<br />where a.statistic# = b.statistic#<br />AND A.SID=C.SID<br />AND ( B.NAME like '%storage%' or b.name like '%offload%')<br />and a.sid=1542<br />order by a.value desc; </em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>NAME VALUE<br />---------------------------------------------------------------- ----------<br /><strong>cell physical IO bytes eligible for predicate offload 0</strong></em></span><br />
<span style="font-size: x-small;"><em>cell simulated physical IO bytes returned by predicate offload 0<br />cell simulated physical IO bytes eligible for predicate offload 0<br />cell physical IO bytes saved by storage index 0</em></span><br />
<span style="font-size: x-small;"></span><br />
Notice that before running the query, <em>"cell physical IO bytes eligible for predicate offload"</em> is 0.<br />
Now, let's run the query again.<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"><em>SQL> SELECT /*+ index_ffs (a,scan_date_indx) parallel_index(a,scan_date_indx,4) */ COUNT(SCAN_DATE)<br /> 2 FROM IMAGE.INS_DOC_S A<br /> 3 WHERE SCAN_DATE >= TO_DATE('01/01/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS')<br /> 4 and scan_date < to_date('01/05/2012 0:0:0', 'DD/MM/YYYY HH24:MI:SS');</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>COUNT(SCAN_DATE)<br />----------------<br /> 10381208</em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
Now let's see if statistics values have changed:<br />
<em><span style="font-size: x-small;"></span></em><br />
<span style="font-size: x-small;"><em>SQL> select b.name,a.value from v$sesstat a, v$statname b, v$session c<br />where a.statistic# = b.statistic#<br />AND A.SID=C.SID<br />AND ( B.NAME like '%storage%' or b.name like '%offload%')<br />and a.sid=1542<br />order by a.value desc; </em></span><br />
<span style="font-size: x-small;"><em></em></span><br />
<span style="font-size: x-small;"><em>NAME VALUE<br />---------------------------------------------------------------- ----------<br /><strong>cell physical IO bytes eligible for predicate offload 4798046208</strong><br />cell simulated physical IO bytes eligible for predicate offload 0<br />cell simulated physical IO bytes returned by predicate offload 0<br />cell physical IO bytes saved by storage index 0</em></span><span style="font-size: small;"></span><br />
<span style="font-size: small;"></span><br />
<span style="font-size: small;">After we execute the query, <em>"cell physical IO bytes eligible for predicate offload"</em> had jump to 4798046208. Meaning, smart scan did happen, and this offload is all due to this query.</span><br />
Cool and effective!<br />
:)<br />
<br />
That's all for now. Hope you've enjoyed this post. <br />
As always, comments are most welcomed.<br />
<br />
Merav Kedem,<br />
<div dir="ltr" style="text-align: left;">
Founder of</div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">DB OPTimize</span></div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">Oracle Performance Tuning & DBA Consulting</span></div>
<div dir="ltr" style="text-align: left;">
<a href="http://www.dboptimize.co.il/"><span style="color: #249fa3;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3;">merav@dboptimize.co.il</span></a></div>
<br />
<span style="font-size: x-small;"></span><br />
<br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"></span><br />
<div>
<span style="font-size: x-small;"></span><br /></div>
Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com0tag:blogger.com,1999:blog-3647965877069534489.post-42822296356455275362012-07-12T09:37:00.001-07:002012-08-13T22:53:52.510-07:00No, It is not always the database fault..<span style="background-color: white;">When things are starting to get wrong, Database ( i.e DBA ) is always the first </span>tier to blame. <span style="background-color: white;">I am sure you have faced this situation, probably much more than once or twice.</span><br />
Guilty as charged, until proven innocent, right?<br />
<br />
Well, it is not that nothing can't go wrong in the database, but I wish it would stop be treated as the black sheep of the family.<br />
<br />
Recently, I had been in a situation where application was constantly complaining for bad response time, day after day. First thing to explore was whether application changed its behaviour, since most of the time that is the cause for performance issues. Surprisingly enough, it did not. No new SQLs, no major changes in execution count. <br />
Second, I went to look for database changing its behaviour. But it didn't. No changes in execution plan (usually major cause for performance degradation). As much as I have looked, from the database side, it looked like database didn't change its behavior. <br />
<br />
So what could be wrong?<br />
<br />
Storage can go wrong for example, and that was the case here.<br />
<br />
Looking for problems in the wrong place will not assist in solving them. But just saying "it is not the database" probably won't help. You will probably always have to prove it... <br />
Average physical read/write time is what oracle experiencing when dealing with IO. But underneath these numbers, two more tiers are involved. Storage and File System.<br />
<br />
Well, after checking average physical read time and average physical write time of several files in this database, it was clear (unfortunately, just to me for a while) that writes became much slower, at exactly the time when the application have started to complain.<br />
<br />
Don't think it was easy. For many days Storage guys continues to say "Nothing is wrong with the storage" (how many times have you heard this? I wonder), but finally, when numbers became so high, it couldn't be ignored any more.<br />
<br />
At last, spotlight went to spot on the right place (and stopped pointing to the wrong place, the database). Well, probably just until the next complaint will rise again...but I guess I can't win it all..<br />
<br />
Here is a query that can be run in Oracle 10g and above. It might look scary, but in short, it's just go over dba_hist_filestatxs (where file statistics are held overtime), and since numbers there are incremented every snapshot (it doesn't hold the deltas), I have used LAG analytic function to subtract current value from preceding value (i.e, current snapshot from preceding snapshot), and I did it for every statistics I wanted to observe.<br />
That's it.<br />
<br />
<div class="scrollbox">
<br />
select * from (<br />
select <br />
b.begin_interval_time begin_interval_time , <br />
nullif((phywrts - ( lag(phywrts,1,0) over( order by a.snap_id ) )),0) delta_phywrts, <br />
trunc( (writetim*10 - ( lag(writetim*10,1,0) over( order by a.snap_id ) ) ) / <br />
nullif((phywrts - ( lag(phywrts,1,0) over( order by a.snap_id ) )),0) ,3 ) avg_write_ms, <br />
nullif((phyrds - ( lag(phyrds,1,0) over( order by a.snap_id ) )),0) delta_phyrds, <br />
trunc( (readtim*10 - ( lag(readtim*10,1,0) over( order by a.snap_id ) ) ) / <br />
nullif((phyrds - ( lag(phyrds,1,0) over( order by a.snap_id ) )),0) ,3 ) avg_read_ms, <br />
nullif((singleblkrds - ( lag(singleblkrds,1,0) over( order by a.snap_id ) )),0) delta_singleblkrds, <br />
trunc( (singleblkrdtim*10 - ( lag(singleblkrdtim*10,1,0) over( order by a.snap_id ) ) ) / <br />
nullif((singleblkrds - ( lag(singleblkrds,1,0) over( order by a.snap_id ) )) ,0),3 ) avg_singleblkrds_ms<br />
from dba_hist_filestatxs a , <br />
dba_hist_snapshot b <br />
where filename = 'my_file.dbf'<br />
and a.snap_id=b.snap_id <br />
) <br />
where to_char(begin_interval_time,'HH24') between 7 and 11 -- in case you wish to see only specific hours<br />
and to_char(begin_interval_time,'HH24') not in ('FRIDAY','SATURDAY ') -- in case you wish to filter nonworking days<br />
order by begin_interval_time;<br />
<br /></div>
<br />
<br />
<br />
Below is part of the real life output, where major changes in average write time can clearly be seen, especially since 24.5. I have taken statistics of a specific data file, each time between 09:30-12:00, in normal working days (not in a weekend). Snapshot interval is 15 minutes. (For simplification, although above query shows reads and writes statistics, below I am showing only writes, since this is the issue relevant for this post). <br />
<br />
on 7.5, average write time was between 2-7 ms.<br />
on 16.5, average write time was between 6-11 ms.<br />
on 21.5, average write time was between 6-14 ms.<br />
on 24.5, average write time was between 12-21 ms. <br />
on 13.6, average write time was very very bad. around 40 ms write time. This is 10 times slower than a month ago!<br />
Finally, on 6/7, average write time went back to normal. went down to around 5 ms. <br />
<br />
Pay attention to the increasing values of the 3rd column, avg_write_ms, which shows the average physical write time of a specific datafile, for every snapshot.<br />
<br />
<div style="height: 300px; overflow: auto; padding-bottom: 10px; padding-left: 10px; padding-right: 10px; padding-top: 10px; white-space: pre; width: 700px;">
<table style="width: 700px;"><tbody>
<tr><td></td></tr>
</tbody></table>
<table><tbody>
<tr><td>BEGIN_INTERVAL_TIME</td><td>DELTA_PHYWRTS</td><td>AVG_WRITE_MS</td></tr>
<tr><td>===================</td><td>=============</td><td>============</td></tr>
<tr><td>07-MAY-12 09:30:05</td><td>1970</td><td>3.395</td></tr>
<tr><td>07-MAY-12 09:45:10</td><td>2007</td><td>4.489</td></tr>
<tr><td>07-MAY-12 10:00:12</td><td>1679</td><td>2.829</td></tr>
<tr><td>07-MAY-12 10:15:17</td><td>2120</td><td>3.037</td></tr>
<tr><td>07-MAY-12 10:30:19</td><td>2355</td><td>2.169</td></tr>
<tr><td>07-MAY-12 10:45:23</td><td>1609</td><td>2.094</td></tr>
<tr><td>07-MAY-12 11:00:24</td><td>750</td><td>7.533</td></tr>
<tr><td>07-MAY-12 11:15:26</td><td>737</td><td>2.713</td></tr>
<tr><td>07-MAY-12 11:30:28</td><td>779</td><td>3.465</td></tr>
<tr><td>07-MAY-12 11:45:29</td><td>625</td><td>2.448</td></tr>
<tr><td></td></tr>
<tr><td>16-MAY-12 09:30:48</td><td>572</td><td>6.73</td></tr>
<tr><td>16-MAY-12 09:45:49</td><td>645</td><td>5.395</td></tr>
<tr><td>16-MAY-12 10:00:51</td><td>576</td><td>8.072</td></tr>
<tr><td>16-MAY-12 10:15:52</td><td>720</td><td>10.513</td></tr>
<tr><td>16-MAY-12 10:30:54</td><td>622</td><td>11.495</td></tr>
<tr><td>16-MAY-12 10:45:55</td><td>745</td><td>7.208</td></tr>
<tr><td>16-MAY-12 11:01:00</td><td>692</td><td>9.205</td></tr>
<tr><td>16-MAY-12 11:15:01</td><td>920</td><td>10.793</td></tr>
<tr><td>16-MAY-12 11:30:03</td><td>839</td><td>8.116</td></tr>
<tr><td>16-MAY-12 11:45:05</td><td>755</td><td>8.172</td></tr>
<tr><td></td></tr>
<tr><td>21-MAY-12 09:30:54</td><td>2156</td><td>8.409</td></tr>
<tr><td>21-MAY-12 09:45:57</td><td>2016</td><td>14.627</td></tr>
<tr><td>21-MAY-12 10:00:59</td><td>1877</td><td>10.436</td></tr>
<tr><td>21-MAY-12 10:15:03</td><td>2224</td><td>6.753</td></tr>
<tr><td>21-MAY-12 10:30:06</td><td>1145</td><td>12.043</td></tr>
<tr><td>21-MAY-12 10:45:10</td><td>869</td><td>12.105</td></tr>
<tr><td>21-MAY-12 11:00:12</td><td>774</td><td>11.989</td></tr>
<tr><td>21-MAY-12 11:15:14</td><td>857</td><td>8.191</td></tr>
<tr><td>21-MAY-12 11:30:16</td><td>737</td><td>8.71</td></tr>
<tr><td>21-MAY-12 11:45:18</td><td>826</td><td>12.421</td></tr>
<tr><td></td></tr>
<tr><td>24-MAY-12 09:30:27</td><td>661</td><td>14.19</td></tr>
<tr><td>24-MAY-12 09:45:29</td><td>715</td><td>12.881</td></tr>
<tr><td>24-MAY-12 10:00:30</td><td>800</td><td>15.637</td></tr>
<tr><td>24-MAY-12 10:15:32</td><td>716</td><td>12.835</td></tr>
<tr><td>24-MAY-12 10:30:33</td><td>895</td><td>19.307</td></tr>
<tr><td>24-MAY-12 10:45:37</td><td>799</td><td>19.148</td></tr>
<tr><td>24-MAY-12 11:00:38</td><td>716</td><td>21.983</td></tr>
<tr><td>24-MAY-12 11:15:41</td><td>841</td><td>21.878</td></tr>
<tr><td>24-MAY-12 11:30:44</td><td>724</td><td>15.138</td></tr>
<tr><td>24-MAY-12 11:45:46</td><td>653</td><td>15.773</td></tr>
<tr><td></td></tr>
<tr><td>13-JUN-12 09:30:15</td><td>24608</td><td>24.476</td></tr>
<tr><td>13-JUN-12 09:45:17</td><td>24249</td><td>31.427</td></tr>
<tr><td>13-JUN-12 10:00:21</td><td>20164</td><td>39.09</td></tr>
<tr><td>13-JUN-12 10:15:23</td><td>19807</td><td>38.68</td></tr>
<tr><td>13-JUN-12 10:30:24</td><td>19572</td><td>38.088</td></tr>
<tr><td>13-JUN-12 10:45:26</td><td>18812</td><td>41.594</td></tr>
<tr><td>13-JUN-12 11:00:29</td><td>19003</td><td>40.993</td></tr>
<tr><td>13-JUN-12 11:15:33</td><td>21660</td><td>44.534</td></tr>
<tr><td>13-JUN-12 11:30:34</td><td>23242</td><td>38.171</td></tr>
<tr><td>13-JUN-12 11:45:36</td><td>21055</td><td>42.248</td></tr>
<tr><td></td></tr>
<tr><td>06-JUL-12 09:30:50</td><td>3057</td><td>6.319</td></tr>
<tr><td>06-JUL-12 09:45:51</td><td>3406</td><td>5.733</td></tr>
<tr><td>06-JUL-12 10:00:52</td><td>3880</td><td>8.103</td></tr>
<tr><td>06-JUL-12 10:15:53</td><td>4324</td><td>5.381</td></tr>
<tr><td>06-JUL-12 10:30:54</td><td>5409</td><td>4.906</td></tr>
<tr><td>06-JUL-12 10:45:55</td><td>2714</td><td>5.036</td></tr>
<tr><td>06-JUL-12 11:00:56</td><td>2723</td><td>4.697</td></tr>
<tr><td>06-JUL-12 11:15:57</td><td>2434</td><td>7.781</td></tr>
<tr><td>06-JUL-12 11:30:58</td><td>3804</td><td>4.91</td></tr>
<tr><td>06-JUL-12 11:45:59</td><td>2243</td><td>5.737</td></tr>
</tbody></table>
<tr><td></td></tr>
</div>
<br />
<span style="font-family: inherit;"><br /></span><span style="font-family: inherit;">Ladies and gentlemen of the jury, what is your verdict?<br />We have found the defendant NOT GUILTY, your honor.</span><br />
<span style="font-family: inherit;">To sum it all up, as stated in the beginning, Database is not always to blame. </span><span style="font-family: inherit;">This time the jury found it innocent.</span><br />
:)<br />
First thing to check is the application. If you see that applications is behaving more or less the same (no new SQLs, same execution count per SQL), check the database.<br />
And if the database is behaving more or less the same, it is simply not there.<br />Something else is the source of the problem.<br />
<br />
<span style="font-family: inherit;">As always, comments are most welcome..</span><br />
<br />
<div dir="ltr" style="text-align: left;">
<br />
Merav Kedem,</div>
<div dir="ltr" style="text-align: left;">
Founder of</div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">DB OPTimize</span></div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">Oracle Performance Tuning & DBA Consulting</span></div>
<div dir="ltr" style="text-align: left;">
<a href="http://www.dboptimize.co.il/"><span style="color: #249fa3;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3;">merav@dboptimize.co.il</span></a></div>
<br />
<br class="Apple-interchange-newline" />Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com1tag:blogger.com,1999:blog-3647965877069534489.post-78317738254520999092012-03-20T08:20:00.003-07:002012-03-20T08:33:39.954-07:00Bind variables in Oracle 11.2<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;">A bit of an history before I begin...</span><br />
<span style="font-family: inherit;">Oracle 10.2 had exposed bind variables values passed to a SQL statement via a new view named </span>V$SQL_BIND_CAPTURE.<span style="font-family: inherit;"> Nevertheless, t</span><span style="background-color: white; font-family: inherit; text-align: -webkit-left;">o limit the overhead, Oracle captured the bind at most every 15 minutes for a given cursor. </span><br />
<span style="font-family: inherit;"><span style="line-height: 18px;"><br /></span></span><br />
<span style="font-family: inherit;"><span style="background-color: white; text-align: -webkit-left;">In other words, you were not able to see bind values for each SQL execution.</span></span><br />
<span style="font-family: inherit;"><span style="text-align: -webkit-left;">In some cases, especially when binds values have major influence on SQL execution, knowing the bind values passed to a given execution could be very important.</span></span><br />
<span style="text-align: -webkit-left;"><span style="font-family: inherit;"><br /></span></span><br />
<span style="font-family: inherit;">Oracle 11.2 has extended this ability.V$SQL_MONITOR was expanded and a new column BIND_XML was added to it.</span><br />
V$SQL_MONITOR <span style="font-family: inherit;"> is one of my favourite additions to 11g. </span><span style="font-family: monospace;">It</span><span style="background-color: white; font-family: inherit;"> </span><span style="background-color: white; font-family: inherit;">displays very useful information on SQL statement executions and it</span><span style="background-color: white; font-family: inherit;"> is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time. (</span><span style="background-color: white;">My only complaint about this view is that it is not kept in history. No dba_hist_sql_monitor yet. Maybe someone in Oracle will read this post and add it to their TODO list...).</span><br />
<span style="background-color: white;"><br /></span><br />
<br />
<div style="background-color: white;">
<span style="font-family: inherit;">Anyway, with the addition of BIND_XML new column, this means that in 11gR2 we will be able to see bind variables values for each execution of such SQL statements. Sure, it is still only partitial picture of bind variables world (since we will have bind information only for those statements who run in parallel or run longer that 5 seconds), but it is still more than we had before...</span></div>
<div style="background-color: white; text-align: -webkit-left;">
<br /></div>
<div style="background-color: white; text-align: -webkit-left;">
And as examples always make life much easier, here is a simple demonstration:</div>
<div style="background-color: white; text-align: -webkit-left;">
<br /></div>
<div style="background-color: white; text-align: -webkit-left;">
</div>
<i>SQL> var a1 number;</i><br />
<i>SQL> var a2 varchar2(30);</i><br />
<i>SQL> var a3 varchar2(40);</i><br />
<i>SQL></i><br />
<i>SQL> exec :a1 := 1000;</i><br />
<i><br /></i><br />
<i>PL/SQL procedure successfully completed.</i><br />
<i><br /></i><br />
<i>SQL> exec :a2 := '%A%';</i><br />
<i><br /></i><br />
<i>PL/SQL procedure successfully completed.</i><br />
<i><br /></i><br />
<i>SQL> exec :a3 := '01-JAN-2010';</i><br />
<i><br /></i><br />
<i>PL/SQL procedure successfully completed.</i><br />
<i><br /></i><br />
<i>SQL> set timing on</i><br />
<i><br /></i><br />
<i>SQL> select count(*)</i><br />
<i> 2 from dba_tables t, dba_objects o</i><br />
<i> 3 where o.object_id > :a1</i><br />
<i> 4 and t.table_name like :a2</i><br />
<i> 5 and o.created > to_date(:a3,'DD-MON-YYYY')</i><br />
<i> 6 and o.object_type='TABLE'</i><br />
<i> 7 and o.owner=t.owner</i><br />
<i> 8 and o.object_name=t.table_name;</i><br />
<i><br /></i><br />
<i> COUNT(*) </i><br />
<i>---------- </i><br />
<i> 2163 </i><br />
<i><br /></i><br />
<i>SQL> set long 1000</i><br />
<i>SQL> set lines 130</i><br />
<i>SQL> select xmltype(binds_xml) from v$sql_monitor</i><br />
<i> 2 where sql_text like 'select count(*)%from dba_tables t, dba_objects o%';</i><br />
<i><br /></i><br />
<i>XMLTYPE(BINDS_XML) </i><br />
<i>--------------------------------------------------------------------------------</i><br />
<i><binds> </i><br />
<i> <bind name="<span style="color: #cc0000;">:A1</span>" pos="1" dty="2" dtystr="NUMBER" maxlen="22" len="2"><span style="color: #cc0000;">1000</span></bind> </i><br />
<i> <bind name="<span style="color: #cc0000;">:A2</span>" pos="2" dty="1" dtystr="VARCHAR2(32)" maxlen="32" csid="38" len="3"><span style="color: #cc0000;">%A%</span></bind> </i><br />
<i> <bind name="<span style="color: #cc0000;">:A3</span>" pos="3" dty="1" dtystr="VARCHAR2(128)" maxlen="128" csid="38" len="11"><span style="color: #cc0000;">01-JAN-2010</span></bind> </i><br />
<i></binds> </i><br />
<i> </i><br />
As you can see, since running this SQL took a bit more than 5 seconds, its execution as well as all bind variable values passed for this execution can be instantly observed in v$sql_monitor.<br />
<br />
<br />
<div style="background-color: white;">
<span style="font-family: inherit;"><span style="text-align: -webkit-left;">Note #1: It is possible to </span><span style="line-height: 18px;">change the 15 minutes bind capture interval by setting an undocumented parameter </span><span style="line-height: 18px;">“_cursor_bind_capture_interval”, (though it</span><span style="line-height: 18px;"> can have a large impact on system performance),</span><span style="line-height: 18px;"> i.e </span></span></div>
<span style="font-family: inherit;"><i><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;">ALTER SYSTEM SET</span><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;"> </span><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;">"_cursor_bind_capture_interval"</span><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;"> </span><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;">=</span><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;"> 3</span><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;">0</span><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;">;</span><span style="background-color: #f9f9f9; line-height: 15px; white-space: pre;"> </span></i><span style="background-color: #f9f9f9; font-style: italic; line-height: 15px; white-space: pre;">-- capture every 30 seconds</span> </span><br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;"><span style="background-color: white;">Note #2: v$sql_monitor requires a licenses for Oracle Diagnostics & Tuning Packs </span> </span><br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;">Comments are most welcome..</span><br />
<br />
<div dir="ltr" style="text-align: left;">
<br />
Merav Kedem,</div>
<div dir="ltr" style="text-align: left;">
Founder of</div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">DB OPTimize</span></div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">Oracle Performance Tuning & DBA Consulting</span></div>
<div dir="ltr" style="text-align: left;">
<a href="http://www.dboptimize.co.il/"><span style="color: #249fa3;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3;">merav@dboptimize.co.il</span></a></div>
<br />
<span style="font-family: inherit;"><br /></span><br />
<span style="font-family: inherit;"><br /></span><br />
<div style="background-color: white; font-family: Tahoma, sans-serif;">
<br /></div>Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com0tag:blogger.com,1999:blog-3647965877069534489.post-91471882696201990832011-10-21T08:43:00.000-07:002011-10-21T08:45:13.163-07:00AWR Formatter - worth a tryI have bumped into<span style="font-family: "Times New Roman", "serif"; font-size: 12pt; mso-ansi-language: EN-US; mso-bidi-language: HE; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-US;"> </span>one of <a href="http://tkyte.blogspot.com/2011/04/two-things.html">Tom Kyte's posts</a>, recommending an <a href="http://tylermuth.wordpress.com/2011/04/20/awr-formatter/">AWR Formatter</a> plug-in, written by Tyler Muth. <br />
As Tom Kytes said: "It's pretty cool - works as a Chrome plugin - and it makes an AWR report a little more 'friendly' to use. It creates hot links for many of the wait events (so you know what they mean) and it summarizes up a lot of stuff - making the AWR report a lot more "interactive". Check it out and give him feedback on it if you have time."<br />
<br />
<div dir="ltr" style="text-align: left;">
<span class="Apple-style-span">Merav Kedem,</span></div>
<div dir="ltr" style="text-align: left;">
<span class="Apple-style-span">Founder of</span></div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">DB OPTimize</span></div>
<div dir="ltr" style="text-align: left;">
<span style="color: black;">Oracle Performance Tuning & DBA Consulting</span></div>
<div dir="ltr" style="text-align: left;">
<a href="http://www.dboptimize.co.il/"><span style="color: #249fa3;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3;">merav@dboptimize.co.il</span></a></div>
<br />
<br />Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com0tag:blogger.com,1999:blog-3647965877069534489.post-78080901392080606312011-10-11T08:27:00.000-07:002011-10-11T08:27:27.616-07:00Bad screen design, bad performance<div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">Recently one of my customers had loaded a new version into production. Immediately performance issues have started to rise. I have spotted a new query that became one of the top-3 heaviest SQLs running in the instance. Certainly not something that can be ignored…<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">The query was something like:<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">Select distinct ….<o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">From T1<o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">Where code = :1<o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">And ID <b>like :2</b><o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">Order by …;<o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">This query was being executed 10k-20k a day, with average execution time 1.5 seconds, and had an intensive CPU usage. That summed up to 4-8 hours a day of cpu usage.<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">Next thing I went to v$sql_bind_capture, to examine the values sent to ID parameter. To my surprise I saw that most of the time (more than 95%) application was sending the complete ID without adding "%" as I would have expect it to be since query is written with LIKE operator " <i>and ID like :2</i>" and not written with equal operation on ID. That looked very weird. Why using LIKE without using % , right?<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">As a result of this use of LIKE operator in the query Oracle couldn't apply an index unique scan, and forced to use an index range scan + sort unique thus became a performance issue. <o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">Let's examine the facts again:<o:p></o:p></span></div><div class="MsoListParagraphCxSpFirst" dir="LTR" style="direction: ltr; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-add-space: auto; mso-list: l1 level1 lfo1; text-align: left; text-indent: -18.0pt; unicode-bidi: embed;"><span style="font-size: 10pt;">1.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span dir="LTR"></span><span style="font-size: 10pt;">The combination of (CODE, ID) is unique since a unique index was built on those 2 columns.<o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" dir="LTR" style="direction: ltr; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-add-space: auto; mso-list: l1 level1 lfo1; text-align: left; text-indent: -18.0pt; unicode-bidi: embed;"><span style="font-size: 10pt;">2.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span dir="LTR"></span><span style="font-size: 10pt;">Application rarely uses wild card "%" when passing values to ID. Most of the time they only send a complete number, i.e <i>…and ID like '12345'</i> , and appose to <i>and</i> <i>ID like '12345%'</i> , so in fact only one unique row will be returned.<o:p></o:p></span></div><div class="MsoListParagraphCxSpLast" dir="LTR" style="direction: ltr; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-add-space: auto; mso-list: l1 level1 lfo1; text-align: left; text-indent: -18.0pt; unicode-bidi: embed;"><span style="font-size: 10pt;">3.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span dir="LTR"></span><span style="font-size: 10pt;">Query is using distinct and order by on the returned rows. In case no wild card is sent, both distinct and order by can be omitted from the query since only one unique row will be returned anyway.<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">From what I have seen it was clear to me that performance became here an issue due to some bad design of something. It looked like someone was trying to generalize a use of a query. <o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">I thought to myself: If you want to send a complete number, use equal operator. If you wish to apply a wild card, use a like operator. Certainly do not use like for both cases.<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">Well, the time for a little investigation had come. After a little research with the application, it appeared there was a new screen allowing the user to search for ID. The screen has a combo box which its default value was set to "Begins with" (which sent the LIKE operator), so every time a user was searching for ID the same query with LIKE was sent in. User is passing <i>12345%</i>, LIKE is being used (right). User is passing <i>12345</i>, again, LIKE is being used (wrong).<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">I have asked the application to change three things:<o:p></o:p></span></div><div class="MsoListParagraphCxSpFirst" dir="LTR" style="direction: ltr; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-add-space: auto; mso-list: l0 level1 lfo2; text-align: left; text-indent: -18.0pt; unicode-bidi: embed;"><span style="font-size: 10pt;">1.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span dir="LTR"></span><span style="font-size: 10pt;">Split this query into two different queries. One with LIKE and the other with equal operator "=".<o:p></o:p></span></div><div class="MsoListParagraphCxSpMiddle" dir="LTR" style="direction: ltr; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-add-space: auto; mso-list: l0 level1 lfo2; text-align: left; text-indent: -18.0pt; unicode-bidi: embed;"><span style="font-size: 10pt;">2.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span dir="LTR"></span><span style="font-size: 10pt;">Query with equal operator should omit DISTINCT + ORDER BY.<o:p></o:p></span></div><div class="MsoListParagraphCxSpLast" dir="LTR" style="direction: ltr; margin-bottom: .0001pt; margin-bottom: 0cm; margin-left: 36.0pt; margin-right: 0cm; margin-top: 0cm; mso-add-space: auto; mso-list: l0 level1 lfo2; text-align: left; text-indent: -18.0pt; unicode-bidi: embed;"><span style="font-size: 10pt;">3.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span dir="LTR"></span><span style="font-size: 10pt;">Redesign the screen, so default combo value will be set to "equal" (as this was the major use of the users), and in this case query with equal operation will be used.<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">Query with equal operator should look like:<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">Select ….<o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">From T1<o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">Where code = :1<o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; margin-left: 36.0pt; text-align: left; unicode-bidi: embed;"><i><span style="font-size: 10pt;">And ID <b>= :2</b>;<o:p></o:p></span></i></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">Indeed, after 2 weeks application had passed a fix to production and a performance relief could be noticed. The new query average execution time went down to 0.004 second (remember, query using LIKE had average execution time of 1.5 sec). And that summed up to only 40-80 seconds a day (as opposed to 4-8 hours when using LIKE).<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">Of course, query with LIKE operator is still being used, but only when really needed, and that is how it should be. <o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">To sum it all up, what we saw here was bad screen design which lead to bad performance…<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><span style="font-size: 10pt;">There is no doubt on my mind. As I said before and will say again, DBAs should work together with application. DBAs should definitely participate during design phases. Otherwise this type of scenario would occur over and over again…<o:p></o:p></span></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
<div dir="ltr" style="text-align: left;"><span class="Apple-style-span" style="font-size: x-small;">Merav Kedem,</span></div><div dir="ltr" style="text-align: left;"><span class="Apple-style-span" style="font-size: x-small;">Founder of</span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: x-small;">DB OPTimize</span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: x-small;">Oracle Performance Tuning & DBA Consulting</span></div><div dir="ltr" style="text-align: left;"><a href="http://www.dboptimize.co.il/"><span style="color: #249fa3; font-size: x-small;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3; font-size: x-small;">merav@dboptimize.co.il</span></a></div></div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" dir="LTR" style="direction: ltr; text-align: left; unicode-bidi: embed;"><br />
</div>Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com1tag:blogger.com,1999:blog-3647965877069534489.post-35532134501947677192011-07-31T09:47:00.000-07:002011-07-31T09:51:10.819-07:00Thinking of adding a new index? Tune SQL's text before<div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Creating an index can certainly do marvelous things to SQLs. Even after all those years in the field, it is still always thrilling for me to witness huge performance improvement seen after applying a suitable index to an unturned query. The performance gain is simply spectacular and the notices slump<span style="font-family: "Tahoma", "sans-serif"; font-size: 10pt;"> </span>in resource consumption this query is taking (I/O, CPU, etc) is immediate. </div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Nevertheless, adding an index does have its cost. Good things never come for free. Every index occupies storage, and each index has a side effect on DMLs, causing some overhead (Oracle need to updated index block, therefore must read them before, from memory or from disk). </div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">I often see cases when indexes are created too often, tables end up with dozen indexes and more, and yet, SQL are still not tuned.</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Moreover, before applying an index, you should always ask yourself whether this SQL worth adding an index. Sometimes it could be SQL which runs only few times a day, and the way it performs is good enough for its customers. It does a Full Table Scan? so what. If it runs only once a day, during night, and takes 2 minutes, than this could be just fine as it is. No need to add extra index just for this once a day execution. </div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">So basically, every situation needs to be carefully examined, then according to the required needs, decide whether it is worthwhile adding an index to improve SQL performance.</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Another important advice before applying a new index is to tune the text itself (assuming you can change the text and it is not coming from some third-party product), i.e:</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; text-indent: -18pt; unicode-bidi: embed;">1.<span style="font-family: ""Times New Roman"", "serif";"> Understand what the SQL is aimed to do. </span>Ask yourself (and go back to the application if necessary) what is it that this SQL is doing. Being able to say it in your own words makes it easier to understand it, then to rewrite it.</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; text-indent: -18pt; unicode-bidi: embed;">2.<span style="font-family: ""Times New Roman"", "serif";"> </span>Make sure there are no unnecessary joins. This is extremely important. You will be surprise to know how many times I have identified unnecessary joins in SQLs written by application, seeing join with large tables without a reason. Just imagine what a needless join with a table that has 18M rows can do to SQL performance (small clue: really bad things).</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; text-indent: -18pt; unicode-bidi: embed;">3.<span style="font-family: ""Times New Roman"", "serif";"> </span>Make sure all join predicates exist (to avoid Cartesian joins)</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; text-indent: -18pt; unicode-bidi: embed;">4.<span style="font-family: ""Times New Roman"", "serif";"> </span>Make sure all needed filter predicates exists (where clauses match the relevant conditions). Apply filters which yield the smallest result set as possible.</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Of course there are many more issues with writing a tuned SQL, though this post will not cover them all.</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><br />
</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Then, if, and only if, text is 100% tuned, go ahead and search whether a suitable index can improve query performance.</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Yet, remember that though you are tuning a specific SQL, a new index can and probably will influence other SQLs (not just DMLs but also other queries) for better or for worse. It's always a tradeoff.</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Therefore, before you adjust the index to your system:</div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;">Try to identify what will be its influence on other SQLs running on the system: which SQLs will be influence and how will they be influence by the adding the new index (there are tools which do that). Of course, whenever possible, first apply the index in your testing environment, which resembles production as much as possible (load, schema, data), and examine index influence on your specific SQL, on other queries, and on DMLs. <br />
<br />
<div dir="ltr" style="text-align: left;">Merav Kedem,</div><div dir="ltr" style="text-align: left;">Founder of </div><div dir="ltr" style="text-align: left;"><span style="color: black;">DB OPTimize</span></div><div dir="ltr" style="text-align: left;"><span style="color: black;">Oracle Performance Tuning & DBA Consulting</span></div><div dir="ltr" style="text-align: left;"><a href="http://www.dboptimize.co.il/"><span style="color: #249fa3;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3;">merav@dboptimize.co.il</span></a></div></div>Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com0tag:blogger.com,1999:blog-3647965877069534489.post-45194399172772488802011-06-10T11:58:00.000-07:002011-06-13T08:36:02.751-07:00Beyond Database Tuning - Part 2 (or adjusting schema to application needs)<span style="font-size: x-small;"><span style="font-family: "Times New Roman", "serif"; mso-ansi-language: EN-US; mso-bidi-language: HE; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-US;">In my previous post </span>"</span><a href="http://meravkedem.blogspot.com/2011/05/beyond-database-tuning.html"><span style="font-size: x-small;">Beyond Database Tuning</span></a><span style="font-size: x-small;">" I have shown a situation where application was running a very heavy SQL (~2 hours) each day, while in fact, after speaking with the application, I understood that this SQL is simply not needed and its output is only functioning as a loop counter in a Java code.</span><br />
<div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">What run inside this loop was another heavy SQL which is the subject of this post. </span></div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">Inner SQL had looked like that: </span></div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;"><br />
</span></div><span style="font-size: x-small;"><br />
</span><br />
<em><span style="font-size: x-small;"> SELECT …<br />
FROM T1 s,<br />
</span><span style="font-size: x-small;"><span style="color: #e06666;"><strong>(SELECT max(max(seq)) over (partition by id order by id ) maxseq,<br />
id <br />
FROM T1 s2<br />
WHERE seq is not null <br />
GROUP BY id) maxresults</strong> </span> </span></em><br />
<em><span style="font-size: x-small;"> WHERE s.id = maxresults.id AND<br />
s.seq = maxresults.maxseq AND<br />
s.col_a = 0 AND<br />
s.col_b = :1 AND<br />
s.col_c < :2 AND<br />
s.col_d = :3 AND<br />
rownum < 100 </span></em><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">Application was constantly running this very heavy SQL hour after hour day after day. It accessed a very big table T1 that has 146M rows on 2.1M 8k blocks.</span><br />
<span style="font-size: x-small;">Each execution took 35 minutes on average. Since each execution took so long, only 20-70 executions were able to run each day.</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">After a short time examining this SQL I knew that as it is now, tuning capabilities were very limited. Most of the time was spent on building the inline view with the analytic function (marked in red). Oracle had to first build it all before being able to then join it with T1, therefore it <span style="font-family: "Times New Roman", "serif"; mso-ansi-language: EN-US; mso-bidi-language: HE; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-US;">couldn't </span>benefit from the count stopkey resulting from "rownum < 100".</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">Again, I went to speak with the application to fully understand the logic behind it.</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">It appeared that every time application was doing something on id, a new row was inserted into table T1 with id and seq=max(seq) + 1. This running number was <u>per ID</u> (compare to a running number on whole table), thus <u>an Oracle sequence couldn't be used here</u>. I have also found that application was mostly interested in max(seq) for each id, nevertheless, it didn’t hold this value anywhere. </span><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">It was clear to me that the way schema was designed simply didn’t match application needs.</span><br />
<span style="font-size: x-small;">This is when I knew that schema design must be changed. There has to be a table holding max(seq) for a given id, and this change would surly lead to performance boost. </span><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">Therefore I have created a new table T1_MAXSEQ ( ID number, SEQ number), with unique index on (ID,SEQ). Now, something has to keep this table updated and fully match values in table T1.</span><br />
<span style="font-size: x-small;">I found that application was already using a before insert trigger on T1, which set the correct value of SEQ to max(seq) + 1 for any given ID. Since DMLs were not intensive on T1, </span><span style="font-size: x-small;">I have decided to alter this trigger so it would also insert a new row into T1_MAXSEQ when a new ID is inserted into T1, and update an existing row to SEQ+1 for an existing ID.</span><br />
<br />
<span style="font-size: x-small;">Schema change (new table and trigger update) was <u>minimal and transparent to the application</u>. All was left for them to do was to rewrite the SQL as I have instructed, so that it will now join T1 with T1_MAXSEQ instead of using inline view to find max(seq) for every IDs.</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">SQL should now look like that:</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<em><span style="font-size: x-small;"> SELECT …<br />
FROM T1 s,<br />
<span style="color: #e06666;"> <strong>T1_MAXSEQ maxresults </strong></span> </span></em><br />
<em><span style="font-size: x-small;"> WHERE s.id = maxresults.id AND<br />
s.seq = maxresults.maxseq AND<br />
s.col_a = 0 AND<br />
s.col_b = :1 AND<br />
s.col_c < :2 AND<br />
s.col_d = :3 AND<br />
rownum < 100 </span></em><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">By adjusting schema to application needs and then rewriting this SQL, execution went down <strong>from 35 minutes to 1 sec</strong>, thus application was able to execute this SQL 25k times a day (compare to only 20-70 before).</span><br />
<span style="font-size: x-small;">Oracle is now using the new small table T1_MAXSEQ as the outer table in the nested loop, thus capable of <span style="font-family: "Times New Roman", "serif"; mso-ansi-language: EN-US; mso-bidi-language: HE; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-US;">benefit </span>from count stopkey (execution plan before and after the change can be seen in the link below).</span><br />
<span style="font-size: x-small;">Heavy overload on the instance as well as on machine and disks was stopped. SQL performance was improved dramatically, allowing huge increase in application capacity and productivity.</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;">This can be visully seen in the following pdf: </span><a href="http://www.dboptimize.co.il/oracle_adjust_schema_to_application_needs.pdf"><span style="font-size: x-small;">http://www.dboptimize.co.il/oracle_adjust_schema_to_application_needs.pdf</span></a><br />
<span style="font-size: x-small;"><br />
</span><br />
<div dir="ltr"><span style="font-size: x-small;">Again, just from a short conversation with the application and by asking the right questions I manage to understand where the problem resides and expand my ways to solve this problem.</span></div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;"><br />
</span></div><div class="MsoNormal" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">I will say it again (and probably not for the last time): Most of the performance issues I have seen in instances were application issues and not instance configuration issues. Knowing your application is one of the most important steps in each tuning process. </span></div><span style="font-size: x-small;"><br />
</span><br />
<div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Merav Kedem,</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Founder of </span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: x-small;">DB OPTimize</span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: x-small;">Oracle Performance Tuning & DBA Consulting</span></div><div dir="ltr" style="text-align: left;"><a href="http://www.dboptimize.co.il/"><span style="color: #249fa3; font-size: x-small;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3; font-size: x-small;">merav@dboptimize.co.il</span></a></div>Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com0tag:blogger.com,1999:blog-3647965877069534489.post-786957096817681552011-05-04T13:47:00.000-07:002011-05-07T11:31:06.647-07:00Beyond database tuning<span style="font-size: x-small;">Let me share with you something that happened to me today.</span><br />
<span style="font-size: x-small;">I saw this very heavy SQL, running several times a day, taking up to 2 hours each time, consuming lot of database resources (CPU & IO).</span><br />
<span style="font-size: x-small;">This select was doing a count on a very big table (150M rows) joined with a smaller table (5M rows) and had some non selective filter predicates in its where clause.</span><br />
<span style="font-size: x-small;">Tuning capability didn’t look so promising for this SQL.</span><br />
<span style="font-size: x-small;">I have decided to go and ask the application for the purpose of this SQL, and investigate what it does with its result (count).</span><br />
<span style="font-size: x-small;">What I found was quite a shock. They code was basically doing that:</span><br />
<br />
<div style="margin-left: 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;"><span style="font-size: x-small;"><span style="mso-bidi-font-style: italic;"><span style="mso-list: Ignore;">1.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span></span><span dir="ltr"></span>select count(*) from T1, T2 where ... /* <i>This brings back a number after 2 hours processing. Lets call it X */</i></span></div><br />
<div style="margin-left: 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;"><span style="font-size: x-small;"><span style="mso-list: Ignore;">2.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span><span dir="ltr"></span>devide X/100 (to get number of loops to be made)</span></div><br />
<div style="margin-left: 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;"><span style="font-size: x-small;"><span style="mso-list: Ignore;">3.<span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span></span><span dir="ltr"></span>for 1 in 1.. x/100 {</span></div><div style="margin-left: 18pt; text-indent: 36pt;"><span style="font-size: x-small;"><span style="mso-spacerun: yes;"> </span><span style="mso-spacerun: yes;"> </span>select ... from T1,T2 where ... and rownum =< 100 /* <i>rowset to be processed. 100 each time */</i></span></div><div style="text-indent: 36pt;"><span style="font-size: x-small;">}</span></div><br />
<span style="font-size: x-small;">As a result, row processing (the SELECT inside the loop) couldn't not start till the count query is finished. </span><br />
<span style="font-size: x-small;">In other words, program is wasting two hours for nothing waiting for this count to finish, while in fact this count is unnecessary, causing unneeded overhead, and is just bad code writing. </span><br />
<span style="font-size: x-small;">Application should remove the count query, and simply run select in a loop till no_data_found is returned. It should logically look like that (pseudo):</span><br />
<br />
<span style="font-size: x-small;">cont_processing = 1</span><br />
<span style="font-size: x-small;">while ( cont_processing ) {</span><br />
<span style="font-size: x-small;"> select ... from T1,T2 where ... and rownum =< 100 ; -- just the inner loop</span><br />
<span style="font-size: x-small;"> if no_data_found then cont_processing =0</span><br />
<span style="font-size: x-small;">}</span><br />
<br />
<span style="font-size: x-small;">I know. This is pure code tuning, and has nothing to do with SQL tuning. Is it DBA responsibility? Some will say yes, and some will say no. But, if I wouldn't have gone to the application, and examine the code by myself, this extremely heavy SQL will continue to run in the database, interfering others, causing performance degradation.</span><br />
<br />
<span style="font-size: x-small;">What is the conclusion from this real life story?</span><br />
<span style="font-size: x-small;">I believe that DBA and application should work together. It is always proving itself. So my advice to you is to talk with your application representative as much as possible. Know the source of the SQL you see running in your database. Learn their code, and remember: Knowledge is power, and when you know more, you expand your options for solving a problem.</span><br />
<br />
<div dir="ltr" style="text-align: left;"><span style="font-size: xx-small;">Merav Kedem,</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: xx-small;">Founder of </span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: xx-small;">DB OPTimize</span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: xx-small;">Oracle Performance Tuning & DBA Consulting</span></div><div dir="ltr" style="text-align: left;"><a href="http://www.dboptimize.co.il/"><span style="color: #249fa3; font-size: xx-small;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3; font-size: xx-small;">merav@dboptimize.co.il</span></a></div><div dir="ltr" style="text-align: left;"> </div>Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com3tag:blogger.com,1999:blog-3647965877069534489.post-65677918773170238082011-04-23T10:43:00.000-07:002011-04-28T01:51:34.311-07:00Tuning a frequently executed SQL<div dir="ltr"><div dir="ltr"><span style="font-size: x-small;">When we search for untuned SQLs we can look for SQL that its execution time is objectively long, but we should also search for SQL which has high amount of short executions, or in other words frequently executed SQL. When dealing with frequently executed SQLs, even a small improvement in one execution (average execution time) will yield a performance improvement, since this "small" improvement of one execution is multiplied by its many executions.</span></div><div dir="ltr"><span style="font-size: x-small;">In other words, when looking at v$sql, look for the ones with high executions:</span><br />
<span style="font-size: x-small;"> select * from v$sql order by executions desc;</span><br />
<span style="font-size: x-small;">or for the ones with high average execution time:</span><br />
<span style="font-size: x-small;"> select * from v$sql order by elapsed_time/executions desc; </span><br />
<br />
</div><div dir="ltr"><span style="font-size: x-small;">I was visiting a customer which one of his top three heaviest SQLs was a query executed 1.5M a day, with average execution of 0.013 sec. Allegedly, when we look at its average execution time, this SQL won't be our first candidate for tuning, but when we look at its overall consumption (execution time of all its executions), well, that would be another story. </span></div><div dir="ltr"><span style="font-size: x-small;">I have tuned this query by simply changing the order of index columns, causing average execution to go down to 0.005 sec (2.6 times faster).</span></div><div dir="ltr"><span style="font-size: x-small;">After that overall resource consumption of the query went down dramatically. Complete tuning flow can be visually seen in the following pdf:<span dir="ltr"></span></span></div><a href="http://www.dboptimize.co.il/Oracle_frequently_executed_sql.pdf"><span style="font-size: x-small;">http://www.dboptimize.co.il/Oracle_frequently_executed_sql.pdf</span></a><br />
<br />
<span style="font-size: x-small;">Note: <span dir="ltr">The example shown is for Oracle, thought the methodology of tuning fast and frequently executed query is relevant for all databases: Oracle, MSSQL, DB2, Sybase, Mysql, etc.</span></span><br />
<br />
<div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Merav Kedem,</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Founder of </span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: x-small;">DB OPTimize</span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: x-small;">Oracle Performance Tuning & DBA Consulting</span></div><div dir="ltr" style="text-align: left;"><a href="http://www.dboptimize.co.il/"><span style="color: #249fa3; font-size: x-small;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="color: #249fa3; font-size: x-small;">merav@dboptimize.co.il</span></a></div></div>Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com0tag:blogger.com,1999:blog-3647965877069534489.post-64879994907346153742011-04-23T01:14:00.000-07:002011-04-24T02:33:40.370-07:00Abnormal program behavior and massive log file sync<div dir="ltr"><span style="font-size: 8pt;">Historical performance data is extremely important when tuning application. This gives us the ability to instantly know when things start go wrong, and the ability to investigated over the past what start causing things to go wrong. Often I am feeling like a modern Sherlock Holmes, searching for criminals :)</span></div><div dir="ltr"><span style="font-size: 8pt;">OEM does this, and so does several third party tools (such as Precise). Of course this can be done by manual scripts also.</span></div><div dir="ltr"><span style="font-size: 8pt;">Here is an example:</span></div><div dir="ltr"><span style="font-size: 8pt;">One of my customers was replicating data from mainframe to Oracle, and was complaining that replication has started working very slowly. With this information I went comparing program's current behavior to program's normal behavior bases on overtime activity information collected and saved for this instance. Indeed I saw that suddenly the program have started waiting much longer than it use to on "log file sync". This was clue #1.</span></div><div dir="ltr"><span style="font-size: 8pt;">When looking at a wider time frame of program's activity I have spotted the exact time when things start going wrong. This was clue #2.</span></div><div dir="ltr"><span style="font-size: 8pt;">Ok, something was changed. Now I needed to find out what was changed that was causing replication program to start waiting longer and longer for "log file sync".</span></div><div dir="ltr"><span style="font-size: 8pt;">As we all know one of the reasons for "log file sync" is massive commits. I went looking for overtime information on Commits, and saw that the amount of commits were doubled compared to their usual amount per day (3.4M instead of 1.8M). This was clue #3. Suspicious....</span></div><div dir="ltr"><span style="font-size: 8pt;">I needed to look for the cause of this massive increased in commits. First, to answer - when do we do commits? After DMLs of course. So let's look for suspicious DMLs (new DMLs, DMLs with high executions, DMLs with increased executions, etc). This was clue #4.</span></div><div dir="ltr"><span style="font-size: 8pt;">Now we are getting closer to solving the mystery. When adding clue #4 (suspicious DMLs) to clue #2 (time when things start going wrong), it wasn't hard to find the criminal to blame. A massive new UPDATE had started at that time, executing over 2.6M executions a day, causing the massive commits, and the increase of "log file sync".</span></div><div dir="ltr"><span style="font-size: 8pt;">Now all I have to do was to go back to the application and ask them few questions, such as: what was this SQL, why was it added, why was it executing so many times a day.</span></div><div dir="ltr"><span style="font-size: 8pt;">You will be surprise to know that the answer was "by mistake". Well, they remove this UPDATE and things went back to normal. </span></div><div dir="ltr"><span style="font-size: 8pt;">All this investigation was finished by less than an hour. No doubt it would have been much harder and much longer to find the root cause of the abnormal program behavior without overtime information saved on application activity and instance activity.</span></div><div dir="ltr"><span style="font-size: 8pt;">All this investigation and conclusion can be <span style="font-family: "Times New Roman","serif"; font-size: 8pt;">visually </span>seen in the following link: <a href="http://www.dboptimize.co.il/Oracle_abnormal_program_behavior_Redo_log.pdf">http://www.dboptimize.co.il/Oracle_abnormal_program_behavior_Redo_log.pdf</a></span></div><div dir="ltr"><br />
</div><span style="font-size: 8pt;"><span style="font-size: 8pt;">Remember: from my experience at least 80% of instance performance problems are caused by application... </span><span style="font-size: 8pt;">Much less are caused by wrong instance tuning (init parameters etc). It is always beneficial to know your application and talk to people who are responsible to it.</span></span><br />
<br />
<div dir="ltr" style="text-align: left;"><span style="font-size: xx-small;">Merav Kedem,</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: xx-small;">Founder of </span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: xx-small;">DB OPTimize</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: xx-small;"><span style="color: black;">Oracle Performance Tuning & DBA Consulting</span></span></div><div dir="ltr" style="text-align: left;"><a href="http://www.dboptimize.co.il/"><span style="font-size: xx-small;"><span style="color: #249fa3;">http://www.dboptimize.co.il/</span></span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="font-size: xx-small;"><span style="color: #249fa3;">merav@dboptimize.co.il</span></span></a></div><div dir="ltr" style="text-align: left;"></div>Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com0tag:blogger.com,1999:blog-3647965877069534489.post-45147064907079158122011-04-10T07:39:00.000-07:002011-04-28T03:08:29.810-07:00Identify heaviest plan_line_id for a given execution plan (Oracle 11g)<div dir="rtl" style="text-align: right;" trbidi="on"><span style="font-size: x-small;"></span><br />
<div style="text-align: left;"><span style="font-size: x-small;"></span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><span style="font-size: 10pt;"><span style="font-size: 10pt;">I think the most mysterious question to me (well, as far as Oracle was the subject) was always that:</span><br />
<u><span style="font-size: 10pt;">For a given SQL</span></u><span style="font-size: 10pt;"> and its execution plan, where does Oracle spend most of its time? </span><br />
<br />
<span style="font-size: 10pt;">Oracle 11g revealed this mystery by exposing Real Time SQL Monitoring, v$sql_monitor, and v$sql_plan_monitor. However, these performance views will only show information either for relatively long running queries ("when it has consumed at least 5 seconds of CPU or I/O time") or for all parallel queries. Suppose you have SQL with shorter execution time, running many times? You won't be able to find it there...</span><br />
<span style="font-size: 10pt;">And before Oracle 11g was released, things were a bit harder...</span></span></span><span style="font-size: x-small;"></span></div><div dir="ltr" style="text-align: left;"><br />
<span style="font-size: 10pt;">In the following session I will demonstrate how to find, for a particular execution plan, in which execution plan step and on which object Oracle spends most of it time.</span><br />
<br />
</div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Oracle used is 11.1.0.7<br />
Application is running the following SQL 600k times each day:</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> select * <br />
from t1 <br />
where ID= :1 <br />
order by SEQ;</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Let's find sql_id of this SQL:</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like 'select * from t1 where ID= :1 order by SEQ';</span><br />
<br />
</div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> SQL_ID PLAN_HASH_VALUE SQL_TEXT<br />
------------- --------------- --------------------------------------------------------------<br />
b652k1fnuqz5w 406502908 select * from t1 where ID= :1 order by SEQ</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Let's find execution plan used:</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('b652k1fnuqz5w'));</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> PLAN_TABLE_OUTPUT<br />
--------------------------------------------------------------------------------------------------------------------------------------------<br />
SQL_ID b652k1fnuqz5w, child number 0<br />
-------------------------------------<br />
select * from t1 where ID= :1 order by SEQ</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> Plan hash value: 406502908</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> ----------------------------------------------------------------------------------<br />
| Id | Operation | Name | Rows | Bytes | Cost |<br />
----------------------------------------------------------------------------------<br />
| 0 | SELECT STATEMENT | | | | 1 |<br />
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 4 | 512 | 1 |<br />
|* 2 | INDEX RANGE SCAN | T1_IX1 | 4 | | 1 |<br />
----------------------------------------------------------------------------------</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> Predicate Information (identified by operation id):<br />
---------------------------------------------------</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> 2 - access("ID"=:1)<br />
filter("ID"=:1)</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> Note<br />
-----<br />
- cpu costing is off (consider enabling it)</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> 24 rows selected.</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"></span><br />
<div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">Execution plan is very simple. Oracle is using INDEX RANGE SCAN on T1_IX1, applying predicate "ID = :1", then, it is using rowids stored in the index to fetch requested data " select * ".</span></div><div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;"><br />
</span></div><div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">How can we tell where does Oracle spend its time? </span></div><div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;"><br />
</span></div><div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">Let's have a look at v$active_session_history. This magnificent view was introduced in 10g, though in Oracle 11g several valuable columns were added to it. Execution plan step information is one example. </span><span style="font-size: x-small;">For each sample and for each row added to this view (which reflect session's activity), it states to which step this activity is related.</span></div><div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">Therefore, if we select all samples for the given sql_id, and group all those returned rows by </span><br />
<span style="font-size: x-small;">sql_plan_operation, sql_plan_option, sql_plan_line_id, current_obj# and event, we will be able to see the breakdown of sql_plan_line_id and find the heaviest step in the plan.</span></div><div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">The step with the most samples will be the heaviest execution plan step, and our tuning efforts should be focused there.</span></div><div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">Of course, for a simple plan it's quite easy, but go figure an execution plan with several dozens of steps without knowing which step is problematic. </span></div><div class="MsoNormal" dir="ltr" style="direction: ltr; margin: 0cm 0cm 0pt; text-align: left; unicode-bidi: embed;"><span style="font-size: x-small;">With this technique mystery can be revealed.</span></div><span style="font-size: x-small;"></span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> SQL> select sql_plan_operation operation, sql_plan_options options, sql_plan_line_id line_id, current_obj# obj#, event, count(*)<br />
2 from v$active_session_history<br />
3 where sql_id='b652k1fnuqz5w'<br />
4 and sql_plan_hash_value=406502908<br />
5 group by sql_plan_operation, sql_plan_options, sql_plan_line_id, current_obj#,event;</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;"> OPERATION OPTIONS LINE_ID OBJ# EVENT COUNT(*)<br />
--------------- --------------- ---------- ---------- ------------------------- ----------<br />
INDEX RANGE SCAN 2 3657730 db file sequential read 44<br />
TABLE ACCESS BY INDEX ROWID 1 302302 db file sequential read 110</span></div><div dir="ltr" style="text-align: left;"><br />
<br />
<span style="font-size: x-small;">We can see that most of the time is spent on fetching the data (select *) from the table. 110 samples out of 154 (44+110) samples (71.4%) were captured on TABLE ACCESS BY INDEX ROWID, while only 44 samples out of 156 samples (28.6%) were captured on INDEX RANGE SCAN. </span><span style="font-size: x-small;">In other words fetching * from the table using rowid brought by the index is very expensive.</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Let's examine table (current_obj#=302302):</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> SQL> select table_name, num_rows, blocks<br />
2 from dba_tables<br />
3 where (owner,table_name) =<br />
4 ( select owner,object_name from dba_objects where object_id=302302);</span><br />
<br />
</div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> TABLE_NAME NUM_ROWS BLOCKS<br />
---------------------- ------------------- ------------<br />
T1 25019183 479813</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Let's examine index (current_obj#=3657730):</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> SQL> select i.table_name,i.index_name,i.column_name,i.column_position ,c.num_distinct<br />
2 from dba_ind_columns i,dba_tab_columns c<br />
3 where (index_owner,index_name) =<br />
4 ( select owner,object_name from dba_objects where object_id=3657730)<br />
5 and i.table_owner = c.owner<br />
6 and i.table_name = c.table_name<br />
7 and i.column_name = c.column_name<br />
8 order by column_position;</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"> TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION NUM_DISTINCT<br />
---------------------- --------------------- --------------------------- --------------------------- --------------------------<br />
T1 T1_IX1 ID 1 5733888<br />
T1 T1_IX1 SEQ 2 236</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Looking at the index definition and statistics of the used index T1_IX1(ID,SEQ), we see that the index is well suited for this statement (where id = :1 order by seq). </span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Saying that, we need to go back with that to the Application and find out whether they do need to " select * " from the table (table T1 has 31 columns).<br />
In case needed columns list is much narrow than "select *", application should eliminate "select *" use, and replace it with only needed columns. Then, if only a small set of columns are fetched, those columns can be added to the index (covering index), and Oracle can fetch needed data only from the index, without accessing the table.</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Few notes:</span><br />
<span style="font-size: x-small;">1) v$active_session_history is part of <span style="font-family: Palatino-Roman; font-size: x-small;"><span style="font-family: Palatino-Roman; font-size: x-small;">Oracle Diagnostics Pack, therefore requires <span style="font-family: Palatino-Roman; font-size: x-small;"><span style="font-family: Palatino-Roman; font-size: x-small;">Oracle Diagnostics Pack license. v$sql_monitor and v$sql_plan_monitor are part of <span style="font-family: Palatino-Roman; font-size: x-small;"><span style="font-family: Palatino-Roman; font-size: x-small;">Oracle Tuning Pack, therefore requires Oracle Tuning Pack license.</span></span></span></span></span></span></span><br />
<span style="font-size: x-small;">2) Regardless the cost of fetching all columns, selecting * is never recommended. Suppose that someone added a column to the table without notifying all applications and one application is using select * in its code. Suddenly an extra unexpected column is returned back to the application. </span></div><div dir="ltr" style="text-align: left;"></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">3) sql_plan_operation and sql_plan_options are new columns added to v$active_session_history on version 11g. These two columns do not exist in v$active_session_history in version 10g. Nevertheless, there are ways to located heavy steps in 10g also. This will be covered on next session. <br />
To be continued...</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><br />
</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Merav Kedem,</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;">Founder of </span></div><div dir="ltr" style="text-align: left;"><span style="color: black; font-size: x-small;">DB OPTimize</span></div><div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"><span style="color: black;">Oracle Performance Tuning & DBA Consulting</span></span></div><div dir="ltr" style="text-align: left;"><a href="http://www.dboptimize.co.il/"><span style="font-size: x-small;">http://www.dboptimize.co.il/</span></a><br />
<a href="mailto:merav@dboptimize.co.il"><span style="font-size: x-small;">merav@dboptimize.co.il</span></a></div><span style="font-size: x-small;"><br />
</span><br />
<span style="font-size: x-small;"><br />
</span><br />
<div dir="ltr" style="text-align: left;"><span style="font-size: x-small;"></span></div></div>Merav Kedem's Bloghttp://www.blogger.com/profile/04962151891192655993noreply@blogger.com1