Abdul Hafeez Kalsekar -- Oracle database tuning case studies

   This new feature in Oracle 12C is as default always on. Each TRUNCATE or DROP commands performed on a partition automatically triggers asynchronous global index maintenance. It means that you don’t need to wait for global INDEX maintenance  The column DBA_INDEXES.ORPHANED_ENTRIES shows that Oracle is aware that the index may have keys referring to dropped partitions. There is a pre-seeded daily job that tidies them up; by default, it runs every night from 22:00.  Global index maintenance is decoupled from the DROP and TRUNCATE partition maintenance operation without rendering a global index unusable. Index maintenance is done asynchronously and can be delayed to a later point-in-time.   Delay global index maintenance to off-peak hours without affecting index availability, and reduce and truncate partition and sub-partition maintenance operations faster and with fewer resources at the point in time for partition maintenance operations.   When combined wit...

   There are many  documents Online   for this  but since we  had requirement  to perform same   thought of   documenting my own    Instead of importing  table in same database its preferable to import into   dump file .  Below is the high level procedure on how the restoration works. 1) Identifies the set of tablespace that needs to be recovered for this table restore. 2) Creates temporary instance with a random unique name 3) Starts database in nomount state and performs control file restore based on until time/scn value defined. 4) Sets new destinations for the identified data files to be restored 5) Then initiates the restoration of SYSTEM,SYSAUX,UNDO  tablespaces for CDB and SYSTEM and SYSAUX and EXAMPLE tablespace  for PDB 6) Perform recovery of database until time/scn by restoring archive logs and applying them to temporary database. 7) Once done its open the Database in open read ...

-- This is not written by me and is a Oracle provided script  -- 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 l...

@ashtop session_state,event sql_id='3rtbs9vqukc71' "timestamp'2013-10-05 01:00:00'" "timestamp'2013-10-05 03:00:00'" @ashtop session_state,event,p2text,p2,p3text,p3 sql_id='3rtbs9vqukc71' "timestamp'2013-10-05 01:00:00'" "timestamp'2013-10-05 03:00:00'" @ashtop event2 1=1 sysdate-1/24/12 sysdate   ( last 5 min )  @ashtop event2,program2  1=1 sysdate-1/24/12 sysdate   ( last 5 min )  @ashtop event2,program2 sql_id='3rtbs9vqukc71'  sysdate-1/24/12 sysdate   ( last 5 min )  @ashtop trunc(sample_time, 'MI'),session,state,wait_class 1=1 sysdate-1/24/12 sysdate  @ashtop sid, username,sql_id,event,session_state,program,module "event=db file scattered read" &5min @ashtop username,sql_id,event,session_state,program  1=1 &5min @ashtop username,sql_id,event,session_state,p2text,p2,p3text,p3  1=1 &hour @ashtop username,sql_id,event,session_state,p2text,p2,p3text,p3  ...

  Kyle hailey Top sql and   Top session and Top Procedure  https://github.com/khailey-zz/ashmasters/blob/master/ash_top_procedure.sql   -- Top session  */ col name for a12 col program for a25 col CPU for 9999 col IO for 9999 col TOTAL for 99999 col WAIT for 9999 col user_id for 99999 col sid for 9999 set linesize 120 select         decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')                                                         "STATUS",         topsession.sid             "SID",         u.username  "NAME",         topsession.program                  "PROGRAM",         max(topsession.CPU)        ...

 @sqlid ndhghhdjjd  % -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. col sql_sql_text head SQL_TEXT format a150 word_wrap col sql_child_number head "CH#" for 9999 col cpu_sec_exec   FOR 999999.999 col ela_sec_exec   FOR 999999.999 col lios_per_exec  FOR 9999999999 col pios_per_exec  FOR 9999999999 prompt Show SQL text, child cursors and execution stats for SQLID &1 child &2 select  hash_value, plan_hash_value, child_number sql_child_number, sql_text sql_sql_text from  v$sql  where  sql_id = ('&1') and child_number like '&2' order by sql_id, hash_value, child_number / select  child_number sql_child_number, plan_hash_value plan_hash, parse_calls parses, loads h_parses, executions, fetches, rows_processed,   rows_processed/nullif(fetches...

  e – sample event names (this is always done automatically) 1 – sample P1 values 2 – sample P2 values 3 – sample P3 values s – sample SEQ# values And lets run waitprof now, on SID 142, gather only events waited on and take 100000 samples: @waitprof noprint 142 e 100000 @waitprof noprint 142 e1 100000 @waitprof print 142 e3 100000 @waitprof print 142 e123 100000 -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -------------------------------------------------------------------------------- -- -- File name:   waitprof.sql ( Session Wait Profiler ) -- Purpose:     Sample V$SESSION_WAIT at high frequency and show resulting  --              session wait event and parameter profile by session -- -- Author:      Tanel Poder -- Copyright:   (c) http://www.tanelpoder.com...

 @swc program2||event2 1=1 @help swc.sql COL obj           FOR A30 COL objt          FOR A50 COL wait_chain    FOR A300 WORD_WRAP COL distinct_sids FOR 9999 HEAD "#Blkrs" COL "%This"       FOR A6 PROMPT PROMPT -- Display Session Wait Chain Signatures script v0.1 BETA by Tanel Poder ( https://tanelpoder.com ) WITH      bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)   , sq AS (     SELECT          REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')                ||CASE WHEN CONNECT_BY_ISLEAF = 1 AND ses.blocking_session IS NOT NULL                        THEN ' -> [idle blocker '||ses.blocking_instance||','||ses.blocking_session||' ('||ses.program||')]' ELSE NULL              ...

 @ash_wait_chains event2 1=1 sysdate-1 sysdate  @ash_wait_chains event2 1=1 sysdate-1/24  sysdate @ash_wait_chains program2||event2 1=1 sysdate-1 sysdate @ash_wait_chains sql_opname||':'||event2 1=1 sysdate-1/24 sysdate @ash_wait_chains event2 sql_id='3rtbs9vqukc71' "timestamp'2013-10-05 01:00:00'" "timestamp'2013-10-05 03:00:00'"   -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -------------------------------------------------------------------------------- -- -- File name:   ash_wait_chains.sql (v0.8) -- Purpose:     Display ASH wait chains (multi-session wait signature, a session --              waiting for another session etc.) --               -- Author:      Tanel Poder -- Copyright:   (c) http://blog.tane...

 @dashtop session_state,event 1=1 "TIMESTAMP'2013-09-09 21:00:00'" "TIMESTAMP'2013-09-09 22:00:00'" @dashtop.sql session_state, event ,p1text ,p1  top_level_sql_id='xxxxx' sysdate-1/24 sysdate @dashtop.sql top_level_sql_id,sql_exec_id "session_id = 997 and session_serial# = 58505" "timestamp '2021-02-26 05:36:37'" "timestamp '2021-02-26 08:03:09'" @dashtop event2,wait_class,blocking_session_status ,blocking_session 1=1 sysdate-1/24 sysdate  @dashtop event2,wait_class,blocking_session_status ,blocking_session wait_class=concurrency sysdate-1/24 sysdate  -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. -------------------------------------------------------------------------------- -- @dashtop session_state,event 1=1 "TIMESTAMP'2013-09-09 21:00:00'...