On May 31, 6:28 pm, joel garry <joel-ga..._at_home.com> wrote:
> On May 31, 1:27 pm, grasp06110 <grasp06..._at_yahoo.com> wrote:
>
>
>
> > > Woo-hoo, got it to work,
>
> > > If I reverse the columns in the index I get the performance I'm
> > > looking for. In other words the behavior I am observing is:
>
> > > If the column with the handful of distinct values is used first in the
> > > index the index is ignored in spite of several attemts to get it
> > > recognized.
>
> > > If the column with the number of distinct values of about 1/10 the
> > > number of rows in the table is used first in the index then the index
> > > is used if I do something like the following:
>
> > > select
> > > /*+ index(ccp ccp_sid_pid)*/
> > > *
> > > from
> > > str,
> > > ccp
> > > where
> > > str.sid = ccp.sid
> > > and property = 'FOO'
>
> > > SELECT STATEMENT Optimizer=CHOOSE (Cost=3941922 Card=953924
> > > Bytes=915767040)
> > > NESTED LOOPS (Cost=3941922 Card=953924 Bytes=915767040)
> > > TABLE ACCESS (FULL) OF STR (Cost=60153 Card=1293923
> > > Bytes=1212405851)
> > > TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCP (Cost=3 Card=1
> > > Bytes=23)
> > > INDEX (UNIQUE SCAN) OF CCP_SID_PID (UNIQUE) (Cost=2 Card=1)
>
> > > This query returns data in about 30 msec v several minutes!
>
> > > I am supprized at the cost (3941922) given that I get first rows so
> > > quickly.
>
> > > If I do a select count(*) from the above query and add RULE to the
> > > hint I get back the count (about 1million) in about 15 seconds (I'm
> > > guessing that this is a good approximation of how long it will take
> > > Oracle to access all of the rows?). It takes about 1 sec to get back
> > > the first 10k rows. Fortunately, most of the people looking at these
> > > data will only be interested in the first rows (generally < the first
> > > 10k rows.
>
> > One more minor detail: the rule uses the alias of the table.
>
> > This ignores the index:
>
> > select
> > /*+ index(my_table my_index) */
> > *
> > from
> > my_table mt,
> > my_other_table mot
> > where
> > mt.motid = mot.motid
> > and some_col = 'FOO'
>
> > This uses the index:
>
> > select
> > /*+ index(mt my_index) */
> > *
> > from
> > my_table mt,
> > my_other_table mot
> > where
> > mt.motid = mot.motid
> > and some_col = 'FOO'
>
> > Can someone recommend a good book(s) or other resouce(s) that provides
> > (preferably) comprehensive information on Oracle query (and database)
> > tuning as well as act as a refernce for quick info regarding specific
> > questions for Oracle 10g as well as older implementations
> > (specifically 9i)?
>
> > Thanks,
> > John- Hide quoted text -
>
> > - Show quoted text -
>
> The Performance Tuning manual to start, then Jonathan Lewis' CBO book
> (and blog) for true understanding, and Cary Milsap's book for a decent
> methodology. Also seehttp://www.dbaoracle.net/readme-cdos.htm#subj10
> and google for people like Jonathan Gennick, Wolfgang Breitling, Tom
> Kyte (asktom.oracle.com and his books, too), Tim Gorman, indeed,
> anyone onhttp://www.oaktable.net/members.jsp(with various
> specialties). There are plenty of "how to read a trace file" papers
> floating around.
>
> Iterations of reading and rereading the books and getting hands dirty
> seems to work best for many people.
>
> jg
> --
> @home.com is bogus. "Mozart, in a letter to his father, reported that
> at the première of his "Paris" symphony, the audience actually
> applauded in the middle of the last movement. Was young Wolfgang
> offended? Not in the least! He wrote that he was so happy he recited a
> rosary on the spot!" - Max R.
Thanks for the references!
Received on Fri Jun 15 2007 - 21:04:37 CDT
