java_pool_size = 0 is valid. It evaluates to 32K in 816. Not completely
sure about 817 though.
--
Terry Dykstra
Canadian Forest Oil Ltd.
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3ddcd4d4$0$1287$ed9e5944_at_reading.news.pipex.net...
> "Natra" <natra_at_libbbero.it> wrote in message
> news:ariioh$c0f$1_at_newsreader.mailgate.org...
> > Hi boys,
> > I've a great problem on a Linux Box Red Hat 7.2 with Oracle 817 with
patch
> > (hardware: P3 1000 Mhz - 512 MbRam).
> > Before installing the Linux Box mi database worked on a Ora 816 (on the
> same
> > hardware) on WinNT (SerPack 6a).
> > In order to migrate DB to the new OS I've used the export utility from
the
> > command prompt.
> > After the installation of new enviroment I've operate the Import ( from
> > Linux shell command line) of .dmp file (160 Mb) and all seems to be done
> > very well.
> > After installing the ERP procedure that uses Oracle DB I've immediately
> > verified an incredible slowly performance in quite simple query (with
> order
> > by) in a 50.000 row table, (this query in NT environment takes arount 50
> > sec. with a 99% CPU time - in the new environment takes around 28
minutes
> > with 98 % of CPU time ad around 14% of memory).
> <snip>
>
> More proof that NT is better than Linux :(
>
> My first guess would be that in the old environment the schema had been
> analyzed, but in the new environement this hasn't been done, and the
default
> of analyze ... estimate has been taken. So first thing to do would be
> analyze the schema
> exec dbms_stats.gather_schema_stats('SCHEMA_NAME');
>
> > open_cursors = 300
>
> way too low try 3000. won't affect performance as such.
>
> > #open_cursors = 256
> >
> > max_enabled_roles = 30
> > db_file_multiblock_read_count = 32
>
> probably too high can you really read 256k in a single read from the OS.
>
> > db_block_buffers = 45000
> >
> > shared_pool_size = 48321843
> > # shared_pool_size = 9000000
> >
> > large_pool_size = 614400
> > java_pool_size = 0
>
> I'm not sure java_pool_size=o is a valid value, I rather suspect you have
an
> SGA of
>
> buffer cache 351mb (45000*8k)
> shared pool 47mb
> java pool 20mb
> total 418mb
>
> but you only have 512mb ram available. you will get swapping and thus poor
> performance.
>
> > log_checkpoint_interval = 10000
> > log_checkpoint_timeout = 1800
> >
> > processes = 150
> > #processes = 100
> >
> > log_buffer = 163840
> > #log_buffer = 32768
> >
> > parallel_max_servers = 5
> > max_dump_file_size = 10240
> >
> > rollback_segments = ( rbs0, rbs1, rbs2, rbs3, rbs4, rbs5, rbs6, rbs7,
> rbs8,
> > rbs9, rbs10, rbs11, rbs12, rbs13, rbs14, rbs15 )
> >
> > background_dump_dest = /home/oracle/admin/colfi/bdump
> > core_dump_dest = /home/oracle/admin/colfi/cdump
> > resource_manager_plan = system_plan
> > user_dump_dest = /home/oracle/admin/colfi/udump
> >
> > db_block_size = 8192
>
> Is 8k the block size of the underlying file system or is it someother
value?
> If it is say 2k you'd be well advised to recreate with a 2k block size.
>
> >
> > remote_login_passwordfile = exclusive
> >
> > os_authent_prefix = ""
> >
> > job_queue_processes = 2
> > #job_queue_processes =1
> >
> > job_queue_interval = 30
> > distributed_transactions = 500
> > open_links = 4
> >
> > mts_dispatchers = "(PROTOCOL=TCP)(SER=MODOSE)"
> > mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
> >
> > dml_locks = 748
> > compatible = "8.1.0"
> > sort_area_size = 65536
>
> This value may also be two low I tend to set 1mb, and you have no
> sort_area_retained_size which you should set to half the value of
> sort_area_size.
>
>
> My recommendations in order then
>
> 1. *Consider* if 8k is the appropriate block size, if not recreate at file
> system block size. Do this first as it means blowing everything else away
to
> change.
> 2. Reduce the memory requirements of the SGA (or buy more RAM).
> 3. Analyze the schema.
>
> As you are going between versions you may get unexpected changes in plan
for
> expensive SQL statements, but the above should be a basic start point.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
>
>
Received on Thu Nov 21 2002 - 11:15:38 CST
