[ORACLE] analyzes 23 oracle sessions and performance statements, and 23 oracle session statements.

[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;