Home » RDBMS Server » Performance Tuning » How long lock should be in order to take snapshot of locks (11.2.0.4, windows 2012 SE)
How long lock should be in order to take snapshot of locks [message #659625] Tue, 24 January 2017 19:03 Go to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear Sir/Mdm,

as our customer is using SE, there's no way we can have historical information of locks. every time they ask me to look at locking issues, the issue is already gone.

DBA_HIST_ACTIVE_SESS_HISTORY is not populated

there's no equivalent in STATSPACK as well.

from http://www.orafaq.com/node/854, I'm thinking of taking snapshot of v$lock, v$process, dba_objects.

I'm intending to modify statspack package script and install it as another schema.

based on https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2027.htm#REFRN30121

CTIME came close to the time the lock is held, what should this value be to qualify taking snapshot of the lock?

code is like


PROCEDURE snap_latch IS

    /*  Capture Latch details statistics */

     begin

         insert into stats$latch_children
              ( snap_id
              , dbid
              , instance_number
              , latch#
              , child#
              , gets
              , misses
              , sleeps
              , immediate_gets
              , immediate_misses
              , spin_gets
              , wait_time
              )
         select l_snap_id
              , p_dbid
              , p_instance_number
              , latch#
              , child#
              , gets 
              , misses
              , sleeps
              , immediate_gets
              , immediate_misses
              , spin_gets
              , wait_time
           from v$latch_children;

Re: How long lock should be in order to take snapshot of locks [message #659626 is a reply to message #659625] Tue, 24 January 2017 19:37 Go to previous messageGo to next message
BlackSwan
Messages: 25715
Registered: January 2009
Location: SoCal
Senior Member
juniordbanewbie wrote on Tue, 24 January 2017 17:03
Dear Sir/Mdm,

as our customer is using SE, there's no way we can have historical information of locks. every time they ask me to look at locking issues, the issue is already gone.

post full results from SQL below

SELECT * FROM V$VERSION;


One of two realities should exist.
1) lock error gets logged to application log file
2) lock error gets logged to alert_SID.log file

to find & fix this issue you need to know which SQL was active when the error occurs & you need the error code & message
You need to decide how you are going to capture these two necessary details.
Let us know your plan.
Re: How long lock should be in order to take snapshot of locks [message #659628 is a reply to message #659626] Tue, 24 January 2017 22:17 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear Black Swan,


SELECT * FROM v$version;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production                       
PL/SQL Release 11.2.0.4.0 - Production                                          
CORE	11.2.0.4.0	Production                                                      
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production                         
NLSRTL Version 11.2.0.4.0 - Production

My plan:

Modifying existing spctab.sql and spcpkg.sql

Modify function snap to include procedure snap_lock (my own procedure) and of course rename STATS$ to AUX$. This is make sure the public synonym don't get overwritten by my own script. At the same time I remove those procedures or functions that are not required.

which SQL was active when the error occurs & you need the error code & message
=>how do I know which sql is active. If it is present in v$sql, does it means that it is active
regarding the error, my customer has yet to give details on this, he just show me

EM Alert: Clear:<service_name> - Session 276 is blocking other sessions

as the alert_SID log is not archived it is hard for me to search, so what I did is use adrci similar to http://docs.oracle.com/cd/E11882_01/server.112/e22490/adrci.htm#SUTIL1475



SHOW ALERT -P "MESSAGE_TEXT LIKE '%blocking other sessions%'"

Maybe I was wrong on searching the message.
correct if my customer need to give me more details on the error code

Re: How long lock should be in order to take snapshot of locks [message #659629 is a reply to message #659628] Tue, 24 January 2017 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 25715
Registered: January 2009
Location: SoCal
Senior Member
>EM Alert: Clear:<service_name> - Session 276 is blocking other sessions

"The sky is falling!"

What evidence exists that any application ERROR really exists?

Turn off EM & no more problems exist.
EM is not required to start, run or manage any Oracle database.
Re: How long lock should be in order to take snapshot of locks [message #659632 is a reply to message #659629] Tue, 24 January 2017 23:58 Go to previous messageGo to next message
juniordbanewbie
Messages: 169
Registered: April 2014
Senior Member
Dear Black Swan,

he mentioned that because of this blocking session, application runs slow.

What evidence exists that any application ERROR really exists?
=>no evidence at the moment

TURN OFF EM is not an option because my customer insist on using EM to schedule rman backup.
Re: How long lock should be in order to take snapshot of locks [message #659636 is a reply to message #659625] Wed, 25 January 2017 02:24 Go to previous message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
You could use DBMS_SERVER_ALERT.SET_THRESHOLD to set a threshold for the metric BLOCKED_USERS and the various ENQUEUE_% metrics. The alert message details should tell you what is going on.
Previous Topic: Tuning update query - for more than 3 billion rows
Next Topic: How to identify index type for partitioned tables
Goto Forum:
  


Current Time: Sat Nov 18 04:21:32 CST 2017

Total time taken to generate the page: 0.01496 seconds