Oracle DBA (12c/19c) Articles and Scripts

  Note:  Proof read any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage. Listed below are some SQL queries which are very useful for performance tuning. these are based on the ACTIVE SESSION HISTORY V$ VIEW to get a current perspective of performance and the DBA_HIST_* AWR HISTORY TABLES for obtaining performance data pertaining to a period of time in the past. -- TOP RECENT WAIT EVENTS SET LINESIZE 132 PAGESIZE 60 COL EVENT FORMAT A60 SELECT * FROM (    SELECT ACTIVE_SESSION_HISTORY.EVENT,           SUM(ACTIVE_SESSION_HISTORY.WAIT_TIME +               ACTIVE_SESSION_HISTORY.TIME_WAITED) TTL_WAIT_TIME    FROM V$ACTIVE_SESSION_HISTORY ACTIVE_SESSION_HISTORY    WHERE ACTIVE_SESSION_HISTORY.EVENT IS NOT NULL    GROUP BY ACTIVE_SESSION_HISTORY.EVENT    ORDER BY 2 DESC) WHERE ROWNUM <= 10 / -- TOP WAIT EVENTS SINCE INSTANCE STARTUP SET LINESIZE 132 PAGESIZE 60 COL EVENT FORMAT A60 SELECT * FROM (      SELECT  EVENT, TOTAL_WAI

set colsep | set linesize 100 pages 100 trimspool on numwidth 14 col name format a25 col owner format a15 col "Used (GB)" format a15 col "Free (GB)" format a15 col "(Used) %" format a15 col "Size (M)" format a15 SELECT d . status "Status" , d . tablespace_name "Name" , TO_CHAR ( NVL ( a . bytes / 1024 / 1024 / 1024 , 0 ) , '99,999,990.90' ) "Size (GB)" , TO_CHAR ( NVL ( a . bytes - NVL ( f . bytes , 0 ) , 0 ) / 1024 / 1024 / 1024 , '99999999.99' ) "Used (GB)" , TO_CHAR ( NVL ( f . bytes / 1024 / 1024 / 1024 , 0 ) , '99,999,990.90' ) "Free (GB)" , TO_CHAR ( NVL ( ( a . bytes - NVL ( f . bytes , 0 ) ) / a . bytes * 100 , 0 ) , '990.00' ) "(Used) %" FROM sys . dba_tablespaces d , ( select tablespace_name , sum ( bytes ) bytes from dba_data_files group by tablespace_name ) a , ( select tablespa