Re: Ora816 - 100% CPU usage after Import

"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

******************************************