Home » RDBMS Server » Performance Tuning » Please help for Improving the Explain plan (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Please help for Improving the Explain plan [message #645529] Tue, 08 December 2015 03:00 Go to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
Dear,

Please can you help me for below mentioned query for improving explain plan.


Having function based index on trunc(actual_bill_dtm).


select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   trunc(actual_bill_dtm) > DATE '2015-12-08'

Explain Plan:

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |   114K|    26M| 18691   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BILLSUMMARY            |   114K|    26M| 18691   (1)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | BILLSUMMARY_KPI_OPT_IX |   114K|       | 17853   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(TRUNC(INTERNAL_FUNCTION("ACTUAL_BILL_DTM"))>TO_DATE(' 2015-12-08 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
       filter(TRUNC(INTERNAL_FUNCTION("ACTUAL_BILL_DTM"))>TO_DATE(' 2015-12-08 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))



Re: Please help for Improving the Explain plan [message #645530 is a reply to message #645529] Tue, 08 December 2015 03:06 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
1. You might get a better execution if you remove the hint and let the CBO decide whether to use your index.
2. Drop the FBI, create a normal index, and change your predicate to actual_bill_dtm > to_date('2015-12-08','yyyy-mm-dd')

How many rows in the table? How many rows does the query return?
Re: Please help for Improving the Explain plan [message #645534 is a reply to message #645529] Tue, 08 December 2015 03:45 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
My first question is: why would you need to express the WHERE condition as "trunc(actual_bill_dtm) > DATE '2015-12-08'"?

If I understand correctly, you have a timestamp that includes data AND time, right? If so, you can just calculate the date AND TIME as of which you want to get data (in your example it would be '2015-12-09', which is equivalent to '2015-12-09 00:00') and change the condition to:

actual_bill_dtm >= DATE '2015-12-09'


Just a thought.

By the way, John's suggestion is missing the fact that you want to extract records belonging to the NEXT DAY and on (i.e. an entry registered on '2015-12-08 01:00:00' should NOT be included).

[Updated on: Tue, 08 December 2015 03:48]

Report message to a moderator

Re: Please help for Improving the Explain plan [message #645537 is a reply to message #645530] Tue, 08 December 2015 03:48 Go to previous messageGo to next message
msol25
Messages: 368
Registered: June 2011
Senior Member
John,

Table is having 48 million records,But we are retrieving very few rows i.e. just 1000 Records.In my case, FBI table index was not getting used.So,I have given index hint.

I am not in position to delete the index in production.
Re: Please help for Improving the Explain plan [message #645621 is a reply to message #645537] Thu, 10 December 2015 01:20 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
Could you please upload the ddl of your index.
Re: Please help for Improving the Explain plan [message #645626 is a reply to message #645537] Thu, 10 December 2015 03:25 Go to previous messageGo to next message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
msol25 wrote on Tue, 08 December 2015 09:48
But we are retrieving very few rows i.e. just 1000 Records.

Your XPlan doesn't agree with this statement. Are your statistics up to date?

[Updated on: Thu, 10 December 2015 03:25]

Report message to a moderator

Re: Please help for Improving the Explain plan [message #647604 is a reply to message #645626] Wed, 03 February 2016 21:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
** I apologize. After re-reading this post I see the John has already discussed this issue. **

Assuming your stats are up-to-date and the numbers you have posted are accurate, then your query should be doing an INDEX RANGE SCAN, not and INDEX FULL SCAN.

The reason you are not getting the right index usage is because of the trunc function applied to the column in the query (assuming that actual_bill_dtm is a DATE column). I do not understand what trunc is intended to do since

select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   trunc(actual_bill_dtm) > DATE '2015-12-08'


and

select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   actual_bill_dtm > DATE '2015-12-08'


will provide the same answer will they not?

1. make sure stats are up-to-date
2. check to see if there is skew on your date column actual_bill_dtm
3. use this query and let Oracle do the driving

select *
from    dm_irb_gsm.billsummary b
where   actual_bill_dtm > DATE '2015-12-08'



You should get a query plan that uses the index to only fetch the 114K rows out of the 48 million you want.

? is there anything else you have not told that maybe we should know ?

If you want to learn the skill of SQL Tuning, you can get my book from Amazon. Here are some free book related items you can review before you purchase the book.

Provided below are:

1. the first chapter of the book.  Reading this will help you decide if the book is something you are interested in before you spend money on it.
2. the scripts from the book.  You can use these in your tuning work regardless of it you purchase the book or not later.  These are free.
3. an organizational document that will help you record the significant events of a SQL Tuning session so you can explain to someone else later how you solved a problem.
4. a brief description of the kind of info you will want to provide to ORAFaq, when you are looking for detailed tuning help.

These artifacts are free, you do not need to buy the book to use them, and you can give them to others freely as well. Good luck. Kevin



Kevin

[Updated on: Wed, 03 February 2016 21:10]

Report message to a moderator

Re: Please help for Improving the Explain plan [message #647617 is a reply to message #647604] Thu, 04 February 2016 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Thu, 04 February 2016 03:08
I do not understand what trunc is intended to do since

select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   trunc(actual_bill_dtm) > DATE '2015-12-08'


and

select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   actual_bill_dtm > DATE '2015-12-08'


will provide the same answer will they not?



They won't provide the same answer. See FDAVIDOV's post above.
Re: Please help for Improving the Explain plan [message #647848 is a reply to message #647617] Wed, 10 February 2016 00:30 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes you are correct, they will not return the same answer.

Seems to me this does though.

select  /*+ index( b BILLSUMMARY_KPI_OPT_IX) */ *
from    dm_irb_gsm.billsummary b
where   actual_bill_dtm >= DATE '2015-12-09'
Previous Topic: Oracle index creation
Next Topic: Performance issue enq: TX index contention
Goto Forum:
  


Current Time: Thu Feb 22 19:42:10 CST 2018

Total time taken to generate the page: 0.09471 seconds