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 19:57:04 GMT
Message-ID: <20060213145748.614$Pd@newsreader.com>


"Jining Han" <jining.han_at_gmail.com> wrote:
> The database is 10.1.0.4 RAC on HP-UX Itanium.
>
> I am having a hard time with this query. The best I have done is
> about 5 minutes with a 2GB db_cache_size (the box is running short on
> memory if I add more to the cache),

How many blocks is the entire sales table?

Is "D.ORG_NBR = '100000' and D.BRNC_ID = '5000'" a constant, or do the actual values change with each execution?

> and I have played with
> pga_aggregate_target without seeing obvious gains. My users want to
> bring this down to <= 2 min.

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?

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?

What about partioning? Do you have it? If so, on what? If not, can you do it?

>
> The only thing I can think of trying now is to create some large block
> tablespaces (database is currently 8k blocks) since all analysis
> (statspack, traces, etc) indicates sequence read being the bottleneck.

I see no reason to think that that will help. You seem to be doing a little less than one IO (presumably against "sales") for each row fetched before the group-by. This suggests your cluster factor of "sales" rows WRT dept_id (dept_id is what FK_SALES$LNDR is an index of, right?) is poor in 8K blocks, and there is little reason to think it will magically be better in 32K blocks.

What is the relationship between D.ORG_NBR and D.ORG_ID?

If you force your query to execute without parallelization, what happens?

I think you need to make the sales table more clustered, so that the rows needed for any given query are found next to each other. Perhaps the easiest way to do that (effectively, not literally) is to build an index including all the "sales" column needed for the query, which it appears would be (DEPT_ID, SRC_SYS_CD, PROD_CD, SALE_AMT, SALE_ID, DISCOUNT_AMT, CUST_ID). Xho

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

Original text of this message

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