Bobby Durrett's DBA Blog

Here is a script I hacked together Tuesday night to figure out how to fit all my production data files and temp files on a development server for a RMAN restore of a production backup onto a new server:

DECLARE 
    CURSOR DF_CURSOR IS 
        SELECT
            *
        FROM dba_data_files
        order by FILE_ID;

    DF_REC DF_CURSOR%ROWTYPE;

    CURSOR TF_CURSOR IS 
        SELECT
            *
        FROM dba_temp_files
        order by FILE_ID;

    TF_REC TF_CURSOR%ROWTYPE;

    db01 number;
    db02 number;
    db03 number;
    db04 number;
    db05 number;
    db06 number;
    db07 number;
    db08 number;
    db09 number;

BEGIN
db08 := 16561550;
db07 := 19548242;  
db06 := 91252087;  
db05 := 29913520;  
db04 := 18507885;  
db03 := 6206062;   
db02 := 64145394;  
db01 := 265206680; 
db09 := 323990034;

    OPEN DF_CURSOR;
LOOP
    FETCH DF_CURSOR INTO DF_REC;
    EXIT WHEN DF_CURSOR%NOTFOUND;

    IF (DB01 > ((DF_REC.BYTES/1024)+1024)) THEN

       dbms_output.put_line('set newname for datafile '||DF_REC.FILE_ID||' to  '''||
         substr(DF_REC.FILE_NAME,1,16)||'db01/NEWDBXX'||substr(DF_REC.FILE_NAME,29)||
         ''';');
       DB01 := DB01 - (DF_REC.BYTES/1024);

    ELSIF (DB09 > ((DF_REC.BYTES/1024)+1024)) THEN

       dbms_output.put_line('set newname for datafile '||DF_REC.FILE_ID||' to  '''||
         substr(DF_REC.FILE_NAME,1,16)||'db09/NEWDBXX'||substr(DF_REC.FILE_NAME,29)||
         ''';');
       DB09 := DB09 - (DF_REC.BYTES/1024);

    ELSIF (DB06 > ((DF_REC.BYTES/1024)+1024)) THEN

       dbms_output.put_line('set newname for datafile '||DF_REC.FILE_ID||' to  '''||
         substr(DF_REC.FILE_NAME,1,16)||'db06/NEWDBXX'||substr(DF_REC.FILE_NAME,29)||
         ''';');
       DB06 := DB06 - (DF_REC.BYTES/1024);

    end if;

END LOOP;
CLOSE DF_CURSOR;

    OPEN TF_CURSOR;
LOOP
    FETCH TF_CURSOR INTO TF_REC;
    EXIT WHEN TF_CURSOR%NOTFOUND;    
   IF (DB06 > ((TF_REC.BYTES/1024)+1024)) THEN

       dbms_output.put_line('set newname for tempfile '||TF_REC.FILE_ID||' to  '''||
         substr(TF_REC.FILE_NAME,1,16)||'db06/NEWDBXX'||substr(TF_REC.FILE_NAME,29)||
         ''';');
       DB06 := DB06 - (TF_REC.BYTES/1024);

    end if;

END LOOP;
CLOSE TF_CURSOR;
END;
/

The filesystems and paths to the datafiles and temp files looked like this:

/var/opt/oracle/db01/OLDDBXX/dbf/system01.dbf

/var/opt/oracle/db06/NEWDBXX/dbf/system01.dbf

Lots of stuff is hard coded such as the space available in each filesystem like this:

db08 := 16561550;

The units are kilobytes which is the output of bdf in HP-UX.

Might not be useful since it isn’t that parametrized and automatic but if you need some code for fitting a bunch of datafiles in a new set of filesystems it might help you find a place to start.  Also, I tried the three filesystems with the most free space first and didn’t really need any others so that is why the if statements only have db01, db09, and db06 but you could add more.

Output is RMAN commands like these:

set newname for datafile 1 to  ‘/var/opt/oracle/db01/NEWDBXX/dbf/system01.dbf’;

set newname for tempfile 1 to  ‘/var/opt/oracle/db06/NEWDBXX/dbf/temp01.dbf’;

– Bobby

I’ve been thinking about how to use my blog to describe different individuals who have helped me learn about Oracle performance tuning.  I was thinking about having a single blog entry or page describing several people but have decided that will be too long and overwhelming to read or to write.  So, I got the idea of writing about a single individual at a time.  Now, the challenge about writing about someone is that this is only my perspective of them and I could have some details wrong.  So, if there are any errors or negative implications then that is on me and no reflection on the individual I’m describing.  I’m including this person because their work has helped me in my job as an Oracle DBA in the performance tuning area so the intent is to be positive and to encourage other people to benefit from their work.  In most cases these individuals have free or inexpensive materials available that can be of great worth to an Oracle DBA who is working on tuning.

So, I’m starting with Craig Shallahamer because he was the first individual who really helped me learn about Oracle performance.  I started as an Oracle DBA in December of 1994 while working with Campus Crusade for Christ.  At CCC we migrated all of our applications off of a small IBM mainframe and on to an Oracle database running PeopleSoft applications.  But, at some point our new donations system was having bad performance.  It was slower than the mainframe system we had just replaced.  A couple of managers in our IT area ran into Craig and connected him up with me.  It ended up that we were saturating one of our disk arrays with random I/O while our other arrays were underutilized.  Craig’s free papers on Oracle waits and the use of operating system monitoring utilities such as sar -d really helped me understand how to diagnose our disk I/O issues.

If you want to check out Craig’s resources go to his OraPub website.  I have read several of his papers – most notably ones about Oracle waits and about response time/queuing theory.  Also, I’ve learned from his free tool called OSM which records V$ table values and operating system tool outputs.  Craig sells a new tool called Stori which I know he is excited about, and he has a number of other free tools as well. I haven’t been able to take one of his paid courses but I’ve read the materials for his firefighting class several years ago and it was very helpful.  I also read Craig’s first book which is on forecasting performance.

It is hard to do justice to someone’s work and I’m not sure I’m explaining it that well.  I think of Craig in terms of operating system/hardware, Oracle internals, waits, mathematical models, performance tuning theory.  It has been helpful for me to just take one of his excel spreadsheets and play with the values and think about what the curve means in terms of queuing and performance.  Most DBAs I know aren’t that comfortable with operating system utilities like sar and don’t know what values to look for.  And, few people seem to understand queuing theory and other mathematical models of Oracle systems.

So, Craig is a good source for Oracle performance information that may not be readily available to the average DBA unless they know where to look.  I hope others find his work as helpful in their jobs as I have.

– Bobby

In Oracle when you compare a character and a number in a SQL WHERE clause Oracle converts the character value to a number rather than the other way around.  I built a simple example to help me understand how this works.  I wanted to see if the results were different depending on whether you converted the number to a character or the other way around, and found out that the results are different.

Here is how I setup the two tables I would join:

create table ntest (ncolumn number);
create table ctest (ccolumn varchar2(2000));

insert into ntest values (1);
insert into ctest values ('1');
insert into ctest values ('x');

commit;

Here is the query with the comparison of the numeric column ntest.ncolumn to the character column ctest.ccolumn and the results:

SQL> select ncolumn,ccolumn
  2  from ntest,ctest
  3  where
  4  ntest.ncolumn=ctest.ccolumn;
ERROR:
ORA-01722: invalid number

This is the equivalent of adding the to_number conversion function explicitly:

SQL> select ncolumn,ccolumn
  2  from ntest,ctest
  3  where
  4  ntest.ncolumn=to_number(ctest.ccolumn);
ERROR:
ORA-01722: invalid number

In both cases you get the error because it tries to convert the value ‘x’ to a number.

But, if you explicitly convert the number column to a character you don’t get the error:

SQL> select ncolumn,ccolumn
  2  from ntest,ctest
  3  where
  4  to_char(ntest.ncolumn)=ctest.ccolumn;

   NCOLUMN CCOLUMN
---------- ------------
         1 1

The two choices of which column to convert produce two different results.  When you design your application if you have to compare character and number values you should put in the to_char or to_number to force the conversion to be the way you need it to be, or at least you should be aware that Oracle converts a character to a number by default.

– Bobby

Hopefully this isn’t too redundant.  Saw another performance issue today caused by a type mismatch between a bind variable and the column it was compared to in a SQL statement.  I saw this in some real code and then built a simple testcase to prove out the concept: zip of testcase SQL and log.

The column being compared against was type VARCHAR2 and the bind variable was NUMBER.  Converting the variable to a character type with TO_CHAR resolved the issue.

Table definition:

create table test
(OWNER  VARCHAR2(30),
 TABLE_NAME VARCHAR2(30),
 TEST_COLUMN VARCHAR2(2000));

create index testi on test(TEST_COLUMN);

PL/SQL variable of the wrong type (number):

declare
V_NUMBER number;
begin

V_NUMBER := 1;

Execute immediate passing the bind variable that does not match the type of the column TEST_COLUMN:

execute immediate 'UPDATE TEST SET TABLE_NAME=''UPDATED'' 
WHERE TEST_COLUMN=:1' using V_NUMBER;

Note that I put in a carriage return so it would fit the blog width.  It is one line in the script.

Execute immediate converting the variable to a character before passing it so it matches the column in the where clause:

execute immediate 'UPDATE TEST SET TABLE_NAME=''UPDATEDAGAIN'' 
WHERE TEST_COLUMN=:1' using to_char(V_NUMBER);

With the TO_CHAR the update is much faster:

No TO_CHAR:

Elapsed: 00:00:01.45

With TO_CHAR:

Elapsed: 00:00:00.04

Plan without the TO_CHAR is full scan:

SQL_ID  41vfab6v87w4g, child number 0
-------------------------------------
UPDATE TEST SET TABLE_NAME='UPDATED' WHERE TEST_COLUMN=:1

Plan hash value: 3859524075

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |       |       |  2579 (100)|          |
|   1 |  UPDATE            | TEST |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     2 |    46 |  2579   (4)| 00:00:37 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("TEST_COLUMN")=:1)

Plan with the TO_CHAR is index range scan:

SQL_ID  3229aq5w36kst, child number 0
-------------------------------------
UPDATE TEST SET TABLE_NAME='UPDATEDAGAIN' WHERE TEST_COLUMN=:1

Plan hash value: 3736755925

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |       |       |       |     5 (100)|          |
|   1 |  UPDATE           | TEST  |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| TESTI |     2 |    46 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST_COLUMN"=:1)

– Bobby

I was working on some disk performance problems and came across a simple way to test the latency or delay built into the networks that connect my database servers to their disk storage.  All I have to do is read some disk blocks from a table several times to be sure they are cached in the disk storage server and then flush the database buffer cache and read from the table once more.  Since the blocks are not cached in the database buffer cache but are cached on the disk array the time it takes to read the blocks approaches the time it takes to copy the blocks over the network.  Of course there is some CPU on both the source and target to copy the blocks in memory but the physical disk read is eliminated and you see close to the minimum time it is possible for a transfer to take.

So, here is my simple test script.  It assumes the user, password, and tnsnames.ora name are passed as parameters and that the user is a DBA user like SYSTEM.

connect &1/&2@&3

-- create test that will show how fast reads
-- can be when cached on the disk subsystem

-- document version

select * from v$version;

-- create small table

drop table test;
create table test as select * from dba_tables where rownum < 1000;

-- query three times to get cached in the disk system

select sum(blocks) from test;
select sum(blocks) from test;
select sum(blocks) from test;

-- flush from database cache

alter system flush buffer_cache;

-- reconnect so session waits are cleared

disconnect
connect &1/&2@&3

select sum(blocks) from test;

-- show average scattered read
-- should be best time you can
-- get since blocks are cached
-- in the disk subsystem

VARIABLE monitored_sid number;

begin

SELECT sid into :monitored_sid from v$session 
where audsid=USERENV('SESSIONID');

end;
/

select  
total_waits,
time_waited_micro/total_waits
FROM V$SESSION_EVENT a
WHERE a.SID= :monitored_sid
and event='db file scattered read';

Here is what the output looks like on a slow system with network problems (over NFS in my case):

TOTAL_WAITS TIME_WAITED_MICRO/TOTAL_WAITS
----------- -----------------------------
          4                       1410.25

Here is what it looks like on a nice fiber network:

TOTAL_WAITS TIME_WAITED_MICRO/TOTAL_WAITS
----------- -----------------------------
          4                          40.5

40 microseconds for a disk read is sweet compared to 1410!

I’ve uploaded the script and three runs I made with it here.

– Bobby

P.S.  I realized this morning that the first example with 40 microseconds for a read is really just pulling from the operating system’s filesystem cache because the database I was testing on doesn’t use direct IO.  Here is a test on a system with the SAN that uses direct IO:

TOTAL_WAITS TIME_WAITED_MICRO/TOTAL_WAITS
----------- -----------------------------
          5                         478.4

I ran this several times and this was the best result.  The others were in the 600-700 microsecond range mostly.  So, I guess when you run the test script you are really testing everything behind the scenes that could benefit from caching except for the database’s buffer cache.  Without direct IO you may never reach your SAN network because you will have cached the blocks at the OS filesystem cache level.

I’ve seen some very good information posted in this feed which combines blog postings from many different Oracle performance experts who are part of what is called the “Oak Table”

http://www.oaktable.net/feed/blog-rss.xml (NO LONGER EXISTS)

I’ve been using Internet Explorer to keep track of new posts in its “Feeds” section of the Favorites.  Here is how to add the Oak Table blog feed to Internet Explorer:

Go to the URL listed above and click on “Subscribe to this feed”

Click on Subscribe button

Success!  Now click on Favorites and then Feeds

For any feed in your list if you see the feed name in a darker font it means there is a new post.  So, as I have time, I’ll go to my feeds and see which of the ones I’ve subscribed to have new posts.  If you are looking for performance tuning information I highly recommend the Oak Table feed.

– Bobby

In my forum discussion about free buffer waits I came across a term that I didn’t understand: “inode lock contention”.  I’m pretty sure I had seen this same term years ago on one of Steve Adams’ pages on IO.  But, I didn’t really understand what the term meant and so it was hard to understand whether this was something I was seeing on our production system that was experiencing “free buffer waits”.

First I had to figure out what an inode was.  I knew that it had something to do with the way Unix filesystems work but reading this article(NO LONGER EXISTS) really helped clear up what inodes are at least on HP-UX.  Inodes are small chunks of bytes that are used to define a Unix filesystem.  On HP-UX’s VxFS filesystems a type 1 inode can point to up to 10 extents of one or more contiguous 8K blocks on a large filesystem.  The filesystem I’ve been testing on appears to have 32 meg extents if I’m reading this output from lvdisplay correctly:

LV Size (Mbytes)            1472000
Current LE                  46000

Total size of 1,472,000 meg divided by 46,000 logical extents = 32 meg per extent.

Since the inode can point to 1 to 10 extents it could point to between 32 and 320 meg.

My test case had 15 tables that were more than 1 gigabytes each.  It seems like each table should span multiple inodes so even if there is locking at the inode level it looks like it won’t lock the entire table at once.  Still, it seems unlikely to me that every time a table is updated that reads from all the other parts of the table pointed to by the same inode are really blocked by an inode lock.  Yet that is what this document(NO LONGER EXISTS) suggests:

“During a read() system call, VxFS will acquire the inode lock in shared mode, allowing many processes to read a single file concurrently without lock contention. However, when a write() system call is made, VxFS will attempt to acquire the lock in exclusive mode. The exclusive lock allows only one write per file to be in progress at a time, and also blocks other processes reading the file. These locks on the VxFS inode can cause serious performance problems when there are one or more file writers and multiple file readers.”

It uses the term “file” but I assume if you have a large file that has multiple inodes it means it will lock just the pieces associated with the one inode that points to the blocks that are being written.  The article goes on to explain how you can use the “cio” option to enable concurrent IO and eliminate this inode contention preventing writers from blocking readers.  But, I’ve been testing with just the direct IO options and not the cio option and seeing great results.  So, would I see even better improvement with concurrent io?

I didn’t want to mess with our current filesystem mount options since testing had proven them to be so effective but I found that in glance, a performance monitoring tool like top, you have an option to display inode waits.  So, I took a test that was running with direct IO and had 15 merge statements loading data into the same empty table at once and ran glance to see if there were any inode waits.  There were not:

So, I don’t know if I can depend on this statistic in glance or not.  It appears that the direct IO mount options are all we need:

mincache=direct,convosync=direct

filesystemio_options=DIRECTIO

There may be some case within Oracle 11.2.03 on HP-UX 11.31 where you can be hampered by inode lock contention despite having direct IO enabled but my tests have not confirmed it and I’ve banged pretty hard on my test system with a couple of different types of tests.

– Bobby

I just read this post about a new patch that will allow you to affect the way the optimizer calculates clustering factor and hence how likely it is to choose a particular index for a query plan.

http://richardfoote.wordpress.com/2013/05/08/important-clustering-factor-calculation-improvement-fix-you/

Pretty cool.  I haven’t tried it, but it looks promising.

– Bobby

This is a good web site with Oracle internals information:

http://www.ixora.com.au/notes/ (NO LONGER EXISTS)

I’ve benefited from this one for years.

– Bobby

In my previous post I explained that setting db_writer_processes=1, dbwr_io_slaves=32 made a 2-3 times reduction in run time of my test of 15 concurrent updates.

Further testing has shown that an even greater improvement – really 10 times – can be made by switching to direct IO and maxing out the db writer processes.

To switch my test database to direct IO I had to do two things:

1. Ask one of our Unix administrators to remount the filesystem that contains the datafile being tested using these options: mincache=direct,convosync=direct
2. Change this parameter: filesystemio_options=directIO

Then I switched to what the documentation says is the maximum number of db writers:

1. db_writer_processes=36
2. dbwr_io_slaves=0

I had setup a test that generated free buffer waits by changing my update statements to update more blocks than could be held in the buffer cache and I had set log_checkpoint_interval back to its default of 0 so we wouldn’t get frequent checkpoints.  I also increased the redo logs to 2 gig so they wouldn’t switch and checkpoint frequently.  So, my test was getting plenty of free buffer waits and it took roughly 30 minutes for my 15 concurrent update statements to update 1 million rows each.  This was with my current production settings of db_writer_processes=4 and dbwr_io_slaves=0.

Here is a profile of the time spent by one update statement with no direct io and db_writer_processes=4 and dbwr_io_slaves=0:

TIMESOURCE                    SECONDS PERCENTAGE
-------------------------- ---------- ----------
TOTAL_TIME                       2503        100
free buffer waits                2097         84
db file scattered read            253         10
CPU                                61          2
UNACCOUNTED_TIME                   57          2
db file sequential read            26          1
latch: redo copy                    5          0
events in waitclass Other           2          0
log buffer space                    1          0

Here is a profile with the direct io options and db_writer_processes=36, dbwr_io_slaves=0:

TIMESOURCE                    SECONDS PERCENTAGE
-------------------------- ---------- ----------
TOTAL_TIME                        171        100
free buffer waits                  51         30
UNACCOUNTED_TIME                   41         24
db file scattered read             34         20
CPU                                23         13
log buffer space                   16          9
events in waitclass Other           3          2
latch: redo copy                    2          1

Incredible.  Thanks to Jonathan Lewis and Mark Powell for all of their patient discussion of this issue with me on our forum thread.

So, I guess the bottom line is that if you can’t get your filesystems mounted with direct IO options then the IO slaves may be the way to go in certain scenarios.  But, with direct IO it appears that upping the number of db writers is better than using IO slaves, at least in a scenario like mine were you have many concurrent updates filling the buffer cache with updated blocks and waiting on free buffer waits.

– Bobby