Re: 66,000.00 times slower, please your opinion

From: Mladen Gogala <mgogala_at_allegientsystems.com>
Date: Thu, 17 Feb 2005 17:42:41 -0500
Message-ID: <42151DE1.4000106@allegientsystems.com>

Juan Carlos Reyes Pacheco wrote:

>
>Based on this waits
>db file scattered read 2,588,531 0 4,836 2 ########
>db file sequential read 1,847,717 0 441 0 ########
>I think recreating the table will solve the problem, What do you think?
>
>Thank you in advance
>
>
>

I think that you are doing too many table accesses. The only thing that will solve the problem is
to repartition the whole disk farm, re-create the database, pre-create the tables and run the
query before you import the data. That ought to speed it up significantly. Of course, rewriting
the SQL which does so gruesome number on your database would also be an option.
So, let's dispense some special advice to a special customer like you:

1) Make sure that your buffer cache hit ratio is >99.9%.
2) Make sure that your dictionary cache ratio is >99.9%
4) Make sure that your library cache ratio is >99.9%
5) set spin_count=2048
6) Set CURSOR_SPACE_FOR_TIME=TRUE and assign a gigantic shared pool.
7) Use ANALYZE to analyze your data and make sure that SYS.AUX_STATS$ 

isn't populated.

    DBMS_STATS is for wimps. Real DBA uses ANALYZE. 8) Set up very large DB_FILE_MULTIBLOCK_READ_COUNT, so that OS has to split each

    IO into several requests. That'll speed them, full table scans, up. 9) Set up a degree of parallelism to 16 times the number of CPU units you have. The more, the merrier.
10) If your RDBMS is >= 9.2.0.5, make sure that you have OPEN_CURSORS set to 2048 and

      SESSION_CACHED_CURSOR=0.
11) Make sure that all your indexes and tables are in the separate tablespaces, with separate files.

      Oracle is expensive. When you already paid for it so much, the least it can do is to manage an

     open file descriptor table of a decent size. 12) Make sure that you don't set FILSYSTEMIO_OPTIONS to DIRECTIO or SETALL. For that money,

     your system should cope with double buffering with ease. You have to somehow justify 64TB of RAM.
13) Make sure that you don't run out of your precious advice.

-- 
Mladen Gogala
Oracle DBA
Ext. 121


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 17 2005 - 17:46:30 CST