Announcement

Collapse
No announcement yet.

[INSTRUCTIONS] Testing VPD Query Performance

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [INSTRUCTIONS] Testing VPD Query Performance

    The performance of various queries in OTM can vary dramatically, depending on whether a VPD profile is used or not.

    While working with a client on a migration this weekend, we realized that certain scheduled queries were obliterating the database (taking > 5 minutes to process, and scheduled to run at 5 minute intervals). Upon further investigation, we realized that this query would run in a few seconds without a VPD profile, but took forever with one.

    First, login to your OTM database as GLOGDBA (the user the OTM app logs in as):
    Code:
    $ sqlplus glogdba/[email protected]
    
    SQL*Plus: Release 10.2.0.3.0 - Production on Mon Sep 17 11:46:08 2007
    
    Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL>
    Next, set timing on, to get query processing times:
    Code:
    SQL> set timing on
    Now, run your query to get the runtime with no VPD profile applied.
    Note: Query truncated to protect private data.
    Code:
    SQL> select sh.shipment_gid from SHIPMENT sh ... and (rownum <= 140);
    
    no rows selected
    
    Elapsed: 00:00:01.77
    SQL>
    Next, set your user to DBA.ADMIN to test again, with a VPD profile applied, but one without any restrictions. Run the query again:
    Code:
    SQL> exec vpd.set_user('DBA.ADMIN');          
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> select sh.shipment_gid from SHIPMENT sh ... and (rownum <= 140);
    
    no rows selected
    
    Elapsed: 00:00:01.85
    SQL>
    Finally, test one more time, changing the VPD profile to match your OTM user:
    Code:
    SQL> exec vpd.set_user('GUEST.ADMIN');
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    
    SQL> select sh.shipment_gid from SHIPMENT sh ... and (rownum <= 140);
     
    no rows selected
    
    Elapsed: 00:07:34.83
    
    SQL>
    Of course, resolution is another matter, but at least you can identify if VPD is a root cause.

    Finally, here's a link to a good blog post on tracing your sql queries within sqlplus:

    So What Co-operative: Trace it!

    Hope this helps!!

    --Chris
    Last edited by chrisplough; September 18, 2007, 06:46.

  • #2
    Re: [INSTRUCTIONS] Testing VPD Query Performance

    Just linking in a related post that discusses some great performance improvements that Ian Lo achieved through tuning:Another about some performance issues when a VPD profile is used:and some negative affects of changing the VPD granularity:--Chris
    Last edited by chrisplough; September 18, 2007, 08:51.

    Comment


    • #3
      Re: [INSTRUCTIONS] Testing VPD Query Performance

      set _kks_use_mutex_pin=false on Db tier.

      Comment


      • #4
        Re: [INSTRUCTIONS] Testing VPD Query Performance

        Kristof,

        Thanks for the setting. Doing some research, this appears to be related to an issue specific to the HP-UX platform, but it would be interesting to see if hit helps other platforms also. I know that Ian is running on AIX and the bulk of our clients are running on Linux.

        Here's some details on the issue: Oracle Doc ID# 433631.1
        Mutex Latch Spin Causes High Cpu on Non-CAS Platforms (HP-UX PA-RISC )
        CPU usage greatly increased on 10.2.0.2 compared to 9.2.0.6 while performing testing..
        Problem only occurs on HP-UX PA-RISC.
        Disabling mutexes reduced CPU usage by 10%-15%

        To avoid using Mutex latches, you can set _kks_use_mutex_pin=false

        10.2.0.2 defaults the use of mutexes for certain shared cursor operations,
        instead of library cache latches and library cache pin latches and library
        cache pins. Mutexes use the CAS (compare and swap) operation.
        This is going to be resolved shortly in 10.2.0.4 - as part of bug # 5399325.

        Thanks,
        Chris

        Comment

        Working...
        X