[ORACLE] analyzes 23 oracle sessions and performance statements, and 23 oracle session statements.
**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************
-- View Cluster waiting for cache busy -- wait for resource buffer busy -- data block consistent read wait
SELECT inst_id, event, p1 file_number, p2 block_number, wait_time
FROM gv $ session_wait
WHERE eventIN ('Global cache busy', 'buffer busy global cache', 'buffer busy global C ');
-- Global lock session
SELECT
D1.INST _ ID inst_id, s. sid, p. spid,
D1.RESOURCE _ NAME1 resource_name,
S. sid, sw. EVENT, sw. WAIT_TIME
FROM gv $ ges_enqueue d1, gv $ process p, gv $ session s, gv $ session_wait sw
WHERE blocker = 1
AND (d1.INST _ ID = p. INST_IDand d1.pid = p. spid)
AND (p. inst_id = s. INST_IDand p. addr = s. paddr)
AND (s. INST_ID = sw. INST_IDand s. sid = sw. sid)
ORDERBY sw. WAIT_TIMEdesc;
-- Query the ratio of write operations of cache Fusion
Select a. INST_ID "Instance ",
A. VALUE/B. value "Cache fusion writes ratio"
From gv $ sysstat a, GV $ SYSSTAT B
Where a. name = 'dbwr fusion writes'
And B. name = 'physical writes'
And B. INST_ID = A. inst_id
Groupby a. INST_ID, A. VALUE/B. VALUE;
-- Query the network address
SELECT * from gv $ CLUSTER_INTERCONNECTS;
-- Consistent data block request time
SELECT b1.INST _ ID, b2.VALUE "blocks recevied ",
B1.VALUE "block recevied time ",
(B1.value/b2.value) * 10) "avg block rec time (MS )"
FROM gv $ sysstat b1, gv $ sysstat b2
WHERE b1.name = 'gc cr block receive time'
AND b2.name = 'gc cr blcoks recevied'
AND b1.inst _ id = b2.INST _ ID
AND b2.value <> 0;
-- View process and sga Resources
SELECT resource_name, current_utilization cu, max_utilization mu,
A. INITIAL_ALLOCATION, limit_value lv
FROM gv $ resource_limit
WHERE max_utilization> 0;
-- View shared_pool_size Resources
SELECT *
From v $ SGASTAT
WHERENAMELIKE 'g % ';
-- View parallel oracle Processes
SELECT inst_id, statistic, value
FROM gv $ pq_sysstat
WHEREvalue> 0
Orderby1, 2;
-- View ongoing transactions of the regression segment
Select a. name, B. xacts, c. sid, c. SERIAL #, c. USERNAME, d. SQL _TEXT
From v $ rollname a, v $ rollstat B, v $ session c, v $ sqltext d, v $ transaction e
Where a. usn = B. usn
And B. usn = e. xidusn
And c. taddr = e. addr
And c. SQL _address = d. address
And c. SQL _hash_value = d. hash_value;
-- View the unod transaction table
Select xidusn, xidslot, xidsqn, ubablk, ubafil, ubarecfrom v $ transaction;
-- View the oracle process of the Operating System
SELECT a. username, a. COMMAND, a. program, spid, sid, a. serial #
FROM v $ session a, v $ process B
WHERE a. paddr = B. addr
And spid = '000000 ';
-- View the SQL statement corresponding to the operating system process
Select B. username, a. SQL _text
From v $ SQL a, v $ session B
Where B. SQL _address = a. address
And B. SQL _hash_value = a. hash_value
And B. sid = '000000 ';
-- View the number of CPUs
Selectname, valuefrom v $ parameter wherename = 'cpu _ count ';
-- Library cache hit rate
Selectsum (pins)/(sum (pins) + sum (reloads) * 100 "hit ratio"
From v $ librarycache;
-- Data Dictionary hit rate
Select (1-sum (getmisses)/sum (gets) * 100 "hit ratio"
From v $ rowcache;
-- PGA memory sorting hit rate
Select a. value "Disk Sorts", B. value "Memroy sorts ",
Round (100 * B. value)/decode (. value + B. value), 0, 1, (. value + B. value), 2) "Pct memory sorts"
From v $ sysstat a, v $ sysstat B
Where a. name = 'sorts (disk )'
And B. name = 'sorts (memory )';
-- Cache zone, cache hit rate
Select (1-sum (decode (name, 'Physical reads', value, 0 ))/
(Sum (decode (name, 'db block gets', value, 0) +
Sum (decode (name, 'consistent gets', value, 0 ))
)
) * 100 "hit radio"
From v $ sysstat;
-- 10 large system memory usage statements, total memory usage
Selectsum (pct_bufgets) "percent"
From (selectrank () over (orderby buffer_getsdesc) as rank_bufgets,
To_char (100 * ratio_to_report (buffer_gets) over (), '192. 99') pct_bufgets
From v $ sqlarea)
Where rank_bufgets <11;
-- View dirty Blocks
Select objd, count (*)
From v $ bh
Where dirty = 'y'
Groupby objdorderbycount (*);
-- View oracle Operating System Processes
Select * from v $ processwhere backgroundisnull;
-- View the SQL statement corresponding to the operating system process
Select SQL _textfrom v $ SQL
Where SQL _idin
(Select SQL _idfrom gv $ session
Where paddrin (select addrfrom gv $ processwhere program = 'oracle. EXE (SHAD )'));
-- View hotspot objects
-- View cpu usage of each session
Select ss. sid, se. SERIAL #, se. command, ss. valueCPU, se. username, se. program
From v $ sesstatss, v $ session se
Where ss. statistic # in
(Select statistic #
From v $ statname
Wherename = 'cpu used by this session ')
And se. sid = ss. sid
And ss. sid> 6
OrderbyCPUdesc;
-- View the SQL statement corresponding to the pid
Select s. sid, w. event, w. wait_time, w. seq #, q. SQL _text
From v $ session_wait w, v $ session s, v $ process p, v $ sqlarea q
Where s. paddr = p. addr
-And s. sid = 146
And s. SERIAL # = 6488
And s. SQL _address = q. address;