Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How can I further reduce physical read for this query short of throwing memory at it?

Re: How can I further reduce physical read for this query short of throwing memory at it?

From: <xhoster_at_gmail.com>
Date: 13 Feb 2006 22:05:52 GMT
Message-ID: <20060213170638.013$yV@newsreader.com>


"Jining Han" <jining.han_at_gmail.com> wrote:
> Thanks for all the questions! My answers are embedded.
>
> >Is "D.ORG_NBR = '100000' and D.BRNC_ID = '5000'" a constant, or do the
> >actual values change with each execution?
>
> It varies.

That means you are effectively accessing random parts of SALES. Since your SALES table is about 40 Gig (from later post), then no amount of tweaking of the buffer cache is going to enable you to access random data from SALES without going to disk to get it. So packing the relevant data tighter is about the only hope, it seems to me.

>
> >If you execute the query again immediately after the first execution
> >finishes, how long does the second execution take and what are the block
> >access counts for it?
>
> About the same

When you tested it, did you change the ORG_NBR and BRNC_ID bind values for the second execution or not? If you did not, then that result surprises me, which makes me wonder if my assumptions about what is going on aren't right. I didn't think a parallel NL would bypass the buffer cache, and if it did you should have a lot of direct IO reported rather than consistent gets, right? Is SALES in the recycle pool?

>
> > Is fulfilling this query the main load on your database? If you have
> > to degrade performance on other things to make this better, is that OK?
>
> It depends how much the degradation would be. By the way, I have
> tested some bitmap indexes and a join index. Still more testing to get
> better understanding of performance improvement.

I wouldn't expect a bitmap index to help much. Nor a join index, unless it contains all the columns necessary to fulfill the query without going to the tables themselves.

>
> >What about partioning? Do you have it? If so, on what? If not, can
> >you do it?
>
> The table is partition on a column not used in the query. I am
> currently trying to get approval to partition the table by SRC_SYS_CD,
> then subpartition by the current partition key.

I wouldn't expect that partitioning on that would help much, assuming most of your business is in Europe and America. If you omit the SRC_SYS_CD restriction in the where clause, how many rows are retrieved (before the group-by is applied)?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB
Received on Mon Feb 13 2006 - 16:05:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US