敗家貓

看到不錯的整理資料

來源:http://www.rocket99.com/oracle/oracle22.html

Oracle Database Monitoring and Tuning

set serverout on size 999999

set linesize 155

declare
     begin
     dbms_output.put_line(' ');
     dbms_output.put_line('------------- Start report for waiting sessions with current SQL ---------------');
     for x in (select vs.inst_id, vs.sid || ',' || vs.serial# sidser, vs.sql_address, vs.sql_hash_value,
     vs.last_call_et, vsw.seconds_in_wait, vsw.event, vsw.state
     from gv$session_wait vsw, gv$session vs
     where vsw.sid = vs.sid
     and vsw.inst_id = vs.inst_id
     and vs.type <> 'BACKGROUND'
     and vsw.event NOT IN ('rdbms ipc message'
     ,'smon timer'
     ,'pmon timer'
     ,'SQL-Net message from client'
     ,'lock manager wait for remote message'
     ,'ges remote message'
     ,'gcs remote message'
     ,'gcs for action'
     ,'client message'
     ,'pipe get'
     ,'Null event'
     ,'PX Idle Wait'
     ,'single-task message'
     ,'PX Deq: Execution Msg'
     ,'KXFQ: kxfqdeq - normal deqeue'
     ,'listen endpoint status'
     ,'slave wait'
     ,'wakeup time manager'))
     loop
begin
     dbms_output.put_line('Event WaitState InstID SidSerial LastCallEt SecondsInWait');
     dbms_output.put_line('------------------------- -------------------- ------ ----------- ---------- -------------');
     dbms_output.put_line(rpad(x.event,25) ||' '|| rpad(x.state,20) ||' '|| lpad(x.inst_id,6) ||' '|| lpad(x.sidser,11) ||'
     '|| lpad(x.last_call_et,10) ||' '|| lpad(x.seconds_in_wait,13));
     dbms_output.put_line(' SQLText ');
     dbms_output.put_line('----------------------------------------------------------------');
     for y in (select sql_text
     from gv$sqltext
     where address = x.sql_address
     and hash_value = x.sql_hash_value
     and inst_id = x.inst_id
     order by piece)
     loop
     dbms_output.put_line(y.sql_text);
     end loop;
     end;
     end loop;
     dbms_output.put_line('-------------- End report for sessions waiting with current SQL ----------------');
     dbms_output.put_line(' ');
end;

SELECT * FROM product_component_version ;

SELECT sum(bytes)/1024 "free space in KB"
FROM dba_free_space;
SELECT sum(bytes)/1024 "used space in KB"
FROM dba_segments;

SELECT * FROM V$SESSION ;

SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS,
  PCT_INCREASE, MIN_EXTLEN
FROM DBA_TABLESPACES;

SELECT TABLESPACE_NAME, CONTENTS, STATUS
FROM DBA_TABLESPACES;

SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,
       MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES;

SELECT FILE#,T1.NAME,STATUS,ENABLED,BYTES,CREATE_BYTES,T2.NAME
FROM   V$DATAFILE T1, V$TABLESPACE T2
WHERE  T1.TS# = T2.TS# ;

SELECT tablespace_name,COUNT(*) AS fragments,
   SUM(bytes) AS total,
   MAX(bytes) AS largest
FROM dba_free_space
GROUP BY tablespace_name;

SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS;

SELECT TABLESPACE_NAME, COUNT(*), MAX(BLOCKS), SUM(BLOCKS)
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NUMBER ;

SELECT segment_name, extent_id, blocks, bytes
      FROM dba_extents
      WHERE segment_name = TNAME ;

SELECT segment_name, extent_id, blocks, bytes
      FROM dba_extents
      WHERE segment_name = TNAME ;

SELECT segment_name,segment_type,
       max_extents, extents
FROM dba_segments
WHERE extents+5 > max_extents
AND segment_type<>'CACHE';

SELECT s.segment_name,s.segment_type,s.tablespace_name,s.next_extent
FROM dba_segments s
WHERE NOT EXISTS (SELECT 1
   FROM dba_free_space f
   WHERE s.tablespace_name=f.tablespace_name
   HAVING max(f.bytes) > s.next_extent);

SELECT blocks as BLOCKS_USED, empty_blocks
FROM dba_tables
WHERE table_name=TNAME;

SELECT chain_cnt AS CHAINED_BLOCKS
FROM dba_tables
WHERE table_name=TNAME;

SELECT COUNT(*) AS EXTENT_COUNT
FROM dba_extents
WHERE segment_name=TNAME;

SELECT SEGMENT_NAME,TABLESPACE_NAME,OWNER,STATUS
FROM DBA_ROLLBACK_SEGS;

/* General Rollback Segment Information */

SELECT t1.name, t2.extents, t2.rssize, t2.optsize, t2.hwmsize, t2.xacts, t2.status
FROM   v$rollname t1, v$rollstat t2
WHERE  t2.usn = t1.usn ;

/* Rollback Segment Information - Active Sessions */

select t2.username, t1.xidusn, t1.ubafil, t1.ubablk, t2.used_ublk
from v$session t2, v$transaction t1
where t2.saddr = t1.ses_addr 

SELECT T1.NAME, T2.EXTENTS, T2.RSSIZE, T2.OPTSIZE, T2.HWMSIZE,
          T2.XACTS, T2.STATUS
FROM   V$ROLLNAME T1, V$ROLLSTAT T2
WHERE  T1.USN = T2.USN AND
       T1.NAME LIKE '%RBS%';

SELECT s.sid, s.serial#
 FROM v$session s
 WHERE s.saddr in
  (SELECT t.ses_addr
    FROM V$transaction t, dba_rollback_segs r
    WHERE t.xidusn=r.segment_id
    AND r.tablespace_name='RBS');

SELECT T1.USERNAME, T2.TABLESPACE, T2.CONTENTS, T2.EXTENTS, T2.BLOCKS
FROM V$SESSION T1, V$SORT_USAGE T2
WHERE T1.SADDR = T2.SESSION_ADDR ;

SELECT index_name,table_name,uniqueness
FROM dba_indexes
WHERE index_name in
  (SELECT constraint_name
   FROM dba_constraints
   WHERE table_name = TNAME
    AND constraint_type in ('P','U')) ;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA1','COMPANY');

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA1');



ANALYZE TABLE COMPANY COMPUTE STATISTICS ;

set pagesize 0;

select 'TABLE:',table_name,'current' from user_tables 
    union
select 'SYNONYM:',synonym_name,table_owner from user_synonyms  
order by 1,2 ;

SELECT constraint_name,table_name, column_name
FROM dba_cons_columns
WHERE table_name = TNAME
ORDER BY table_name, constraint_name, position
END IF;

SELECT constraint_name, table_name,
     constraint_type, validated, status
FROM dba_constraints;

select 
    b.uniqueness, a.index_name, a.table_name, a.column_name 
from user_ind_columns a, user_indexes b
where a.index_name=b.index_name 
order by a.table_name, a.index_name, a.column_position;

SELECT trigger_name, status
 FROM dba_triggers ;

SELECT    SUM(PINS) EXECS,
          SUM(RELOADS)MISSES,
          SUM(RELOADS)/SUM(PINS) HITRATIO
FROM      V$LIBRARYCACHE ;

SELECT    SUM(GETS) HITS,
          SUM(GETMISSES) LIBMISS,
          SUM(GETMISSES)/SUM(GETS) RCRATIO
FROM      V$ROWCACHE ;

SELECT NAME, VALUE
FROM   V$SYSSTAT WHERE NAME IN
   ('DB BLOCK GETS','CONSISTENT GETS','PHYSICAL READS');

SELECT NAME, VALUE
FROM   V$SYSTAT
WHERE NAME LIKE '%SORT%';

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME='RECURSIVE CALLS' ;

SELECT CLASS,COUNT
FROM V$WAITSTAT
WHERE CLASS LIKE '%UNDO%' ;

SELECT NAME, PHYRDS,PHYWRTS
FROM V$DATAFILE DF, V$FILESTAT FS
WHERE DF.FILE#=FS.FILE# ;

/* Kill a session, specified by the returned sess-id / serial number */

SELECT sid, serial#, username from v$session

ALTER SYSTEM KILL SESSION 'sessid,ser#'