EXADATA non rolling Patching

QFSD (Quarterly Full Stack Download)

Patch number would be - year month date of patch application 160709

This patch includes 

- BP (Bundle Patch) --- For GI and RDBMS home in Compute node - Use Opatch utility to apply the patch.Opatch version should be same across all the compute nodes(GI/RDBMS). While running catbundle.sql you need a downtime . ./datapatch.sh will login to database and run the neccesary scripts like catbundle.sql etc. 

- YUM Update (ISO Patching) - For Compute node- Firmware update - Use dbnodeupdate.sh to apply this patch. While applying this patch compute node will reboot twice , or 5 times it depends . Complete reboot will take may be an hour. Some error may encounter after the last reboot, you should ignore it as it was due to oracle coding issue. Patching will be done for all the compute nodes first, then proceed with cell server patching

- OJVM  Patch --  For compute node, this patch is required only if customer using java libraries.

- IB Switch Patch -- For infiniband switch. This patch will be applied  using patchmanager utility, but this utility must be invoked from ILOM console. You will be login to ILOM console using spsh utility.

-Cell patch   --  For Cell server.  This patch will be applied  using patchmanager utility.
You have to run exacheck utility to verify the cell status and for any hardware issues. if there is any issues you have to fix it prior to patching.  
As a prerequisite for rolling patching,  asm powerlimit should be set to min 4, disk repair time should be minimum of 8 hours (by default 3.5 hours) and ADVM should be set to GI version.  You will be invoking cell patching from compute node itself. In that compute node cat/etc/cell_groups file should have cell server entries of each cell nodes, else it won't apply cell patch for all the cell servers. Reboot all the cell servers one by one to verify the cell server bootup issue, also run patchmgr precheck utility. then apply the patch using patchmgr utlity, this will pickup the cell server ips from cell_groups file and apply the cell patch to cell server one by one. oracle will quot 4 hours for each cell server patch, by realistic patching can be completed in 3.5 hours on all the cell servers. It again depends on ASM resync operation.  After the patching flash cache will be dropped, so you have to recreate it else you will face performance issues. 

PDU patch - For Power distribution unit. How many PDU s will have?  This patch will be released once a year. Yearly once you can see this patch as part of QFSD. This patch will be applied using patchmgr.

High level steps
===============

Cell sever patch
------------------
Check for any critical alert and status of disks using DCLI
clear the cell critical alert if any
Check the ssh equivalence for both compute and cell node
Check for Compute node and cell node uptime,If the uptime is more than 128 days it is recommended to reboot
Reboot the cell and compute node
Make all griddisk inactive and shutdown cell services
unzip qfsd patches
Apply the cell patch 
Check the imageinfo once cell Patch completed and activate the griddisks


Bundle Patch(BP)  Grid and RDBMS patching
---------------- -------------------------
Logon to Each compute node and Bundle patch can be applied parallely
Apply JDBC patch on GI only


ISO Patching(Compute node Pathcing)
-----------------------------------
Make sure that all NFS and ZFS file system are unmounted and comment out in FStab
Do the precheck and notifies for any conflicating RPM's which needs to be removed
Make the file system backup and reboot the node and update the image
Bringup the clusterware stack and enable the CRS

IB5 critical work around
--------------------------
IB Switches will not be upgraded in all the QFSD release,It will be only upgraded if your switch is below specific version and some critical fix will be given
As a root user locate the IBS using below command
ibswitches
then ssh to switch
take the spsh console and run the below command



Technical steps
===============


GI_HOME:/oracle_crs/product/11.2.0.4/crs_1
ORACLE_HOME: /oracle/product/11.2.0.4/db_1

Patch location: /oracle/depot/JULY2016-QFSDP/

drwxr-xr-x 2 oracle dba 4096 Sep 16 15:17 16486998
drwxr-xr-x 2 oracle dba 4096 Sep 16 15:18 23727132
drwxr-xr-x 3 oracle dba 4096 Sep 20 09:18 23274210


Cell Patching
==============
Check for any critical alert and status of disks using DCLI
------------------------------------------------------------

dcli -g /root/cell_group -l root "cellcli -e list alerthistory where endTime=null and alertShortName=Hardware and alertType=stateful and severity=critical" ---> 

Please check theses 3 steps before you stop cluster
dcli -g /root/cell_group -l root "cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome"
dcli -g /root/cell_group -l root "cellcli -e list cell attributes cellsrvStatus,msStatus,rsStatus detail"

clear the cell critical alert if any
-------------------------------------

dcli -g /root/cell_group -l root "cellcli -e DROP ALERTHISTORY ALL"

Check the ssh equivalence for both compute and cell node
--------------------------------------------------------

dcli -g /root/cell_group -l root "hostname -i"
dcli -g /root/dbs_group -l root "hostname -i"


Check for Compute node and cell node uptime,If the uptime is more than 128 days it is recommended to reboot
--------------------------------------------------------------------------------------------------------------
dcli -g /root/cell_group -l root "uptime"
dcli -g /root/dbs_group -l root "uptime"

Stop and disable the CRS
-------------------------
dcli -g /root/dbs_group -l root "/oracle_crs/product/11.2.0.4/crs_1/bin/crsctl check crs"
dcli -g /root/dbs_group -l root "/oracle_crs/product/11.2.0.4/crs_1/bin/crsctl stop crs -f"
dcli -g /root/dbs_group -l root "/oracle_crs/product/11.2.0.4/crs_1/bin/crsctl disable crs"

Check for Compute node and cell node uptime,If the uptime is more than 128 days it is recommended to reboot
--------------------------------------------------------------------------------------------------------------
dcli -g /root/cell_group -l root "uptime"
dcli -g /root/dbs_group -l root "uptime"

Reboot the cell and compute node
---------------------------------

dcli -g /root/dbs_group -l root "shutdown -F -r now"
dcli -g /root/cell_group -l root "shutdown -F -r now"

Make all griddisk inactive and shutdown cell services
------------------------------------------------------
dcli -g  /root/cell_group -l root "cellcli -e alter griddisk all inactive"
dcli -g /root/cell_group -l root "cellcli -e alter cell shutdown services all"

unzip qfsd patches

Apply the cell patch using the below commands
-------------------------------------------------

./patchmgr -cells /root/cell_group -reset_force
./patchmgr -cells /root/cell_group -cleanup
./patchmgr -cells /root/cell_group -patch_check_prereq
./patchmgr -cells /root/cell_group -patch

Check the imageinfo once cell Patch completed
-------------------------------------------------
dcli -g /root/cell_group -l root imageinfo
dcli -g /root/cell_group -l root "cellcli -e alter griddisk all active"

Bundle Patch(BP)
=============
Grid and RDBMS patching
========================
Logon to Each compute node and Bundle patch can be applied parallely

/oracle_crs/product/11.2.0.4/crs_1
/oracle/product/11.2.0.4/db_1
GI_HOME:/oracle_crs/product/11.2.0.4/crs_1
ORACLE_HOME: /oracle/product/11.2.0.4/db_1

% /oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch version
% /oracle/product/11.2.0.4/db_1/OPatch/opatch version

/oracle/product/11.2.0.4/db_1/OPatch/opatch lspatches -oh /oracle/product/11.2.0.4/db_1
% /oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch lsinventory -detail -oh /oracle_crs/product/11.2.0.4/crs_1
% /oracle/product/11.2.0.4/db_1/OPatch/opatch lsinventory -detail -oh /oracle/product/11.2.0.4/db_1

% unzip p23274515_112040_Linux-x86-64.zip
# chown -R oracle:oinstall /u01/app/oracle/patches/23274515

export ORACLE_HOME=/oracle_crs/product/11.2.0.4/crs_1

/oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/23274515/23061511
/oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/23274515/23054319
/oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/23274515/22502505

/oracle/product/11.2.0.4/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/23274515/23061511
/oracle/product/11.2.0.4/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/23274515/23054319/custom/server/23054319



# /u01/app/11.2.0.4/grid/crs/install/rootcrs.pl -unlock

/oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch napply -oh /oracle_crs/product/11.2.0.4/crs_1 -local <UNZIPPED_PATCH_LOCATION>/23274515/23061511
/oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch napply -oh /oracle_crs/product/11.2.0.4/crs_1 -local <UNZIPPED_PATCH_LOCATION>/23274515/23054319
/oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch napply -oh /oracle_crs/product/11.2.0.4/crs_1 -local <UNZIPPED_PATCH_LOCATION>/23274515/22502505

Apply JDBC patch on GI only
$ cd <PATCH_TOP_DIR>/23727132

/oracle_crs/product/11.2.0.4/crs_1/OPatch/opatch apply -local



export ORACLE_HOME=/oracle/product/11.2.0.4/db_1

/u01/patches/23274515/23054319/custom/server/23054319/custom/scripts/prepatch.sh -dbhome /oracle/product/11.2.0.4/db_1
/u01/app/oracle/product/11.2.0.4/db_2/OPatch/opatch napply -oh /oracle/product/11.2.0.4/db_1 -local <UNZIPPED_PATCH_LOCATION>/23274515/23061511
/u01/app/oracle/product/11.2.0.4/db_2/OPatch/opatch napply -oh /oracle/product/11.2.0.4/db_1 -local <UNZIPPED_PATCH_LOCATION>/23274515/23054319/custom/server/23054319
/u01/patches/23274515/23054319/custom/server/23054319/custom/scripts/postpatch.sh -dbhome /oracle/product/11.2.0.4/db_1

Make sure that Cell Patching is completed and griddisk is made active before running the below command
======================================================================================================
/oracle_crs/product/11.2.0.4/crs_1/rdbms/install/rootadd_rdbms.sh
/oracle_crs/product/11.2.0.4/crs_1/crs/install/rootcrs.pl –patch


ISO Patching(Compute node Pathcing)
===================================
Stop the clusterware
dcli -g /root/dbs_group -l root "/oracle_crs/product/11.2.0.4/crs_1/bin/crsctl check crs"
dcli -g /root/dbs_group -l root "/oracle_crs/product/11.2.0.4/crs_1/bin/crsctl stop crs -f"

Make sure that all NFS and ZFS file system are unmounted and comment out in FStab

Unzip p16486998_121232_Linux-x86-64.zip and copy the patch to all compute nodes

below command will do the precheck and notifies for any conflicating RPM's which needs to be removed

./dbnodeupdate.sh -u -l /ora01/patches/23274210/Infrastructure/12.1.2.3.2/ExadataDatabaseServer_OL6/p23564643_121232_Linux-x86-64.zip -v -N 

Below command will make the file system backup and reboot the node and update the image
./dbnodeupdate.sh -u -l /ora01/patches/23274210/Infrastructure/12.1.2.3.2/ExadataDatabaseServer_OL6/p23564643_121232_Linux-x86-64.zip

Below command will bringup the clusterware stack and enable the CRS
./dbnodeupdate.sh -c

IB5 critical work around
=========================
IB Switches will not be upgraded in all the QFSD release,It will be only upgraded if your switch is below specific version and some critical fix will be given

As a root user locate the IBS using below command
ibswitches
then ssh to switch
take the spsh console and run the below command

-> set /SP/services/http secureredirect=disabled
-> set /SP/services/http servicestate=disabled
-> set /SP/services/https servicestate=disabled
-> exit

Optimizer queries to fetch the execution paln

Optimizer Known Records during Execution
*********************************************************************************
col savtime for a40
col object_name for a40
select savtime,owner,object_name,rowcnt,blkcnt From sys.WRI$_OPTSTAT_TAB_HISTORY w, dba_objects o
where o.owner='&owner' and o.object_name='&Obj_name' And o.object_id = w.obj#
order by o.owner, o.object_name,w.savtime;


*********************************************************************************
Blocked Session
-------------------
set lines 200 pages 1010
spool block_session.log append
COLUMN sess format A15
col BLOCK for 999
col lmode for 999
col CTIME for 9999
col REQUEST for 999
col USERNAME for a9
col STATUS for a8
col ID1 for 999999999
col ID2 for 9999999
col SERIAL# for 99999
col event for a20
select systimestamp from dual;
SELECT a.inst_id,substr(DECODE(request,0,'Holder: ','Waiter: ')||a.sid,1,15) sess, b.serial#, a.type, a.id1, a.id2,
        a.lmode, a.request,a.block, a.ctime, b.username, b.status, b.sql_id, b.event, b.ROW_WAIT_OBJ# from    gv$session b,
        (select distinct b.*
        from    gv$lock a,
                gv$lock b
        where   a.id1 = b.id1
        and     a.id2 = b.id2
        and     a.request > 0) a
where   a.sid = b.sid
and     a.inst_id = b.inst_id
order by a.id1, a.id2, a.block desc, ctime
/
select * from gv$access where object='PL_IMAGE_ITEM_GROUP';
SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE'
SELECT * FROM V$OPEN_CURSOR WHERE SID = --
SELECT s.inst_id, O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
 S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
 FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, gV$SESSION S,
 V$PROCESS P, V$SQL SQ
 WHERE L.OBJECT_ID = O.OBJECT_ID
 AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
 AND S.SQL_ADDRESS = SQ.ADDRESS;
SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;


ActiveEvents
*********************************************************************************
select to_char(sysdate,'DD-MON-YY HH24:MI:SS') dt, inst_id, count(1) tot, count(case when username is null then 1 end) bg,
count(case when username is not null then 1 end) wbg,
count(case when status='ACTIVE' and username is not null then 1 end) act_user from gv$session group by inst_id
/
---------------------------------------------------
set lines 165 pages 1000 time on
spool wait_event07NovEvening.log append
set heading on
col inst for 99
col spid for a11
col username for a16
col osuser for a14
col event for a30 trunc
col inst for 99999
col sid for 99999
col et for 9999999
col dt for a20
col system_date for a18
col module for a18 trunc
col blocker for 999999
col bs for 99999
col Bi for 99
col rr for 99
col obj for 9999999
col wt for 99
select systimestamp from dual;
select s.inst_id as inst,s.sid as sid,p.spid as spid,s.username,s.status,s.sql_id,s.event,s.module,s.last_call_et as "et(s)",s.blocking_session bs,s.
row_wait_obj# as obj,BLOCKING_INSTANCE as Bi,ROW_WAIT_ROW# as rr, s.wait_time WT
from gv$session s,gv$process p
where wait_class !='Idle'and s.paddr = p.addr and s.inst_id=p.inst_id
order by inst,event
/
---------------------------------------------
select sql_id,event,count(*) from gv$session where event ='gc cr request' group by sql_id,event order by 3
--------------------------------------------------
set lines 1000
column module format a40
column event format a40
select inst_id, sid, serial#, username, status, sql_id, module, to_char(logon_time,'DD-MM HH24:MI') logon, (sysdate-logon_time)*24*60*60 diff, last_call_et lce, event from gv$session where wait_class !='Idle' and username is not null and username not like 'SYS' AND STATUS='ACTIVE'
---------------------------------------------------
select program,module,event,sql_id,prev_sql_id,status from gv$session where STATUS='ACTIVE' AND TYPE !='BACKGROUND';
----------------------------------------------------
select module,count(1) from gv$session group by module order by 2 desc;
oracle@HBFCPRDDB2:/home/oracle/amit$ cat lo.sql
set linesize 132
col target for a60
col "SID" for 99999
SELECT SID,  target || opname target , TOTALWORK, SOFAR, TIME_REMAINING, ELAPSED_SECONDS FROM V$SESSION_LONGOPS
where time_remaining > 0
order by time_remaining
/
-----------------------------------------------------
set lines 250 pages 9999
select inst_id,event,count(1) from gv$session where wait_class != 'Idle' group by inst_id,event order by 1,2 desc;
---------------------------------------------------
select inst_id,status,count(1) from gv$session where wait_class !='IDLE' and username is not null group by inst_id,status order by 1,2
----------------------------------------------------
select inst_id,count(*) from gv$session where status='ACTIVE' group by inst_id;
------------------------------------------------------
select sid,serial#,username,osuser,event,sql_id,p1,p2,p3,last_call_et as et from v$session where username='SYS'
---------------------

STALE STATS
************
select table_name, stale_stats, last_analyzed from dba_tab_statistics where owner='&OWNER'order by last_analyzed desc

Plan
*********************************************************************************
set lines 100 pages 2000
select * from table(dbms_xplan.display_cursor('&sid'))
select * from table(dbms_xplan.display_cursor(null,null,'iostats last +cost'));
select * from table(dbms_xplan.display_cursor('&sql_id',&child))
select * from table(dbms_xplan.display_awr('&sql_id'))
select * from table(dbms_xplan.display_awr('&sql_id',&plan_h_v));
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_no, 'ALLSTATS LAST +COST +BYTES')); (/*+ gather_plan_statistics */)
select * from table(dbms_xplan.display_awr('&sql_id',NULL,NULL,'ADVANCED'));
explain plan for select * from sh.sales where cust_id=:b1 and promo_id=:b2;
select * from table(dbms_xplan.display) ;

select t.*
from v$sql s,
table(dbms_xplan.display_cursor(s.sql_id, s.child_number, '+peeked_binds')) t
where sql_id = '&1'
/
*********************************************************************************
-- Show the Plan Hash Values for a Given SQLID Over a Given Period

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/
-----------------------
Audit
*********************************************************************************
select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;
select * from dba_audit_trail;

Hard Parsing
*********************************************************************************
set line 200 pagesize 5000
col inst_id for 99
col sql_text for a100
col first_load_time for a20
col PARSING_SCHEMA_NAME for a14
SELECT inst_id,sql_id,first_load_time,PARSING_SCHEMA_NAME, sql_text
   FROM gv$sql
  WHERE parsing_schema_name not in ('XDB', 'SYS', 'DBSNMP', 'FLOWS_030000', 'EXFSYS', 'SPOTLIGHT')
and first_load_time > to_char(sysdate-1/(24*60), 'YYYY-MM-DD/HH24:MI:SS')
/
---------------
set line 200 pagesize 5000
col inst_id for 99
col sql_text for a100
col first_load_time for a20
col PARSING_SCHEMA_NAME for a14
SELECT inst_id,sql_id,first_load_time,PARSING_SCHEMA_NAME, sql_text
   FROM gv$sql
  WHERE parsing_schema_name not in ('XDB', 'SYS', 'DBSNMP', 'FLOWS_030000', 'EXFSYS', 'SPOTLIGHT')
and first_load_time > to_char(sysdate-1/(24*60), 'YYYY-MM-DD/HH24:MI:SS')
/

Hidden Parameters
*********************************************************************************
col KSPPINM for a30
col ksppstvl for a30
SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b WHERE a.indx = b.indx and a.ksppinm like '%&name%' ORDER BY a.ksppinm
/
--------------------

Bind Values
*********************************************************************************
select DISTINCT 'VAR ' || SUBSTR(NAME,2) || CASE WHEN DATATYPE_STRING='DATE' THEN ' VARCHAR2(20)' ELSE ' ' || DATATYPE_STRING END || ';' || CHR(10) ||'EXEC ' || NAME || ':=' || CASE WHEN DATATYPE_STRING='NUMBER' then VALUE_STRING ELSE '''' || VALUE_STRING || ''';' END "DECLARE_VAR"
from v$sql_bind_capture where sql_id='&sqlid';
select sn.END_INTERVAL_TIME, sb.NAME, sb.VALUE_STRING from DBA_HIST_SQLBIND sb, DBA_HIST_SNAPSHOT sn where sb.sql_id='4ywfmsqh97pdn' and sb.WAS_CAPTURED='YES' and
sn.snap_id=sb.snap_id order by sb.snap_id, sb.NAME;
--------------------

SQL HISTORY
*********************************************************************************
All SQL hist
----------------
select sql_id, sum(t.elapsed_time_delta/1000)/sum(t.executions_delta) " Executed(in ms)"
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterdays stats
and    executions_delta>0
group  by sql_id
/
-------------------------
set lines 300 pages 50000 verify off
select inst_id,sql_id,plan_hash_value, child_number, executions, buffer_gets, round(buffer_gets/decode(executions,0,1,executions)) gets_exec, disk_reads, elapsed_time/executions/1000 as etpe_ms, round(rows_processed/decode(executions,0,1,executions)) rows_exec from gv$sql where sql_id='&sql_id';
select inst_id,sql_id,plan_hash_value, child_number, executions, buffer_gets, round(buffer_gets/decode(executions,0,1,executions)) gets_exec, disk_reads,
elapsed_time/1000/1000 as "et in s", round(rows_processed/decode(executions,0,1,executions)) rows_exec from gv$sql where sql_id='&sql_id';
-------------------------
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 avg_etime_ms,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
-------------------------
--Machine
--------------------
set lines 200 pages 200
col begin_interval_time for a30
--break on plan_hash_value on startup_time skip 1
select snap_id, node, begin_interval_time, sql_id, plan_hash_value, execs, avg_etime, avg_lio,m,
round(execs*avg_etime/60,0) total_time from (
select ss.snap_id, ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(executions_delta,0) execs,
round((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000,3) avg_etime,
round((buffer_gets_delta/decode(nvl(executions_delta,0),0,1,executions_delta))) avg_lio,
S.module as m
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where s.sql_id = nvl('&sql_id','&sql_id')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 --and plan_hash_value=3099046451
order by 1, 2, 3)
order by 2,1,3
/
---------------------------------------------------------
select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, q.SORTS_TOTAL
, q.SORTS_DELTA
, q.PARSING_SCHEMA_NAME
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) ela_exec_in_ms
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&SQLID')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
group by s.snap_id
, q.sql_id
, q.plan_hash_value
, q.SORTS_TOTAL
, q.SORTS_DELTA
, q.PARSING_SCHEMA_NAME
order by s.snap_id, q.sql_id, q.plan_hash_value;
---------------------------------------------------------
col BEGIN_INTERVAL_TIME for a30
select snap.begin_interval_time, snap.instance_number, dhs.sql_id, dhs.plan_hash_value, dhs.elapsed_time_delta, dhs.cpu_time_delta, dhs.executions_delta,
trunc(dhs.elapsed_time_delta/dhs.executions_delta/1000,2) elapsed_per_exec_ms
from DBA_HIST_SQLSTAT dhs, dba_hist_snapshot snap
where dhs.snap_id = snap.snap_id
and dhs.instance_number = snap.instance_number
and dhs.sql_id = '&sql_ID'
and dhs.executions_delta > 0
order by snap.begin_interval_time desc;
----------------------------------------------------------
column sql_text for a40 wrap
col module for a22
select sql_id,plan_hash_value,child_number, sql_text, executions, buffer_gets, buffer_gets/executions as buffer_exec,rows_processed/executions as rows_exec,
module
from gv$sql
where sql_id='&sql_id'
/
-----------------------
Average
-------
select ss.snap_id, ss.instance_number node, to_char(begin_interval_time,'DD-MON-YY HH24MI'), s.sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 "avg_etime_(ms)",
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio,
(iowait_delta/decode(nvl(iowait_delta,0),0,1,executions_delta)) avg_iowait,
rows_processed_delta
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
------------------------
set long 90000
set lines 160 pages 2020
select sql_id, sql_fulltext from   v$sqlarea where  sql_id = '&entersqlid';
------------------------
select sql_fulltext from v$sqlarea where sql_id='&sql_id';
------------------------
select sql_id,sql_text from v$sqlarea where sql_text like '%&sqltext%';
------------------------
select inst_id, child_number, plan_hash_value, executions, buffer_gets, buffer_gets/executions as "buff_exe", rows_processed from gv$sql where sql_id='&sql_id';
------------------------
select s.inst_id,s.sid,s.sql_id,s.event,s.program,s.module,sa.executions,sa.buffer_gets/sa.executions gets,sa.ELAPSED_TIME/sa.executions elsped,sa.cluster_wait_time
from gv$session s,v$sqlarea sa where sa.sql_id=s.sql_id and s.status='ACTIVE' and s.type<>'BACKGROUND' and executions>0;
------------------------
select s.sid,s.sql_id,s.program,s.module,sa.executions,sa.buffer_gets/sa.executions gets,sa.ELAPSED_TIME/sa.executions elsped,sa.sql_text
from v$session s,v$sqlarea sa where sa.sql_id=s.sql_id
and s.sql_id='&sqlid';
-----------------------
select * from (select sql_id,sql_text,round(buffer_gets/decode(executions,0,1,executions)) gpe,executions,module,elapsed_time/1000000 ela
from gv$sqlarea order by 3 desc) where rownum <=10;

Table Details
*********************************************************************************
select owner,table_name,num_rows from dba_tables where table_name = '&name';
-----------------------
set lines 200
set pages 10000
set verify off;
col table_name for a30
select owner, table_name, partitioned, num_rows, blocks, last_analyzed,
global_stats, round(sample_size/decode (num_rows, 0, 1, null, 1, num_rows)*100,0) estimate_percent , partitioned, temporary
from dba_tables
where table_name = upper('&1');
set verify on;
-----------------------

*********************************************************************************
Index Details
*********************************************************************************
break on index_name skip 1
column column_name format a30
column column_expression for a30
set lines 120 pages 50000
select c.index_name,
        c.column_name "COLUMN_NAME", c.column_position,
        a.column_expression
from    dba_ind_columns c,
        dba_ind_expressions a
where   c.table_owner='&SCHEMA'
and     c.table_name='&table_name'
and     a.index_name(+) = c.index_name
and     a.table_owner(+) = c.table_owner
and     a.index_owner(+) = c.index_owner
and     a.table_name(+) = c.table_name
and     a.column_position(+) = c.column_position
order by 1,3
/
----------------
select owner, index_name, blevel, leaf_blocks, clustering_factor, degree from dba_indexes where owner='&owner' and table_name='&table' order by 1,2;
oracle@HBFCPRDDB2:/home/oracle/amit$ cat index _columns.sql
column column_name for a30
break on index_name skip 1 on report
select index_name, substr(column_name,1,30) column_name, COLUMN_POSITION from dba_ind_columns where TABLE_OWNER='&owner' and TABLE_NAME='&table' order by 1,3
/
----------------
select owner, Table_name, index_name, BLEVEL, LEAF_BLOCKS, last_analyzed, CLUSTERING_FACTOR, NUM_ROWS, PARTITIONED from dba_indexes where index_name='&ind_name';
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NUM_DISTINCT,NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, HISTOGRAM from dba_tab_columns where column_name=''
------------------
*********************************************************************************
Object Details
*********************************************************************************
set lines 399 pages 1000
col owner for a20
col object_name for a30
select owner,object_name,object_type,status,object_id ,data_object_id from dba_objects where data_object_id=&obj
/
----------------
set lines 200 pages 1000
col object_name for a30
select owner,object_id,data_object_id,object_name,object_type,status from dba_objects where object_name='&object_name'
/
*********************************************************************************
REDO SIZE
*********************************************************************************
set lines 300
col module format a30
col redo for 9999999999999
select * from (
SELECT s.inst_id,s.sid, module,username,osuser, sql_hash_value, value redo, to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss') lt
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND name = 'redo size'
ORDER BY redo desc
)where rownum<10
/

execute dbms_stats.gather_table_stats(ownname =>'SBIH',tabname=>'TBL_TRANSACTIONS',estimate_percent=>10,
method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',cascade=>true,degree=>16);

alter session set "_use_nosegment_indexes"=true;
CREATE INDEX "SYSADM"."PS_IDX01" ON "SYSADM"."PS_CT_LSTIMEDTLS"("CT_LS_ID") TABLESPACE "PSINDEX" nosegment;

select dbms_metadata.get_ddl('TABLE','&TAB_NAME','&SCHEMA') from dual;
select dbms_metadata.get_ddl('INDEX','&INDEX_NAME','&SCHEMA') from dual;

select id, parent_id, operation
from (
select level lvl, id, parent_id, lpad(' ',level) || operation || ' ' || options
|| ' ' || object_name as operation
from plan_table
start with id = 0
connect by prior id = parent_id
)
order by lvl desc, id;


exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'CUSTDOMAIN',tabname => 'FTIME' , estimate_percent => 100, block_sample => TRUE,
degree => 8, method_opt => 'FOR ALL COLUMNS SIZE 1', granularity => 'ALL', cascade => TRUE);
exec dbms_stats.gather_schema_stats( ownname => 'APR_LND',estimate_percent => dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => 8,no_invalidate=>false , cascade => TRUE);


set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 avg_etime_ms,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, MODULE
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where s.parsing_schema_name='&USER'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and ss.begin_interval_time like '%30-AUG-16%'
order by 7 desc
/