Re: QUERY TUNING

From: ddf <oratune_at_msn.com>
Date: Thu, 18 Dec 2008 06:22:29 -0800 (PST)
Message-ID: <955c22c2-f3d9-4d75-b42c-8b3de2bb17b2@s9g2000prm.googlegroups.com>


Comments embedded.
On Dec 18, 4:10 am, 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
>

Nicely formatted. I don't see a problem with the plan.

> I GOT A COMPLAIN THAT ITS TAKING VERY LONG TIME ..
And you've used which tools to investigate this complaint? EXPLAIN PLAN won''t be enough, I'm afraid. I'd be starting with autotrace, and then I'd use Statspack if autotrace didn't provide any usable clues.

> THEN AFTER CHECKING IT , I PLANNED TO CREATE A FUNTION BASED INDEX (MY
> bran_code COLUMN DOSNT HAVE ANY NULL VALUES) .
>

Why? What function are you using in your query? I see none.

> 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
>
> ---------------------------------------------------------------------------­------------------------------------

This is the exact same plan as before, so your function-based index is useless. And, as I stated before, using the plan, and nothing but the plan, won't help here. You need per-query statistics (reported by autotrace) and/or an overview of system activity during the query period (this is what Statspack provides). You'll not solve this without the proper tools, just like you cannot change a flat tire with a hammer.

>
> PLZ SUGGEST ME FOR THE IMPROVEMENT..
Uwe the proper tools and you'll probably see the solution. No one here, given the lack of information in your post, can tell you otherwise.

>
> REGARDS,
> Mohd Mehraj Hussain

David Fitzjarrell Received on Thu Dec 18 2008 - 08:22:29 CST

Original text of this message