Home » RDBMS Server » Performance Tuning » Please help for tuning .sql query (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Please help for tuning .sql query [message #644307] Tue, 03 November 2015 03:44 Go to next message
msol25
Messages: 368
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 #644308 is a reply to message #644307] Tue, 03 November 2015 03:49 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Your object statistics are massively wrong. Can you not see that? You need to correct that before considering anything else.
Re: Please help for tuning .sql query [message #644309 is a reply to message #644308] Tue, 03 November 2015 03:52 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member

Dear John,

There is having any solution using hint to correct upto some extent?
Re: Please help for tuning .sql query [message #644310 is a reply to message #644309] Tue, 03 November 2015 03:55 Go to previous messageGo to next message
John Watson
Messages: 7265
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 #644311 is a reply to message #644309] Tue, 03 November 2015 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 13017
Registered: September 2008
Location: Rainy Manchester
Senior Member
There is no better pan than the one you've got for what you are trying to do.
Correcting the stats will cause the cost to drop.
Re: Please help for tuning .sql query [message #644312 is a reply to message #644311] Tue, 03 November 2015 03:59 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
There might be: an index fast full scan, using multiblock reads. But without accurate stats (system as well as object) the CBO has no chance of finding it. If it exists.
Re: Please help for tuning .sql query [message #644313 is a reply to message #644310] Tue, 03 November 2015 04:02 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Thanks John,

Please can you give me any document for this analysis?

Because,Not able to improve my query performance using try and run basis.

Re: Please help for tuning .sql query [message #644315 is a reply to message #644313] Tue, 03 November 2015 04:05 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Hi 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?
Re: Please help for tuning .sql query [message #644316 is a reply to message #644313] Tue, 03 November 2015 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

- OraFAQ Forum Guide, Detailed OraFAQ Forum Guide section, Performance Tuning paragraph
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

You can also have a look at Database Performance Tuning Guide.

Re: Please help for tuning .sql query [message #644317 is a reply to message #644315] Tue, 03 November 2015 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Who will read 486499673 rows?

Re: Please help for tuning .sql query [message #644318 is a reply to message #644315] Tue, 03 November 2015 04:12 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
msol25 wrote on Tue, 03 November 2015 10:05
Hi 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 #644319 is a reply to message #644317] Tue, 03 November 2015 04:13 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear Michel,


Here I want to say it's retrieving 48 million rows(Approx) and table is also having around 48 million rows.So,In this case if optimizer is taking wrong decision for using index.But,If I am giving FULL Table scan hint, then why performance is not getting improved.I hope in this case my cost should get improved.But,It's not happening.Why?
Re: Please help for tuning .sql query [message #644320 is a reply to message #644313] Tue, 03 November 2015 04:14 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
msol25 wrote on Tue, 03 November 2015 10:02
Thanks John,

Please can you give me any document for this analysis?

Because,Not able to improve my query performance using try and run basis.

Read this book, http://www.amazon.com/gp/product/1501022695

Re: Please help for tuning .sql query [message #644321 is a reply to message #644319] Tue, 03 November 2015 04:17 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Er.... there is a difference between 48 million and 480 million.
Re: Please help for tuning .sql query [message #644322 is a reply to message #644321] Tue, 03 November 2015 04:29 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Thanks John,

Any solution for this at query level like hint etc?
Re: Please help for tuning .sql query [message #644323 is a reply to message #644322] Tue, 03 November 2015 04:30 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
You have ignored everything I have said. You appear to be unteachable. Goodbye.
Re: Please help for tuning .sql query [message #644324 is a reply to message #644322] Tue, 03 November 2015 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use hint /*+ FASTER */.

Re: Please help for tuning .sql query [message #645267 is a reply to message #644324] Tue, 01 December 2015 22:24 Go to previous message
Kevin Meade
Messages: 2102
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
Previous Topic: OPT_PARAM - WORK_AREA_POLICY
Next Topic: MULTI TABLE INSERT USING BULK COLLECT
Goto Forum:
  


Current Time: Sun Feb 25 12:42:19 CST 2018

Total time taken to generate the page: 0.02379 seconds