Abdul Hafeez Kalsekar -- Oracle database tuning case studies

 -- NAME: RACDIAG.SQL -- ------------------------------------------------------------------------ -- AUTHOR: -- Michael Polaski - Oracle Support Services -- Copyright 2002, Oracle Corporation -- ------------------------------------------------------------------------ -- PURPOSE: -- This script is intended to provide a user friendly guide to troubleshoot -- RAC hung sessions or slow performance scenerios. The script includes -- information to gather a variety of important debug information to determine -- the cause of a RAC session level hang. The script will create a file -- called racdiag_.out in your local directory while dumping hang analyze -- dumps in the respective user_dump_dest/background_dump_dest/diagnostic_dest on all nodes. -- -- ------------------------------------------------------------------------ -- DISCLAIMER: -- This script is provided for educational purposes only. -- ------------------------------------------------------------------------ -- Script output is as...

  This is  very old issue where undo extends puck  up high retention ( more then defined undo_retention) and  undo block remains in unexpired state for long time .  This is because of   _undo_autotune which is  by default set to true .   _undo_autotune   will try to override the undo_retention parameter. When _undo_autotune is set to True (default value), based on the size of the undo tablespace Oracle will try to keep the undo segments to higher time than defined in undo_retention parameter. SQL> select TABLESPACE_NAME,STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY tablespace_name,STATUS order by tablespace_name; TABLESPACE_NAME STATUS SUM(BYTES) COUNT(*) ------------------------------ --------- ----------------- ---------- UNDOTBS1 EXPIRED 65536 1 UNDOTBS1 UNEXPIRED 10285809664 3271 UNDOTBS2 EXPIRED 142802944 6 UNDOTBS2 UNEXPIRED 4242735104 642 Suggested Solutions : One of below solution   can  ...

  set echo off -- -- PGA and UGA usage script for trend analysis. -- Updated: 2019.01.04 -- -- This script is meant to be run via SQL*Plus. -- The user of this script needs to have the -- SELECT privilege on the following views: -- -- - V$SESSION -- - V$SESSTAT -- - V$STATNAME -- set feedback off; set heading off; set linesize 262; set newpage none; set show off; set pagesize 55; set trimspool on; set verify off; column "SID AND SERIAL#" FORMAT A19 col SNAP_COLUMN new_value SNAP_TIME col SNAP_EOF_NAME new_value EOF_NAME col SNAP_HOST_NAME new_value THE_HOST_NAME col SNAP_INSTANCE_NAME new_value THE_NAME_OF_THE_INSTANCE col SNAP_RDBMS_VERSION new_value THE_RDBMS_VERSION set term off; select to_char(sysdate,'YYYYMMDD_HH24MISS') "SNAP_COLUMN" from dual; select trim(host_name) "SNAP_HOST_NAME" from v$instance; select trim(instance_name) "SNAP_INSTANCE_NAME" from v$instance; select trim(version) "SNAP_RDBMS_VERSION" from v$instance;...

   Possible Reason : 1) Too many Child Cursors  2)  In memory  enabled  3)  Undersized Sga  4)  Pinned objects  in shared pool  5)  Memory Fragmentation  6) Too many hard parsing ,     Possible Solution :  1) Use  bind variables  2)   Set enough  Sga  Logs  To Connect  1) Tfa  2) Using customized  sql  2)  Using  srdc_db_ora4031.sql  retrieved from 2232371. References :  1) https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/improving-rwp-cursor-sharing.html#GUID-971F4652-3950-4662-82DE-713DDEED317C  2) Troubleshooting: High Version Count Issues (Doc ID 296377.1) ##################################### Logs to Collect  ##################################### Use Tfa  $TFA_HOME/bin/tfactl diagcollect -srdc ora4031 Use your  customized  sql  spool /tmp/memory_status.txt <...

  SET SERVEROUTPUT ON SET LINES 600 ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'; DECLARE     v_analyse_start_time    DATE := SYSDATE - 7;     v_analyse_end_time      DATE := SYSDATE;     v_cur_dt                DATE;     v_undo_info_ret         BOOLEAN;     v_cur_undo_mb           NUMBER;     v_undo_tbs_name         VARCHAR2(100);     v_undo_tbs_size         NUMBER;     v_undo_autoext          BOOLEAN;     v_undo_retention        NUMBER(5);     v_undo_guarantee        BOOLEAN;     v_instance_number       NUMBER;     v_undo_advisor_advice   VARCHAR2(100);     v_undo_health_ret...