1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Bom deletes and wait/locks

Discussion in 'Oracle SCM & Manufacturing' started by stuckonversion11, May 30, 2019.

  1. stuckonversion11

    stuckonversion11 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Ohio
    Hi everyone, I'm new here. We're on an old version of Oracle (11.0.300) and we have a company that does support for us, they're great. But we are struggling to figure out some issues and I wanted to use every resource I could to get some answers. Also, if I am posting this in the wrong place, please tell me and I can move it.

    To give a little backstory, we've had issues like this going on the past few months. The basic gist is, suddenly, for a day or two, we'll have many executions of queries on the database for a very specific thing that ends up causing locks and a bunch of slowdown for the users. We ended up having to restart the concurrent managers and even the database/server one day. We've worked with our DBA and our third party support to try and link the query to a user or application in EBS, but we haven't been able to do so yet. So, my idea was, and what my question for you all is, if I post the query, can you help me find what application in Oracle would be capable of sending it?

    Here's the most recent one:

    DELETE
    FROM BOM.BOM_INVENTORY_COMPONENTS
    WHERE BILL_SEQUENCE_ID = :b1

    Here's the one we dealt with previously:

    DELETE
    FROM WSH_REPORT_TEMP
    WHERE REPORT_TEMP_ID = :b1
    OR CREATION_DATE < SYSDATE - 2

    Thank you all in advance for your help, and again if I need to move this please let me know.

    Thank you
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,685
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    In a multi-user environment where the same statement can be executed by multiple users with the same bind values this can happen; also if there's an index on that column the deletes will 'stack up' behind each other waiting for the first transaction to complete. As sessions queue up for a resource the user memory that the database allocates on a per-session basis also increases; more processes, more consumed memory equate to a slower system. And knowing which application is sending this query probably won't help much on. your end since this an application design issue. That information will give you direction in who to contact to get this addressed (the application tech support team comes to mind).

    Each session records the machine it originates from, along with the program and module that is doing the work. V$SESSION will provide this information to you so that's the first place to look:

    Code (SQL):
    SELECT machine, program, module, COUNT(*) ct
    FROM v$session
    WHERE machine IS NOT NULL
    AND username IS NOT NULL
    GROUP BY machine, program, module
    ORDER BY 4 DESC;
    When you find rows where the CT value is high the machine listed should be the prime target for investigation (my suspicions are this is coming from an application server during a period of peak activity). I would hope that no user or developer has direct access to the database server, running multiple instances of this statement locally.

    Another way to 'thin the herd' is to use the sql_id for that statement; that information is found in V$SQL; when you have the sql_id you can they use it in a query of V$SESSION to return all of the users currently executing that query:

    Code (SQL):
    SELECT username, machine, program, module
    FROM v$session
    WHERE sql_id = <VALUE FROM V$SQL>
    OR prev_sql_id = <VALUE FROM V$SQL>;
    When this issue appears again that query can isolate the sessions likely causing the problem.

    As mentioned previously this is an application design issue (flaw) that the DBA shouldn't be expected to correct.
     
  3. stuckonversion11

    stuckonversion11 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Ohio
    Thanks for your reply. I agree that the DBA should not be expected to resolve the issue, it's more of a business process issue or application setup issue. This should help me narrow down where to look though. The reason I am taking it upon myself to look into it is that no one else is really stepping up to the plate. Also, like you said, these are being run by the APPS user, but aren't some processes initiated by an actual user and then the system then queues it up to run through the APPS user to talk to the database?

    I will try to narrow down the machine its originating from and also use the sql_id to tie it to a user as well. Thank you again for your help. I'm at my wits end with this issue and this will help a lot.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,685
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Connected users run their own processes; they aren't handed-off to a service account. If this is a true service account then an automated job is responsible for such activity, either through cron or some external or internal job scheduler.

    Packages, procedures and functions may be owned by APPS, but if they are executed by other users then the user account calling those programs will be shown as the 'offending party'.
     
  5. stuckonversion11

    stuckonversion11 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Ohio
    Great! That helps narrow it down a lot.