One question worth asking is, are there other things on the machine outside oracle which suddenly steal the filesystem cache and leave a large number of your "physical reads" actually going to disc rather than coming from memory ?The action plan yesterday when everything has nothing to do because the server was hanging....is to reboot the stop the database and reboot the server hoping the the server itself will release
This has occured since I increase the redo log and undo_retention just the other day.
Tue Jan 8 14:58:52 2013 Errors in file /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump/oaprod_ora_8774.trc: ORA-12570: TNS:packet reader failure ORA-06512: at "JSCUS.FUNC_GET_MTL_SRC", line 23 Tue Jan 8 15:03:27 2013 Beginning log switch checkpoint up to RBA [0x21576.2.10], SCN: 0x056e.7527328b Thread 1 advanced to log sequence 136566
Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 2 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 184.108.40.206.0. System parameters with non-default values: _trace_files_public = TRUE processes = 400 sessions = 800 timed_statistics = TRUE shared_pool_size = 301989888 sga_max_size = 1688804248 shared_pool_reserved_size= 30000000 _shared_pool_reserved_min_alloc= 4100 java_pool_size = 67108864 _kghdsidx_count = 1 enqueue_resources = 32000 nls_language = american nls_territory = america nls_sort = binary nls_date_format = DD-MON-RR nls_numeric_characters = ., nls_comp = binary nls_length_semantics = BYTE control_files = /u02/oracle/oaproddata/cntrl01.dbf, /u02/oracle/oaproddata/cntrl02.dbf, /u02/oracle/oaproddata/cntrl03.dbf db_block_checksum = TRUE db_block_size = 8192 db_cache_size = 301989888 compatible = 9.2.0 log_buffer = 10485760 log_checkpoint_interval = 100000 log_checkpoint_timeout = 1200 db_files = 512 db_file_multiblock_read_count= 8 log_checkpoints_to_alert = TRUE dml_locks = 10000 row_locking = always undo_management = AUTO undo_tablespace = APPS_UNDOTS1 undo_suppress_errors = FALSE undo_retention = 3600 db_block_checking = FALSE max_enabled_roles = 100 O7_DICTIONARY_ACCESSIBILITY= FALSE session_cached_cursors = 200 utl_file_dir = /usr/tmp, /usr/tmp, /u02/oracle/oaproddb/9.2.0/appsutil/outbound/OAPROD_oel5, /usr/tmp job_queue_processes = 2 _system_trig_enabled = TRUE cursor_sharing = EXACT parallel_min_servers = 0 parallel_max_servers = 8 background_dump_dest = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/bdump user_dump_dest = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/udump max_dump_file_size = 20480 core_dump_dest = /u02/oracle/oaproddb/9.2.0/admin/OAPROD_oel5/cdump optimizer_features_enable= 9.2.0 db_name = OAPROD open_cursors = 1000 ifile = /u02/oracle/oaproddb/9.2.0/dbs/OAPROD_oel5_ifile.ora sql_trace = FALSE _sort_elimination_cost_ratio= 5 _b_tree_bitmap_plans = FALSE _fast_full_scan_enabled = FALSE optimizer_max_permutations= 2000 query_rewrite_enabled = true _index_join_enabled = FALSE _sqlexec_progression_cost= 2147483647 _like_with_bind_as_equality= TRUE pga_aggregate_target = 4294967296 workarea_size_policy = AUTO aq_tm_processes = 1 olap_page_pool_size = 4194304 PMON started with pid=2 DBW0 started with pid=3 DBW1 started with pid=4 LGWR started with pid=5 CKPT started with pid=6 SMON started with pid=7 RECO started with pid=8 CJQ0 started with pid=9 QMN0 started with pid=10 Tue Jan 8 11:59:18 2013 ALTER DATABASE MOUNT Tue Jan 8 11:59:22 2013 Successful mount of redo thread 1, with mount id 4254113430 Tue Jan 8 11:59:22 2013 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Tue Jan 8 11:59:22 2013 ALTER DATABASE OPEN Tue Jan 8 11:59:23 2013 Thread 1 opened at log sequence 136562 Current log# 3 seq# 136562 mem# 0: /u02/oracle/oaproddata/log03a.dbf Current log# 3 seq# 136562 mem# 1: /u02/oracle/oaproddata/log03b.dbf Successful open of redo thread 1 Tue Jan 8 11:59:23 2013 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Jan 8 11:59:23 2013 SMON: enabling cache recovery Tue Jan 8 11:59:23 2013 Successfully onlined Undo Tablespace 368. Tue Jan 8 11:59:23 2013 SMON: enabling tx recovery Tue Jan 8 11:59:23 2013 Database Characterset is UTF8 replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN Tue Jan 8 12:02:18 2013 Beginning log switch checkpoint up to RBA [0x21573.2.10], SCN: 0x056e.7521cc0e Thread 1 advanced to log sequence 136563 Current log# 4 seq# 136563 mem# 0: /u02/oracle/oaproddata/log04a.dbf Current log# 4 seq# 136563 mem# 1: /u02/oracle/oaproddata/log04b.dbf Tue Jan 8 12:22:33 2013 Completed checkpoint up to RBA [0x21573.2.10], SCN: 0x056e.7521cc0e Tue Jan 8 12:50:04 2013 Beginning log switch checkpoint up to RBA [0x21574.2.10], SCN: 0x056e.752326a7 Thread 1 advanced to log sequence 136564 Current log# 3 seq# 136564 mem# 0: /u02/oracle/oaproddata/log03a.dbf Current log# 3 seq# 136564 mem# 1: /u02/oracle/oaproddata/log03b.dbf Tue Jan 8 13:10:19 2013 Completed checkpoint up to RBA [0x21574.2.10], SCN: 0x056e.752326a7 Tue Jan 8 13:49:16 2013 Beginning log switch checkpoint up to RBA [0x21575.2.10], SCN: 0x056e.7524a192 Thread 1 advanced to log sequence 136565 Current log# 4 seq# 136565 mem# 0: /u02/oracle/oaproddata/log04a.dbf Current log# 4 seq# 136565 mem# 1: /u02/oracle/oaproddata/log04b.dbf Tue Jan 8 14:10:16 2013 Completed checkpoint up to RBA [0x21575.2.10], SCN: 0x056e.7524a192
yxes2013 wrote:not entirely true. 1.7MB is default maximum size as shipped by Oracle.
3. Can you increase your SGA to handle more appropriately the volume required by the queries?Is it already 1.7 which is the limit of 32 bit Oracle. which we have.
yxes2013 wrote:Well, you might go back to the concepts manual and review about SGA and PGA. You stated earlier you set SGA to 1.7G, please do a "show SGA" and show us what it thinks. You are using sga_max_size, and not sga_target, so you may not have the buffers set optimally, and it may be smaller than you think. Tuning is an iterative task.
Physical memory is 12Gb...But my database is 32 bit so it takes only 2Gb max. I am correct? So lots of idle RAM?
Can you give me what size do I set. But again what has this to do with the "good" performance at some days?This may go along with what Jonathan said about something outside Oracle making it slow. I think what that something is, and I'm speculating here, is some of your programs use lots and lots of PGA, to the point where your system chokes. So the solution would be to make your pga_aggregate_target much smaller. But I wouldn't make any adjustments without gathering evidence, one data point is what linux thinks is being used (top and swapinfo are gross measures I use, I'm not on linux). There are also SGA and PGA advisors, as well as db cache advice. (I may be confused as to what is in what version, see your performance tuning guide. Also, some of the docs are misleading about this stuff, google what Jonathan has had to say. And there is lots of misinformation on the net specifically on this subject.)
SQL*Plus: Release 220.127.116.11.0 - Production on Wed Jan 9 09:31:27 2013 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 18.104.22.168.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 22.214.171.124.0 - Production SQL> show sga Total System Global Area 1688804248 bytes Fixed Size 453528 bytes Variable Size 1375731712 bytes Database Buffers 301989888 bytes Redo Buffers 10629120 bytes