Re: QUERY TUNING

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 18 Dec 2008 04:22:15 -0800 (PST)
Message-ID: <65f3445e-c6a5-46c8-b1b3-2d453a5d68cd@a26g2000prf.googlegroups.com>


On Dec 18, 5: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
>
> 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

This appears to be some version of Oracle 10g, based on the "SORT GROUP BY NOSORT" in the explain plan, and the DBMS XPLAN type format of the explain plan which includes the predicate information.

An index full scan reads the index one block at a time (db file sequential reads), unlike index fast full scans (mostly db file scattered reads). If 99% (or possibly even 5%) of the rows will be returned by the query, a full table scan would likely be faster than reading every block in the index, one block at a time. Oracle is predicting that it will return 299,000 rows from the index, and each of those will cause a single block read (possibly an in memory read or a physical read from disk) of the table. If there are 299,000 rows to be grouped, that grouping operation is possibly spilling to disk, utilizing the temp tablespace.

You stated that you created a function based index for substr (bran_code,3,1), yet the two explain plans appear to be identical. Unless there were only a small percentage of rows with the third character of BRAN_CODE not equal to 'D', AND there were (some how) a histogram on the virtual column created for the function based index, it seems unreasonable that any type of index access would be automatically used by Oracle for this query - the cost based optimizer would assume that a large percentage of the rows would be returned, and would seemingly select a full tablescan. If you have silly setting for OPTIMIZER_INDEX_COST_ADJ (set to 1 for instance), DB_FILE_MULTIBLOCK_READ_COUNT (set to 0, which is reset to a value of 1 prior to Oracle 10.2.0.4), OPTIMIZER_MODE (set to FIRST_ROWS), etc., the cost based optimizer may inaccurately calculate what it believes to be the most efficient access plan for the data.

I suggest that you create a 10046 trace at level 8 for the session executing this query to see what the session is doing. If you flush the shared pool, or slightly modify the query, you may also create a 10053 trace at level 1 to determine why Oracle determined that a full scan of the index is the most efficient access plan for the data.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Dec 18 2008 - 06:22:15 CST

Original text of this message