Re: QUERY TUNING
- From: Charles Hooper <hooperc2000@xxxxxxxxx>
- Date: Thu, 18 Dec 2008 04:22:15 -0800 (PST)
On Dec 18, 5:10 am, mehraj hussain <mhdmeh...@xxxxxxxxx> 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.
.
- Follow-Ups:
- Re: QUERY TUNING
- From: joel garry
- Re: QUERY TUNING
- Prev by Date: Re: QUERY TUNING
- Next by Date: Re: QUERY TUNING
- Previous by thread: Re: QUERY TUNING
- Next by thread: Re: QUERY TUNING
- Index(es):
Relevant Pages
|
