Oracle 9.2.0.1.0 on AIX 5300-07

UNIX AIX
1

Seek help from all of the experts here.
Database Oracle 9.2.0.1.0 run on AIX 5300-07.
The system is running so slow that sometime 1 click of changing screen will take more than 5 minutes.
The hardware in this LPAR is 6CPUs, 21GB-RAM, 42GB-Paging and run on p570 server POWER 5+.

And the vmo setting is as below where
minperm = 3%, maxperm=90%, maxclient=90%, lru_file_repage=0 and lru_poll_interval = 10.

With the Oracle init.ora is as below:
*.aq_tm_processes=1
*.background_dump_dest='/app/oracle/admin/PROD/bdump'
*.compatible='9.2.0.1.0'
*.control_file_record_keep_time=7
*.control_files='/data05/control01.ctl','/data10/control02.ctl','/data07/control03.ctl'
*.core_dump_dest='/app/oracle/admin/PROD/cdump'
*.cpu_count=6
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_cache_advice='OFF'
*.db_cache_size=3674210304
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_files=300
*.db_name='PROD'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
*.dml_locks=4860
*.enqueue_resources=5124
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='PROD'
*.java_pool_size=15286400
*.job_queue_processes=10
*.large_pool_size=576777216
*.log_buffer=2048000
*.log_checkpoint_interval=10000000
*.log_checkpoint_timeout=0
*.log_checkpoints_to_alert=TRUE
*.max_enabled_roles=120
*.open_cursors=2000
*.db_writer_processes=4
*.pga_aggregate_target=2007986918
*.processes=1500
*.log_archive_dest_2='location=/data11/arch'
*.log_archive_dest_1='location=/data06/arch'
*.log_archive_dest_3='service=hlarep reopen=60 optional arch'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_dest_state_3=DEFER
*.remote_archive_enable=send
*.log_archive_start=TRUE
*.query_rewrite_enabled='TRUE'
*.remote_dependencies_mode='SIGNATURE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_cached_cursors=500
*.sga_max_size=8176279568
*.shared_pool_reserved_size=247815065
*.shared_pool_size=1503013120
*.sort_area_size=4194304
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.transactions=2000
*.undo_management='AUTO'
*.undo_retention=600
*.undo_suppress_errors=TRUE
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/app/oracle/admin/PROD/udump'
*.utl_file_dir='/data06/utlfiles'
*.workarea_size_policy='AUTO'
*.optimizer_index_cost_adj=100
*.optimizer_index_caching =70
*.audit_trail=DB
*.disk_asynch_io=TRUE
*.dg_broker_start=true
*.lock_sga=true

May I know what is wrong with all these setting? Is there anything on O/S or Database setting that is wrong which affect the slowness?
As I know from my DBA, the Database only using 8GB of memory and I do not understand why it can't use all 21GB of them.

2

Your DBA is referring to *.sga_max_size=8176279568 ~ 8 GB memory used, but *.pga_aggregate_target=2007986918 ~ 1,8 GB is also memory on your server consumed by your database.

Did you investigate if there is something very busy during such slownesses? Did topas or sar show CPUs near 100% or very busy disks?

3

Hi,
please show output of

vmstat -I 5 5

and

mount

commands and

lsattr -El aio0

and let us know if this is a oltp or rather a data warehouse database. In addition the output of

iostat -D

could be helpful.
In addition you have

*.lock_sga=true

set - do you use large pages - in any other case that is real bad habit and on AIX rather couterproductive in most cases.
Kind regards
zxmaus