Abdul Hafeez Kalsekar -- Oracle database tuning case studies

  Enter the Main Content Alert log file 60-min AWR report at the time of the error The script must be run at the time where temp usage is high / ORA-1652 is reported It's highly recommended to run the script at least 3-4 times at distinct intervals of the problem to provide a more consistent image of the problem to the support. alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'; set pagesize 9999 spool /tmp/dba_data_files.html set markup html on SELECT A.inst_id, A.tablespace_name TABLESPACE, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM gv$sort_segment A, (SELECT B.INST_ID, B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM gv$tablespace B, gv$tempfile C WHERE B.ts# = C.ts# AND c.inst_id=b.inst_id GROUP BY B.INST_ID, B.name, C.block_size ) D WHERE A.tablespace_name = D.name AND A.inst_id =D.inst_id GROUP BY a.inst_id, A.tablespace_name, D.mb_total ORDER B...

   DBA has seen ORA-00600 trace file generated every time ORA-00600 or ORA-07445 occurs which has each and every detail about this error.  Generating trace files for ORA-00600 or ORA-07445 is the default behaviour of Oracle, but the Same doesn’t happen in case of other errors. We are using ERRORSTACK to  capture  details  for all other  oracle errors and events .  By enabling error stack we are force capturing  error  details in alert log and with its trace file details . ERRORSTACK Parameters: 0 – Error stack only 1 – Error stack and function call stack (if implemented) 2 – As 1 plus the ProcessState 3 – As 2 plus the context area (all cursors and current cursor highlighted) Enable ALTER SYSTEM SET EVENT ='<ORAError> trace name errorstack level <LevelNumber>' SCOPE=spfile; e.g To set errorstack for ORA – 1652 ,Syntax will be   alter system set events ‘1652 trace name errorstack level 3’;  -- system level  alte...

 -- NAME: CELLPERFDIAG.SQL -- ------------------------------------------------------------------------ -- AUTHOR: Michael Polaski - Oracle Support Services -- ------------------------------------------------------------------------ -- PURPOSE: -- This script is intended to provide a user friendly guide to troubleshoot -- cell performance specifically to identify which cell(s) may be problematic.  -- The script will create a file called cellperfdiag_<timestamp>.out in your  -- local directory. set echo off set feedback off column timecol new_value timestamp column spool_extension new_value suffix select to_char(sysdate,'Mondd_hh24mi') timecol, '.out' spool_extension from sys.dual; column output new_value dbname select value || '_' output from v$parameter where name = 'db_name'; spool cellperfdiag_&&dbname&&timestamp&&suffix set trim on set trims on set lines 160 set long 10000 set pages 60 set verify off alter session set opt...

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...