Re: VPD Pre/Post Implementation Issues
- From: Mogens Nørgaard <mln@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 22 Aug 2004 22:18:35 +0200
Never underestimate the price of using indexes on DML. We recently had a
beautiful case where a certain insert statement took at very long time.
The table had 160 million rows, and seven indexes defined on it.
So I figured on paper that each index could easily have a b-level of
four, which meant that each insert could produce as many as 7x4+1=29 LIO's.
Johannes Djernes from Miracle then had to go and do the real work (I'm
the director, after all) and found the number to be 30.
Now, an LIO is not an LIO, as I discovered when listening to Anjo's "The
price of logical IO's" presentation recently. But in general, to quote
Tom Kyte loosely, a logical IO requires a latch, and a latch is a lock.
And when you add "overhead" in the shape of VPD (which I happen to love,
since I helped define some of it many moons ago), it's time for a test
or two. Preferably on BIGEMP instead of EMP :-).
Mogens
Raj Jamadagni wrote:
> Vivek,
>
> No matter how you do it there will be impact on performance. More so if you
> use dynamic policies
> Vs. static policies. As Mogens mentioned, docs are good, but nothing beats
> testing. You will soon
> realize that you'll need more indexes on the tables (that have policies
> enabled), than you'd like,
> but sometimes it is the only way to bring certain queries in acceptable
> performance limits. These
> indexes will expecially be on the columns using which you'll enforce the
> policies in _most_ cases.
>
> set serveroutput on
> begin
> for i in 1 .. 1000
> loop
> dbms_output.put_line('Nothing beats testing in your OWN environment.');
> end loop;
> end;
> /
>
> See, I told you thousand times .. 8:)
> Raj
>
> --- Mogens_Nørgaard <mln@xxxxxxxxxxxx> wrote:
>
>
>>Test the various scenarios in your environment via 10046 traces with and
>>without VPD, single-row stuff and multi-row stuff and bulk loads,
>>reports, etc. It's easy to measure that way.
>>
>>VPD means overhead, of course. Whether it is too much or not for your
>>situation, well, it depends.
>>
>>I seem to recall that the Oracle standard documentation is pretty good
>>on this topic. Have you read them already?
>>
>>Mogens
>
>
>
> =====
> Best Regards
> Raj
> ---------------------------------------------------------
> select mandatory_disclaimer from company_requirements;
>
>
>
>
> __________________________________
> Do you Yahoo!?
> New and Improved Yahoo! Mail - 100MB free storage!
> http://promotions.yahoo.com/new_mail
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
