Oracle Database Blog: Experiments & Learnings

July 26, 2011

Other Options !

Filed under: Oracle RDBMS — Tags: , , , , , — Saurabh Manroy @ 9:50 am

In my last  post, I demonstrated how stored outlines ditched me. Then I started looking out for other options like SQL profiles and dbms_advanced_rewrite package.

a.) SQL Profiles

Tuning task was created using AWR snapshots. Endpoints of snapshot were chosen from the time interval when query wasn’t performing as expected.

DECLARE
 l_sql_tune_task_id  VARCHAR2(100);
BEGIN
 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                         begin_snap  => 12956,
                         end_snap    => 12959,
                         sql_id      => '8pg71n7580t88',
                         scope       => DBMS_SQLTUNE.scope_comprehensive,
                         time_limit  => 60,
                         task_name   => '8pg71n7580t88_tuning_task',
                         description => 'Tuning task for statement 8pg71n7580t88 in AWR.');
 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '8pg71n7580t88_tuning_task');

SELECT DBMS_SQLTUNE.report_tuning_task('8pg71n7580t88_tuning_task') AS recommendations FROM dual;

 

 

Recommendation from SQL profile was to restructure the SQL statement which was not a possibility.  So, I moved to next option.

b.) DBMS_ADVANCED_REWRITE

While doing tests, I found that hinting the query with proper index worked and the query ran like a trace of bullet. So, without changing the application code, I thought of trying this option. Then  the same multibyte characterset  glitch : With multibyte characterset, this package didn’t work as expected. I google*d it and found couple of posts reporting same erratic  behavior with UTF8 characterset. So, using dbms_advanced_rewrite package to fix the plan was ruled out.

Further actions

There were two indexes on one of the tables (FILL). One was local index based on 6 columns  (PK_FILL)  and another was a Global index based on just 1 column (IND_FILL_ACCTFILL).  When query performed suboptimally, it used  PK_FILL index to perform a range scan. This used to cause lots of buffer gets and hence delay in processing.

Areas of Concern in the execution plan:

Good Plan

|   5 |      PARTITION RANGE ITERATOR          |                   |     8 |   720 |   179   (0)| 00:00:03 |   KEY |   KEY |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| FILLITEM          |     8 |   720 |   179   (0)| 00:00:03 |   KEY |   KEY |
|   7 |        INDEX RANGE SCAN                | INX_FILLITEM_ACCT |   168 |       |   127   (0)| 00:00:02 |   KEY |   KEY |
|   8 |      TABLE ACCESS BY GLOBAL INDEX ROWID| FILL              |     3 |   102 |     6   (0)| 00:00:01 | ROW L | ROW L |
|   9 |       INDEX RANGE SCAN                 | IND_FILL_ACCTFILL |     3 |       |     3   (0)| 00:00:01 |       |       |

Bad Plan

|   5 |      PARTITION RANGE ITERATOR          |                   |    12 |  1080 |   175   (0)| 00:00:03 |   KEY |   KEY |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| FILLITEM          |    12 |  1080 |   175   (0)| 00:00:03 |   KEY |   KEY |
|*  7 |        INDEX RANGE SCAN                | INX_FILLITEM_ACCT |   159 |       |   127   (0)| 00:00:02 |   KEY |   KEY |
|   8 |      PARTITION RANGE ITERATOR          |                   |     1 |    34 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |       INDEX RANGE SCAN                 | PK_FILL           |     1 |    34 |     3   (0)| 00:00:01 |   KEY |   KEY |

 
Base tables involved in the query were partitioned tables (partitioned month wise). Due to size of the partition, only 20% of table’s data was sampled for analysis. There were lots of inserts happening on these tables every second. So, keeping statistics upto date was another challenge (may be that was the reason, plan got changed everyday). During the night when activity on the database was negligible, I planned to have a closer look at the data distribution in table partition that was having data from last month (May).  I executed multiple queries on the table partition to understand the data distribution and then collected statistics using dbms_stats on this old partition from month of May (with estimate_percent => 100, cascade => true) and created histograms only for columns that actually needed them. Using this new data distribution, the query responded as expected.  I also checked the total amount of data that every partition had for last couple of months. Every month partition reported around 29 to 30 million rows. So, an immediate thought was to copy statistics from old partition (on which 100% data was sampled) to present and future partitions. This would help in making sure that optimizer has same statistics throughout the month (From 1st to 31st).

Similar activity was performed for FILLITEM table.

For Local Indexes like PK_FILL following was performed:

a.)  dbms_stats.set_index_stats was used to update statistics of current month and future months and stats were locked. Statistics Source: from the partition of May.

b.) Stats were also gathered without any partition information. dbms_stats.gather_index_stats (indname=> PK_FILL, partname=> null);

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: