Category: SQL Scripts For DBA

Long Running Operations Monitoring ( V$SESSION_LONGOPS ) in Oracle Database

List operations remaining time > 10 minutes :

SQL>select 
           username,sid, opname, target, sofar, totalwork, units, 
           to_char(start_time,'YYYYMMDD-HH24:MI:SS') StartTime, 
           time_remaining, message  
      from 
           V$SESSION_LONGOPS 
      where 
           time_remaining>600;


USERNAME     SID    OPNAME     TARGET     SOFAR   TOTALWORK  UNITS    STARTTIME         TIME_REMAINING MESSAGE

------------ ------ ---------- ---------- ------- ---------- ------- ----------------- -------------- ---------------------------------------------------------
 TETSUSER    5704   Table Scan TETSUSER.TEST  22414  39677      Blocks    20180920-10:55:49        3676  Table Scan:  JAMES.TEST: 22414 out  of 39677 Blocks done

How to Create SQL Plan Baseline from Cursor Cache

Here is an example of how to create SQL plan baseline on a  12.1 Oracle database.

1)Get all the details of the sql:

SQL> select SQL_ID,HASH_VALUE,CHILD_NUMBER,PLAN_HASH_VALUE,
            EXECUTIONS,ELAPSED_TIME/EXECUTIONS/1000000 Sec_per_exec 
      from  v$sql 
      where sql_id='gjpdb42w841yt' 
      order by ELAPSED_TIME/EXECUTIONS/1000000;

SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS SEC_PER_EXEC
------------- ---------- ------------ --------------- ---------- ------------
gjpdb42w841yt 3095529433 0            3153495478      26         .25 
gjpdb42w841yt 3095529433 3            3153495478      44        2.46 
gjpdb42w841yt 3095529433 1            3153495478       1        7.07 
gjpdb42w841yt 3095529433 2            1409003088      58     1831.29

2) For 12.1, there are following options for 12.1 database. choose one suitable to your situation.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_text          IN  CLOB,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_handle        IN  VARCHAR2,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   attribute_name   IN VARCHAR2,
   attribute_value  IN VARCHAR2,
   fixed            IN VARCHAR2 := 'NO',
   enabled          IN VARCHAR2 := 'YES')
  RETURN PLS_INTEGER;

3) Create a sql baseline for sql_id=’gjpdb42w841yt’ and PLAN_HASH_VALUE=3153495478  .

SQL>var cnt number
SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
     sql_id => 'gjpdb42w841yt', -
     plan_hash_value =>'3153495478');

4) To let newly created sql baseline be used immediately, purge the sql cursor from shared pool as per How To Flush an Object Out The Library Cache by Using DBMS_SHARED_POOL Package.

5) Check the sql baseline :

SQL> select SQL_HANDLE,substr(SQL_TEXT,1,30),ENABLED,
            ACCEPTED,FIXED,REPRODUCED,AUTOPURGE 
      from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE            SUBSTR(SQL_TE ENA   ACC  FIX  REP AUT
--------------------- ------------  ---  ---- ---- ---- ---
SQL_29078efcd1d51d34  select * from YES   YES  NO   YES YES

6) Check the execution plan for this baseline.

SET LONG 10000
SET LINES 200
SET PAGES 300
SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=> 
'SQL_PLAN_2k1wfzm8xa79n2943fd10'));

or

SET LONG 10000 
SET LINES 200 
SET PAGES 300 
SELECT * 
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>
'SQL_29078efcd1d51d34'));

SQL Script to Format Library Cache Lock/pin wait event p3 value

CREATE OR REPLACE PROCEDURE lbc_p3(P3 number)
is

-- purpose: format p3 value (maybe p3 value large than 100,000,000)
-- author: anbob.com(weejar@gmail.com)
-- date: 2016-5-1
-- note: 11.2 tested

v_hex varchar2(50);
v_hexoid varchar2(50);
v_oid number;
v_namespace number;
v_mode varchar2(50);
begin
select to_char(p3,'xxxxxxxxxxxxxxxxxxxxx') into v_hex from dual;
-- object id
select to_number(substr(v_hex,1,length(v_hex)-8),'xxxxxxxxxxxxxx') into v_oid from dual;
-- namespace
select to_number(substr(v_hex,-8,4),'xxxxxxxxxxxxxx') into v_namespace  from dual;
-- mode
select decode(to_number(substr(v_hex,-4),'xxxxxxxxxxxxxx'),3,'exclusive mode',2,'share mode',1,'null mode',0,'no lock/pin held','unknown') into v_mode  from dual;
dbms_output.put_line('---------------------------------------------');
dbms_output.put_line(lpad('Library cache P3 value: ',50,'.')||p3);
dbms_output.put_line(lpad('Library cache P3 value HEX: ',50,'.')||ltrim(v_hex));
dbms_output.put_line(lpad('Object id: ',50,'.')||v_oid);
dbms_output.put_line(lpad('Namespace: ',50,'.')||v_namespace );
dbms_output.put_line(lpad('RequestMode: ',50,'.')||v_mode);
dbms_output.put_line('Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp=''NAMESPACE'' and indx='||v_namespace );
end;
/



SQL> set serveroutput on
SQL> exec lbc_p3(1571747577004035);
---------------------------------------------
..........................Library cache P3 value: 1571747577004035
......................Library cache P3 value HEX: 5957f00010003
.......................................Object id: 365951
.......................................Namespace: 1
.....................................RequestMode: exclusive mode
Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp='NAMESPACE' and indx=1

PL/SQL procedure successfully completed.

SQL> exec lbc_p3(1571747577004034);
---------------------------------------------
..........................Library cache P3 value: 1571747577004034
......................Library cache P3 value HEX: 5957f00010002
.......................................Object id: 365951
.......................................Namespace: 1
.....................................RequestMode: share mode
Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst  WHERE kglsttyp='NAMESPACE' and indx=1

PL/SQL procedure successfully completed.

To Get Object Name and Type From File# and Block#

Given file id and block id, the object details can be queried by following SQL query.

This example shows the object is located on file# = 1 , block # = 68619. 

SQL>select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME 
      from  dba_extents 
     where FILE_ID=1 and 
     68619 BETWEEN block_id AND ( block_id + blocks);

OWNER    SEGMENT_NAME    SEGMENT_TYPE   TABLESPACE_NAME
-------- --------------- -------------- ----------------------------
SYS      C_OBJ#_INTCOL#  CLUSTER        SYSTEM

How to Check Oracle Database Current Number of Sessions / Processes and Maximum Utilization

1)From V$LICENSE View. It shows USER sessions only excluding database system sessions.

SQL> select SESSIONS_CURRENT,SESSIONS_HIGHWATER from v$license;

SESSIONS_CURRENT SESSIONS_HIGHWATER 
---------------- ------------------ 
 771             1346 

SESSIONS_CURRENT  : Current number of concurrent user sessions
SESSIONS_HIGHWATER: Highest number of concurrent user sessions since the instance started.

2) From v$resource_limit view. It shows both users and database system sessions.

SQL> select 
            RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE 
     from 
            v$resource_limit 
     where 
            RESOURCE_NAME in ('processes','sessions');

RESOURCE_NAME  CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
-------------- ------------------- --------------- ---------------
processes      842                 1414            4040
sessions       865                 1439            6096

CURRENT_UTILIZATION : Number of (resources, locks, or processes) 
                         currently being used.
    MAX_UTILIZATION : Maximum consumption of this resource since 
                     the last instance start-up

3)From AWR.

col STARTT format a20
col FINISHH format a20
col RESOURCE_NAME format a13

SQL>select     
           a.snap_id SNAP_ID,
           to_char(b.begin_interval_time,'YYYYMMDD-HH24:MI:SS')  STARTT,
           to_char(b.end_interval_time,'YYYYMMDD-HH24:MI:SS') FINISHH,
           a.resource_name,
           a.CURRENT_UTILIZATION,
           a.max_utilization ,
           a.INITIAL_ALLOCATION,
           a.LIMIT_VALUE
from       
           DBA_HIST_RESOURCE_LIMIT a, 
           DBA_HIST_SNAPSHOT b
where      
          ( a.resource_name like '%process%' or a.resource_name like '%session%')
and        a.snap_id=b.snap_id
and         a.instance_number=b.instance_number
and         a.instance_number=1
and         b.begin_interval_time > sysdate - 2
order by
         a.snap_id,
         a.resource_name;

SNAP_ID    STARTT               FINISHH              RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
---------- -------------------- -------------------- ------------- ------------------- --------------- ---------- ----------
 63141     20171214-10:00:13    20171214-11:00:15    processes     845 1414 4040 4040
 63141     20171214-10:00:13    20171214-11:00:15    sessions      869 1439 6096 6096
 63142     20171214-11:00:15    20171214-12:00:18    processes     847 1414 4040 4040
 63142     20171214-11:00:15    20171214-12:00:18    sessions      871 1439 6096 6096
 63143     20171214-12:00:18    20171214-13:00:21    processes     845 1414 4040 4040
 63143     20171214-12:00:18    20171214-13:00:21    sessions      870 1439 6096 6096
 63144     20171214-13:00:21    20171214-14:00:23    processes     842 1414 4040 4040
 63144     20171214-13:00:21    20171214-14:00:23    sessions      866 1439 6096 6096
 63145     20171214-14:00:23    20171214-15:00:26    processes     843 1414 4040 4040
 63145     20171214-14:00:23    20171214-15:00:26    sessions      869 1439 6096 6096
 63146     20171214-15:00:26    20171214-16:00:29    processes     852 1414 4040 4040
 63146     20171214-15:00:26    20171214-16:00:29    sessions      879 1439 6096 6096
 63147     20171214-16:00:29    20171214-17:00:32    processes     849 1414 4040 4040
 63147     20171214-16:00:29    20171214-17:00:32    sessions      873 1439 6096 6096
 63148     20171214-17:00:32    20171214-18:00:35    processes     848 1414 4040 4040
 63148     20171214-17:00:32    20171214-18:00:35    sessions      872 1439 6096 6096
 63149     20171214-18:00:35    20171214-19:00:02    processes     840 1414 4040 4040
 63149     20171214-18:00:35    20171214-19:00:02    sessions      864 1439 6096 6096
 63150     20171214-19:00:02    20171214-20:00:05    processes     841 1414 4040 4040
 63150     20171214-19:00:02    20171214-20:00:05    sessions      865 1439 6096 6096
 63151     20171214-20:00:05    20171214-21:00:08    processes     845 1414 4040 4040
 63151     20171214-20:00:05    20171214-21:00:08    sessions      871 1439 6096 6096
 63152     20171214-21:00:08    20171214-22:00:12    processes     850 1414 4040 4040
 63152     20171214-21:00:08    20171214-22:00:12    sessions      875 1439 6096 6096
 63153     20171214-22:00:12    20171214-23:00:15    processes     854 1414 4040 4040
 63153     20171214-22:00:12    20171214-23:00:15    sessions      880 1439 6096 6096
 63154     20171214-23:00:15    20171215-00:00:17    processes     849 1414 4040 4040
 63154     20171214-23:00:15    20171215-00:00:17    sessions      873 1439 6096 6096
 63155     20171215-00:00:17    20171215-01:00:21    processes     851 1414 4040 4040
 63155     20171215-00:00:17    20171215-01:00:21    sessions      877 1439 6096 6096
 63156     20171215-01:00:21    20171215-02:00:24    processes     837 1414 4040 4040
 63156     20171215-01:00:21    20171215-02:00:24    sessions      861 1439 6096 6096
 63157     20171215-02:00:24    20171215-03:00:27    processes     847 1414 4040 4040
 63157     20171215-02:00:24    20171215-03:00:27    sessions      872 1439 6096 6096
 63158     20171215-03:00:27    20171215-04:00:01    processes     846 1414 4040 4040
 63158     20171215-03:00:27    20171215-04:00:01    sessions      872 1439 6096 6096
 63159     20171215-04:00:01    20171215-05:00:04    processes     841 1414 4040 4040
 63159     20171215-04:00:01    20171215-05:00:04    sessions      865 1439 6096 6096
 63160     20171215-05:00:04    20171215-06:00:06    processes     839 1414 4040 4040
 63160     20171215-05:00:04    20171215-06:00:06    sessions      864 1439 6096 6096
 63161     20171215-06:00:06    20171215-07:00:09    processes     847 1414 4040 4040
 63161     20171215-06:00:06    20171215-07:00:09    sessions      871 1439 6096 6096
 63162     20171215-07:00:09    20171215-08:00:12    processes     843 1414 4040 4040
 63162     20171215-07:00:09    20171215-08:00:12    sessions      866 1439 6096 6096
 63163     20171215-08:00:12    20171215-09:00:14    processes     852 1414 4040 4040
 63163     20171215-08:00:12    20171215-09:00:14    sessions      878 1439 6096 6096

46 rows selected.

4) From OEM Repository.

 col COLUMN_LABEL format a30
 col MINIMUM format 99.99
 col MAXIMUM format 99.99
 col AVERAGE format 99.99

SQL>select 
           to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL, 
           MINIMUM,MAXIMUM,AVERAGE 
    from   MGMT$METRIC_DAILY 
   where   TARGET_NAME='RACTEST' 
     and   COLUMN_LABEL in ('Session Limit Usage (%)') 
   order by 
           ROLLUP_TIMESTAMP,
           COLUMN_LABEL;

TO_CHAR(RO COLUMN_LABEL                   MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Session Limit Usage (%)        2.87    6.40    5.22
2017-07-16 Session Limit Usage (%)        6.45   14.57   13.67
2017-07-17 Session Limit Usage (%)        7.96   14.40   14.07
2017-07-18 Session Limit Usage (%)       13.99   14.71   14.34
2017-07-19 Session Limit Usage (%)       14.14   14.94   14.63
2017-07-20 Session Limit Usage (%)       14.55   15.04   14.80
...
..
.
2017-12-04 Session Limit Usage (%)       14.88   15.88   15.47
2017-12-05 Session Limit Usage (%)       14.45   15.16   14.80
2017-12-06 Session Limit Usage (%)       13.99   14.98   14.58
2017-12-07 Session Limit Usage (%)       13.86   14.32   14.09
2017-12-08 Session Limit Usage (%)       13.88   14.37   14.12
2017-12-09 Session Limit Usage (%)       13.80   14.40   14.04
2017-12-10 Session Limit Usage (%)       13.91   14.44   14.06
2017-12-11 Session Limit Usage (%)       13.88   14.35   14.07
2017-12-12 Session Limit Usage (%)       13.96   14.30   14.12
2017-12-13 Session Limit Usage (%)       13.98   14.44   14.18
2017-12-14 Session Limit Usage (%)       14.09   14.42   14.24

--
--

SQL> select 
            to_char(ROLLUP_TIMESTAMP,'YYYY-MM-DD'), COLUMN_LABEL, 
            MINIMUM,MAXIMUM,AVERAGE 
       from MGMT$METRIC_DAILY 
      where TARGET_NAME='RACTEST' 
        and COLUMN_LABEL in ('Process Limit Usage (%)') 
   order by ROLLUP_TIMESTAMP,
            COLUMN_LABEL;

TO_CHAR(RO COLUMN_LABEL                   MINIMUM MAXIMUM AVERAGE
---------- ------------------------------ ------- ------- -------
2017-07-15 Process Limit Usage (%)        3.76    9.16    7.33
2017-07-16 Process Limit Usage (%)        9.21   21.31   20.00
2017-07-17 Process Limit Usage (%)       11.34   21.09   20.60
2017-07-18 Process Limit Usage (%)       20.50   21.49   21.00
2017-07-19 Process Limit Usage (%)       20.74   21.83   21.44
2017-07-20 Process Limit Usage (%)       21.36   22.00   21.71
...
..
.
2017-12-09 Process Limit Usage (%)       20.25   20.99   20.58
2017-12-10 Process Limit Usage (%)       20.40   21.16   20.61
2017-12-11 Process Limit Usage (%)       20.37   21.01   20.63
2017-12-12 Process Limit Usage (%)       20.47   20.97   20.70
2017-12-13 Process Limit Usage (%)       20.52   21.06   20.78
2017-12-14 Process Limit Usage (%)       20.72   21.11   20.89