Home » RDBMS Server » Performance Tuning » May you explain to me about AWR? (Oracle 10.2.0.1, Solaris Sparc 64bit)
| May you explain to me about AWR? [message #409040] |
Thu, 18 June 2009 21:50  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi gurus!
I've got something like latch in my prod database, then I queried it, but really, I did not understand about some values which following:
logvnp@VNP> @dba_scripts\tunning\tuning_latch\latch_gets
LATCH TYPE SIMPLE GETS SPIN GETS SLEEP GETS
------------------------------ ------------------ -------------- --------------
AWR Alerted Metric Element lis 7886622 100.00% 0 0.00% 0 0.00%
Consistent RBA 3862730 100.00% 7 0.00% 1 0.00%
FAL request queue 23062 100.00% 0 0.00% 0 0.00%
FAL subheap alocation 23062 100.00% 0 0.00% 0 0.00%
FIB s.o chain latch 6177 99.98% 1 0.02% 0 0.00%
FOB s.o list latch 133978 99.98% 24 0.02% 0 0.00%
In memory undo latch 20891276 100.00% 484 0.00% 31 0.00%
JOX SGA heap latch 1502 100.00% 0 0.00% 0 0.00%
JS Sh mem access 35810 100.00% 0 0.00% 0 0.00%
JS mem alloc latch 42097 100.00% 0 0.00% 0 0.00%
JS queue access latch 42097 100.00% 0 0.00% 0 0.00%
JS queue state obj latch 9602494 100.00% 2 0.00% 0 0.00%
JS slv state obj latch 120922 100.00% 1 0.00% 0 0.00%
KGX_diag 2 100.00% 0 0.00% 0 0.00%
KMG MMAN ready and startup req 371459 100.00% 1 0.00% 0 0.00%
KMG resize request state objec 224 99.12% 2 0.88% 0 0.00%
KTF sga latch 81611 99.98% 13 0.02% 0 0.00%
KWQMN job cache list latch 22186 99.99% 1 0.00% 1 0.00%
KWQP Prop Status 1426 100.00% 0 0.00% 0 0.00%
Memory Management Latch 3041 99.77% 7 0.23% 0 0.00%
Memory Queue Message Subscribe 3808 99.95% 2 0.05% 0 0.00%
Memory Queue Message Subscribe 6 100.00% 0 0.00% 0 0.00%
Memory Queue Message Subscribe 6 100.00% 0 0.00% 0 0.00%
Memory Queue Message Subscribe 6 100.00% 0 0.00% 0 0.00%
Memory Queue Subscriber 5932 99.95% 3 0.05% 0 0.00%
NLS data objects 7 100.00% 0 0.00% 0 0.00%
OS file lock latch 2 100.00% 0 0.00% 0 0.00%
OS process 255765 100.00% 0 0.00% 0 0.00%
OS process allocation 538046 99.45% 2971 0.55% 7 0.00%
OS process: request allocation 160442 99.97% 44 0.03% 4 0.00%
PL/SQL warning settings 6258448 100.00% 0 0.00% 0 0.00%
QMT 1 100.00% 0 0.00% 0 0.00%
Reserved Space Latch 1 100.00% 0 0.00% 0 0.00%
SGA IO buffer pool latch 1 100.00% 0 0.00% 0 0.00%
SQL memory manager latch 730 100.00% 0 0.00% 0 0.00%
SQL memory manager workarea li 47710684 100.00% 68 0.00% 3 0.00%
STREAMS LCR 6 100.00% 0 0.00% 0 0.00%
STREAMS Pool Advisor 5 100.00% 0 0.00% 0 0.00%
Shared B-Tree 44638 100.00% 0 0.00% 0 0.00%
Streams Generic 6 100.00% 0 0.00% 0 0.00%
X$KSFQP 635 100.00% 0 0.00% 0 0.00%
active checkpoint queue latch 3536236 100.00% 7 0.00% 0 0.00%
active service list 3255520 99.93% 2355 0.07% 11 0.00%
address list 2 100.00% 0 0.00% 0 0.00%
alert log latch 13 100.00% 0 0.00% 0 0.00%
archive control 42295 99.96% 16 0.04% 0 0.00%
archive process latch 393151 99.99% 33 0.01% 22 0.01%
begin backup scn array 26870 100.00% 0 0.00% 0 0.00%
bq:time manger info latch 46 100.00% 0 0.00% 0 0.00%
buffer pool 8649 100.00% 0 0.00% 0 0.00%
bufq statistics 2848 99.96% 1 0.04% 0 0.00%
cache buffer handles 4971041 99.99% 315 0.01% 0 0.00%
cache buffers chains 8920676449 99.98% 2069828 0.02% 1643 0.00%
cache buffers lru chain 31247334 99.96% 11681 0.04% 132 0.00%
cache table scan latch 283 100.00% 0 0.00% 0 0.00%
change tracking state change l 3 100.00% 0 0.00% 0 0.00%
channel anchor 1 100.00% 0 0.00% 0 0.00%
channel handle pool latch 180405 99.92% 135 0.07% 1 0.00%
channel operations parent latc 7013586 99.99% 849 0.01% 1 0.00%
checkpoint queue latch 47002794 100.00% 872 0.00% 0 0.00%
client/application info 1205021 100.00% 25 0.00% 0 0.00%
commit callback allocation 16518 100.00% 0 0.00% 0 0.00%
compile environment latch 9298405 100.00% 0 0.00% 0 0.00%
constraint object allocation 1236 100.00% 0 0.00% 0 0.00%
database property service latc 1064 100.00% 0 0.00% 0 0.00%
datapump attach fixed tables l 232 93.17% 17 6.83% 0 0.00%
datapump job fixed tables latc 232 92.80% 18 7.20% 0 0.00%
device information 351 100.00% 0 0.00% 0 0.00%
dictionary lookup 3075 100.00% 0 0.00% 0 0.00%
dml lock allocation 17555637 99.99% 1058 0.01% 1 0.00%
dummy allocation 239088 97.65% 5762 2.35% 0 0.00%
enqueue hash chains 262867004 99.98% 48494 0.02% 199 0.00%
enqueue sob latch 8 100.00% 0 0.00% 0 0.00%
enqueues 165892708 99.92% 132226 0.08% 195 0.00%
error message lists 1444 97.90% 31 2.10% 0 0.00%
event group latch 80309 99.99% 8 0.01% 0 0.00%
event range base latch 52 100.00% 0 0.00% 0 0.00%
file cache latch 96178 100.00% 0 0.00% 0 0.00%
file number translation table 10 100.00% 0 0.00% 0 0.00%
generalized trace enabling lat 1 100.00% 0 0.00% 0 0.00%
global KZLD latch for mem in S 77204 100.00% 0 0.00% 0 0.00%
global ctx hash table latch 1 100.00% 0 0.00% 0 0.00%
global tx hash mapping 46851450 100.00% 26 0.00% 1 0.00%
granule operation 37 100.00% 0 0.00% 0 0.00%
hash table column usage latch 863221 99.96% 326 0.04% 8 0.00%
hash table modification latch 78207 100.00% 0 0.00% 0 0.00%
image handles of buffered mess 3807 99.92% 3 0.08% 0 0.00%
instance enqueue 1 100.00% 0 0.00% 0 0.00%
internal temp table object num 125 100.00% 0 0.00% 0 0.00%
intra txn parallel recovery 41279 99.98% 9 0.02% 0 0.00%
job_queue_processes free list 7 100.00% 0 0.00% 0 0.00%
job_queue_processes parameter 59515 100.00% 0 0.00% 0 0.00%
kks stats 13079192 95.78% 570423 4.18% 6229 0.05%
kmcptab latch 370 100.00% 0 0.00% 0 0.00%
krbmrosl 12643 100.00% 0 0.00% 0 0.00%
ksuosstats global area 75830 100.00% 0 0.00% 1 0.00%
ksv instance 2 100.00% 0 0.00% 0 0.00%
ktm global data 17241 100.00% 0 0.00% 0 0.00%
kupp process latch 28 100.00% 0 0.00% 0 0.00%
kwqbsgn:msghdr 5738 99.93% 4 0.07% 0 0.00%
kwqbsn:qsga 43849 100.00% 0 0.00% 0 0.00%
kwqbsn:qxl 88 100.00% 0 0.00% 0 0.00%
kwqi:kchunk latch 4 100.00% 0 0.00% 0 0.00%
lgwr LWN SCN 3938782 99.99% 333 0.01% 1 0.00%
library cache 1494917765 99.16% 9764206 0.65% 2963563 0.20%
library cache load lock 2575470 99.93% 1666 0.06% 17 0.00%
library cache lock 351740459 99.91% 332579 0.09% 174 0.00%
library cache lock allocation 8553222 99.98% 1894 0.02% 4 0.00%
library cache pin 600993071 99.98% 125561 0.02% 326 0.00%
library cache pin allocation 9025194 99.96% 3807 0.04% 0 0.00%
list of block allocation 488123 99.47% 2595 0.53% 0 0.00%
loader state object freelist 111414 100.00% 0 0.00% 0 0.00%
logminer context allocation 620 100.00% 0 0.00% 0 0.00%
longop free list parent 36377 100.00% 0 0.00% 0 0.00%
managed standby latch 6 100.00% 0 0.00% 0 0.00%
message pool operations parent 44239 99.98% 11 0.02% 0 0.00%
messages 25724560 99.97% 8286 0.03% 0 0.00%
mostly latch-free SCN 3942046 99.87% 5316 0.13% 5 0.00%
multiblock read objects 6183081 100.00% 211 0.00% 0 0.00%
ncodef allocation latch 19952 100.00% 0 0.00% 0 0.00%
object queue header heap 707635 100.00% 0 0.00% 0 0.00%
object queue header operation 105353960 100.00% 1711 0.00% 3 0.00%
object stats modification 52198 100.00% 0 0.00% 1 0.00%
parallel query alloc buffer 154711 99.99% 21 0.01% 0 0.00%
parallel query stats 831 77.81% 237 22.19% 0 0.00%
parallel txn reco latch 55253 99.77% 122 0.22% 4 0.01%
parameter list 122005 99.98% 26 0.02% 0 0.00%
parameter table allocation man 136688 93.61% 9317 6.38% 15 0.01%
pass worker exception to maste 19 100.00% 0 0.00% 0 0.00%
post/wait queue 2070210 100.00% 42 0.00% 0 0.00%
process allocation 160398 99.94% 54 0.03% 38 0.02%
process group creation 159123 99.15% 1367 0.85% 0 0.00%
process queue 4458 98.76% 56 1.24% 0 0.00%
process queue reference 206131 99.97% 71 0.03% 1 0.00%
qm_init_sga 1 100.00% 0 0.00% 0 0.00%
qmn state object latch 6 100.00% 0 0.00% 0 0.00%
qmn task queue latch 189296 99.82% 332 0.18% 0 0.00%
query server freelists 3199 97.50% 82 2.50% 0 0.00%
query server process 99 100.00% 0 0.00% 0 0.00%
queue sender's info. latch 6652 99.64% 24 0.36% 0 0.00%
redo allocation 19943526 99.90% 20758 0.10% 7 0.00%
redo copy 80 100.00% 0 0.00% 0 0.00%
redo writing 15989327 99.96% 4250 0.03% 1611 0.01%
reg$ timeout service time 1 100.00% 0 0.00% 0 0.00%
resmgr group change latch 262690 100.00% 0 0.00% 0 0.00%
resmgr:actses active list 503317 100.00% 9 0.00% 0 0.00%
resmgr:actses change group 123295 100.00% 0 0.00% 0 0.00%
resmgr:actses change state 18815 100.00% 0 0.00% 0 0.00%
resmgr:free threads list 235577 97.25% 6560 2.71% 100 0.04%
resmgr:method mem alloc latch 6 100.00% 0 0.00% 0 0.00%
resmgr:plan CPU method 5 100.00% 0 0.00% 0 0.00%
resmgr:resource group CPU meth 3870913 99.89% 4094 0.11% 45 0.00%
resmgr:schema config 259678 100.00% 0 0.00% 0 0.00%
resumable state object 198 100.00% 0 0.00% 0 0.00%
row cache objects 4230393395 99.82% 7511556 0.18% 24003 0.00%
rules engine aggregate statist 6496 99.98% 1 0.02% 0 0.00%
rules engine evaluation contex 7 100.00% 0 0.00% 0 0.00%
rules engine rule set statisti 86320 99.99% 7 0.01% 0 0.00%
rules engine rule statistics 1911 100.00% 0 0.00% 0 0.00%
sequence cache 404134 99.92% 323 0.08% 2 0.00%
session allocation 479208543 99.04% 4654928 0.96% 1717 0.00%
session idle bit 157538415 100.00% 5574 0.00% 29 0.00%
session state list latch 323682 99.85% 491 0.15% 0 0.00%
session switching 23729 100.00% 0 0.00% 0 0.00%
session timer 399506 100.00% 0 0.00% 0 0.00%
shared pool 964689462 98.64% ####### 1.21% 1450987 0.15%
shared server configuration 1 100.00% 0 0.00% 0 0.00%
shrink stat allocation latch 68446 100.00% 0 0.00% 0 0.00%
sim partition latch 621 99.84% 0 0.00% 1 0.16%
simulator hash latch 296192923 100.00% 755 0.00% 0 0.00%
simulator lru latch 279405227 99.94% 160525 0.06% 195 0.00%
slave class 1292 100.00% 0 0.00% 0 0.00%
slave class create 4585 88.68% 0 0.00% 585 11.32%
sort extent pool 286527 100.00% 4 0.00% 0 0.00%
spilled messages latch 24 100.00% 0 0.00% 0 0.00%
state object free list 7448 100.00% 0 0.00% 0 0.00%
statistics aggregation 86999 100.00% 0 0.00% 0 0.00%
temp lob duration state obj al 165 100.00% 0 0.00% 0 0.00%
temporary table state object a 826 100.00% 0 0.00% 0 0.00%
threshold alerts latch 84317 100.00% 0 0.00% 0 0.00%
trace latch 370 99.73% 1 0.27% 0 0.00%
transaction allocation 1061503 99.95% 494 0.05% 0 0.00%
transaction branch allocation 34803065 99.99% 2112 0.01% 18 0.00%
undo global data 61876283 100.00% 574 0.00% 4 0.00%
user lock 11599554 99.70% 27967 0.24% 6823 0.06%
185 rows selected.
logvnp@VNP>
AWR Alerted Metric Element lis;
Consistent RBA;
FAL request queue;
FAL subheap alocation;
FIB s.o chain latch ;
FOB s.o list latch;
In memory undo latch;
JOX SGA heap latch;
In top_sessions that have been displayed by OEM in which the MMON, m001 background process just show now. And in alert_log file, it contents some information:
| Quote: |
$ cat $ORACLE_BASE/admin/VNP/bdump/alert_VNP.log
Fri Jun 19 09:15:19 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 25821K exceeds notification threshold (24576K)
KGL object name :create table CCS_HCM.dk_ps_tra_042009 as SELECT 1 muc, 'CHI TIET DAU KY' tenmuc,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='032009' and khoanmuctt_id in (1,210)),0)+
nvl((select sum(tragoc) from CCS_HCM.ct_tra where ckn_chu='032009' and (cuocnong=0 or (cuocnong=1 and chukyno=26120)) and khoanmuctt_id in (1,210)),0)
,'fm999,999,999,999,999,999') cuoctb,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='032009' an
Fri Jun 19 09:15:19 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 25819K exceeds notification threshold (24576K)
Details in trace file /u02/app/oracle/admin/VNP/udump/vnp_ora_2230.trc
KGL object name :create table CCS_HCM.dk_ps_tra_042009 as SELECT 1 muc, 'CHI TIET DAU KY' tenmuc,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='032009' and khoanmuctt_id in (1,210)),0)+
nvl((select sum(tragoc) from CCS_HCM.ct_tra where ckn_chu='032009' and (cuocnong=0 or (cuocnong=1 and chukyno=26120)) and khoanmuctt_id in (1,210)),0)
,'fm999,999,999,999,999,999') cuoctb,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='032009' an
Fri Jun 19 09:21:27 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 25817K exceeds notification threshold (24576K)
KGL object name :create table CCS_HCM.dk_ps_tra_042009 as SELECT 1 muc, 'CHI TIET DAU KY' tenmuc,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='032009' and khoanmuctt_id in (1,210)),0)+
nvl((select sum(tragoc) from CCS_HCM.ct_tra where ckn_chu='032009' and (cuocnong=0 or (cuocnong=1 and chukyno=26120)) and khoanmuctt_id in (1,210)),0)
,'fm999,999,999,999,999,999') cuoctb,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='032009' an
Fri Jun 19 09:21:36 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 25840K exceeds notification threshold (24576K)
KGL object name :create table CCS_HCM.dk_ps_tra_052009 as SELECT 1 muc, 'CHI TIET DAU KY' tenmuc,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='042009' and khoanmuctt_id in (1,210)),0)+
nvl((select sum(tragoc) from CCS_HCM.ct_tra where ckn_chu='042009' and (cuocnong=0 or (cuocnong=1 and chukyno=26121)) and khoanmuctt_id in (1,210)),0)
,'fm999,999,999,999,999,999') cuoctb,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='042009' an
Fri Jun 19 09:21:36 2009
Memory Notification: Library Cache Object loaded into SGA
Heap size 25835K exceeds notification threshold (24576K)
Details in trace file /u02/app/oracle/admin/VNP/udump/vnp_ora_2394.trc
KGL object name :create table CCS_HCM.dk_ps_tra_052009 as SELECT 1 muc, 'CHI TIET DAU KY' tenmuc,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='042009' and khoanmuctt_id in (1,210)),0)+
nvl((select sum(tragoc) from CCS_HCM.ct_tra where ckn_chu='042009' and (cuocnong=0 or (cuocnong=1 and chukyno=26121)) and khoanmuctt_id in (1,210)),0)
,'fm999,999,999,999,999,999') cuoctb,
to_char(nvl((select sum(nogoc) from CCS_HCM.ct_no where ckn_chu='042009' an
$
|
I knew MMON performs various manageability-related background tasks, such as:
- Issuing alerts whenever a given metrics violates its threshold value
- Taking snapshots by spawning additional process (MMON slaves)
- Capturing statistics value for SQL objects which have been recently modified
In my prod database, I set the undocumented parameter:
logvnp@VNP> select name, value
2 from v$parameter
3 where name like '_kg%';
NAME VALUE
---------------------------------- --------------------------
_kgl_large_heap_warning_threshold 25165824
25MB for keeping the object's size in library cache threshold, so, the object which CCS_HCM.dk_ps_tra_052009 was created before, can not loaded by statement into library cache, and when AWR scans time by time, it detected, generated warning information into alert_log file. I understood that, don't I? Am I wrong?
Please correct me!
Thank you very much!
[Updated on: Thu, 18 June 2009 22:00] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: May you explain to me about AWR? [message #409063 is a reply to message #409051] |
Fri, 19 June 2009 02:37  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Dear Michel & B.Swan!
I think I find some thing cause latch shared_pool, but may you confirm to me?
The wait_class column
logvnp@VNP> select sid from spid_and_pid
2 where pid=8726
3 /
sid
--------------------
671
logvnp@VNP> col wait_class format a15
logvnp@VNP> select wait_class
2 from v$session
3 where sid=671
4 /
WAIT_CLASS
-----------
Concurrency
logvnp@VNP> select sql_id
2 from v$sql
3 where exists (select sql_address from v$session
4 where sid=617);
SQL_ID
-------------------------------------
13q3wf3wfy23f
logvnp@VNP> select count(executions)
2 from v$sql
3 where sql_id='13q3wf3wfy23f';
COUNT(EXECUTIONS)
-----------------
47852
logvnp@VNP>
Oap, 47852 executions concurrency. I am really amazing. Please see the sql statement:
logvnp@VNP> create table sqltext as
2 select sql_fulltext, cpu_time/1000000 cpu_by_sec
3 from v$sql
4 where sql_id='13q3wf3wfy23f';
Table created.
logvnp@VNP> col sql_fulltext format a50
logvnp@VNP> set linesize 150
logvnp@VNP> select sql_fulltext, cpu_by_sec
2 from (select * from sqltext
3 where rownum<=1);
SQL_FULLTEXT CPU_BY_SEC
-------------------------------------------------- ----------
select distinct qltn.chukyno_dangchu(chukyno,null, .129236
null) from
CCS_HCM.ct_tra_052009 where ph
ieu_id=:"SYS_B_0"
Using function in a query, with 47852 conccurently executions time. Is it a latch: shared_pool latch, isn't it?
Thank you!
Oh, I am sorry for a reason: Why did I track the sid 617? I've used the tool-prstat to track process in solaris
Thank you!
[Updated on: Fri, 19 June 2009 02:48] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Nov 25 16:20:03 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
