This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Aug 29, 2013 9:28 PM by yxes2013 RSS

Database Locking

yxes2013 Newbie
Currently Being Moderated

Hi All,

 

11.2.0.1

 

I am still resolving the locking issues in our database

Often the delete is blocked by some transaction.

 

The operations said that it is intermittent. Last night they were able to delete the 1M transactions without the locking.

But the past days lock often occurred, and they have to bounced the database to release the locks.

 

I there a way I can compare what happened last night why the batch was able to delete smoothly , and compare it against the other night where the delete was blocked?

By using ASH, AWR, ADDM?

 

I also run now this procedure, to identify the blocking sql statement:

 

SQL> exec  dbms_workload_repository.add_colored_sql(sql_id => 'fn3qv2dhsu3nb');

         PL/SQL procedure successfully completed.


Can I now run AWR,ASH,ADDM, to identify/capture the sql being run? Or I need to wait 1 hr? or more?



Thanks a lot,


zxy




  • 1. Re: Database Locking
    PavanKumar Guru
    Currently Being Moderated

    Hi,

     

    1. Create an snaphot

    2. wait for some time let your transactions or sql which you are facing let it get execute

    3. Create one more snapshot

    4.  now compare using select * from table(dbms_workload_repository.awr_sql_report_text  (db_id, inst_num, :b_snap_id, :e_snap_id, 'sql_id'));

      It will provide the results of sql_id which you requested for snap

     

    HTH

     

    - Pavan Kumar N

  • 2. Re: Database Locking
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Pravin,

     

    But I am querying now the sql_id > fn3qv2dhsu3nb  but it does not exist in V$ACTIVE_SESSION_HISTORY, or DBA_HIST_ACTIVE_SESS_HISTORY.

    Does it mean the program is parsing another form of sql statement?

  • 3. Re: Database Locking
    PavanKumar Guru
    Currently Being Moderated

    Most probably, might not the correct sql_id.

     

    - Pavan Kumar N

  • 4. Re: Database Locking
    JohnWatson2 Newbie
    Currently Being Moderated

    I fail to see why this problem is still not fixed.

    Have you configured the Resource Manager to cancel a blocking idle session automatically after a short while?

    Have you configured the alert system to notify you immediately the problem occurs?

     

    Any half-way decent consultant would do this for you in an hour. 

  • 5. Re: Database Locking
    JohnWatson Guru
    Currently Being Moderated

    Apologies, the comment above by user 975480 was in fact by me, I would never be so rude as to use an anonymous account deliberately. I've had some issues recently with trying to change identity in Oracle's systems.

    --

    John Watson

    Oracle Certified Master DBA

  • 6. Re: Database Locking
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Pravin,

     

    But I am very very sure that I was able to select that SQL_ID before. Do V$ACTIVE_SESSION_HISTORY, and DBA_HIST_ACTIVE_SESS_HISTORY getting purged every week?

     

    I was sure because I followed the advise of ADDMRPT which is:

    The session with ID 9 and serial number 15 in instance number 1 was the

          blocking session responsible for 100% of this recommendation's benefit.

     

    Then I selected it from DBA_HIST_ACTIVE_SESS_HISTORY  where session_id=9 and session_serial#=15

    and I got this:

     

    10331    2855050503    1    37118282    17-AUG-13 03.57.56.542000000 AM    9    15    "FOREGROUND"        85    "fn3qv2dhsu3nb"    "N"    0    3    "SELECT"    15227495945932149007    "fn3qv2dhsu3nb"    3    4123103620        ""    ""                                            "db file sequential read"    2652584166    232    "file#"    1    "block#"    91148    "blocks"    1    "User I/O"    1740759767    0    "WAITING"    47484    "NO HOLDER"                ""    -1    0    0    0    94    "V8 Bundled Exec"    12553            1168    "N"    "Y"    "Y"    "Y"    "N"    "N"    "N"    "N"    "N"    "N"    "N"    "N"    "N"    "N"    "N"    3435489550    "ABCmatter@sprod (TNS V1-V3)"    "ABCmatter@sprod (TNS V1-V3)"    ""    ""    "sprod"    48254    ""    0    0    4603047    34565    99594    4650664    1        8192        8192    1015808



    Is ther a way to capture the blocker proactively? I mean can I write a trigger or procedure or schedule task. That whenever there is blocking enqueue statement, it will spool it to txtfile?

    So that even if I am not at the office I can still capture it?



    Thanks a lot,

  • 7. Re: Database Locking
    yxes2013 Newbie
    Currently Being Moderated

    Thanks John

     

    I fail to see why this problem is still not fixed.

    We can not kill the blocking process becuase my boss want the actual query investigated  so that it would be given to the 3rd party vendor for them to resolve.

    The are claiming that their app is not causing the issue

    Have you configured the Resource Manager to cancel a blocking idle session automatically after a short while?

    Can you give me hint of this? or sample docs.

    Have you configured the alert system to notify you immediately the problem occurs?

    Can you give me hint of this? or sample docs?


    Any half-way decent consultant would do this for you in an hour.

    I can only afford USD5 snacks an hour

  • 8. Re: Database Locking
    Fran Guru
    Currently Being Moderated

    I can't understand how you keep working as DBA if you haven't any idea of Oracle databases and have to question everything, either as still refuse to learn, read any documentation or look on your own in google or similar pages, but the height is to try to buy people with some snacks .....Do you know what is ethics and how far you can abuse this forum?

    Since you have plenty of money, why don't you buy a license and open a SR?

     

    Please moderators, make something with this kind of guys.....

  • 9. Re: Database Locking
    sybrand_b Guru
    Currently Being Moderated

    +1

     

    I urge the moderators to remove the yxes2013 account. These forums are *discussion* forums, not a place for free consultancy.

    I urge everyone to ignore any 'contribution' from yxes2013. He has gone way too far : most of his contributions are abuse and many are insulting regulars, given them unwanted nicknames, and asking for their salaries.

     

    ---------

    Sybrand Bakker

    Senior Oracle DBA

  • 10. Re: Database Locking
    yxes2013 Newbie
    Currently Being Moderated

    Oh Sorry Mr Fran Sir, I did not mean to abuse 

  • 11. Re: Database Locking
    sb92075 Guru
    Currently Being Moderated

    in different thread you posted below

     

    By the way I got good news . I found this post which answers my issue. > https://forums.oracle.com/thread/2474099

    He got the query similar to mine but more enhanced to perfection.  You should get a copy of this. It will help you a lot.

    I know by your answers you do not know how to get the blocker query. Sometimes I got second thought if you are really gurus here.

     

    But this script was able to do it.

     

    SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')

    || ' User '

    ||s1.username

    || '@'

    || s1.machine

    || ' ( SID= '

    || s1.sid

    || ' ) with the statement: '

    || sqlt2.sql_text

    ||' is blocking the SQL statement on '

    || s2.username

    || '@'

    || s2.machine

    || ' ( SID='

    || s2.sid

    || ' ) blocked SQL -> '

    ||sqlt1.sql_text AS blocking_status

    FROM Gv$lock l1,

    Gv$session s1 ,

    Gv$lock l2 ,

    Gv$session s2 ,

    Gv$sql sqlt1 ,

    Gv$sql sqlt2

    WHERE s1.sid =l1.sid

    AND s2.sid =l2.sid

    AND sqlt1.sql_id= s2.sql_id

    AND sqlt2.sql_id= s1.prev_sql_id

    AND l1.BLOCK =1

    AND l2.request > 0

    AND l1.id1 = l2.id1

    AND l2.id2 = l2.id2

    /

     

     

    Note that it has AND l2.id2 = l2.id2 too  Maybe it is need as a dummy condition to make it work?

     

     

     

     

    Thanks

     

    why are you not using your previous solution?

  • 12. Re: Database Locking
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    yxes2013 wrote:

    ...

    Any half-way decent consultant would do this for you in an hour.

    I can only afford USD5 snacks an hour

    Considering the amount of time you are spending on the forum trying unsuccessfully to solve your problem for weeks, your "boss" could easily hire someone else probably more efficient. That would worth the money.

     

    Nicolas.

  • 13. Re: Database Locking
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated

    Sybrand, I think I already told you that I'd second you on this but beleive me, moderator cannot do anything on that side, except keeping an eye and locking thread when they slip down.

    If you really want a discussion about what are the possibilities against this user, please use the feedback forum to address the concern to the admin of OTN site.

    That said, no one can refrain this user to create a new account...

     

    As you said, the best way to move it out is to ignore it.

     

    Nicolas.

  • 14. Re: Database Locking
    yxes2013 Newbie
    Currently Being Moderated

    Hi Sybrand,

     

    I tried that on test and it seems good but on actual prod scenario the program but it did not list the complete sql statement as it is very long. I was not able to show the FROM table clause.

    There are lots of similar of long statemeinnt  and we can not Identify which one. Each time I run addm, awr, ash, it has different sql statement locking.

    The sql_id that was locking before is not the same as the one locking now.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points