Skip to Content

Locks in NRIV Table

Hi ALL


I am new to production support while monitoring the data loads BASIS find the locks in BW system.

We got more alerts for Row lock contention IN BW production .Kindly help me understanding and resolving the issue.

SQL:

SELECT /*+ FIRST_ROWS (1) */ "FROMNUMBER", "TONUMBER", "NRLEVEL" FROM "NRIV" WHERE "CLIENT"=:A0 AND "OBJECT"=:A1 AND "SUBOBJECT"=:A2 AND "NRRANGENR"=:A3 AND "TOYEAR"=:A4 FOR UPDATE


please suggest the solution to avoid the above locks.


Regards,

Jagadeeshwar.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Dec 31, 2014 at 05:14 AM

    Hi,

    Please refer note 142237 .

    Also have a look at this thread http://scn.sap.com/thread/1682587

    Hope this helps.

    Regards.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member jagadeeshwar dasari

      Hi Jagadeesh,

      With this SQL info, are you getting any dumps in production system? Please check in ST22 or SM21 for this. You may get more details of error as with mere a SQL statement it is hard to identify the issue.

      Most probably in your process chains many processes are running in parallel and trying to access NRIV table for number range assignments.

      Let us know what you find in BW prod in above transaction codes.

      Thanks

      Amit

  • Dec 31, 2014 at 06:16 AM

    Hi Jagadeeshwar,

    This seems to be more of a Number Range issue which is handled by the Basis ....the Basis Team should be able to help you on this.

    Rgds

    SVU

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 31, 2014 at 07:49 AM

    Hi Jagadeeshwar

    can you please download below oss note and try

    note 399207

    Regards

    Satish.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 02, 2015 at 03:19 PM

    Check note 1453114

    A NRIV lock indicates a problem with an unbuffered number range



    Regards,

    Rahul S

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Identify top 200 Dimensions and top 160 Master Data Number Range objects based on their Number Range Level (considered all the NR objects which are having more than 100000 as NR Level).

      Then

      1.  Go to T.Code SNRO  --> Provide the Object Number --> click on ‘Change’ --> Under the ‘Customizing’ tab --> Select ‘ Buffering = Main Memory Buffering’ and 'No. of numbers in buffer' = 500.


      You need client opening before this change.


      Hope this will helps.

  • avatar image
    Former Member
    Sep 06, 2015 at 03:31 AM

    When we load data(Insert/Update or Delete) into a table a lock is set(Enqueue) and when the loading process  is complete the lock is removed(dequeue).

    This is common for all OLTP(SAP Source Systems) and OLAP(Datawarehouses) systems.

    SAP Locking concept explains different types of locking & Relationship between SAP Locks and Database Locks

    However let’s focus two types of locks - Shared(S) & Exclusive(E).

    Shared Lock - Several users or processes can have access to locked data at the same time in Display Mode. Exclusive Locks - Just as it says.

    When your BW ETL(Process Chains) jobs are running find out the types of Locks occurring when DTP Load, DSO Activation, SID Generation & OHD’s are running. If lots of time is spent on SID generation then Number Range Buffering is your fix.

    Blocking is wait situation that are currently caused by locks which is explained in detail via the DBA Cockpit for MS SQL

    Deadlock occurs when two or more processes or users prevent each other from proceeding with the SQL statement.

    SAP uses Database parameters  to manage MS SQL Server locks, Here is how we Analyze Deadlocks when running SAP on MSSQL. You can find the same for your DB.


    Real World Example to explain Locks, Blocks, Deadlocks, Bad Code & Optimizations.

    Let's use a daily commute in the Bay Area as an example - Start home at 6:25 and reach Starbucks by 6:33 and reach train station at 6:45 to ride the 6:48 ACE Train to get to work by 8 AM. Now let's say our 8 AM meeting is canceled - Leave home at 7:10 get to starbucks at 7:23 and will reach train station at 7:50 AM and take the 7:53 train.

    Locks - Cash Registers

    Blocks - All the traffic of parents dropping kids at school, and all the people in front of me in starbucks waiting to order

    Deadlocks - People creating a spill while in a hurry while picking up their drinks, cash register hung up

    Optimizations - Adding an extra cash register during peak times, your regular order should you be a regular customer, paying with your app

    Slow Performing Code - People taking longer time to give exact change, spending long time to order or changing order multiple times, a new pot of coffee being brewed now


    Metrics to track and avoid slowness / locking are


    - Jobs running more than 1 hour

    - Which jobs are not starting on time and not finishing on time

    - Full loads with over say 1 Million rows

    - Inconsistent Scheduling of process chains (same chain triggered twice)



    Create a table like below for the Top 20 Process chains by runtime


    In the above example if there are 14000 unique SID's generated for 0PCA_ITEMNO ( this detail is available in your process chain log) set the number range buffer to 2300 records. It would have accessed the NRIV table 5 times before and now with this config setting it will go there once there by reducing locks.

    To avoid general slowness in your BW Production System

    House Keeping / Performance Tuning Activities in SAP BW Systems -- PART 1 ( it has links to all parts)

    Important Housekeeping Principles for keeping your SAP NetWeaver BW in good shape – Webinar Presentation

    - Periodic Jobs and Tasks in SAP BW

    Finally if your RS* tables are growing there are multiple programs to reduce those.


    dim_table.jpg (51.6 kB)
    Add comment
    10|10000 characters needed characters exceeded