Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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
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 - 06:42:59 CST