Could you post what result you have when running the following :
Code:
show parameter target
show parameter policy
show parameter cache
show parameter buffer
show parameter ga
set pages 999
set lines 200
select * from gv$resource_limit ;
select * from v$db_cache_advice ;
select * from v$pgastats ;
select * from v$pga_target_advice ;
Please do not make the mistake most DBAs do ... when you say you have 88 GB basically assigned to oracle that means that you have only 10 GB left - for the OS and all oracle processes, monitoring, batches, backups, ... - if you have a thousand userconnections, you might have a couple of hundred to a couple of thousand processes in parallel (we have about 2600 ...) - And each of these processes needs memory ... that adds up very fast to 50, 60 GB or more - depending on what these processes are doing - and your box clearly does the same explaining the 35+% pagingspace utilization
[QUOTE=ctsgnb;302470664]Could you post what result you have when running the following :
Code:
show parameter target
show parameter policy
show parameter cache
show parameter buffer
show parameter ga
set pages 999
set lines 200
select * from gv$resource_limit ;
select * from v$db_cache_advice ;
select * from v$pgastats ;
select * from v$pga_target_advice ;
?
output
Code:
SQL> show parameter target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
pga_aggregate_target big integer 6G
sga_target big integer 80G
SQL> show parameter policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 32G
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 500
SQL> show parameter buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
db_block_buffers integer 0
log_buffer integer 62906368
use_indirect_data_buffers boolean FALSE
SQL> show parameter ga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
max_commit_propagation_delay integer 0
pga_aggregate_target big integer 6G
pre_page_sga boolean FALSE
sga_max_size big integer 80G
sga_target big integer 80G
SQL> set pages 999
SQL> set lines 200
SQL> select * from gv$resource_limit ;
INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
---------- ------------------------------ ------------------- --------------- ---------- ----------
1 processes 231 359 1200 1200
1 sessions 296 489 2400 2400
1 enqueue_locks 228 6584 26956 26956
1 enqueue_resources 205 6289 10532 UNLIMITED
1 ges_procs 0 0 0 0
1 ges_ress 0 0 0 UNLIMITED
1 ges_locks 0 0 0 UNLIMITED
1 ges_cache_ress 0 0 0 UNLIMITED
1 ges_reg_msgs 0 0 0 UNLIMITED
1 ges_big_msgs 0 0 0 UNLIMITED
1 ges_rsv_msgs 0 0 0 0
1 gcs_resources 0 0 0 0
1 gcs_shadows 0 0 0 0
1 dml_locks 20 585 10000 UNLIMITED
1 temporary_table_locks 0 7 UNLIMITED UNLIMITED
1 transactions 3 214 2640 UNLIMITED
1 branches 0 1 2640 UNLIMITED
1 cmtcallbk 5 36 2640 UNLIMITED
1 sort_segment_locks 115 627 UNLIMITED UNLIMITED
1 max_rollback_segments 75 75 2640 65535
1 max_shared_servers 0 0 UNLIMITED UNLIMITED
1 parallel_max_servers 0 12 12 3600
Êã ÇÎÊíÇÑ 22 ÕÝ
SQL> select * from v$db_cache_advice ;
ID NAME BLOCK_SIZE ADV SIZE_FOR_ESTIMATE SIZE_FACTOR BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS ESTD_PHYSICAL_READ_TIME ESTD_PCT_OF_DB_TIME_FOR_READS
---------- -------------------- ---------- --- ----------------- ----------- -------------------- ------------------------- ------------------- ----------------------- -----------------------------
ESTD_CLUSTER_READS ESTD_CLUSTER_READ_TIME
------------------ ----------------------
3 DEFAULT 8192 ON 7520 .0998 930130 2.008 74635297 560396 39.7
0 0
3 DEFAULT 8192 ON 15040 .1997 1860260 1.5594 57959899 417918 29.6
0 0
3 DEFAULT 8192 ON 22560 .2995 2790390 1.3485 50121978 350949 24.8
0 0
3 DEFAULT 8192 ON 30080 .3993 3720520 1.2448 46269211 318030 22.5
0 0
3 DEFAULT 8192 ON 37600 .4992 4650650 1.1782 43790523 296851 21
0 0
3 DEFAULT 8192 ON 45120 .599 5580780 1.1389 42330501 284376 20.1
0 0
3 DEFAULT 8192 ON 52640 .6988 6510910 1.0945 40682522 270296 19.1
0 0
3 DEFAULT 8192 ON 60160 .7986 7441040 1.0575 39304625 258523 18.3
0 0
3 DEFAULT 8192 ON 67680 .8985 8371170 1.0173 37811414 245764 17.4
0 0
3 DEFAULT 8192 ON 75200 .9983 9301300 1.0003 37178833 240359 17
0 0
3 DEFAULT 8192 ON 75328 1 9317132 1 37168845 240274 17
0 0
3 DEFAULT 8192 ON 82720 1.0981 10231430 .9835 36556783 235044 16.6
0 0
3 DEFAULT 8192 ON 90240 1.198 11161560 .974 36204222 232032 16.4
0 0
3 DEFAULT 8192 ON 97760 1.2978 12091690 .9597 35671233 227478 16.1
0 0
3 DEFAULT 8192 ON 105280 1.3976 13021820 .9456 35148379 223011 15.8
0 0
3 DEFAULT 8192 ON 112800 1.4975 13951950 .9355 34771862 219794 15.6
0 0
3 DEFAULT 8192 ON 120320 1.5973 14882080 .9203 34207971 214976 15.2
0 0
3 DEFAULT 8192 ON 127840 1.6971 15812210 .9094 33800706 211496 15
0 0
3 DEFAULT 8192 ON 135360 1.7969 16742340 .8982 33385214 207946 14.7
0 0
3 DEFAULT 8192 ON 142880 1.8968 17672470 .8894 33059309 205161 14.5
0 0
3 DEFAULT 8192 ON 150400 1.9966 18602600 .8662 32193806 197766 14
0 0
Êã ÇÎÊíÇÑ 21 ÕÝ
SQL> select * from v$pgastats ;
select * from v$pgastats
*
íæÌÏ ÎØà ÚäÏ ÓØÑ 1:
ORA-00942: ÇáÌÏæá Ãæ ÇáÌÏæá ÇáÇÚÊÈÇÑí ÛíÑ ãæÌæÏ
SQL> select * from sys.v$pgastats ;
select * from sys.v$pgastats
*
íæÌÏ ÎØà ÚäÏ ÓØÑ 1:
ORA-00942: ÇáÌÏæá Ãæ ÇáÌÏæá ÇáÇÚÊÈÇÑí ÛíÑ ãæÌæÏ
SQL> select * from v$pga_target_advice ;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ------------------- ----------------------------- --------------------
805306368 .125 ON 6.1539E+12 3.3709E+11 95 18145
1610612736 .25 ON 6.1539E+12 2.5813E+11 96 11166
3221225472 .5 ON 6.1539E+12 6.8188E+10 99 1663
4831838208 .75 ON 6.1539E+12 3.9611E+10 99 0
6442450944 1 ON 6.1539E+12 1.9264E+10 100 0
7730940928 1.2 ON 6.1539E+12 1.8177E+10 100 0
9019430912 1.4 ON 6.1539E+12 1.8177E+10 100 0
1.0308E+10 1.6 ON 6.1539E+12 1.8177E+10 100 0
1.1596E+10 1.8 ON 6.1539E+12 1.8177E+10 100 0
1.2885E+10 2 ON 6.1539E+12 1.8177E+10 100 0
1.9327E+10 3 ON 6.1539E+12 1.8177E+10 100 0
2.5770E+10 4 ON 6.1539E+12 1.8177E+10 100 0
3.8655E+10 6 ON 6.1539E+12 1.8177E+10 100 0
5.1540E+10 8 ON 6.1539E+12 1.8177E+10 100 0
Êã ÇÎÊíÇÑ 14 ÕÝ
SQL> spool off;
Hello,
All the commands on AIX are running very slow.
Below is few stats but I didn't find any issue in cpu or memory reosurces
vmstat
System configuration: lcpu=4 mem=6144MB ent=1.00
kthr memory page faults cpu
----- -----------... (2 Replies)
Hello,
Please help me with a script with which I can check long running processes on the database server and the os is AIX.
Best regards,
Vishal (5 Replies)
Hi Guys,
Need you help in one point!
I am working on one shell script which takes following steps :
1. Taking one query result from oracle database
2. Exporting that result to Xls file
3. Mailing that file to my own mail ID
Now, I want to give a threshold limit to one of the column... (0 Replies)
good morning
what is the better solution to examen a P570 ?
because i use topas and nmon, and the results are totally different !!!
with nmon, i have 80% free cpu, and with nmon, i have 90% of used cpu !!!!!!
i take a shot with an intervall of 10s during 10 mn.
thank you (0 Replies)
Hi,
I am running an oracle db 9.2.0.5.0 on ibm p5 550 aix 5.3 with 10g ram, 10G swap space 3 database instances each SGA about 500Meg.
I am getting the following error in my alert log file from time to time:
skgpspawn failed:category = 27142, depinfo = 11, op = fork, loc = skgpspawn3
... (0 Replies)
hi,
how can i diplay:
- the ammount RAM used /free
- ammount of ram used from a pid or prozess
we have the problem, that malloc is returing a NULL pointer errno = 12 ( not enough space).
but i think there is still ram free.
nmon : shows all memory used ?
Memory Use Physical Virtual... (7 Replies)
