Home » RDBMS Server » Performance Tuning » Checking Lock history
Checking Lock history [message #605639] Mon, 13 January 2014 00:17 Go to next message
VIP2013
Messages: 91
Registered: June 2013
Member
Oracle version:Release 11.2.0.3.0. Its a two node RAC.

We want to monitor the historical(last one day) locks in DB and respective sql statements and object. So we are using below sql.
Its grouping (blocking_session,user_id,program) to know the severity(column cpt) of the specific blocking session at that point in time, so that we will debug more into that to remediate the issue or avoid future occurrence.

So here, what should be the ideal value of 'cpt' for considering the locking as a issue/concern?

SELECT *
  FROM (SELECT a.sql_id,
               a.sample_time,
               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,
               (SELECT object_name
                FROM dba_objects
                WHERE object_id = a.current_obj#
                 AND ROWNUM < 2) object_name,
                 (SELECT dbms_ROWID.ROWID_create (
                                        1,
                                        o.data_object_id,
                                        current_file#,
                                        current_block#,
                                        current_row#
                                        ) row_id 
                FROM dba_objects o 
                WHERE data_object_id=a.current_obj# and rownum<2
                )
                 BLOCKING_SESSION_STATUS,
                  decode(session_state, 'WAITING','Waiting',
                        'Working') state,
               a.user_id,
               a.program,
               s.sql_text
          FROM sys.dba_hist_ACTIVE_SESS_HISTORY a, sys.dba_hist_sqltext s
         WHERE     a.sql_id = s.sql_id
               AND blocking_session_serial# <> 0
               AND a.user_id <> 0
           AND a.sample_time >= TO_DATE (TRIM ('1-jan-2014 00:00:00'), 'dd-mon-yyyy hh24:mi:ss')
           AND a.sample_time <= TO_DATE (TRIM ('12-jan-2014 23:00:00 '), 'dd-mon-yyyy hh24:mi:ss')
ORDER BY a.sample_time)
WHERE rn = 1

Re: Checking Lock history [message #605685 is a reply to message #605639] Mon, 13 January 2014 09:46 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So here, what should be the ideal value of 'cpt' for considering the locking as a issue/concern?
why are you looking for a problem that does not exist; since no error is being thrown?
Locks are designed into the database & the fact that they occur does not indicate any problem which needs to be solved.

It appears you suffer from Compulsive Tuning Disorder.
Previous Topic: Tuning suggestions on query
Next Topic: Performance issue
Goto Forum:
  


Current Time: Fri Mar 29 09:14:58 CDT 2024