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_at_allegientsystems.com> 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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 18 2005 - 00:55:32 CST
