1 2 Previous Next 21 Replies Latest reply: Aug 29, 2013 11:28 PM by yxes2013 RSS

    Database Locking

    yxes2013

      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
          Pavan Kumar

          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

            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
              Pavan Kumar

              Most probably, might not the correct sql_id.

               

              - Pavan Kumar N

              • 4. Re: Database Locking
                JohnWatson2

                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

                  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

                    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

                      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

                        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

                          +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

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

                            • 11. Re: Database Locking
                              sb92075

                              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

                                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

                                  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

                                    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