OraFAQ Forum: Performance Tuning » How long lock should be in order to take snapshot of locks

Home » RDBMS Server » Performance Tuning » How long lock should be in order to take snapshot of locks (11.2.0.4, windows 2012 SE)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How long lock should be in order to take snapshot of locks [message #659625]

Tue, 24 January 2017 19:03

juniordbanewbie
Messages: 250
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;

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

juniordbanewbie
Messages: 250
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

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

juniordbanewbie
Messages: 250
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.

Report message to a moderator

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

John Watson
Messages: 8964
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.

Report message to a moderator

Re: How long lock should be in order to take snapshot of locks [message #668127 is a reply to message #659636]

Thu, 08 February 2018 02:49

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

thanks for your solution.

Report message to a moderator

Previous Topic:	Can we delete specific execution plan in the db buffer for a statement
Next Topic:	Partitioning in table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 18:28:42 CST 2025