Re: 66,000.00 times slower, please your opinion

  • From: Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx>
  • To: Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx>
  • Date: Fri, 18 Feb 2005 01:52:36 -0400

Thanks Mladen what I could do without your advices  
This rose, for you @>--, my best friend.


On Thu, 17 Feb 2005 17:42:41 -0500, Mladen Gogala
<mgogala@xxxxxxxxxxxxxxxxxxxx> wrote:
> 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
> 
> 


-- 
Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i

8 years of experience in Oracle 7,8i,9i,10g and developer 6i
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2005