Bobby Durrett's DBA Blog

In an earlier post I described the process that I go through to check the plans of production SQL queries to see if they change when you do a system upgrade or patch.  Today I’ve been following that process to prepare for a production patch from 11.2.0.1 to 11.2.0.3.  I extracted 2000 queries from production and got their plans on both our patched 11.2.0.3 test instance and our un-patched 11.2.0.1 production instance.

Unfortunately, most of the plans changed.  The reason in this case is that we had to change an init parameter to resolve an issue we found in testing.  Now most of the plans are different.  Ack!

But, the good thing is that I can run all of the queries (no updates, deletes, or inserts) against our test database and see which ones run for a long time.  Then I can check their plan and runtime on production.  That will at least help me catch any major gotchas.

I’ve done all this before, but the thing I’m doing differently this time is using this setting in my sqlplus script that tests all the production SQL against our test database:

SET AUTOTRACE TRACEONLY

What is cool about this setting is that it will still give you the plan and statistics but won’t list out all the returned rows.  I have no idea how much output the queries return but all I really want is their elapsed time and plans.

So, like I said in my previous post this process isn’t perfect but it may help me detect any big plan changes caused by our patch and init change and address them before going to production.

– Bobby

We usually use locally partitioned indexes on partitioned tables.  Many of our data warehouse tables are partitioned by date and partitions get added or removed over time.  It is much easier to maintain the indexes on this kind of table if they are locally partitioned.  You can just drop a partition without rebuilding the indexes and you can rebuild indexes on a particular partition if needed, such as after compressing the partition.

But, I was asked whether a particular query would run faster with a global non-partitioned index on a partitioned table instead of a locally partitioned index.  I said that I did not know of any reason to expect better performance from a global non-partitioned index, but it got me thinking so I did some experiments.

I have found a case where a global non-partitioned index on a table partitioned by date produces better performance than a locally partitioned index.  Here is a zip of my test case.

With the global index my query ran in 68701 microseconds and with the local index it ran in 86816 microseconds.

I’m not sure if this is significant or not.  It looks like each partition of a locally partitioned index has its own tree so you have to at least hit the root block on every partition if your query doesn’t have any condition which limits the partitions to be considered.

Anyway, I’d say stick with locally partitioned indexes unless you prove that something else is faster and you know the index maintenance isn’t a problem.  But this test case shows that it is at least possible to get better performance with a non-partitioned global index on a partitioned table so there may be some special case where the performance benefit is great enough to be worth the maintenance headache.

– Bobby

I’ve uploaded a sql script that gets an AWR report for yesterday, a week from yesterday, and four weeks from yesterday for the same time period – 10 am to 4  pm.

I’ve been manually reviewing the previous day’s AWR report on a database against the previous week and month.  So, I scripted up a sqlplus script that will automatically figure out the snap_ids I need for the three reports and call awrrpt.sql with the necessary variables set.  This might be helpful for someone else who is doing daily monitoring as I am.  I mainly just check the”Top 5 Timed Events” and “SQL ordered by Elapsed Time” to see if they have changed from the previous week and month.

Just download the zip, unzip it, and run awrdayweekmonth.sql on your unix database server and it will generate three AWR reports in your current directory.

– Bobby

P.S.  One detail I forgot in my original post.  I’m only interested in Monday-Friday so if you run this script on a Monday it will show you the AWR for Friday, a week ago Friday, and four weeks ago Friday.

I’m helping a coworker learn how to do Oracle performance tuning and today I think we found some good documentation on which V$ views to use for tuning which can also help with reading an AWR report.  In a previous post I talked about how I was asked for AWR documentation and couldn’t find any comprehensive documentation of all the details of an AWR report.  This is still true, but Chapter 10 of the 11gR2 Performance Tuning Guide does have a nice detailed review of the V$ views and how to use them to resolve certain types of performance problems.

Years ago I learned about how to use the V$ views for performance tuning from papers and books by Craig Shallahamer, Don Burleson, and others.  I don’t believe the older versions of the performance tuning guide had the details that these authors had in their materials.  I remember reading the version 7 performance tuning guide multiple times but I have to confess I haven’t read the 11.2 guide cover to cover.  It looks like chapter 10 is more comprehensive than earlier guides in terms of describing the V$ views and how to use them for tuning.  So, I’m going to read it along with my coworker and see if this chapter is helpful.  The jury is still out, but at first look it seems like a worthwhile chapter to study.

– Bobby

I’ve known for a while that if you have an Exadata table compressed with Query High compression and you want to load data into it and have it be compressed as it is loading you have to use direct path loads such as those you get with an insert with an append hint.

I just found out today that this has been true since at least version 9.2 with basic compression.  So, if you have a compressed table and can load it with inserts that have the append hint you won’t need to re-compress the table after the load.

Here is my 9.2 test:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0    Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> 
SQL> drop table test;

Table dropped.

SQL> 
SQL> create table test as select * from dba_tables;

Table created.

SQL> 
SQL> select blocks from user_segments where segment_name='TEST';

    BLOCKS
----------
        48

SQL> 
SQL> alter table test move compress;

Table altered.

SQL> 
SQL> select blocks from user_segments where segment_name='TEST';

    BLOCKS
----------
        16

SQL> 
SQL> truncate table test;

Table truncated.

SQL> 
SQL> alter table test move compress;

Table altered.

SQL> 
SQL> insert into test select * from dba_tables;

2740 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select blocks from user_segments where segment_name='TEST';

    BLOCKS
----------
        40

SQL> 
SQL> truncate table test;

Table truncated.

SQL> 
SQL> alter table test move compress;

Table altered.

SQL> 
SQL> insert /*+append */ into test select * from dba_tables;

2740 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select blocks from user_segments where segment_name='TEST';

    BLOCKS
----------
        16

Uncompressed the table is 48 blocks and if you insert the same rows without the append hint you get 40 blocks which is about the same.

Compressed the table shrinks down to 16 blocks.  If you insert the same data with an append hint you get the same size 16 blocks.  So, the append hint causes the data to be compressed as it is loaded.

Here is a zip of my tests on several versions of Oracle and on Exadata.

– Bobby

A coworker of mine just brought a new hint to my attention – the APPEND_VALUES hint.  I’m used to the append hint which makes insert statements use direct path instead of conventional loading.  Here is what the old standby append looks like:

insert /*+append */ into target select * from source;

Here is what the new append_values hint looks like:

insert /*+append_values */ into target values (‘some data’);

It looks like this in new in 11.2.  I tried my tests on an 11.1 instance and got different results.  I got an error on 11.2 trying to use a regular for loop:

SQL> 
SQL> BEGIN
  2  
  3    FOR k IN 1..10 LOOP
  4      FOR j IN 1..10000 LOOP
  5        INSERT /*+append_values */ INTO test VALUES (1);
  6      END LOOP;
  7      commit;
  8    END LOOP;
  9  END;
 10  /
BEGIN
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after 
modifying it in parallel
ORA-06512: at line 5

But I don’t get this error on 11.1.  Also, on my 11.2 instance my test ran 14 seconds without the hint and 10 seconds with it, but on 11.1 and 10.2 I saw almost no difference.

Here is the 11.2 test with the hint:

SQL> DECLARE
  2    TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  3    testlist NumList;
  4  BEGIN
  5  
  6    FOR j IN 1..10000 LOOP
  7      testlist(j) := j;
  8    END LOOP;
  9  
 10    FOR k IN 1..1000 LOOP
 11  
 12      FORALL i IN 1..10000
 13        INSERT /*+ append_values */
 14        INTO test
 15        VALUES (testlist(i));
 16  
 17      commit;
 18  
 19    END LOOP;
 20  
 21  END;
 22  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.01

Here is the 11.2 test without the hint:

SQL> DECLARE
  2    TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  3    testlist NumList;
  4  BEGIN
  5  
  6    FOR j IN 1..10000 LOOP
  7      testlist(j) := j;
  8    END LOOP;
  9  
 10    FOR k IN 1..1000 LOOP
 11  
 12      FORALL i IN 1..10000
 13        INSERT
 14        INTO test
 15        VALUES (testlist(i));
 16  
 17      commit;
 18  
 19    END LOOP;
 20  
 21  END;
 22  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.01

My two test scripts and their logs are in a zip here which you can download and try yourself.  I’m not sure if it is possible to construct an example with more dramatic improvement in performance but 10 seconds versus 14 is still worthwhile.

– Bobby

I’m at the East Coast Oracle Users Conference and wanted to write a blog post about the first day, which is now done.  Rather than summarize all the sessions I thought I would focus on what struck me the most – namely the confirmation I received that cardinality is at the heart of SQL tuning.

I was reviewing my upcoming SQL tuning presentation and became afraid that I had focused too much on determining the number of rows, or cardinality, that queries or parts of queries returned.  But I attended two Tom Kyte sessions this morning that both reenforced the idea that I was on the right track.  One quote from a session was “A wrong plan is always caused by wrong cardinality estimates”.  He revised “always” to “almost certainly” which is still pretty strong.

My talk isn’t only about poorly performing queries where the optimizer has a bad cardinality estimate.  It is also about queries where the estimate is correct but no good plan exists.  I.e. You might need an index.  But, when I manually tune a query, regardless of why it is slow, I start with doing counts on various parts of the query to find out the true cardinality.

Evidently Oracle is putting research and development money into improving the optimizer’s cardinality estimates in future releases.  If Oracle and Tom Kyte see cardinality as central to proper query optimization and if my own experience confirms it then it makes a strong case to me that cardinality is at the heart of SQL tuning – whether automatically done by the optimizer or manually done by a human.

– Bobby

I just found out that my alternate talk for the East Coast Oracle Users Conference is needed.  So, I’ll be doing my Exadata talk on Wednesday and my Intro to SQL Tuning talk Thursday.  This makes the conference even more fun for me since I’ll be busier making sure I do a good job on both talks.

Here is my schedule and links to the PowerPoint presentations:

Wednesday, October 17th, 1:15 pm-2:15 pm Exadata Distinctives

Thursday, October 18th, 11:30 am-12:30 pm Intro to SQL Tuning

If you are coming to the conference come check these out.  They are both focused primarily on tuning.  There is only a small overlap between the two.  A couple of my SQL tuning examples come from Exadata systems.

– Bobby

I was working on a package that we use to gather statistics on the partition level after data is loaded and found some quirks that apply when the partition has subpartitions.

1. You can’t use dbms_stats.gather_table_stats to gather stats on just one subpartition. (SEE THE COMMENTS BELOW FOR CLARIFICATION ON THIS)
2. If you gather stats on a partition with granularity=>’PARTITION’ it doesn’t gather stats at the subpartition level.

So, if you update some data in subpartitions of a partition and want to update statistics on both the partition and its subpartitions you have to call dbms_stats.gather_table_stats twice.  Once with granularity=>’PARTITION’  and once with granularity=>’SUBPARTITION’.

i.e. If you have a subpartitioned table called SUBPARTAB and partition PART_20120101 and you want to gather stats at the partition and subpartition level you run the following commands.

execute dbms_stats.gather_table_stats(
NULL,'SUBPARTAB','PART_20120101',
granularity=>'PARTITION');

execute dbms_stats.gather_table_stats(
NULL,'SUBPARTAB','PART_20120101',
granularity=>'SUBPARTITION');

Note that there is no subpartition parameter to dbms_stats.gather_table_stats.  You can only specify the partition name.

Here an example script that demonstrates gathering stats at the global, partition, and subpartition levels and shows which update subpartition stats.

– Bobby

I got the following Facebook message about the East Coast Oracle Users Conference which starts in less than two weeks:

It’s Friday and ECO is just 12 days away. Looking forward to seeing everyone! Please spread this discount code to friends and colleagues 100512ECO, it will be good through tomorrow AM.

I’m doing my “Introduction to SQL Tuning” talk.  The PowerPoint is here.  The initial paper on the topic is here.  I’m speaking at 11:30 am on Thursday, October 18th.  The main days of the conference are October 17th and 18th.

I think the promotional code 100512ECO from the Facebook post will give you discounted registration.  This might be a nice lower cost conference for people who live near North Carolina and who didn’t attend Openworld in San Francisco this week (I didn’t).  It is two days instead of four, but has some top speakers and a good selection of content.

– Bobby