Oracle Database Administrator

Thursday, December 31, 2020

Performing Database health checks

Performing Database health checks, when there is an issue reported by Application users.

1. Check the Database details
2. Monitor the consumption of resources
3. Check the Alert Log
4. Check Listener log
5. Check Filesystem space Usage
6. Generate AWR Report
7. Generate ADDM Report
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database

1. Check the Database details :

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

For RAC:
-------
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

2. Monitor the consumption of resources :

select * from v$resource_limit where resource_name in ('processes','sessions');

The v$session views shows current sessions (which change rapidly),
while the v$resource_limit shows the current and maximum global resource utilization for some system resources.

3. Check the Alert Log :

$locate alert_<ORACLE_SID>
--- OR ---
UNIX/Linux command to locate the alert log file
-----------------------------------------------
$ find / -name 'alert_*.log' 2> /dev/null
vi <alert_log_location_of_the_above_output>
shift+g
?ORA- ---> press enter key
press 'n' to check backwards/up side and 'N' for forward/down side search.
:q! --and press enter, for exiting vi editor

11G
===
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col NAME for a15
col VALUE for a60
select name, value from v$diag_info where name = 'Diag Trace';
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/diag/$ORACLE_SID/trace directory

Before 11G
==========
$ sqlplus "/as sysdba"
set pages 9999 lines 300
show parameter BACKGROUND_DUMP_DEST;
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory

4. Check Listener log :

$locate listener.log
--- OR ---
UNIX/Linux command to locate the listener log file
--------------------------------------------------
$ find / -name 'listener.log' 2> /dev/null
vi <listener.log>
shift+g
?TNS- ---> press enter key
press 'n' to check backwords and 'N' for forword search.
AND
shift+g
?error ---> press enter key
press 'n' to check backwords and 'N' for forword search.
:q! --and press enter, for exiting vi editor
--- OR ---
$lsnrctl status
from the output you can get the listener log location (see the value for "Listener Log File" in the output).

5. Check Filesystem space Usage :

df -h (Linux / UNIX)
df -g (AIX)

6. Generate AWR Report :

Generate AWR report for current and before to compare
SQL> @?/rdbms/admin/awrrpt.sql (For RAC, @?/rdbms/admin/awrrpti.sql - for each instance)
If Required,
SQL> @?/rdbms/admin/awrddrpt.sql ----> Produces Workload Repository Compare Periods Report

7. Generate ADDM Report :

Generate ADDM report for current and before to compare.
ADDM report provides Findings and Recommendations to fix the issue.
SQL> @?/rdbms/admin/addmrpt.sql (For RAC, @?/rdbms/admin/addmrpti.sql - for each instance)

8. Finding Locks,Blocker Session and Waiting sessions in a oracle database :

Select * from v$lock;
Select * from gv_$lock; (For RAC)

A fast way to check blocking/waiting situations
-----------------------------------------------

SELECT * FROM v$lock WHERE block > 0 OR request > 0;

set pages 50000 lines 32767

select object_name,s.inst_id,s.sid,s.serial#,p.spid,s.osuser,s.program,s.server,s.machine,s.status from gv$locked_object l,gv$session s,gv$process p,dba_objects o where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

set pages 50000 lines 32767
col OBJECT_NAME for a40
col USERNAME for a10
col LOCKED_MODE for a15
col OBJECT_OWNER for a15
col OS_USER_NAME for a12

SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, '(oracle)') AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',
b.locked_mode) locked_mode,b.os_user_name FROM dba_objects a, gv$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;

Blocker Session and Waiting sessions:

column Username format A15

column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'

column Request format 990 heading 'REQ'

column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup

SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;

To find waiters:

set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;

Blocking details:

set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;

set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20

select (select username from v$session where sid = a.sid ) blocker,a.sid, 'is blocking ',(select username from v$session where sid =b.sid) blockee,b.sid from v$lock a, v$lock b where a.block =1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

set pages 50000 lines 32767
select blocking_session, sid, serial#, wait_class,seconds_in_wait, username, osuser, program, logon_time from v$session where blocking_session is not NULL order by 1;

To Find out Blocking Sessions & Session Details

WHICH LOCK MODES ARE REQUIRED FOR WHICH TABLE ACTION?

==========================================================
THE FOLLOWING TABLE DESCRIBES WHAT LOCK MODES ON DML ENQUEUES ARE ACTUALLY
GOTTEN FOR WHICH TABLE OPERATIONS IN A STANDARD ORACLE INSTALLATION.
OPERATION LOCK MODE LMODE LOCK DESCRIPTION
------------------------- --------- ----- ----------------
SELECT NULL 1 NULL
SELECT FOR UPDATE SS 2 SUB SHARE
INSERT SX 3 SUB EXCLUSIVE
UPDATE SX 3 SUB EXCLUSIVE
DELETE SX 3 SUB EXCLUSIVE
LOCK FOR UPDATE SS 2 SUB SHARE
LOCK SHARE S 4 SHARE
LOCK EXCLUSIVE X 6 EXCLUSIVE
LOCK ROW SHARE SS 2 SUB SHARE
LOCK ROW EXCLUSIVE SX 3 SUB EXCLUSIVE
LOCK SHARE ROW EXCLUSIVE SSX 5 SHARE/SUB EXCLUSIVE
ALTER TABLE X 6 EXCLUSIVE
DROP TABLE X 6 EXCLUSIVE
CREATE INDEX S 4 SHARE
DROP INDEX X 6 EXCLUSIVE
TRUNCATE TABLE X 6 EXCLUSIVE
-----------------------------------------------------------

HOW COMPATIBILITY OF LOCKS WORK

=============================================================================

THE COMPATIBILITY OF LOCK MODES ARE NORMALLY REPRESENTED BY FOLLOWING MATRIX:

       NULL    SS      SX      S       SSX     X

-----------------------------------------------------------------------------

 NULL  YES     YES     YES     YES     YES     YES

   SS  YES     YES     YES     YES     YES     NO

   SX  YES     YES     YES     NO      NO      NO

    S  YES     YES     NO      YES     NO      NO

  SSX  YES     YES     NO      NO      NO      NO

    X  YES     NO      NO      NO      NO      NO

Which views can be used to detect locking problems?

A number of Oracle views permits to detect locking problems.

V$SESSION_WAIT When a session is waiting on a resource, it can be found waiting on the enqueue wait event,

e.g. SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';
- SID identifier of session holding the lock
- P1, P2, P3 determine the resource when event = 'enqueue'
- SECONDS_IN_WAIT gives how long the wait did occurs

V$SESSION session information and row locking information

- SID, SERIAL# identifier of the session
- LOCKWAIT address of the lock waiting, otherwise null
- ROW_WAIT_OBJ# object identified of the object we are waiting on
(object_id of dba_objects)
- ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# file_id , block_id and
row location within block of the locked row

V$LOCK list of all the locks in the system

- SID identifier of session holding the lock
- TYPE, ID1 and ID2 determine the resource
- LMODE and REQUEST indicate which queue the session is waiting on, as follows:
LMODE > 0, REQUEST = 0 owner
LMODE = 0, REQUEST > 0 acquirer
LMODE > 0, REQUEST > 0 converter
- CTIME time since current mode was converted (see Note 223146.1)
- BLOCK are we blocking another lock
BLOCK = 0 non blocking
BLOCK = 1 blocking others

DBA_LOCK or DBA_LOCKS formatted view on V$LOCK (created via
$ORACLE_HOME/rdbms/admin/catblock.sql)

- SESSION_ID == SID in V$LOCK
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
- MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
- LAST_CONVERT == CTIME of V$LOCK
- BLOCKING_OTHERS formatted value of BLOCK from V$LOCK

V$TRANSACTION_ENQUEUE subset of V$LOCK for the blocking TX resources only
(same description as for the V$LOCK view)

V$ENQUEUE_LOCK subset of V$LOCK for the system resources only and
blocked TX resources only. (same description as for the V$LOCK view)

DBA_DML_LOCKS subset of the V$LOCK for the DML (TM) locks only
(created via $ORACLE_HOME/rdbms/admin/catblock.sql
- same description as the DBA_LOCK view)

V$LOCKED_OBJECT same info as DBA_DML_LOCKS, but linked with the
rollback and session information

- XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION

- OBJECT_ID object being locked
- SESSION_ID session id
- ORACLE_USERNAME oracle user name
- OS_USER_NAME OS user name
- PROCESS OS process id
- LOCKED_MODE lock mode

V$RESOURCE list of all the currently locked resources in the system.
Each row can be associated with one or more rows in V$LOCK

- TYPE, ID1 and ID2 determine the resource

DBA_DDL_LOCKS has a row for each DDL lock that is being held, and
one row for each outstanding request for a DDL lock. It is subset of DBA_LOCKS
same description as the DBA_LOCK view

DBA_WAITERS view that retrieve information for each session waiting on a
lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)

- WAITING_SESSION waiting session
- HOLDING_SESSION holding session
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
- MODE_HELD lock type held
- MODE_REQUESTED lock type requested

DBA_BLOCKERS view that gives the blocking sessions (created via $ORACLE_HOME/rdbms/admin/catblock.sql)

-HOLDING_SESSION holding session

SELECT B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,

DECODE(B.ID2, 0, A.OBJECT_NAME,

'TRANS-'||TO_CHAR(B.ID1)) OBJECT_NAME,

B.TYPE,

DECODE(B.LMODE,0,'--WAITING--',

1,'NULL',

2,'ROW SHARE',

3,'ROW EXCL',

4,'SHARE',

5,'SHA ROW EXC',

6,'EXCLUSIVE',

'OTHER') "LOCK MODE",

DECODE(B.REQUEST,0,' ',

1,'NULL',

2,'ROW SHARE',

3,'ROW EXCL',

4,'SHARE',

5,'SHA ROW EXC',

6,'EXCLUSIVE',

'OTHER') "REQ MODE"

FROM DBA_OBJECTS A, V$LOCK B, V$SESSION C

WHERE A.OBJECT_ID(+) = B.ID1

AND B.SID = C.SID

AND C.USERNAME IS NOT NULL

ORDER BY B.SID, B.ID2;

SELECT NVL(S.USERNAME,'INTERNAL') USERNAME,

NVL(S.TERMINAL,'NONE') TERMINAL,

L.SID||','||S.SERIAL# KILL,

U1.NAME||'.'||SUBSTR(T1.NAME,1,20) TAB,

DECODE(L.LMODE,1,'NO LOCK',

2,'ROW SHARE',

3,'ROW EXCLUSIVE',

4,'SHARE',

5,'SHARE ROW EXCLUSIVE',

6,'EXCLUSIVE',NULL) LMODE,

DECODE(L.REQUEST,1,'NO LOCK',

2,'ROW SHARE',

3,'ROW EXCLUSIVE',

4,'SHARE',

5,'SHARE ROW EXCLUSIVE',

6,'EXCLUSIVE',NULL) REQUEST

FROM V$LOCK L,

V$SESSION S,

SYS.USER$ U1,

SYS.OBJ$ T1

WHERE L.SID = S.SID

AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,L.ID2)

AND U1.USER# = T1.OWNER#

AND S.TYPE != 'BACKGROUND'

ORDER BY 1,2,5

SET LINESIZE 80

SET PAGESIZE 66

COLUMN LMODE HEADING 'LOCK|HELD' FORMAT A4

COLUMN REQUEST HEADING 'LOCK|REQ.' FORMAT A4

COLUMN USERNAME FORMAT A10 HEADING USERNAME

COLUMN TAB FORMAT A30 HEADING TABLE NAME;

COLUMN LADDR HEADING ID1 - ID2 FORMAT A16

COLUMN LOCKT HEADING LOCK|TYPE FORMAT A4

SELECT NVL(S.USERNAME,'INTERNAL') USERNAME,

DECODE(COMMAND,

0,'NONE',DECODE(L.ID2,0,U1.NAME||'.'||SUBSTR(T1.NAME,1,20),

'ROLLBACK SEGMENT')) TAB,

DECODE(L.LMODE,1,'NOLK', 2,' RS ', 3,' RX ',

4,' S ', 5,' SRX', 6,' X ','NONE') LMODE,

DECODE(L.REQUEST,1,'NOLK', 2,' RSH ', 3,' RX ',

4,' S ', 5,' SRX', 6,' X ','NONE') REQUEST,

L.ID1||'-'||L.ID2 LADDR, L.TYPE LOCKT

FROM V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1

WHERE L.SID = S.SID AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,1)

AND U1.USER# = T1.OWNER# AND S.TYPE != 'BACKGROUND'

ORDER BY 1,2,5

SET LINES 200

SET PAGESIZE 66

BREAK ON KILL ON SID ON USERNAME ON TERMINAL

COLUMN KILL HEADING 'KILL STRING' FORMAT A13

COLUMN RES HEADING 'RESOURCE TYPE' FORMAT 999

COLUMN ID1 FORMAT 9999990

COLUMN ID2 FORMAT 9999990

COLUMN LOCKING HEADING 'LOCK HELD/LOCK REQUESTED' FORMAT A40

COLUMN LMODE HEADING 'LOCK HELD' FORMAT A20

COLUMN REQUEST HEADING 'LOCK REQUESTED' FORMAT A20

COLUMN SERIAL# FORMAT 99999

COLUMN USERNAME FORMAT A10 HEADING "USERNAME"

COLUMN TERMINAL HEADING TERM FORMAT A6

COLUMN TAB FORMAT A30 HEADING "TABLE NAME"

COLUMN OWNER FORMAT A9

COLUMN LADDR HEADING "ID1 - ID2" FORMAT A18

COLUMN LOCKT HEADING "LOCK TYPE" FORMAT A40

COLUMN COMMAND FORMAT A25

COLUMN SID FORMAT 990

SELECT

NVL(S.USERNAME,'INTERNAL') USERNAME,

L.SID,

NVL(S.TERMINAL,'NONE') TERMINAL,

DECODE(COMMAND,

0,'NONE',DECODE(L.ID2,0,U1.NAME||'.'||SUBSTR(T1.NAME,1,20),'NONE')) TAB,

DECODE(COMMAND,

0,'BACKGROUND',

1,'CREATE TABLE',

2,'INSERT',

3,'SELECT',

4,'CREATE CLUSTER',

5,'ALTER CLUSTER',

6,'UPDATE',

7,'DELETE',

8,'DROP',

9,'CREATE INDEX',

10,'DROP INDEX',

11,'ALTER INDEX',

12,'DROP TABLE',

13,'CREATE SEQUENCE',

14,'ALTER SEQUENCE',

15,'ALTER TABLE',

16,'DROP SEQUENCE',

17,'GRANT',

18,'REVOKE',

19,'CREATE SYNONYM',

20,'DROP SYNONYM',

21,'CREATE VIEW',

22,'DROP VIEW',

23,'VALIDATE INDEX',

24,'CREATE PROCEDURE',

25,'ALTER PROCEDURE',

26,'LOCK TABLE',

27,'NO OPERATION',

28,'RENAME',

29,'COMMENT',

30,'AUDIT',

31,'NOAUDIT',

32,'CREATE EXTERNAL DATABASE',

33,'DROP EXTERNAL DATABASE',

34,'CREATE DATABASE',

35,'ALTER DATABASE',

36,'CREATE ROLLBACK SEGMENT',

37,'ALTER ROLLBACK SEGMENT',

38,'DROP ROLLBACK SEGMENT',

39,'CREATE TABLESPACE',

40,'ALTER TABLESPACE',

41,'DROP TABLESPACE',

42,'ALTER SESSION',

43,'ALTER USER',

44,'COMMIT',

45,'ROLLBACK',

46,'SAVEPOINT',

47,'PL/SQL EXECUTE',

48,'SET TRANSACTION',

49,'ALTER SYSTEM SWITCH LOG',

50,'EXPLAIN',

51,'CREATE USER',

52,'CREATE ROLE',

53,'DROP USER',

54,'DROP ROLE',

55,'SET ROLE',

56,'CREATE SCHEMA',

57,'CREATE CONTROL FILE',

58,'ALTER TRACING',

59,'CREATE TRIGGER',

60,'ALTER TRIGGER',

61,'DROP TRIGGER',

62,'ANALYZE TABLE',

63,'ANALYZE INDEX',

64,'ANALYZE CLUSTER',

65,'CREATE PROFILE',

66,'DROP PROFILE',

67,'ALTER PROFILE',

68,'DROP PROCEDURE',

69,'DROP PROCEDURE',

70,'ALTER RESOURCE COST',

71,'CREATE SNAPSHOT LOG',

72,'ALTER SNAPSHOT LOG',

73,'DROP SNAPSHOT LOG',

74,'CREATE SNAPSHOT',

75,'ALTER SNAPSHOT',

76,'DROP SNAPSHOT',

79,'ALTER ROLE',

85,'TRUNCATE TABLE',

86,'TRUNCATE CLUSTER',

87,'-',

88,'ALTER VIEW',

89,'-',

90,'-',

91,'CREATE FUNCTION',

92,'ALTER FUNCTION',

93,'DROP FUNCTION',

94,'CREATE PACKAGE',

95,'ALTER PACKAGE',

96,'DROP PACKAGE',

97,'CREATE PACKAGE BODY',

98,'ALTER PACKAGE BODY',

99,'DROP PACKAGE BODY',

COMMAND||' - ???') COMMAND,

DECODE(L.LMODE,1,'NO LOCK',

2,'ROW SHARE',

3,'ROW EXCLUSIVE',

4,'SHARE',

5,'SHARE ROW EXCLUSIVE',

6,'EXCLUSIVE','NONE') LMODE,

DECODE(L.REQUEST,1,'NO LOCK',

2,'ROW SHARE',

3,'ROW EXCLUSIVE',

4,'SHARE',

5,'SHARE ROW EXCLUSIVE',

6,'EXCLUSIVE','NONE') REQUEST,

L.ID1||'-'||L.ID2 LADDR,

L.TYPE||' - '||

DECODE(L.TYPE,

'BL','BUFFER HASH TABLE INSTANCE LOCK',

'CF',' CONTROL FILE SCHEMA GLOBAL ENQUEUE LOCK',

'CI','CROSS-INSTANCE FUNCTION INVOCATION INSTANCE LOCK',

'CS','CONTROL FILE SCHEMA GLOBAL ENQUEUE LOCK',

'CU','CURSOR BIND LOCK',

'DF','DATA FILE INSTANCE LOCK',

'DL','DIRECT LOADER PARALLEL INDEX CREATE',

'DM','MOUNT/STARTUP DB PRIMARY/SECONDARY INSTANCE LOCK',

'DR','DISTRIBUTED RECOVERY PROCESS LOCK',

'DX','DISTRIBUTED TRANSACTION ENTRY LOCK',

'FI','SGA OPEN-FILE INFORMATION LOCK',

'FS','FILE SET LOCK',

'HW','SPACE MANAGEMENT OPERATIONS ON A SPECIFIC SEGMENT LOCK',

'IN','INSTANCE NUMBER LOCK',

'IR','INSTANCE RECOVERY SERIALIZATION GLOBAL ENQUEUE LOCK',

'IS','INSTANCE STATE LOCK',

'IV','LIBRARY CACHE INVALIDATION INSTANCE LOCK',

'JQ','JOB QUEUE LOCK',

'KK','THREAD KICK LOCK',

'MB','MASTER BUFFER HASH TABLE INSTANCE LOCK',

'MM','MOUNT DEFINITION GLOABAL ENQUEUE LOCK',

'MR','MEDIA RECOVERY LOCK',

'PF','PASSWORD FILE LOCK',

'PI','PARALLEL OPERATION LOCK',

'PR','PROCESS STARTUP LOCK',

'PS','PARALLEL OPERATION LOCK',

'RE','USE_ROW_ENQUEUE ENFORCEMENT LOCK',

'RT','REDO THREAD GLOBAL ENQUEUE LOCK',

'RW','ROW WAIT ENQUEUE LOCK',

'SC','SYSTEM COMMIT NUMBER INSTANCE LOCK',

'SH','SYSTEM COMMIT NUMBER HIGH WATER MARK ENQUEUE LOCK',

'SM','SMON LOCK',

'SN','SEQUENCE NUMBER INSTANCE LOCK',

'SQ','SEQUENCE NUMBER ENQUEUE LOCK',

'SS','SORT SEGMENT LOCK',

'ST','SPACE TRANSACTION ENQUEUE LOCK',

'SV','SEQUENCE NUMBER VALUE LOCK',

'TA','GENERIC ENQUEUE LOCK',

'TD','DDL ENQUEUE LOCK',

'TE','EXTEND-SEGMENT ENQUEUE LOCK',

'TM','DML ENQUEUE LOCK',

'TO','TEMPORARY TABLE OBJECT ENQUEUE',

'TT','TEMPORARY TABLE ENQUEUE LOCK',

'TX','TRANSACTION ENQUEUE LOCK',

'UL','USER SUPPLIED LOCK',

'UN','USER NAME LOCK',

'US','UNDO SEGMENT DDL LOCK',

'WL','BEING-WRITTEN REDO LOG INSTANCE LOCK',

'WS','WRITE-ATOMIC-LOG-SWITCH GLOBAL ENQUEUE LOCK',

'TS',DECODE(L.ID2,0,'TEMPORARY SEGMENT ENQUEUE LOCK (ID2=0)',

'NEW BLOCK ALLOCATION ENQUEUE LOCK (ID2=1)'),

'LA','LIBRARY CACHE LOCK INSTANCE LOCK (A=NAMESPACE)',

'LB','LIBRARY CACHE LOCK INSTANCE LOCK (B=NAMESPACE)',

'LC','LIBRARY CACHE LOCK INSTANCE LOCK (C=NAMESPACE)',

'LD','LIBRARY CACHE LOCK INSTANCE LOCK (D=NAMESPACE)',

'LE','LIBRARY CACHE LOCK INSTANCE LOCK (E=NAMESPACE)',

'LF','LIBRARY CACHE LOCK INSTANCE LOCK (F=NAMESPACE)',

'LG','LIBRARY CACHE LOCK INSTANCE LOCK (G=NAMESPACE)',

'LH','LIBRARY CACHE LOCK INSTANCE LOCK (H=NAMESPACE)',

'LI','LIBRARY CACHE LOCK INSTANCE LOCK (I=NAMESPACE)',

'LJ','LIBRARY CACHE LOCK INSTANCE LOCK (J=NAMESPACE)',

'LK','LIBRARY CACHE LOCK INSTANCE LOCK (K=NAMESPACE)',

'LL','LIBRARY CACHE LOCK INSTANCE LOCK (L=NAMESPACE)',

'LM','LIBRARY CACHE LOCK INSTANCE LOCK (M=NAMESPACE)',

'LN','LIBRARY CACHE LOCK INSTANCE LOCK (N=NAMESPACE)',

'LO','LIBRARY CACHE LOCK INSTANCE LOCK (O=NAMESPACE)',

'LP','LIBRARY CACHE LOCK INSTANCE LOCK (P=NAMESPACE)',

'LS','LOG START/LOG SWITCH ENQUEUE LOCK',

'PA','LIBRARY CACHE PIN INSTANCE LOCK (A=NAMESPACE)',

'PB','LIBRARY CACHE PIN INSTANCE LOCK (B=NAMESPACE)',

'PC','LIBRARY CACHE PIN INSTANCE LOCK (C=NAMESPACE)',

'PD','LIBRARY CACHE PIN INSTANCE LOCK (D=NAMESPACE)',

'PE','LIBRARY CACHE PIN INSTANCE LOCK (E=NAMESPACE)',

'PF','LIBRARY CACHE PIN INSTANCE LOCK (F=NAMESPACE)',

'PG','LIBRARY CACHE PIN INSTANCE LOCK (G=NAMESPACE)',

'PH','LIBRARY CACHE PIN INSTANCE LOCK (H=NAMESPACE)',

'PI','LIBRARY CACHE PIN INSTANCE LOCK (I=NAMESPACE)',

'PJ','LIBRARY CACHE PIN INSTANCE LOCK (J=NAMESPACE)',

'PL','LIBRARY CACHE PIN INSTANCE LOCK (K=NAMESPACE)',

'PK','LIBRARY CACHE PIN INSTANCE LOCK (L=NAMESPACE)',

'PM','LIBRARY CACHE PIN INSTANCE LOCK (M=NAMESPACE)',

'PN','LIBRARY CACHE PIN INSTANCE LOCK (N=NAMESPACE)',

'PO','LIBRARY CACHE PIN INSTANCE LOCK (O=NAMESPACE)',

'PP','LIBRARY CACHE PIN INSTANCE LOCK (P=NAMESPACE)',

'PQ','LIBRARY CACHE PIN INSTANCE LOCK (Q=NAMESPACE)',

'PR','LIBRARY CACHE PIN INSTANCE LOCK (R=NAMESPACE)',

'PS','LIBRARY CACHE PIN INSTANCE LOCK (S=NAMESPACE)',

'PT','LIBRARY CACHE PIN INSTANCE LOCK (T=NAMESPACE)',

'PU','LIBRARY CACHE PIN INSTANCE LOCK (U=NAMESPACE)',

'PV','LIBRARY CACHE PIN INSTANCE LOCK (V=NAMESPACE)',

'PW','LIBRARY CACHE PIN INSTANCE LOCK (W=NAMESPACE)',

'PX','LIBRARY CACHE PIN INSTANCE LOCK (X=NAMESPACE)',

'PY','LIBRARY CACHE PIN INSTANCE LOCK (Y=NAMESPACE)',

'PZ','LIBRARY CACHE PIN INSTANCE LOCK (Z=NAMESPACE)',

'QA','ROW CACHE INSTANCE LOCK (A=CACHE)',

'QB','ROW CACHE INSTANCE LOCK (B=CACHE)',

'QC','ROW CACHE INSTANCE LOCK (C=CACHE)',

'QD','ROW CACHE INSTANCE LOCK (D=CACHE)',

'QE','ROW CACHE INSTANCE LOCK (E=CACHE)',

'QF','ROW CACHE INSTANCE LOCK (F=CACHE)',

'QG','ROW CACHE INSTANCE LOCK (G=CACHE)',

'QH','ROW CACHE INSTANCE LOCK (H=CACHE)',

'QI','ROW CACHE INSTANCE LOCK (I=CACHE)',

'QJ','ROW CACHE INSTANCE LOCK (J=CACHE)',

'QL','ROW CACHE INSTANCE LOCK (K=CACHE)',

'QK','ROW CACHE INSTANCE LOCK (L=CACHE)',

'QM','ROW CACHE INSTANCE LOCK (M=CACHE)',

'QN','ROW CACHE INSTANCE LOCK (N=CACHE)',

'QO','ROW CACHE INSTANCE LOCK (O=CACHE)',

'QP','ROW CACHE INSTANCE LOCK (P=CACHE)',

'QQ','ROW CACHE INSTANCE LOCK (Q=CACHE)',

'QR','ROW CACHE INSTANCE LOCK (R=CACHE)',

'QS','ROW CACHE INSTANCE LOCK (S=CACHE)',

'QT','ROW CACHE INSTANCE LOCK (T=CACHE)',

'QU','ROW CACHE INSTANCE LOCK (U=CACHE)',

'QV','ROW CACHE INSTANCE LOCK (V=CACHE)',

'QW','ROW CACHE INSTANCE LOCK (W=CACHE)',

'QX','ROW CACHE INSTANCE LOCK (X=CACHE)',

'QY','ROW CACHE INSTANCE LOCK (Y=CACHE)',

'QZ','ROW CACHE INSTANCE LOCK (Z=CACHE)','????') LOCKT

FROM V$LOCK L,

V$SESSION S,

SYS.USER$ U1,

SYS.OBJ$ T1

WHERE L.SID = S.SID

AND T1.OBJ# = DECODE(L.ID2,0,L.ID1,1)

AND U1.USER# = T1.OWNER#

AND S.TYPE != 'BACKGROUND'

ORDER BY 1,2,5

BLOCKING SESSIONS ON RAC DATABASE:

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE

|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '

|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS

FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2

WHERE S1.SID=L1.SID AND S2.SID=L2.SID

AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID

AND L1.BLOCK > 0 AND L2.REQUEST > 0

AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

SELECT 'ORACLE SESSION WITH USERNAME'|| BLOCKER.USERNAME || ' AND SESSION ID '||BLOCKER.SID

||' IS BLOCKING ' || BLOCKED.USERNAME ||' WITH SESSION ID '|| BLOCKED.SID BLOCK_DESCRIPTION

FROM GV$LOCK LOCKER, GV$SESSION BLOCKER, GV$LOCK LOCKED, GV$SESSION BLOCKED

WHERE BLOCKER.SID=LOCKER.SID AND BLOCKED.SID=LOCKED.SID

AND LOCKER.BLOCK=1

AND LOCKER.ID1 = LOCKED.ID1

AND LOCKER.ID2 = LOCKED.ID2

AND LOCKER.INST_ID = BLOCKER.INST_ID

AND LOCKED.INST_ID = BLOCKED.INST_ID;

BLOCKER INFORMATION:

SELECT S1.USERNAME || '@' || S1.MACHINE || ' ( SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS

FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2

WHERE S1.SID=L1.SID AND S2.SID=L2.SID

AND L1.BLOCK=1 AND L2.REQUEST > 0

AND L1.ID1 = L2.ID1

AND L2.ID2 = L2.ID2 ;

COMMAND TO KILL THE SESSION:

SELECT DISTINCT 'ALTER SYSTEM KILL SESSION '''||S1.SID||','||S1.SERIAL#||',@'||S1.INST_ID||''' IMMEDIATE;'

FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2

WHERE S1.SID=L1.SID AND S2.SID=L2.SID

AND L1.BLOCK=1 AND L2.REQUEST > 0

AND L1.ID1 = L2.ID1

AND L2.ID2 = L2.ID2

ORDER BY 1;

ALTER SYSTEM KILL SESSION 'sid, serial#';

ALTER SYSTEM KILL SESSION 'sid, serial#' immediate;

ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>';

SESSION DETAILS FOR ANY CONNECTION FROM DEVELOPER TOOL:

SET LINESIZE 200

COL MACHINE FORMAT A30

COL OSUSER FORMAT A10

COL SCHEMANAME FORMAT A10

COL MODULE FORMAT A20

SELECT TO_CHAR(LOGON_TIME,'DD/MM/YYYY HH24:MI:SS'),OSUSER,STATUS,SCHEMANAME,MACHINE,SID,MODULE,INST_ID FROM GV$SESSION WHERE TYPE !='BACKGROUND' AND MODULE LIKE '%DEVELOPER' ORDER BY LOGON_TIME ASC;

SESSION DETAILS:

SELECT P.SPID, SUBSTR(S.SID,1,6) SID, SUBSTR(S.SERIAL#,1,8) SERIAL , S.STATUS STATUS,
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME, S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.SID = &SID;

SELECT P.SPID, S.PROCESS CPID, SUBSTR(S.SID,1,6) SID, SUBSTR(S.SERIAL#,1,8) SERIAL , S.STATUS STATUS,
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME, S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.PROGRAM LIKE '%SQLPLUS%';

SELECT P.SPID, S.PROCESS CPID, SUBSTR(S.SID,1,6) SID, SUBSTR(S.SERIAL#,1,8) SERIAL , S.STATUS STATUS,
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME,
ROUND(S.LAST_CALL_ET/60) IDLE_MIN, S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.PROGRAM LIKE '%SQLPLUS%' ;

SELECT P.SPID, S.PROCESS CPID, SUBSTR(S.SID,1,6)||','||SUBSTR(S.SERIAL#,1,8) SIDSERIAL , S.STATUS STATUS,
TO_CHAR(S.LOGON_TIME,'DD-MM-YYYY HH24:MI') LOGON_TIME, ROUND(S.LAST_CALL_ET/60) IDLE_MIN,
SUBSTR(S.USERNAME,1,10) USERNAME , SUBSTR(S.OSUSER,1,10) OSUSER,
S.PROGRAM PROGRAM
FROM GV$SESSION S , GV$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.USERNAME IS NOT NULL
AND S.USERNAME NOT IN ('SYS')
ORDER BY IDLE_MIN DESC;

LONG RUNNING SESSION DETAILS:

COLUMN USERNAME FORMAT 'A10'
COLUMN OSUSER FORMAT 'A10'
COLUMN MODULE FORMAT 'A16'
COLUMN PROGRAM_NAME FORMAT 'A20'
COLUMN PROGRAM FORMAT 'A20'
COLUMN MACHINE FORMAT 'A20'
COLUMN ACTION FORMAT 'A20'
COLUMN SID FORMAT '9999'
COLUMN SERIAL# FORMAT '99999'
COLUMN SPID FORMAT '99999'
SET LINESIZE 200
SET PAGESIZE 30

SELECT
A.SID,A.SERIAL#,A.USERNAME,A.OSUSER,C.START_TIME,
B.SPID,A.STATUS,A.MACHINE,
A.ACTION,A.MODULE,A.PROGRAM
FROM
GV$SESSION A, GV$PROCESS B, GV$TRANSACTION C,
GV$SQLAREA S
WHERE
A.PADDR = B.ADDR
AND A.SADDR = C.SES_ADDR
AND A.SQL_ADDRESS = S.ADDRESS (+)
AND TO_DATE(C.START_TIME,'MM/DD/YY HH24:MI:SS') <= SYSDATE - (60/1440) -- RUNNING FOR 60 MINUTES
ORDER BY C.START_TIME;

SELECT 'SID:'||S.SID||', SERIAL#:'||S.SERIAL#||', USERNAME:'||S.USERNAME||', MACHINE:'||S.MACHINE||

', PROGRAM:'||S.PROGRAM||', HASHVALUE:'||S.SQL_HASH_VALUE||', SQL TEXT:'||NVL(SUBSTR(SQL.SQL_TEXT,1,40),'UNKNOWN SQL'), LAST_CALL_ET

FROM V$SESSION S

LEFT OUTER JOIN V$SQL SQL ON SQL.HASH_VALUE=S.SQL_HASH_VALUE AND SQL.ADDRESS=S.SQL_ADDRESS

WHERE S.STATUS='ACTIVE'

AND S.TYPE <> 'BACKGROUND'

AND LAST_CALL_ET >= 3600;

SESSION SID DETAILS:

COL EVENT FOR A30

SELECT ' SID : '||V.SID || CHR(10)||

' SERIAL NUMBER : '||V.SERIAL# || CHR(10) ||

' ORACLE USER NAME : '||V.USERNAME || CHR(10) ||

' CLIENT OS USER NAME : '||V.OSUSER || CHR(10) ||

' CLIENT PROCESS ID : '||V.PROCESS || CHR(10) ||

' CLIENT MACHINE NAME : '||V.MACHINE || CHR(10) ||

' ORACLE PID : '||P.PID || CHR(10) ||

' OS PROCESS ID(SPID) : '||P.SPID || CHR(10) ||

' SESSION''S STATUS : '||V.STATUS || CHR(10) ||

' LOGON TIME : '||TO_CHAR(V.LOGON_TIME, 'MM/DD HH24:MIPM') || CHR(10) ||

' PROGRAM NAME : '||V.PROGRAM || CHR(10)

FROM V$SESSION V, V$PROCESS P

WHERE V.PADDR = P.ADDR

AND V.SERIAL# > 1

AND P.BACKGROUND IS NULL

AND P.USERNAME IS NOT NULL

AND SID = &SID_NUMBER

ORDER BY LOGON_TIME, V.STATUS, 1

PROMPT SQL STATEMENT

PROMPT --------------

SELECT SQL_TEXT

FROM V$SQLTEXT , V$SESSION

WHERE V$SQLTEXT.ADDRESS = V$SESSION.SQL_ADDRESS

AND SID = &SID_NUMBER

ORDER BY PIECE

PROMPT

PROMPT EVENT WAIT INFORMATION

PROMPT ----------------------

SELECT ' SID '|| &SID_NUMBER ||' IS WAITING ON EVENT : ' || X.EVENT || CHR(10) ||

' P1 TEXT : ' || X.P1TEXT || CHR(10) ||

' P1 VALUE : ' || X.P1 || CHR(10) ||

' P2 TEXT : ' || X.P2TEXT || CHR(10) ||

' P2 VALUE : ' || X.P2 || CHR(10) ||

' P3 TEXT : ' || X.P3TEXT || CHR(10) ||

' P3 VALUE : ' || X.P3

FROM V$SESSION_WAIT X

WHERE X.SID= &SID_NUMBER

PROMPT

PROMPT SESSION STATISTICS

PROMPT ------------------

SELECT ' '|| B.NAME ||' : '||DECODE(B.NAME, 'REDO SIZE', ROUND(A.VALUE/1024/1024,2)||' M', A.VALUE)

FROM V$SESSION S, V$SESSTAT A, V$STATNAME B

WHERE A.STATISTIC# = B.STATISTIC#

AND NAME IN ('REDO SIZE', 'PARSE COUNT (TOTAL)', 'PARSE COUNT (HARD)', 'USER COMMITS')

AND S.SID = &SID_NUMBER

AND A.SID = &SID_NUMBER

--ORDER BY B.NAME

ORDER BY DECODE(B.NAME, 'REDO SIZE', 1, 2), B.NAME

COLUMN USERNAME FORMAT A10

COLUMN STATUS FORMAT A8

COLUMN RBS_NAME FORMAT A10

PROMPT

PROMPT TRANSACTION AND ROLLBACK INFORMATION

PROMPT ------------------------------------

SELECT ' ROLLBACK USED : '||T.USED_UBLK*8192/1024/1024 ||' M' || CHR(10) ||

' ROLLBACK RECORDS : '||T.USED_UREC || CHR(10)||

' ROLLBACK SEGMENT NUMBER : '||T.XIDUSN || CHR(10)||

' ROLLBACK SEGMENT NAME : '||R.NAME || CHR(10)||

' LOGICAL IOS : '||T.LOG_IO || CHR(10)||

' PHYSICAL IOS : '||T.PHY_IO || CHR(10)||

' RBS STARTNG EXTENT ID : '||T.START_UEXT || CHR(10)||

' TRANSACTION START TIME : '||T.START_TIME || CHR(10)||

' TRANSACTION_STATUS : '||T.STATUS

FROM V$TRANSACTION T, V$SESSION S, V$ROLLNAME R

WHERE T.ADDR = S.TADDR

AND R.USN = T.XIDUSN

AND S.SID = &SID_NUMBER

PROMPT

PROMPT SORT INFORMATION

PROMPT ----------------

COLUMN USERNAME FORMAT A20

COLUMN USER FORMAT A20

COLUMN TABLESPACE FORMAT A20

SELECT ' SORT SPACE USED(8K BLOCK SIZE IS ASSSUMED : '||U.BLOCKS/1024*8 ||' M' || CHR(10) ||

' SORTING TABLESPACE : '||U.TABLESPACE || CHR(10)||

' SORT TABLESPACE TYPE : '||U.CONTENTS || CHR(10)||

' TOTAL EXTENTS USED FOR SORTING : '||U.EXTENTS

FROM V$SESSION S, V$SORT_USAGE U

WHERE S.SADDR = U.SESSION_ADDR

AND S.SID = &SID_NUMBER

V$LOCKED_OBJECT: THIS VIEW LISTS ALL LOCKS ACQUIRED BY EVERY TRANSACTION ON THE SYSTEM:

SET LINESIZE 130

SET PAGES 100

COL USERNAME FORMAT A20

COL SESS_ID FORMAT A10

OL OBJECT FORMAT A25

COL MODE_HELD FORMAT A10

SELECT ORACLE_USERNAME || ' (' || S.OSUSER || ')' USERNAME

, S.SID || ',' || S.SERIAL# SESS_ID

, OWNER || '.' || OBJECT_NAME OBJECT

, OBJECT_TYPE

, decode( l.block

, 0, 'Not Blocking'

, 1, 'Blocking'

, 2, 'Global') status

, decode(v.locked_mode

, 0, 'None'

, 1, 'Null'

, 2, 'Row-S (SS)'

, 3, 'Row-X (SX)'

, 4, 'Share'

, 5, 'S/Row-X (SSX)'

, 6, 'Exclusive', TO_CHAR(lmode)) mode_held

FROM V$LOCKED_OBJECT V

, DBA_OBJECTS D

, V$LOCK L

, V$SESSION S

WHERE V.OBJECT_ID = D.OBJECT_ID

AND V.OBJECT_ID = L.ID1

AND V.SESSION_ID = S.SID

ORDER BY ORACLE_USERNAME

, SESSION_ID

SELECT S.INST_ID INSTANCE_ID, S.SID, S.SERIAL#, P.USERNAME OS_PROCESS_OWNER, P.PID, P.SPID OS_PROCESS_ID,

L.SESSION_ID, L.ORACLE_USERNAME, L.OS_USER_NAME, L.PROCESS,

DECODE (LOCKED_MODE,

0, 'NONE',

1, 'NULL',

2, 'ROW-SHARE',

3, 'ROW-EXCLUSIVE.',

4, 'SHARE',

5, 'S/ROW-EXCLUSIVE',

6, 'EXCLUSIVE'

) AS LOCK_TYPE,

O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, S.PROGRAM, S.LOGON_TIME, S.STATUS SESSION_STATUS,

O.STATUS OBJECT_STATUS

FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S, GV$PROCESS P

WHERE (L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID)

AND S.PADDR = P.ADDR

AND L.INST_ID = P.INST_ID

AND S.INST_ID = P.INST_ID

ORDER BY OBJECT_NAME;

BLOCKING SESSION FOR LAST 3 HOURS:

SELECT DISTINCT A.SQL_ID ,A.INST_ID,A.BLOCKING_SESSION,A.BLOCKING_SESSION_SERIAL#,A.USER_ID,S.SQL_TEXT,A.MODULE

FROM GV$ACTIVE_SESSION_HISTORY A ,GV$SQL S

WHERE A.SQL_ID=S.SQL_ID

AND BLOCKING_SESSION IS NOT NULL

AND A.USER_ID <> 0

AND A.SAMPLE_TIME > SYSDATE - 3/24;

BLOCKING SESSION FOR LAST 7 DAYS:

SELECT * FROM (

SELECT A.SQL_ID ,

COUNT(*) OVER (PARTITION BY A.BLOCKING_SESSION,A.USER_ID ,A.PROGRAM) CPT,

ROW_NUMBER() OVER (PARTITION BY A.BLOCKING_SESSION,A.USER_ID ,A.PROGRAM

ORDER BY BLOCKING_SESSION,A.USER_ID ,A.PROGRAM ) RN,

A.BLOCKING_SESSION,A.USER_ID ,A.PROGRAM, S.SQL_TEXT

FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A ,SYS.WRH$_SQLTEXT S

WHERE A.SQL_ID=S.SQL_ID

AND BLOCKING_SESSION_SERIAL# <> 0

AND A.USER_ID <> 0

AND A.SAMPLE_TIME > SYSDATE -1) WHERE RN = 1;

SESSION DETAILS ASSOCIATED WITH ORACLE SID :

SET HEAD OFF

SET VERIFY OFF

SET ECHO OFF

SET PAGES 1500

SET LINESIZE 100

SET LINES 120

PROMPT

PROMPT DETAILS OF SID / SPID / CLIENT PID

PROMPT ==================================

SELECT /*+ CHOOSE*/

'SESSION ID.............................................: '||S.SID,

'SERIAL NUM..............................................: '||S.SERIAL#,

'USER NAME ..............................................: '||S.USERNAME,

'SESSION STATUS .........................................: '||S.STATUS,

'CLIENT PROCESS ID ON CLIENT MACHINE ....................: '||'*'||S.PROCESS||'*' CLIENT,

'SERVER PROCESS ID ......................................: '||P.SPID SERVER,

'SQL_ADDRESS ............................................: '||S.SQL_ADDRESS,

'SQL_HASH_VALUE .........................................: '||S.SQL_HASH_VALUE,

'SCHEMA NAME ..... ......................................: '||S.SCHEMANAME,

'PROGRAM ...............................................: '||S.PROGRAM,

'MODULE .................................................: '|| S.MODULE,

'ACTION .................................................: '||S.ACTION,

'TERMINAL ...............................................: '||S.TERMINAL,

'CLIENT MACHINE .........................................: '||S.MACHINE,

'LAST_CALL_ET ...........................................: '||S.LAST_CALL_ET,

'S.LAST_CALL_ET/3600 ....................................: '||S.LAST_CALL_ET/3600

FROM V$SESSION S, V$PROCESS P

WHERE P.ADDR=S.PADDR and

S.SID=NVL('&SID',S.SID)

Saturday, February 22, 2020

Generating Explain Plan & Gather Statistics

Join Methods : Nested Loop Join, Hash Join, Sort Merge Join

Nested Loops Joins :

Nested loops join an outer data set to an inner data set. For each row in the outer data set that matches the single-table predicates, the database retrieves all rows in the inner data set that satisfy the join predicate. If an index is available, then the database can use it to access the inner data set by rowid.

Hash Joins :

The database uses a hash join to join larger data sets. The optimizer uses the smaller of two data sets to build a hash table on the join key in memory, using a deterministic hash function to specify the location in the hash table in which to store each row. The database then scans the larger data set, probing the hash table to find the rows that meet the join condition.

Sort Merge Joins :

A sort merge join is a variation on a nested loops join. If the two data sets in the join are not already sorted, then the database sorts them. These are the SORT JOIN operations. For each row in the first data set, the database probes the second data set for matching rows and joins them, basing its start position on the match made in the previous iteration. This is the MERGE JOIN operation.

Join Types : Inner Join, Outer Join, Semi Join, Anti Join, Cartesian Join (A join type is determined by the type of join condition.)

Semi joins :

A semi join is a join between two data sets that returns a row from the first set when a matching row exists in the subquery data set. The database stops processing the second data set at the first match. Thus, optimization does not duplicate rows from the first data set when multiple rows in the second data set satisfy the subquery criteria.

Outer Joins :

An outer join returns all rows that satisfy the join condition and also rows from one table for which no rows from the other table satisfy the condition. Thus, the result set of an outer join is the superset of an inner join.

Inner Joins :

An inner join (sometimes called a simple join) is a join that returns only rows that satisfy the join condition. Inner joins are either equijoins or non equijoins.

Access Methods :

Optimizer statistics include the following:

Table statistics
- Number of rows
- Number of blocks
- Average row length
Column statistics
- Number of distinct values (NDV) in a column
- Number of nulls in a column
- Data distribution (histogram)
- Extended statistics
Index statistics
- Number of leaf blocks
- Number of levels
- Index clustering factor
System statistics
- I/O performance and utilization
- CPU performance and utilization

Table Statistics

Table statistics contain metadata that the optimizer uses when developing an execution plan.

In Oracle Database, table statistics include information about rows and blocks.

The optimizer uses these statistics to determine the cost of table scans and table joins. The database tracks all relevant statistics about permanent tables. For example, table statistics stored in DBA_TAB_STATISTICS track the following:

Number of rows
The database uses the row count stored in DBA_TAB_STATISTICS when determining cardinality.
Average row length
Number of data blocks
The optimizer uses the number of data blocks with the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter to determine the base table access cost.
Number of empty data blocks

DBMS_STATS.GATHER_TABLE_STATS commits before gathering statistics on permanent tables.

SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS,
EMPTY_BLOCKS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE OWNER='SH'
AND TABLE_NAME='CUSTOMERS';

NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS LAST_ANAL
---------- ----------- ---------- ------------ ---------
55500 189 1517 0 25-MAY-17

Column Statistics

Column statistics track information about column values and data distribution.

The optimizer uses column statistics to generate accurate cardinality estimates and make better decisions about index usage, join orders, join methods, and so on. For example, statistics in DBA_TAB_COL_STATISTICS track the following:

Number of distinct values
Number of nulls
High and low values
Histogram-related information

The optimizer can use extended statistics, which are a special type of column statistics. These statistics are useful for informing the optimizer of logical relationships among columns.

Index Statistics

The index statistics include information about the number of index levels, the number of index blocks, and the relationship between the index and the data blocks. The optimizer uses these statistics to determine the cost of index scans.

The DBA_IND_STATISTICS view tracks index statistics.

Statistics include the following:

Levels
The BLEVEL column shows the number of blocks required to go from the root block to a leaf block. A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. See Oracle Database Concepts for a conceptual overview of B-tree indexes.
Distinct keys
This columns tracks the number of distinct indexed values. If a unique constraint is defined, and if no NOT NULL constraint is defined, then this value equals the number of non-null values.
Average number of leaf blocks for each distinct indexed key
Average number of data blocks pointed to by each distinct indexed key

SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS AS "LEAFBLK", DISTINCT_KEYS AS "DIST_KEY",

AVG_LEAF_BLOCKS_PER_KEY AS "LEAFBLK_PER_KEY",

AVG_DATA_BLOCKS_PER_KEY AS "DATABLK_PER_KEY"

FROM DBA_IND_STATISTICS

WHERE OWNER = 'SH'

AND INDEX_NAME IN ('CUST_LNAME_IX','CUSTOMERS_PK');

INDEX_NAME BLEVEL LEAFBLK DIST_KEY LEAFBLK_PER_KEY DATABLK_PER_KEY

-------------- ------ ------- -------- --------------- ---------------

CUSTOMERS_PK 1 115 55500 1 1

CUST_LNAME_IX 1 141 908 1 10

For a B-tree index, the index clustering factor measures the physical grouping of rows in relation to an index value The index clustering factor helps the optimizer decide whether an index scan or full table scan is more efficient for certain queries). A low clustering factor indicates an efficient index scan. A clustering factor that is close to the number of blocks in a table indicates that the rows are physically ordered in the table blocks by the index key. If the database performs a full table scan, then the database tends to retrieve the rows as they are stored on disk sorted by the index key. A clustering factor that is close to the number of rows indicates that the rows are scattered randomly across the database blocks in relation to the index key. If the database performs a full table scan, then the database would not retrieve rows in any sorted order by this index key.

The clustering factor is a property of a specific index, not a table. If multiple indexes exist on a table, then the clustering factor for one index might be small while the factor for another index is large. An attempt to reorganize the table to improve the clustering factor for one index may degrade the clustering factor of the other index.

SELECT table_name, num_rows, blocks

FROM user_tables

WHERE table_name='CUSTOMERS';

TABLE_NAME NUM_ROWS BLOCKS

------------------------------ ---------- ----------

CUSTOMERS 55500 1486

CREATE INDEX CUSTOMERS_LAST_NAME_IDX ON customers(cust_last_name);

SELECT index_name, blevel, leaf_blocks, clustering_factor

FROM user_indexes

WHERE table_name='CUSTOMERS'

AND index_name= 'CUSTOMERS_LAST_NAME_IDX';

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

------------------------------ ---------- ----------- -----------------

CUSTOMERS_LAST_NAME_IDX 1 141 9859

Histograms
A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. A histogram sorts values into "buckets," as you might sort coins into buckets.

Based on the NDV and the distribution of the data, the database chooses the type of histogram to create. (In some cases, when creating a histogram, the database samples an internally predetermined number of rows.) The types of histograms are as follows:

Frequency histograms and top frequency histograms
Height-Balanced histograms (legacy)
Hybrid histograms

By default the optimizer assumes a uniform distribution of rows across the distinct values in a column.
For columns that contain data skew (a nonuniform distribution of data within the column), a histogram enables the optimizer to generate accurate cardinality estimates for filter and join predicates that involve these columns.

For example, a California-based book store ships 95% of the books to California, 4% to Oregon, and 1% to Nevada. The book orders table has 300,000 rows. A table column stores the state to which orders are shipped. A user queries the number of books shipped to Oregon. Without a histogram, the optimizer assumes an even distribution of 300000/3 (the NDV is 3), estimating cardinality at 100,000 rows. With this estimate, the optimizer chooses a full table scan. With a histogram, the optimizer calculates that 4% of the books are shipped to Oregon, and chooses an index scan.

BEGIN

DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SH',tabname=> 'COUNTRIES',method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID');

END;

SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM

FROM USER_TAB_COL_STATISTICS

WHERE TABLE_NAME='COUNTRIES'

AND COLUMN_NAME='COUNTRY_SUBREGION_ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM

---------- -------------------- ------------ ---------------

COUNTRIES COUNTRY_SUBREGION_ID 8 FREQUENCY

COL OWNER FORMAT a5

COL TABLE_NAME FORMAT a15

COL PREFERENCE_NAME FORMAT a20

COL PREFERENCE_VALUE FORMAT a30

SELECT * FROM DBA_TAB_STAT_PREFS;

OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE

----- --------------- -------------------- -----------------------------

OE CUSTOMERS NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE

SH SALES STALE_PERCENT 13

Autotrace Setting	Result
`SET AUTOTRACE OFF`	No `AUTOTRACE` report is generated. This is the default.
`SET AUTOTRACE ON EXPLAIN`	The `AUTOTRACE` report shows only the optimizer execution path.
`SET AUTOTRACE ON STATISTICS`	The `AUTOTRACE` report shows only the SQL statement execution statistics.
`SET AUTOTRACE ON`	The `AUTOTRACE` report includes both the optimizer execution path and the SQL statement execution statistics.
`SET AUTOTRACE TRACEONLY`	Like `SET` `AUTOTRACE` `ON`, but suppresses the printing of the user's query output, if any.

The DBMS_XPLAN package supplies five table functions:

DISPLAY - to format and display the contents of a plan table.
DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.

Format Constants
ALIAS	If relevant, shows the "Query Block Name / Object Alias" section
ALLSTATS	A shortcut for 'IOSTATS MEMSTATS'
BYTES	If relevant, shows the number of bytes estimated by the optimizer
COST	If relevant, shows optimizer cost information
IOSTATS	Assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format will show IO statistics for ALL (or only for the LAST as shown below) executions of the cursor
LAST	By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution
MEMSTATS	Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators
NOTE	If relevant, shows the note section of the explain plan
PARALLEL	If relevant, shows PX information (distribution method and table queue information)
PARTITION	If relevant, shows partition pruning information
PREDICATE	If relevant, shows the predicate section
PROJECTION	If relevant, shows the projection section
REMOTE	If relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)
ROWS	If relevant, shows the number of rows estimated by the optimizer
RUNSTATS_LAST	Same as IOSTATS LAST: displays the runtime stat for the last execution of the cursor
RUNSTATS_TOT	Same as IOSTATS: displays IO statistics for all executions of the specified cursor

dbms_xplan.display function has four basic levels

BASIC TYPICAL (default) SERIAL ALL

SELECT * FROM table(dbms_xplan.display);

SELECT * FROM table(dbms_xplan.display(null,null,'ALL'));

SELECT * FROM table(dbms_xplan.display(null,null,'BASIC +COST'));

SELECT * FROM table(dbms_xplan.display(null,null,'TYPICAL -BYTES -ROWS'));

To display runtime statistics for the cursor included in the preceding statement:

SELECT * FROM table ( DBMS_XPLAN.DISPLAY_CURSOR('13adnwhahfkqf', NULL, 'ALLSTATS LAST');

To display the execution plan of the last SQL statement executed by the current session:

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

To display the execution plan of all children associated with the SQL ID 'atfwcg8anrykp':

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('13adnwhahfkqf'));

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('6bv38j6jvmt7g', format => 'TYPICAL +PEEKED_BINDS'));

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('6BV38J6JVMT7G', FORMAT => 'TYPICAL +PEEKED_BINDS'));

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

To display the execution plan for the SQL statement associated with SQL ID 'atfwcg8anrykp' and PLAN HASH 5593697099 in the SQL Tuning Set called 'OLTP_optimization_123":

SELECT * FROM table ( DBMS_XPLAN.DISPLAY_SQLSET('OLTP_optimization_123','atfwcg8anrykp', 5593697099));

To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:

SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('OLTP_optimization_123','atfwcg8anrykp'));

To display runtime statistics for the SQL statement included in the preceding statement:

SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('OLTP_optimization_123', 'atfwcg8anrykp', NULL, 'ALLSTATS LAST');

Operation	Explanation	To Learn More
Access paths	As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement. For example, the optimizer might choose between a full table scan or an index scan..	"Optimizer Access Paths"
Join methods	To join each pair of row sources, Oracle Database must decide how to do it. The "how" is the join method. The possible join methods are nested loop, sort merge, and hash joins. A Cartesian join requires one of the preceding join methods. Each join method has specific situations in which it is more suitable than the others.	"Join Methods"
Join types	The join condition determines the join type. For example, an inner join retrieves only rows that match the join condition. An outer join retrieves rows that do not match the join condition.	"Join Types"
Join order	To execute a statement that joins more than two tables, Oracle Database joins two tables and then joins the resulting row source to the next table. This process continues until all tables are joined into the result. For example, the database joins two tables, and then joins the result to a third table, and then joins this result to a fourth table, and so on.	N/A

STALE STATS FOR TABLE:

SELECT OWNER,TABLE_NAME,STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER='&SCHEMA_NAME' AND TABLE_NAME='&TABLE_NAME';

STALE STATS FOR INDEX :

SELECT OWNER,INDEX_NAME,TABLE_NAME FROM DBA_IND_STATISTICS WHERE OWNER='&SCHEMA_NAME' AND INDEX_NAME='&INDEX_NAME';

FOR GETTING HISTORY OF TABLE STATISTICS :

SELECT OWNER,TABLE_NAME,STATS_UPDATE_TIME FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME='&TABLE_NAME';

FIXED OBJECTS STATS:

1. CHECK FIXED OBJECT STATS

SELECT COUNT(1) FROM TAB_STATS$;

2. GATHER FIXED OBJECTS STATS

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

3. CHECK FIXED OBJECT STATS

SELECT COUNT(1) FROM TAB_STATS$;

GATHER SYSTEM STAT:

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS('START');

-- FEW HOUR DELAY DURING HIGH WORKLOAD

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

FLUSH SHARED POOL

ALTER SYSTEM FLUSH SHARED_POOL;

ROLL BACK IF THE CHANGE IS NOT SUCCESSFUL:

1. EXEC DBMS_STATS.DELETE_FIXED_OBJECTS_STATS();

2. EXEC DBMS_STATS.DELETE_SYSTEM_STATS;

https://blogs.oracle.com/optimizer/how-to-gather-optimizer-statistics-fast

---GATHER STATS FOR A TABLE:

BEGIN

DBMS_STATS.GATHER_TABLE_STATS (

OWNNAME => ‘HR’,

TABNAME => ‘EMP’,

CASCADE => TRUE, —- FOR COLLECTING STATS FOR RESPECTIVE INDEXES

METHOD_OPT=>’FOR ALL INDEXED COLUMNS SIZE 1′,

GRANULARITY => ‘ALL’,

ESTIMATE_PERCENT =>DBMS_STATS.AUTO_SAMPLE_SIZE,

DEGREE => 4);

END;

— FOR A SINGLE TABLE PARTITION

BEGIN

DBMS_STATS.GATHER_TABLE_STATS (

OWNNAME => ‘HR’,

TABNAME => ‘EMP’, — TABLE NAME

PARTNAME => ‘EMP_P1’ — PARTITOIN NAME

METHOD_OPT=>’FOR ALL INDEXED COLUMNS SIZE 1′,

GRANULARITY => ‘APPROX_GLOBAL AND PARTITION’,

DEGREE => 8);

END;

---Statistics Gathering Procedures in the DBMS_STATS Package

Procedure	Collects
GATHER_INDEX_STATS	Index statistics
GATHER_TABLE_STATS	Table, column, and index statistics
GATHER_SCHEMA_STATS	Statistics for all objects in a schema
GATHER_DATABASE_STATS	Statistics for all objects in a database
GATHER_SYSTEM_STATS	CPU and I/O statistics for the system

Default Table and Index Values When Statistics are Missing

Statistic	Default Value Used by Optimizer
Tables Cardinality Average row length Number of blocks Remote cardinality Remote average row length	num_of_blocks * (block_size - cache_layer) / avg_row_len 100 bytes 1 2000 rows 100 bytes
Indexes Levels Leaf blocks Leaf blocks/key Data blocks/key Distinct keys Clustering factor	1 25 1 1 100 800 (8 * number of blocks)

Statistic

Default Value Used by Optimizer

Tables

Cardinality
Average row length
Number of blocks
Remote cardinality
Remote average row length

num_of_blocks * (block_size - cache_layer) / avg_row_len

100 bytes

2000 rows

100 bytes

Indexes

Levels
Leaf blocks
Leaf blocks/key
Data blocks/key
Distinct keys
Clustering factor

100

800 (8 * number of blocks)

Verifying Table Statistics

SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,
TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS')
FROM DBA_TABLES
WHERE TABLE_NAME IN ('SO_LINES_ALL','SO_HEADERS_ALL','SO_LAST_ALL');

This returns the following typical data:

TABLE_NAME                 NUM_ROWS   BLOCKS   AVG_ROW_LEN LAST_ANALYZED
------------------------   --------   -------  ----------- -------------
SO_HEADERS_ALL             1632264    207014            449  07/29/1999
00:59:51
SO_LINES_ALL              10493845   1922196            663  07/29/1999
01:16:09
SO_LAST_ALL

--Verifying Index Statistics

SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT",
LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL",
AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"
FROM DBA_INDEXES
WHERE owner = 'SH'
ORDER BY INDEX_NAME;

NAME                       NUM_ROWS DISTINCT LEAF_BLOCKS      CF   LEVEL   ALFBPKEY
-------------------------- -------- -------- ----------- ------- ------- ----------
CUSTOMERS_PK                  50000    50000         454    4405       2          1
PRODUCTS_PK                   10000    10000          90    1552       1          1
PRODUCTS_PROD_CAT_IX          10000        4          99    4422       1         24
PRODUCTS_PROD_SUBCAT_IX       10000       37         170    6148       2          4
SALES_PROD_BIX                 6287      909        1480    6287       1          1
SALES_PROMO_BIX                4727      459         570    4727       1          1

--Verifying Column Statistics

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME ="PA_EXPENDITURE_ITEMS_ALL"
ORDER BY COLUMN_NAME;

This returns the following data:

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS    DENSITY 
------------------------------ ------------ ---------- ----------- ---------- 
BURDEN_COST                            4300      71957           1 .000232558 
BURDEN_COST_RATE                        675    7376401           1 .001481481 
CONVERTED_FLAG                            1   16793903           1          1 
COST_BURDEN_DISTRIBUTED_FLAG              2      15796           1         .5 
COST_DISTRIBUTED_FLAG                     2          0           1         .5 
COST_IND_COMPILED_SET_ID                 87    6153143           1 .011494253 
EXPENDITURE_ID                      1171831          0           1 8.5337E-07 
TASK_ID                                8648          0           1 .000115634 
TRANSFERRED_FROM_EXP_ITEM_ID        1233787   15568891           1 8.1051E-07

---Verifying Histogram Statistics

SQL> SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE 
     FROM DBA_HISTOGRAMS 
     WHERE TABLE_NAME ="SO_LINES_ALL" AND COLUMN_NAME="S2" 
     ORDER BY ENDPOINT_NUMBER;

This query returns the following typical data:

ENDPOINT_NUMBER   ENDPOINT_VALUE
---------------  ---------------
          1365                 4
          1370                 5
          2124                 8
          2228                18

--Table and Index Stats

Table statistics can be gathered for the database, schema, table or partition.

EXEC DBMS_STATS.gather_database_stats;

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);

EXEC DBMS_STATS.gather_dictionary_stats;

--Index statistics can be gathered explicitly using the GATHER_INDEX_STATS procedure.

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);

The current statistics information is available from the data dictionary views for the specific objects (DBA, ALL and USER views). Some of these view were added in later releases.

DBA_TABLES
DBA_TAB_STATISTICS
DBA_TAB_PARTITIONS
DBA_TAB_SUB_PARTITIONS
DBA_TAB_COLUMNS
DBA_TAB_COL_STATISTICS
DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTIONS

Histogram information is available from the following views.

DBA_TAB_HISTOGRAMS
DBA_PART_HISTOGRAMS
DBA_SUBPART_HISTOGRAMS

Table, column and index statistics can be deleted using the relevant delete procedures.

EXEC DBMS_STATS.delete_database_stats;

EXEC DBMS_STATS.delete_schema_stats('SCOTT');

EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMP');

EXEC DBMS_STATS.delete_column_stats('SCOTT', 'EMP', 'EMPNO');

EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMP_PK');

EXEC DBMS_STATS.delete_dictionary_stats;

--System Stats

EXEC DBMS_STATS.gather_system_stats;

-- Manually start and stop to sample a representative time (several hours) of system activity.

EXEC DBMS_STATS.gather_system_stats('start');

EXEC DBMS_STATS.gather_system_stats('stop');

-- Sample from now until a specific number of minutes.

DBMS_STATS.gather_system_stats('interval', interval => 180);

--Locking Stats

To prevent statistics being overwritten, you can lock the stats at schema, table or partition level.

EXEC DBMS_STATS.lock_schema_stats('SCOTT');

EXEC DBMS_STATS.lock_table_stats('SCOTT', 'EMP');

EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');

If you need to replace the stats, they must be unlocked.

EXEC DBMS_STATS.unlock_schema_stats('SCOTT');

EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'EMP');

EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');

--Transfering Stats

EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');

EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');

EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');

--Creating Histograms

You generate histograms by using the DBMS_STATS package. You can generate histograms for columns of a table or partition. For example, to create a 10-bucket histogram on the SAL column of the emp table, issue the following statement:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('scott','emp', METHOD_OPT => 'FOR COLUMNS SIZE 10 sal');

Gather statistics during the day. Gathering ends after 720 minutes and is stored in the "mystats" table:

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'interval',interval => 720,stattab => 'mystats',statid => 'OLTP');
END;
/

Gather statistics during the night. Gathering ends after 720 minutes and is stored in the "mystats" table:

BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'interval',interval => 720,stattab => 'mystats',statid => 'OLAP');
END;
/

Where estimates vary greatly from the actual number of rows returned, the cursor is marked IS_REOPTIMIZIBLE
and will not be used again. The IS_REOPTIMIZIBLE attribute indicates that this SQL statement should be hard 
parsed on the next execution so the optimizer can use the execution statistics recorded on the initial execution to 
determine a better execution plan

select sql_id,child_number,sql_text,is_reoptimizable from v$sql where sql_id=''
select sql_id,child_number,sql_text,is_reoptimizable from v$sql where sql_text like '% %'

A SQL plan directive is also created, to ensure that the next time any SQL statement that uses similar predicates on the customers table is executed, the optimizer will be aware of the correlation among these columns. On the second execution the optimizer uses the statistics from the initial execution to determine a new plan that has a different join order. The use of statistics feedback in the generation of execution plan is indicated in the note section under the execution plan

The new plan is not marked IS_REOPTIMIZIBLE, so it will be used for all subsequent executions of this SQL statement.

New Reporting Subprograms in DBMS_STATS package Knowing when and how to gather statistics in a timely manner is critical to maintain acceptable performance on any system. Determining what statistics gathering operations are currently executing in an environment and how changes to the statistics methodology will impact the system can be difficult and time consuming. In Oracle Database 12c, new reporting subprograms have been added to the DBMS_STATS package to make it easier to monitor what statistics gathering activities are currently going on and what impact changes to the parameter settings of these operations will have. The DBMS_STATS subprograms are REPORT_STATS_OPERATIONS, REPORT_SINGLE_STATS_OPERATION and REPORT_GATHER_*_STATS. Figure 17 shows an example output from the REPORT_STATS_OPERATIONS function. The report shows detailed information about what statistics gathering operations have occurred, during a specified time window. It gives details on when each operation occurred, its status, and the number of objects covered and it can be displayed in either text or HTML format.

1. Understanding Optimizer Statistics with Oracle Database 12c Release 2 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf

2. Best Practices for Gathering Optimizer Statistics with Oracle Database 12c Release 2 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

3. Analytical SQL in Database 12c Release 2

http://www.oracle.com/technetwork/database/bi-datawarehousing/wp-sqlnaturallanguageanalysis-2431343.pdf

4. SQL Plan Management with Oracle Database 12c Release 2

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

5. Parallel Execution with Oracle Database 12c Fundamentals

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf

Thursday, December 31, 2020

Performing Database health checks

To Find out Blocking Sessions & Session Details

Saturday, February 22, 2020

Generating Explain Plan & Gather Statistics

Column Statistics

Index Statistics

---Statistics Gathering Procedures in the DBMS_STATS Package

Default Table and Index Values When Statistics are Missing

Verifying Table Statistics

---Verifying Histogram Statistics

SQL> SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE FROM DBA_HISTOGRAMS WHERE TABLE_NAME ="SO_LINES_ALL" AND COLUMN_NAME="S2" ORDER BY ENDPOINT_NUMBER; This query returns the following typical data:ENDPOINT_NUMBER ENDPOINT_VALUE --------------- --------------- 1365 4 1370 5 2124 8 2228 18

--Transfering Stats

--Creating Histograms

SQL> SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE FROM DBA_HISTOGRAMS WHERE TABLE_NAME ="SO_LINES_ALL" AND COLUMN_NAME="S2" ORDER BY ENDPOINT_NUMBER;
This query returns the following typical data:
ENDPOINT_NUMBER ENDPOINT_VALUE --------------- --------------- 1365 4 1370 5 2124 8 2228 18