Re: what is statspack 'CPU Time' event

tommy.yan_at_hp.com (Gino) wrote in message news:<8230d425.0402100111.6f3fc1dc_at_posting.google.com>...
> Hello,
>
> here is a performance test program running on hpux 11.23 Itanium2
> Oracle 9i.
> The application is reading many data blocks and after handling, write
> result to database.
> From Statspack I got below information.
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> CPU time 199
> 44.93
> db file sequential read 85,452 173
> 39.02
> SQL*Net more data to client 69,617 23
> 5.19
> db file scattered read 6,578 21
> 4.81
> direct path read 6,443 14
> 3.06
>
> I cannot find what does the word "CPU time" means. Anybody can tell me
> and how to improve the tests?
>
> Thx.
> Tommy
>
> init.ora file
>
> *._kgl_latch_count=64
> *._lgwr_async_io=true
> *._lgwr_io_slaves=4
> *._log_simultaneous_copies=64
> *.log_checkpoint_timeout=999999999
> *.log_checkpoint_interval=999999999
> *.background_dump_dest='/oracle/admin/ora/bdump'
> *.compatible='9.2.0.0.0'
> *.control_files='/oracle/admin/ora/control01.ctl','/oracle/admin/ora/control02.ctl','/oracle/admin/ora/control0
> 3.ctl'
> *.core_dump_dest='/oracle/admin/ora/cdump'
> *.cursor_sharing='force'
> *.cursor_space_for_time=true
> *.db_block_size=4096
> *.db_cache_size=2G
> *.db_domain=''
> *.db_file_multiblock_read_count=32
> *.db_name='ora'
> *.db_writer_processes=8
> *.disk_asynch_io=true
> *.fast_start_mttr_target=6000
> *.hash_join_enabled=TRUE
> *.hpux_sched_noage=178
> *.instance_name='ora'
> *.java_pool_size=0
> *.large_pool_size=8388608
> *.log_buffer=126720000
> *.log_parallelism=4
> *.open_cursors=2000
> *.processes=150
> *.query_rewrite_enabled='FALSE'
> *.remote_login_passwordfile='EXCLUSIVE'
> *.shared_pool_size=1G
> *.sort_area_size=5242880
> *.star_transformation_enabled='FALSE'
> *.undo_management='AUTO'
> *.undo_retention=1
> *.undo_tablespace='UNDOTBS1'
> *.user_dump_dest='/oracle/admin/ora/udump'

Houston, we may have a problem here.
You have:

cursor_sharing=FORCE

and

shared_pool_size=1G

Methinks that you have an overabundance of garbage SQL in the shared pool.
How did you arrive at a shared pool size of 1 GB? I would think that an eighth of that would be a better starting point.

you also have:

log_buffer=126720000

how did you arrive at that figure?

1M may have been a much better starting point.

Your email domain is hp.com.
I think that you may have excellent resources within that company to get you into a more standardized set of initialization parameters to start with, and then begin tweaking as needed from there.

good luck.

Pd Received on Tue Feb 10 2004 - 11:01:08 CST