Friends, I'm sharing some of the database scripts which might be useful for you.
DOWNLOAD the Script HERE.
Finding Hot Blocks which is causing for Cache Buffer Chain Wait Event.
#!/bin/bash
clear scr
echo "*****************************************"
echo "********** Finding HOT Blocks ***********"
echo "*****************************************"
sqlplus -S / as sysdba@$1<<EOF
set line 500 pagesize 100
col OWNER for a15
col STATISTIC_NAME for a20
select * from
(select owner,object_name,object_type,statistic_name,sum(value)
from V\$SEGMENT_STATISTICS
where owner not in ('SYS','SYSMAN','SYSTEM','DBSNMP')
GROUP BY owner,object_name,object_type,statistic_name
ORDER BY SUM(value) DESC)
WHERE ROWNUM <= 20;
EOF
echo "************************************"
echo "************ Session Wait **********"
echo "************************************"
sqlplus -S / as sysdba@$1<<EOF
set line 500 pagesize 500
select sid, event, P1 File#, P2 Block#
from v\$session_wait
where event not in
('SQL*Net message from client',
'SQL*Net message to client',
'null event',
'rdbms ipc message',
'pipe get',
'queue messages',
'jobq slave wait',
'virtual circuit status')
order by P2 desc;
EOF
echo "Please enter the File ID"
read file
echo "Please enter the Block ID"
read block
sqlplus -S / as sysdba@$1<<EOF
select owner,segment_name
from dba_extents
where file_id = $file
and
block_id = $block;
EOF
OUTPUT.
[oracle@node1 script]$ sh HOT_BLOCKS.sh node1
*****************************************
********** Finding HOT Blocks ***********
*****************************************
OWNER OBJECT_NAME OBJECT_TYPE STATISTIC_NAME SUM(VALUE)
--------------- ------------------------------ ------------------ -------------------- ----------
TEST EMP_IDX INDEX logical reads 3719200
TEST EMP TABLE logical reads 2039520
TEST EMP_IDX INDEX db block changes 2030768
TEST EMP TABLE db block changes 2029232
TEST EMP_IDX INDEX space used 1435190
TEST EMP_IDX INDEX physical writes 3879
TEST EMP TABLE physical writes 3058
TEST EMP_IDX INDEX physical write reque 2307
sts
TEST EMP TABLE physical write reque 1532
sts
TEST EMP TABLE physical reads direc 796
t
APEX_030200 WWV_FLOW_PLATFORM_PREFS_FKIDX INDEX logical reads 736
EXFSYS RLM$JOINQKEY INDEX logical reads 528
TEST EMP TABLE physical reads 231
TEST EMP TABLE physical read reques 231
ts
TEST EMP_IDX INDEX physical reads 151
TEST EMP_IDX INDEX physical read reques 151
ts
EXFSYS RLM$SCHACTIONORDER INDEX logical reads 96
APEX_030200 WWV_FLOW_PLATFORM_PREFS TABLE logical reads 64
MDSYS UNIQUE_GEORASTERS INDEX logical reads 64
MDSYS SDO_TOPO_METADATA_TABLE TABLE logical reads 32
20 rows selected.
************************************
************ Session Wait **********
************************************
SID EVENT FILE# BLOCK#
---------- ---------------------------------------------------------------- ---------- ----------
27 wait for unread message on broadcast channel 1398581300 1398502736
46 wait for unread message on broadcast channel 1398568540 1316222260
6 DIAG idle wait 1 1
10 DIAG idle wait 1 1
13 gcs remote message 1 0
16 GCR sleep 0 0
21 smon timer 300 0
24 ASM background timer 0 0
47 Streams AQ: qmn coordinator idle wait 0 0
49 class slave wait 0 0
56 class slave wait 0 0
59 Streams AQ: waiting for time management or cleanup tasks 0 0
62 Streams AQ: qmn slave idle wait 1 0
65 Space Manager: slave idle wait 0 0
69 Space Manager: slave idle wait 1 0
12 ges remote message 80 0
8 PING 0 0
70 VKRM Idle 0 0
2 pmon timer 300 0
4 VKTM Logical Idle Wait 0 0
20 rows selected.
Please enter the File ID
1398581300
Please enter the Block ID
1398502736
no rows selected
No comments:
Post a Comment