QUERY TUNING
mehraj hussain
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
sybrandb
> 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
Helma
>
> - 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?
mehraj hussain
>
> - Show quoted text -
with normal index=30minutes
with function based=more than 1 hr
stats are up to date.. and its a 9i db
Charles Hooper
> 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.
Helma
>
> - 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.
Mladen Gogala
> So my first suggestion is to run those.
Why? What other access plan would optimizer consider, with the proper
statistics?
Mladen Gogala
> THEN AFTER CHECKING IT , I PLANNED TO CREATE A FUNTION BASED INDEX (MY
> bran_code COLUMN DOSNT HAVE ANY NULL VALUES) .
And what predicate do you think that the function index should help you
resolve? The "!="?
ddf
On Dec 18, 4:10 am, mehraj hussain <mhdmeh...@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
Mladen Gogala
> Why? What function are you using in your query? I see none.
What do you think that "substr" is?
Helma
For this SQL i don't think that running the system statistics will
make any difference. But not running system statistics is just a bad
idea, don't you agree?
About the histograms, they work nicely on skewed data. For me, it's
just applying the idea of feeding the optimizer with as much relevant
info as possible and see if the problem is still there. First clean up
your shop before the real work starts.
There may be a connection with the shop i am currently working on.
They refuse to update their 3 year old statistics because ' that may
lead to even more performance problems' , and they prefer to create
new indexes when plan instability hits them again.
So i am currently in a mantra of ' take care of your statistics
first' before trying anything else first.
Mladen Gogala
> to: Mladen Gogala
>
> For this SQL i don't think that running the system statistics will make
> any difference. But not running system statistics is just a bad idea,
> don't you agree?
I do agree, but I usually tend to solve the problem at hand.
....
> So i am currently in a mantra of ' take care of your statistics first'
> before trying anything else first.
If the problem at hand is soluble by providing a better statistics, I am
all for it. The problem with the query given by the OP is that it doesn't
have much room for improvement. The OP would need to reorganize his data
and his queries. Also, the "!=" conditions are notoriously hard to
resolve by using index.
ddf
Something I missed, obviously. :)
However, the FBI isn't used if the query plans posted are, indeed, the
query plans generated before and after the FBI was created. There is
some other reason this query is performing poorly, and the plan won't
show what that is.
David Fitzjarrell
joel garry
>
> 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.
>
http://www.juliandyke.com/Optimisation/Operations/SortGroupByNoSort.html
(And thanks for stimulating me to look, I learned several new things
on the way to finding that).
http://www.centrexcc.com/What%20is%20new%20in%20the%20Oracle%209i%20CBO.pdf
(page 5 example looks like OP to me)
Keep up the good works, the rare brain-fart or typo keeps us on our
tippy-toes.
jg
--
@home.com is bogus.
“When I first heard about [the nondisclosure agreement for DSM-V], I
just went bonkers.” - Dr. Robert Spitzer, psychiatry professor at
Columbia and the architect of DSM-III.
http://www3.signonsandiego.com/stories/2008/dec/18/1n18psych232240-diagnostic-book-psychiatrists-argu/?uniontrib
Charles Hooper
> On Dec 18, 4:22 am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > 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.
>
> tippy-toes.
>
> jg
I was thinking about the HASH GROUP BY optimization in 10g when I
wrote the above. I realized my mistake right after I posted - the OP
had replied that he was running 9i, which caused me to wonder how
could that be the case?
Thanks for the links.