ITPub博客

ASM Disks Performance metric:SELECT a.name          disk_group_name,       b.path          disk_path,       b.reads     &n...

select name,sector_size,block_size,allocation_unit_size,state,type,total_mb,free_mb,(total_mb-free_mb) "used_mb",round((total_mb-free_mb)/total_mb,4)*100 "Used Ratio" from v$asm_diskgroup;

col spid format a10col inst_id format a7col owner format a15col object_name format a20col machine format a30col program format a20select pr.spid,se.inst_id,se.sid,se.serial#,ob.owner,ob.object_name,se.osuser,se.machine,se.terminal,se.program,gvl1.ctime,(select count(*)-1 from gv$lock  w...

select s.SID,s.SERIAL#,t.USED_UBLK,t.USED_UREC from v$transaction t,v$session s where t.ADDR=s.TADDR order by sid

select tablespace_name,sum(decode(status,'EXPIRED',bytes,0)) "EXPIRED", round(sum(decode(status,'EXPIRED',bytes,0))/sum(bytes),4)*100 "Reused Ratio",       sum(decode(status,'UNEXPIRED',bytes,0)) "UNEXPIRED", sum(decode(status,'ACTIVE',bytes,0)) "ACTIVE",  &nbsp...

select inst_id,to_char(first_time,'yyyy/mm/dd') "Date",sum(decode(to_char(first_time,'HH24'),'00',1,0)) H00,sum(decode(to_char(first_time,'HH24'),'01',1,0)) H01,sum(decode(to_char(first_time,'HH24'),'02',1,0)) H02,sum(decode(to_char(first_time,'HH24'),'03',1,0)) H03,sum(decode(to_char(first_time,'...

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') TIME,       SUM(reloads) reloads, round(sum(reloads)/sum(pins),4)*100 "Library Cache Reload Ratio",        SUM(gets) gets, SUM(gethits) gethits, round(sum(gethits)/sum(gets),4)*100 "GetHit ...

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') currenttime,round(1-(physical_read-physical_reads_direct)/(db_block_gets+consistent_gets-physical_reads_direct),4)*100 "Buffer Cache Hit Ratio",       buffer_busy_waits,free_buffer_waitsfrom (SELECT TO_CHAR(SYSDATE, 'yyy...

SELECT s.sid, s.serial# serial, s.module, s.program, s.status session_status, t.status txn_status, t.start_time start_time,       r.name rbs, t.used_urec undo_records, t.used_ublk, t.log_io txn_logical_io,  t.phy_io txn_physical_io, t.cr_get txn_consistent_gets,&...

SELECT s.sid sid, s.serial# serial, s.module module, s.program program, s.event, SUM(su.blocks) temp_blocks,       trunc(SUM(su.blocks) * 8096 / 1024 / 1024) temp_size  , sql.sql_text   FROM v$sort_usage su, v$session s ,v$sql sql WHERE su.session_addr...

select  PID , SID , LADDR , NAME , GETS from GV$LATCHHOLDER where inst_id = USERENV('Instance')

select l.latch# latch,l.name, nvl(c.latches, 0) children, l.gets, l.misses, l.gets-l.misses "Simple Gets",       round((l.gets-l.misses) / l.gets,4)*100 "Simple Gets Ratio(%)",       l.spin_gets, round(l.spin_gets / l.gets,4)*100 "Spin Ge...

SELECT (SELECT parsing_schema_name FROM v$sqlarea WHERE sql_id = a.max_sql_id) SCHEMA,       (SELECT MODULE FROM v$sqlarea WHERE sql_id = a.max_sql_id) MODULE,        a.literal_sql_cnt,        a.ex...

select dbf.tablespace_name,dbf.file_name, gvf.PHYRDS,  gvf.PHYWRTS, round(gvf.phyrds / (gvf.PHYRDS + gvf.PHYWRTS),4)*100  || ' / '||  (1-round(gvf.phyrds / (gvf.PHYRDS + gvf.PHYWRTS),4))*100 "Phyrd/phywt Ratio"  ,       round((gvf.PHYRDS + gvf.PHYWR...

SELECT u.username,       SUM(DECODE(o.object_type, 'INDEX', 1)) "INDEX",       SUM(DECODE(o.object_type, 'TABLE', 1)) "TABLE",       SUM(DECODE(o.object_type, 'CLUSTER', 1)) "CLUSTER",   &nbs...

Check user's informationselect u.username,u.account_status,u.created,u.expiry_date,u.default_tablespace,u.temporary_tablespace,sum(decode(sid,null,0,1)) "current logon",sum(decode(status,null,0,'INACTIVE',0,1)) "current active",round(sum(sysdate-logon_time)*24*60*60 / sum(decode(sid,null,0,1)),0) ...

select y.name,y.bytes/1024/1024 total_mb,(x.total_blocks*y.block_size)/1024/1024 used_mb,(y.bytes-x.total_blocks*y.block_size)/1024/1024 free_mb,round((x.total_blocks*y.block_size)/y.bytes,4)*100 pctfrom v$sort_segment x,(select b.name,sum(a.bytes) bytes,a.block_size from v$tempfile a,v$tablespac...

set linesize 160col ksppinm format a40col ksppstvl format a30col ksppdesc format a190SELECT ksppinm, ksppstvl, ksppdesc  FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx   AND ksppinm like '%&value%';

set pagesize 100set timing onselect * from(select x.tablespace_name,x.contents,x.status,x.extent_management,x.all_bytes/1024/1024 "ALL_SIZE(MB)",y.free_bytes/1024/1024 "FREE_SIZE(MB)",(x.all_bytes-y.free_bytes)/1024/1024 "USED_SIZE(MB)",round((x.all_bytes-y.free_bytes)/x.all_bytes,4)*100 USAGEf...

select count(*) count from v$session where status='ACTIVE';