Re: QUERY TUNING

On Dec 18, 1:05 pm, mehraj hussain <mhdmeh..._at_gmail.com> wrote:
> On Dec 18, 4:29 pm, Helma <helma.vi..._at_hotmail.com> wrote:
>
>
>
>
>
> > On Dec 18, 12:04 pm, sybrandb <sybra..._at_gmail.com> wrote:
>
> > > On 18 dec, 11:10, mehraj hussain <mhdmeh..._at_gmail.com> wrote:
>
> > > >  MY QUERY:
>
> > > > select bran_code,sum(ps_tran_qty) cls_qty
> > > > from bs_brnd_smr where substr(bran_code,3,1) !='D' group by bran_code;
>
> > > > -----------------------------------
> > > > IN THE ABOVE QUERY I HAVE A NORMAL INDEX IN THE BRAN_CODE COLUMN..
> > > > THIS IS THE EXPLAIN PLAN OF THAT QUERY..
>
> > > > PLAN_TABLE_OUTPUT
> > > > ---------------------------------------------------------------------------­­­­-----
> > > > ---------------------------------------------------------------------------­­­­--
> > > > | Id  | Operation                    |  Name        | Rows  | Bytes |
> > > > Cost  |
> > > > ---------------------------------------------------------------------------­­­­--
> > > > |   0 | SELECT STATEMENT             |              |    45 |   405
> > > > |    66 |
> > > > |   1 |  SORT GROUP BY NOSORT        |              |    45 |   405
> > > > |    66 |
> > > > |   2 |   TABLE ACCESS BY INDEX ROWID| BS_BRND_SMR  |   299K|
> > > > 2630K|    66 |
> > > > |*  3 |    INDEX FULL SCAN           | BSL7         |   299K|
> > > > |    26 |
> > > > ---------------------------------------------------------------------------­­­­--
>
> > > > Predicate Information (identified by operation id):
>
> > > > PLAN_TABLE_OUTPUT
> > > > ---------------------------------------------------------------------------­­­­-----
>
> > > > ---------------------------------------------------
>
> > > >    3 - filter(SUBSTR("BS_BRND_SMR"."BRAN_CODE",3,1)<>'D')
>
> > > > Note: cpu costing is off
>
> > > > I GOT A COMPLAIN THAT ITS TAKING VERY LONG TIME ..
> > > > THEN AFTER CHECKING IT , I PLANNED TO CREATE A FUNTION BASED INDEX (MY
> > > > bran_code COLUMN DOSNT HAVE ANY NULL VALUES) .
>
> > > > AFTER THAT I CHECK THE PLAN TABLE :
>
> > > > PLAN_TABLE_OUTPUT
> > > > ---------------------------------------------------------------------------­­­­-----
> > > > ---------------------------------------------------------------------------­­­­--
> > > > | Id  | Operation                    |  Name        | Rows  | Bytes |
> > > > Cost  |
> > > > ---------------------------------------------------------------------------­­­­--
> > > > |   0 | SELECT STATEMENT             |              |    45 |   405
> > > > |   760 |
> > > > |   1 |  SORT GROUP BY NOSORT        |              |    45 |   405
> > > > |   760 |
> > > > |   2 |   TABLE ACCESS BY INDEX ROWID| BS_BRND_SMR  |  5489K|
> > > > 47M|   760 |
> > > > |*  3 |    INDEX FULL SCAN           | BSL7         |  5489K|
> > > > |    26 |
> > > > ---------------------------------------------------------------------------­­­­--
>
> > > > Predicate Information (identified by operation id):
>
> > > > PLAN_TABLE_OUTPUT
> > > > ---------------------------------------------------------------------------­­­­-----
>
> > > > ---------------------------------------------------
>
> > > >    3 - filter(SUBSTR("BS_BRND_SMR"."BRAN_CODE",3,1)<>'D')
>
> > > > Note: cpu costing is off
>
> > > > ---------------------------------------------------------------------------­­­­------------------------------------
>
> > > > PLZ SUGGEST ME FOR THE IMPROVEMENT..
>
> > > > REGARDS,
> > > > Mohd Mehraj Hussain
>
> > > First of all, I suggest you stop typing your text in upper case.
> > > It is SHOUTING.
> > > Secondly, all posts should include a 4 digit version number.
> > > Most questions have version specific answers.
> > > While you can not be bothered to consult the documentation for your
> > > version, I can not be bothered to consult the documentation for 9iR2,
> > > 10gR1, 10gR2, or 11gR1.
>
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA- Hide quoted text -
>
> > > - Show quoted text -
>
> > It seems a oracle 9 database to me, because there the system
> > statistics are not ran by default. ( hence the  Note: cpu costing is
> > off)
> > So my first suggestion is to run those. And, are the statistics
> > accurate? Otherwise the explain plan can't be used. First update your
> > table  and index statistics if needed. What is your tested runtime?- Hide quoted text -
>
> > - Show quoted text -
>
> with normal index=30minutes
>
> with function based=more than 1 hr
>
> stats are up to date.. and its a 9i db- Hide quoted text -
>
> - Show quoted text -

I'm not an expert, but it seems to me that you have a skewed column. Did you create statistics with column histograms?

( exec dbms_stats.gather_table_stats(tabname=>'BS_BR_ND_SMR', cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO' , ownname=>'yourschemaname' )

That would be my first attempt, before creating extra indexes. And otherwise you may want to plunge into the deep with a 10053 trace to see wht the optimizer is doing. Received on Thu Dec 18 2008 - 06:22:24 CST