Please help for tuning .sql query [message #644307] |
Tue, 03 November 2015 03:44 |
|
msol25
Messages: 396 Registered: June 2011
|
Senior Member |
|
|
Dear,
Please help for improving the explain plan for below mentioned query:
select count(1)
from temp bs
where status in(1, 7, 8, 19 )
PLAN_TABLE_OUTPUT
Plan hash value: 2768968421
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 15877 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| OPT_IX | 484M| 1385M| 15877 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STATUS"=1 OR "STATUS"=7 OR "STATUS"=8 OR
"STATUS"=19)
Total Records in table: 48 Million (Approx)
|
|
|
|
|
Re: Please help for tuning .sql query [message #644310 is a reply to message #644309] |
Tue, 03 November 2015 03:55 |
John Watson
Messages: 8964 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Some sites ban hints completely in production systems. And in any case, you have no idea what plan you would hint for.
Tuning is a science. You must follow the Scientific Method. You need to work out what Orcale is doing (OK, you've done that) and why (which you haven't done).Then you can construct a hypothesis for a better way, and test it. THat is where hints may be useful.
So, start by understanding why the CBO did what it did. How do you interpret line Id 3 of your execution plan?
[Updated on: Tue, 03 November 2015 03:56] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Please help for tuning .sql query [message #644318 is a reply to message #644315] |
Tue, 03 November 2015 04:12 |
John Watson
Messages: 8964 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
msol25 wrote on Tue, 03 November 2015 10:05Hi John,
My Query execution completed right now and it's retrieving total: 486499673 rows.But it's taking long time for execution.
Wat should be the best possible way to improve performance? So at least one object statistic is correct, but this statementQuote:Total Records in table: 48 Million (Approx) is nonsense.
To repeat: how do you interpret line Id 3 of your plan? Why do you think the CBO made that decision? Think about cardinality of the table and the query. THink about system statisitcs: the speed of different access methods. Think!
|
|
|
|
|
|
|
|
|
Re: Please help for tuning .sql query [message #645267 is a reply to message #644324] |
Tue, 01 December 2015 22:24 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
how many rows are in the table?
how many rows does your query actually fetch?
select count(*) from temp bs;
select count(*) from temp bs where status in(1, 7, 8, 19 );
A few thoughts:
1. if you are fetching 484 million rows from a table that has 484 million /.02 (2% Rule) rows, then use an index, otherwise do a table scan. PRECISION STYLE VS. WAREHOUSE STYLE.
2. if your table is partitioned on status then do a table scan to exploit partition pruning. PRECISION STYLE TURNED INTO WAREHOUSE STYLE.
3. if your table is not partitioned on status then slap someone and ask why not. STUPIDITY TUNING.
4. if your table has an index on status then see if multiple index range scans help. DEJA VU. (this is in fact what you are doing currently but in the end it really goes back to #1).
5. as was suggested, look at a covering index as an option if there are not a lot of columns being fetched. BIG BILLY GOAT GRUFF TO THE CBO.
Kevin
|
|
|