RE: Primary Key seems to be harmful for performance

  • From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 14 Mar 2006 15:25:27 -0600

All the tables have been analyzed.

thank you

Gene Gurevich
Oracle Engineering
224-405-4079


                                                                           
             "Igor Neyman"                                                 
             <ineyman@perceptr                                             
             on.com>                                                    To 
             Sent by:                  <genegurevich@xxxxxxxxxxxxxxxxxxxxx 
             oracle-l-bounce@f         >, "oracle-l"                       
             reelists.org              <oracle-l@xxxxxxxxxxxxx>            
                                                                        cc 
                                                                           
             03/13/2006 08:02                                      Subject 
             AM                        RE: Primary Key seems to be harmful 
                                       for performance                     
                                                                           
             Please respond to                                             
             ineyman@perceptro                                             
                   n.com                                                   
                                                                           
                                                                           




Gene,

Before playing with optimizer_index_cost_adj and
optimizer_index_caching, try to collect system statistics using
DBMS_STATS.GATHER_SYSTEM_STATS.

Igor

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxxxxxxxxxxx
Sent: Friday, March 10, 2006 6:04 PM
To: oracle-l
Subject: Re: Primary Key seems to be harmful for performance

Mladen:

Thanks for your help. I have posted the SQL and the explain plans a day
or two ago. My optimizer parameteres are set to optimizer_index_cost_adj
100 and optimizer_index_caching to 0 ( I think these are default parms).
I'll need to read up on tuning them. I have tried using ALL_ROWS hint
but that did not change the performance.
Can't throw out Cognos, I'm afraid :(

thank you

Gene Gurevich
Oracle Engineering





             Mladen Gogala

             <gogala@sbcglobal

             .net>
To
             Sent by:                  oracle-l <oracle-l@xxxxxxxxxxxxx>

             oracle-l-bounce@f
cc
             reelists.org


Subject
                                       Re: Primary Key seems to be
harmful
             03/09/2006 06:40          for performance

             PM





             Please respond to

             gogala@sbcglobal.

                    net










On 03/09/2006 09:12:05 AM, genegurevich@xxxxxxxxxxxxxxxxxxxxx wrote:
> I was refering to the fact that i had multiple tables in my query and
they
> all have PKs. I should have been clearer though and say that I have
> disabled a PK on one of the tables. I can't modify the SQL in any way
> because it is generated by a reporting tool (Cognos) it won't add
> hints or +0 etc.

Gene, execution plan would be helpful. What you wrote sounds like a full
table scan, murdered by using index. That usually happens if parameters
with the goal of forcing index use and emulating RBO are in effect
(OPTIMIZER_INDEX_CACHING and cost adjust).
What happens is that Oracle reads every index key and then reads the
corresponding table block, performing, in effect, several times as many
I/O requests as without using an index. Two other things you can do are
to make sure that users that use Cognos have optimizer goal set to
ALL_ROWS and you can fake statistics and make the PK index look bad to
CBO (look at my home page, I describe the way of doing that there).
Third option is to try throwing Cognos out and then getting something
that always generates an optimal execution plan, like Business Objects
or Crystal Reports (duck).
--
Mladen Gogala
http://www.mgogala.com

--
//www.freelists.org/webpage/oracle-l






--
//www.freelists.org/webpage/oracle-l



--
//www.freelists.org/webpage/oracle-l






--
//www.freelists.org/webpage/oracle-l


Other related posts: