Bobby Durrett's DBA Blog

We ran into a performance issue where all of the top queries in our AWR report were doing a LIKE comparison of a NUMBER column against a character literal like this:

customer_number LIKE ‘123456%’

customer_number is a selective predicate and has an index but the index wasn’t being used.  Also the number in the like condition 123456 in the example was the complete customer number.  So, we should just be doing this:

customer_number = 123456

So, I built a test script to test this out.  One interesting thing in my test script was that comparing the number column to a character literal with the = operator didn’t suppress this index.  But using LIKE did.  If you look at the “Predicate Information” section of the plans generated for each query you can see why this is.  In my test script the column of type number is called object_id.

First I did a query with object_id=2, which is comparing a number to a number.  This query used the index and the predicate looked like this:

access(“OBJECT_ID”=2)

But, then I ran the same query with object_id = ‘2’ comparing a number column to a character literal.  But this query also used the index and had the same predicate as above.  So, the character literal ‘2’ must first have been converted to the number 2 so the index could be used.

Next I tried object_id like ‘2’ and this caused a full table scan and the predicate looked like this:

filter(TO_CHAR(“OBJECT_ID”)=’2′)

I think that in order to do a LIKE the object_id had to first be converted to a character string so it could be compared with the literal.  In my test case I didn’t even put in a wildcard character but it didn’t matter.

Lastly, I tried an index on to_char(object_id) and this index was picked up with the like.

So, if you want to take a number and do a LIKE against it just be aware that the optimizer will convert it to a character string first and that any index on the number column will not be used.

– Bobby

I’m still doing state dumps as described in my previous post.

I wanted to collect the state dumps (diag process trace files) from all nodes of my Exadata database and decided to write a couple of scripts to automate it because I anticipate doing more state dumps in the future.  So, I thought I’d share these on the blog.

Both scripts take two arguments: TARGET_HOST and TARGET_SID.  TARGET_HOST is the node in your Exadata cluster that you want to retrieve the state dump from.  TARGET_SID is the instance name on that node.  I.e. If your database name is xyz your TARGET_SID on the third node would be xyz3.

The first script gzips the statedump on the target host and scp’s it back to the host you run the script from.  Within the script you define a local directory where the gzipped dump file will land.  Just be sure there is enough room so you don’t fill up / or some other important filesystem.  Here is the script:

export TARGET_HOST=$1
export TARGET_SID=$2
export DBNAME=`echo $ORACLE_SID|awk '{ print substr($1,1,length($1)-1) }'`
export DIAG_PID=`ssh ${TARGET_HOST} "ps -ef" | grep diag | grep ${TARGET_SID} | awk '{print $2;}'`
export TRACEDIR=/u01/app/oracle/diag/rdbms/$DBNAME/$TARGET_SID/trace
export TRACEFILE=${TRACEDIR}/${TARGET_SID}_diag_${DIAG_PID}.trc
export GZTRACEFILE=${TRACEFILE}.gz
export DUMPDEST=YOURPREFERREDDIRECTORYHERE
ssh ${TARGET_HOST} "gzip < $TRACEFILE > $GZTRACEFILE"
scp ${TARGET_HOST}:${GZTRACEFILE} ${DUMPDEST}
ssh ${TARGET_HOST} "rm $GZTRACEFILE"

So, replace YOURPREFERREDDIRECTORYHERE with the directory on your current host where you want the state dumps to land.  I put this on the first node and ran this script for all nodes in the cluster with the SID’s for the one database that I had gotten the state dumps on.  The script will only work if you are on one of the first 9 nodes because it assumes your current instance number has only one digit.

The second script goes back and truncates the source trace file.  As I mentioned in the previous post the diag process never closes its trace file so to clear out old state dumps you need to cat /dev/null over it.  I didn’t want to put this in the first script because I wanted to make sure the state dumps all got copied over correctly.  Once I have them in my target directory I run this script on all the target hosts and sids:

export TARGET_HOST=$1
export TARGET_SID=$2
export DBNAME=`echo $ORACLE_SID|awk '{ print substr($1,1,length($1)-1) }'`
export DIAG_PID=`ssh ${TARGET_HOST} "ps -ef" | grep diag | grep ${TARGET_SID} | awk '{print $2;}'`
export TRACEDIR=/u01/app/oracle/diag/rdbms/$DBNAME/$TARGET_SID/trace
export TRACEFILE=${TRACEDIR}/${TARGET_SID}_diag_${DIAG_PID}.trc
ssh ${TARGET_HOST} "cat /dev/null >  ${TRACEFILE}"

Now that I have two higher level scripts setup to run these two for every node of my production database I can collect the statedumps into one directory easily and clear out the diag trace files all with two scripts.

– Bobby

One of my favorite children’s books when my children were younger was one about the Berenstain Bears where Father Bear tries to teach his son how to ride a bike and keeps making all these dumb mistakes.  “Let that be a lesson to you” he would say after every goof.

Well, this post is in the same vein.  I was working on an Exadata system trying to get a state dump to find out the source of the high library cache lock waits we have been seeing.  We have a standard statedump/hanganalyze script for Exadata that looks something like this:

oradebug setmypid
oradebug unlimit
oradebug -g all dump hanganalyze 3
oradebug -g all dump systemstate 258
host sleep 90
oradebug -g all dump hanganalyze 3
oradebug -g all dump systemstate 258
oradebug tracefile_name
host sleep 90
oradebug -g all dump hanganalyze 3
oradebug -g all dump systemstate 258
oradebug tracefile_name
exit

We got something like this from Oracle support.  However the oradebug tracefile_name command doesn’t really show you where the state dumps are coming out.  It turns out that when you use the -g all option to do a state dump on every node of an Exadata system the state is dumped by the diag background process.  So, we probably should take out the tracefile_name commands.  But, the alert log has the name of the diag trace file that the state dump goes to so I had no problem finding it.

But, then I got the bright idea of removing (with the Linux rm command) the diag trace file.  It already had 400 megabytes worth of old dumps in it.  Now, in my defense, normally rm’ing a trace file is no big deal.  But the diag trace file is always open.  So, after I removed the trace file and did another statedump I couldn’t find the output anywhere.  Apparently the diag process keeps the file open and all I did with the rm command was remove it from the directory!

Fortunately Oracle support has a document for people such as myself who rm background trace files: “How to recreate background trace file(s) that may have been accidentally deleted [ID 394891.1]”  All you have to do is get the Unix process id of the diag process and plug it into this script:

oradebug setospid 4804 
oradebug close_trace 
oradebug flush 
exit

In this example the Unix process id of diag was 4804.  You can do this command to double check that 4804 really is the diag process:

ps -ef | grep 4804

You should see a process in the format ora_diag_INSTANCENAME.

So, in the future when getting a state dump on Exadata I’m just going to do this command to clear out the old dumps:

cat /dev/null > INSTANCENAME_diag_4804.trc

Once I’ve cleared the diag trace file then I’ll run our standard statedump/hanganalyze script.

Anyway, I learned something today!  Hope this is helpful to others.

– Bobby

Oracle support recommends setting the hidden parameter _unnest_subquery to FALSE to improve the performance of PeopleSoft applications on Oracle databases.  I’m trying to understand the consequence of changing this parameter from its default setting of TRUE.

PeopleSoft applications have a lot of effective dated rows.  I.e. If you change jobs the change is effective on a given date.  So, you don’t have just one job record you have all the records for the various jobs you have had with the company.  The logic looks something like this to look up a given employee’s job as of today:

  SELECT *
    FROM   PS_JOB D
   WHERE   D.EMPLID = 'NOTREAL'
           AND D.EMPL_RCD = 0
           AND D.EFFDT =
                 (SELECT   MAX (J.EFFDT)
                    FROM   PS_JOB J
                   WHERE       J.EMPLID = D.EMPLID
                           AND J.EMPL_RCD = D.EMPL_RCD
                           AND J.EFFDT <= sysdate)
           AND D.EFFSEQ =
                 (SELECT   MAX (K.EFFSEQ)
                    FROM   PS_JOB K
                   WHERE       K.EMPLID = D.EMPLID
                           AND K.EMPL_RCD = D.EMPL_RCD
                           AND K.EFFDT = D.EFFDT)

I didn’t use a real employee id for this plan – NOTREAL is just something I made up as a fake employee id.

If you set _unnest_subquery to FALSE you get a pretty simple plan:

-------------------------------------------------
| Id  | Operation                      | Name   |
-------------------------------------------------
|   0 | SELECT STATEMENT               |        |
|   1 |  TABLE ACCESS BY INDEX ROWID   | PS_JOB |
|*  2 |   INDEX RANGE SCAN             | PSAJOB |
|   3 |    SORT AGGREGATE              |        |
|   4 |     FIRST ROW                  |        |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| PSDJOB |
|   6 |    SORT AGGREGATE              |        |
|   7 |     FIRST ROW                  |        |
|*  8 |      INDEX RANGE SCAN (MIN/MAX)| PSDJOB |
-------------------------------------------------

The way I read this it looks like it uses the PSAJOB index to look up the emplid and empl_rcd and then for each job record it checks to see if it has the max effdt and effseq.

Here is the plan with the default setting of _unnest_subquery=TRUE:

-------------------------------------------------
| Id  | Operation                     | Name    |
-------------------------------------------------
|   0 | SELECT STATEMENT              |         |
|*  1 |  HASH JOIN                    |         |
|   2 |   NESTED LOOPS                |         |
|   3 |    VIEW                       | VW_SQ_2 |
|   4 |     HASH GROUP BY             |         |
|*  5 |      INDEX RANGE SCAN         | PSDJOB  |
|   6 |    TABLE ACCESS BY INDEX ROWID| PS_JOB  |
|*  7 |     INDEX UNIQUE SCAN         | PS_JOB  |
|   8 |   VIEW                        | VW_SQ_1 |
|*  9 |    FILTER                     |         |
|  10 |     HASH GROUP BY             |         |
|* 11 |      INDEX RANGE SCAN         | PSDJOB  |
-------------------------------------------------

In this case it looks like steps 2 through 7 group job records together getting the maximum effseq.  These are then hash joined to the rows from steps 8 through 11 which are grouped together getting the maximum effdt.  In both groupings the PSDJOB index scans have the conditions on emplid and empl_rcd in their predicates so maybe this is where the unnesting occurs.  The D.EMPLID = ‘NOTREAL’ AND D.EMPL_RCD = 0 predicates get mixed in with the subqueries.

In a test with a real employee id the test with _unnest_subquery = FALSE had fewer consistent gets which probably means it is more efficient:

_unnest_subquery = FALSE

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       6671  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

_unnest_subquery = TRUE

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
       6671  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

We started down this path because we had a payroll confirm process start to run too long after patching to 11.2.0.3 from 11.2.0.1.  I think we may have hit bug 14110304 which was introduced in 11.2.0.2.  Setting _unnest_subquery = FALSE fixed the performance issue with the confirm and all of our other testing has gone well with this setting.  Also, I’ve extracted a bunch of production queries and run them on our patched system with the new setting and they work fine.  So, it appears that _unnest_subquery = FALSE is a good setting for PeopleSoft systems just as Oracle recommends and that at least in some cases the effective date logic works better with this setting.

Here is a zip of a script and its log showing the two plans from above.

– Bobby

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