Search This Blog

Total Pageviews

Saturday 26 May 2012


Oracle Lock and Enqueue info  ..... 








"enq: TX - row lock contention" due to lock 


col SQL_TEXT format a50
col OBJECT format a15
col EVENT format a35
SELECT sid, event, wait_time_micro / 1000 time_ms, blocking_session, object_type || ': ' || object_name object, sql_text
      FROM v$session s
      LEFT OUTER JOIN v$sql USING (sql_id)
      LEFT OUTER JOIN dba_objects  ON (object_id = row_wait_obj#)
      WHERE event LIKE 'enq: %';

   Sess
    ID  EVENT                                  TIME_MS BLOCKING_SESSION OBJECT          SQL_TEXT
------- ----------------------------------- ---------- ---------------- --------------- --------------------------------------------------
     37 enq: TX - row lock contention        2,601,852               17 TABLE: ANUJ     update anuj set sal=1001 where EMPNO=7369



WITH sql_app_waits AS
        (SELECT sql_id, SUBSTR(sql_text, 1, 80) sql_text,application_wait_time/1000 app_time_ms,elapsed_time,
                ROUND(application_wait_time * 100 / elapsed_time, 2) app_time_pct,
                ROUND(application_wait_time * 100 /SUM(application_wait_time) OVER (), 2) pct_of_app_time,
                RANK() OVER (ORDER BY application_wait_Time DESC) ranking
         FROM v$sql
         WHERE elapsed_time > 0 AND application_wait_time>0 )
  SELECT sql_text, app_time_ms, app_time_pct,pct_of_app_time
   FROM sql_app_waits
   WHERE ranking <= 10
   ORDER BY ranking  ;


SQL_TEXT                                           APP_TIME_MS APP_TIME_PCT PCT_OF_APP_TIME
-------------------------------------------------- ----------- ------------ ---------------
update anuj set sal=1001 where EMPNO=7369           2625757.49          100             100



select distinct a.sid "waiting sid",d.sql_text "waiting SQL",a.ROW_WAIT_OBJ# "locked object",a.BLOCKING_SESSION "blocking sid",c.sql_text "SQL from blocking 
session" from v$session a, v$active_session_history b, v$sql c, v$sql d
where
a.sql_id=d.sql_id
and a.blocking_session=b.session_id
and c.sql_id=b.sql_id
and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#
and b.CURRENT_FILE#= a.ROW_WAIT_FILE#
and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#


WITH ash_query AS (
         SELECT substr(event,6,2) lock_type,program, h.module, h.action, object_name,
                SUM(time_waited)/1000 time_ms, COUNT( * ) waits, username, sql_text,
                RANK() OVER (ORDER BY SUM(time_waited) DESC)    AS time_rank,
                ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))  OVER (), 2) pct_of_time
          FROM  v$active_session_history h
         JOIN  dba_users u  USING (user_id)
         LEFT OUTER JOIN dba_objects o
              ON (o.object_id = h.current_obj#)
         LEFT OUTER JOIN v$sql s USING (sql_id)
        WHERE event LIKE 'enq: %'
        GROUP BY substr(event,6,2) ,program, h.module, h.action,object_name,  sql_text, username)
 SELECT lock_type,module, username,  object_name, time_ms, pct_of_time, sql_text
 FROM ash_query
 WHERE time_rank < 11
 ORDER BY time_rank;

                           OBJ NAME or
LOCK MODULE       Username TRANS_ID        TIME_MS PCT_OF_TIME SQL_TEXT
---- ------------ -------- ------------ ---------- ----------- --------------------------------------------------
PR                SYS      KET$_AUTOTAS      2,165      100.00 SELECT INSTANCE_NAME, HOST_NAME, NVL(GVI_STARTUP_T
                                                               IME, SYSTIMESTAMP) - INTERVAL '1' SECOND AS SHUTDO
                                                               WN_TIME FROM (SELECT RRI.INSTANCE_NAME AS INSTANCE
                                                               _NAME, RRI.HOST_NAME AS HOST_NAME, FROM_TZ(RRI.STA
                                                               RTUP_TIME, '+00:00') AS RRI_STARTUP_TIME, DBMS_HA_
                                                               ALERTS_PRVT.INSTANCE_STARTUP_TIMESTAMP_TZ(GVI.STAR
                                                               TUP_TIME) AS GVI_STARTUP_TIME FROM RECENT_RESOURCE
                                                               _INCARNATIONS$ RRI LEFT OUTER JOIN GV$INSTANCE GVI
                                                                ON GVI.INSTANCE_NAME = RRI.RESOURCE_NAME WHERE RR
                                                               I.RESOURCE_TYPE = 'INSTANCE' AND :B2 = RRI.DB_UNIQ
                                                               UE_NAME AND :B1 = RRI.DB_DOMAIN) WHERE GVI_STARTUP
                                                               _TIME IS NULL OR GVI_STARTUP_TIME > RRI_STARTUP_TI
                                                               ME GROUP BY INSTANCE_NAME, HOST_NAME, GVI_STARTUP_
                                                               TIME

TX   SQL*Plus     ANUJ     ANUJ                  0         .00 update anuj set sal=1001 where EMPNO=7369





col USERNAME for a20 
col OBJECT_NAME for a20
col SQL_TEXT for a70 wrap
col MODULE for a15
WITH ash_query AS (
     SELECT substr(event,6,2) lock_type,program, 
            h.module, h.action,   object_name,
            SUM(time_waited)/1000 time_ms, COUNT( * ) waits, 
            username, sql_text,
            RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
            ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited)) 
                OVER (), 2)             pct_of_time
      FROM  gv$active_session_history h 
      JOIN  dba_users u  USING (user_id)
      LEFT OUTER JOIN dba_objects o
           ON (o.object_id = h.current_obj#)
      LEFT OUTER JOIN gv$sql s USING (sql_id)
     WHERE event LIKE 'enq: %'
     GROUP BY substr(event,6,2) ,program, h.module, h.action,     object_name,  sql_text, username)
SELECT lock_type,module, username,  object_name, time_ms,pct_of_time,
         sql_text
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;



col sid format a10
 WITH sessions AS
    (SELECT /*+materialize*/    sid, blocking_session, row_wait_obj#, sql_id    FROM v$session)
    SELECT LPAD(' ', LEVEL*3 ) || sid sid, object_name,substr(sql_text,1,40) sql_text
    FROM sessions s
    LEFT OUTER JOIN dba_objects  ON (object_id = row_wait_obj#)
    LEFT OUTER JOIN v$sql USING (sql_id)
    WHERE sid IN (SELECT blocking_session FROM sessions)  OR blocking_session IS NOT NULL
    CONNECT BY PRIOR sid = blocking_session
    START WITH blocking_session IS NULL;

Sess       OBJ NAME or
ID         TRANS_ID     SQL_TEXT
---------- ------------ ----------------------------------------------------------------------
 17
  37       ANUJ         update anuj set sal=1001 where EMPNO=736



 

Oracle Date Calculations ....


Oracle Date Calculations .... 


Oracle Date's Maths with Anuj
Oracle Date Calculations ....
Oracle Date's Maths with Anuj
Oracle Date's Maths .....
Oracle Date Calculations ....

Oracle Date's Maths with Anuj
Oracle Date Calculations ....
Oracle Date's Maths with Anuj
Oracle Date's Maths .....
Oracle Date Calculations ....



select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS') ssysdate ,TO_CHAR(sysdate -1,'DD-MM-YYYY HH24:MI:SS') "ssysdate-1" from dual;

SSYSDATE            ssysdate-1
------------------- -------------------
26-05-2012 16:45:20 25-05-2012 16:45:20



so " -1 " will subtract 1 day from sysdate .

suppose we want to subtract 1 minute , then we have to convert 1day into 1 minute

by dividing by ( 1day = 24 hr ,1hr = 60 min )


1/(24*60)

or

SQL> select 24*60 from dual;

24*60
----------
1440


SQL> select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS')     "ssysdate" ,
    TO_CHAR(sysdate -1,'DD-MM-YYYY HH24:MI:SS')          "SSYSDATE-1day",
    TO_CHAR(sysdate -1/(24*60),'DD-MM-YYYY HH24:MI:SS')  "SSYSDATE-1minute",
    TO_CHAR(sysdate -1/1440,'DD-MM-YYYY HH24:MI:SS')     "SSYSDATE-1minute",
    TO_CHAR(sysdate -10/1440,'DD-MM-YYYY HH24:MI:SS')    "SSYSDATE-10minute" 
    from dual; 

ssysdate            SSYSDATE-1day       SSYSDATE-1minute    SSYSDATE-1minute    SSYSDATE-10minute
------------------- ------------------- ------------------- ------------------- -------------------
26-05-2012 16:45:50 25-05-2012 16:45:50 26-05-2012 16:44:50 26-05-2012 16:44:50 26-05-2012 16:35:50




Subtract 1 Second 

SQL> select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS') ssysdate,TO_CHAR(sysdate -1/(24*60*60),'DD-MM-YYYY HH24:MI:SS') "SSYSDATE-1 second" from dual; 


SSYSDATE            SSYSDATE-1 second
------------------- -------------------
26-05-2012 16:46:15 26-05-2012 16:46:14



select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') ssysdate ,to_char(sysdate + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from dual;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
26-may-2012 04:46:36 26-may-2012 05:46:36



 select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') ssysdate ,to_char(sysdate + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from dual;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
26-may-2012 04:47:07 26-may-2012 05:47:07




SQL> create table ddate ( x date );

Table created.

SQL> insert into ddate values ('1-jan-11') ;

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(x,'dd-mon-yyyy hh:mi:ss') ssysdate , to_char(x + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from ddate;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
01-jan-2011 12:00:00 01-jan-2011 01:00:00




 set linesize 300
 ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
 
 SELECT SYSDATE             as current_date,
       SYSDATE - 1          as minus_1_day,
       SYSDATE - 2/24       as minus_2_hours,
       SYSDATE - 10/24/60   as minus_10_minutes,
       SYSDATE - 1/24/60    as minus_1_minutes,
       SYSDATE  - 30/24/60/60 as minus_30_seconds
FROM   dual;
 
CURRENT_DATE         MINUS_1_DAY          MINUS_2_HOURS        MINUS_10_MINUTES     MINUS_1_MINUTES      MINUS_30_SECONDS
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
05-FEB-2021 07:14:10 04-FEB-2021 07:14:10 05-FEB-2021 05:14:10 05-FEB-2021 07:04:10 05-FEB-2021 07:13:10 05-FEB-2021 07:13:40




===

 

 
 set linesize 300 
 SELECT 
   TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS A.M.') AS "Uppercase1"
   , TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS a.m.') AS "Lowercase1"
   , TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS A.m.') AS "Mixed1"
  ,  TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS A.M.') AS "Uppercase"
  , TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS a.m.') AS "Lowercase"
 , TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS A.m.') AS "Mixed"
FROM DUAL;



Uppercase1    Lowercase1    Mixed1        Uppercase                Lowercase                Mixed
------------- ------------- ------------- ------------------------ ------------------------ ------------------------
06:30:45 P.M. 06:30:45 p.m. 06:30:45 P.M. 2020-12-30 06:30:45 P.M. 2020-12-30 06:30:45 p.m. 2020-12-30 06:30:45 P.M.

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> 
select sysdate , sysdate-1/24,sysdate-.5/24,sysdate-.25/24 from dual;

SYSDATE             SYSDATE-1/24        SYSDATE-.5/24       SYSDATE-.25/24
------------------- ------------------- ------------------- -------------------
26-06-2023 05:34:38 26-06-2023 04:34:38 26-06-2023 05:04:38 26-06-2023 05:19:38

SQL>

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

select to_char(sysdate+n,'DAY','nls_date_language=ENGLISH') day
     from (select level n from dual connect by level <= 7)
    /

DAY
------------------------------------
WEDNESDAY
THURSDAY
FRIDAY
SATURDAY
SUNDAY
MONDAY
TUESDAY





Oracle Lock info In RAC ..... 



for session id in sqlplus
col sid format a12
select sys_context('USERENV','SID') as sid from dual


====================================================
col  Kill_Command format a35
SELECT gvh.inst_id Locking_Inst, gvh.SID Locking_Sid, gvs.serial# Locking_Serial, gvs.status Status,
-- gvs.module Module, 
gvw.inst_id Waiting_Inst, gvw.SID Waiter_Sid,
       DECODE(gvh.TYPE, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-') Waiter_Lock_Type,
       DECODE(gvw.request, 0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                              'Nothing-') Waiter_Mode_Req ,
       'alter system kill session '|| '''' || gvh.SID || ',' || gvs.serial# || ''' immediate;' "Kill_Command"
FROM gv$lock gvh, gv$lock gvw, gv$session gvs
WHERE (gvh.id1, gvh.id2) IN (
                              SELECT id1, id2 FROM gv$lock WHERE request=0
                              INTERSECT
                              SELECT id1, id2 FROM gv$lock WHERE lmode=0
                             )
  AND gvh.id1=gvw.id1
  AND gvh.id2=gvw.id2
  AND gvh.request=0
  AND gvw.lmode=0
  AND gvh.SID=gvs.SID
  AND gvh.inst_id=gvs.inst_id ;
 
LOCKING_INST LOCKING_SID LOCKING_SERIAL STATUS   WAITING_INST WAITER_SID WAITER_LOCK_TYPE      WAITER_MODE_REQ     Kill_Command
------------ ----------- -------------- -------- ------------ ---------- --------------------- ------------------- -----------------------------------
           1          17              5 INACTIVE            1         37 Transaction           Exclusive           alter system kill session '17,5' im
                                                                                                                   mediate;
 
 

Oracle DBA

anuj blog Archive