From oracle-l-bounce@freelists.org Sun Aug 22 15:44:16 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i7MKiFX18759 for ; Sun, 22 Aug 2004 15:44:15 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i7MKiDI18751 for ; Sun, 22 Aug 2004 15:44:13 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B715872C738; Sun, 22 Aug 2004 15:48:32 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27402-63; Sun, 22 Aug 2004 15:48:32 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D012C72C1C3; Sun, 22 Aug 2004 15:48:30 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sun, 22 Aug 2004 15:47:03 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E6FCE72C6A1 for ; Sun, 22 Aug 2004 15:47:01 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27402-38 for ; Sun, 22 Aug 2004 15:47:01 -0500 (EST) Received: from web50005.mail.yahoo.com (web50005.mail.yahoo.com [206.190.38.20]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 2244E72C1C3 for ; Sun, 22 Aug 2004 15:47:01 -0500 (EST) Message-ID: <20040822204830.89716.qmail@web50005.mail.yahoo.com> Received: from [192.234.2.50] by web50005.mail.yahoo.com via HTTP; Sun, 22 Aug 2004 13:48:30 PDT Date: Sun, 22 Aug 2004 13:48:30 -0700 (PDT) From: Raj Jamadagni Subject: Re: VPD Pre/Post Implementation Issues To: oracle-l@freelists.org In-Reply-To: <4128FF9B.1030506@miracleas.dk> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 8065 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rjamya@yahoo.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Mogens, Very true, but our situation was w/o index the sql wouldn't run. The form would freeze and the developer insisted that nothing else (except the VPD) will work. So, my colleague who was attending OOW 2003, checked with Oracle guys who asked "is there a index on *those* columns?". We didn't have one, created and that process finally started working. Give the options ... 1> not working and 2> does more LIO I didn't want to think about option 2. ... getting that stupid thing to work was a priority. So, as we summarized in our case ... the problem wasn't the index but the VPD. And don't you love when they come up with "1=1" conditions where there is nothing to filter by? Cheers Raj --- Mogens_Nørgaard wrote: > 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 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@freelists.org > > put 'unsubscribe' in the subject line. > > -- > > Archives are at http://www.freelists.org/archives/oracle-l/ > > FAQ is at http://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@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ===== Best Regards Raj --------------------------------------------------------- select mandatory_disclaimer from company_requirements; __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! 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@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------