[ORACLE] analyzes 23 oracle sessions and performance statements

[ORACLE] analyzes 23 oracle sessions and performance statements

-- View Cluster wait cache busy -- wait for resource buffer busy -- data block consistent read wait SELECT inst_id, event, p1 file_number, p2 block_number, wait_timeFROM gv $ session_waitWHERE eventIN ('Global cache busy ', 'buffer busy global cache', 'buffer busy global cr '); -- session SELECTd1.INST _ ID inst_id, s. sid, p. spid, d1.RESOURCE _ NAME1 resource_name, s. sid, sw. EVENT, sw. WAIT_TIMEFROM gv $ ges_enqueue d1, gv $ process p, gv $ session s, gv $ session_wait swWHERE blocker = 1AND (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 cache fusion write operations select. INST_ID "Instance",. VALUE/B. value "Cache fusion writes ratio" from gv $ sysstat a, GV $ sysstat bwhere. name = 'dbwr fusion writes 'and B. name = 'physical writes 'and B. INST_ID =. inst_idGROUPBY. INST_ID,. 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 b2WHERE b1.name = 'gc cr block receive time' AND b2.name = 'gc cr blcoks recevied 'AND b1.inst _ id = b2.INST _ IDAND b2.value <> 0; -- view the process, sga resource SELECT resource_name, current_utilization cu, max_utilization mu,. INITIAL_ALLOCATION, limit_value lvFROM gv $ resource_limit aWHERE max_utilization> 0; -- View shared_pool_size resource SELECT * from v $ comment 'G % '; -- view the oracle Parallel Process SELECT inst_id, statistic, valueFROM gv $ pq_sysstatWHEREvalue> 0orderby1, 2; -- view the select. name, B. xacts, c. sid, c. SERIAL #, c. USERNAME, d. SQL _TEXTfrom v $ rollname a, v $ rollstat B, v $ session c, v $ sqltext d, v $ transaction ewhere. usn = B. usnand B. usn = e. xidusnand c. taddr = e. addrand c. SQL _address = d. addressand 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 SELECT a in the operating system. username,. COMMAND,. program, spid, sid,. serial # FROM v $ session a, v $ process bWHERE. paddr = B. addrand spid = '000000'; -- view the SQL statement select B corresponding to the operating system process. username,. SQL _textfrom v $ SQL a, v $ session bwhere B. SQL _address =. addressand B. SQL _hash_value =. hash_valueand 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; -- select. 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 bwhere. 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 (), '2017. 99 ') pct_bufgetsfrom v $ sqlarea) where rank_bufgets <11; -- View dirty blocks select objd, count (*) from v $ bhwhere dirty = 'y' groupby objdorderbycount (*); -- view the oracle operating system process select * from v $ processwhere backgroundisnull; -- view the SQL statement select SQL _textfrom v $ sqlwhere SQL _idin (select SQL _idfrom gv $ sessionwhere 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. programfrom v $ sesstatss, v $ session sewhere ss. statistic # in (select statistic # from v $ statnamewherename = 'cpu used by this session') and se. sid = ss. sidand ss. sid> 6 orderbyCPUdesc; -- view the SQL statement select s corresponding to the pid. sid, w. event, w. wait_time, w. seq #, q. SQL _textfrom v $ session_wait w, v $ session s, v $ process p, v $ sqlarea qwhere s. paddr = p. addr -- and s. sid = 146and s. SERIAL # = 6488and s. SQL _address = q. address;