[INSTRUCTIONS] Testing VPD Query Performance

[chrisplough]

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):

$ 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:

SQL> set timing on

Now, run your query to get the runtime with no VPD profile applied.
Note: Query truncated to protect private data.

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:

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:

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

[chrisplough]

Re: [INSTRUCTIONS] Testing VPD Query Performance

Just linking in a related post that discusses some great performance improvements that Ian Lo achieved through tuning:

http://www.otmfaq.com/forums/f27/otm...rovements-515/

Another about some performance issues when a VPD profile is used:

http://www.otmfaq.com/forums/f28/vpd...erformance-25/

and some negative affects of changing the VPD granularity:

http://www.otmfaq.com/forums/f21/sol...ularity-d-219/

--Chris

[Kristof Stevens]

Re: [INSTRUCTIONS] Testing VPD Query Performance

set _kks_use_mutex_pin=false on Db tier.

[chrisplough]

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