Home » RDBMS Server » Performance Tuning » Performance of a Query (11.5.10, 10g Database)
| Performance of a Query [message #431268] |
Mon, 16 November 2009 06:55  |
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
|
Member |
|
|
All,
I'm not looking for the answer here, im looking to learn! I have a query which takes a fair while to run and i want to speed up its performance.
I am familiar with the Explain Plan, and am aware that lots of full table scans are bad! Correct me if im wrong, but the database should be using the cost based optimiser to find the best way of executing the query but i really dont believe it is in this case.
select
ph.segment1 po_num,
pl.LINE_NUM,
pd.DISTRIBUTION_NUM,
ph.CREATION_DATE,
gcc.SEGMENT1,
pv.vendor_name,
pvs.vendor_site_code,
rh.SEGMENT1 req_num,
cat.segment1||'.'||cat.segment2||'.'||cat.segment3,
prep.FULL_NAME preparer
from po_headers_all ph,
po_lines_all pl,
po_distributions_all pd,
po_req_distributions_all rd,
po_requisition_headers_all rh,
gl_code_combinations gcc,
po_vendors pv,
po_vendor_sites_all pvs,
mtl_categories_b cat,
per_all_people_f prep,
po_requisition_lines_all rl
where ph.org_id = 8073
and ph.PO_HEADER_ID = pl.po_header_id
and pl.PO_LINE_ID = pd.PO_LINE_ID
and pd.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
and pd.REQ_DISTRIBUTION_ID = rd.DISTRIBUTION_ID
and rd.REQUISITION_LINE_ID = rl.REQUISITION_LINE_ID
and rl.REQUISITION_HEADER_ID = rh.REQUISITION_HEADER_ID
and pl.CATEGORY_ID = cat.CATEGORY_ID
and rh.PREPARER_ID = prep.PERSON_ID
and ph.vendor_id = pv.VENDOR_ID
and ph.VENDOR_SITE_ID = pvs.VENDOR_SITE_ID
and ph.creation_date >= '01-MAR-2009'
and trunc(rh.creation_date) BETWEEN prep.EFFECTIVE_START_DATE AND prep.EFFECTIVE_END_DATE
group by ph.segment1,
pl.line_num,
pd.DISTRIBUTION_NUM,
ph.CREATION_DATE,
gcc.SEGMENT1,
pv.vendor_name,
pvs.vendor_site_code,
rh.SEGMENT1,
cat.segment1||'.'||cat.segment2||'.'||cat.segment3,
prep.FULL_NAME
order by ph.segment1, pl.line_num
Now... with a bit of google and searching, i've added the following
/*+ index(prep per_people_f_pk(person_id))
index(cat mtl_categories_b_u1(category_id))
index(pl po_lines_u1(po_line_id))
*/
It seems to run a bit quicker with the above in, but im convinced it can run quicker and i would love to understand it a bit more. Im familiar with what an index is/does but i just really want to build on my knowledge here.
Looking for any input, it would be much appreciated.
Many thanks,
Gary
|
|
|
|
| Re: Performance of a Query [message #431278 is a reply to message #431268] |
Mon, 16 November 2009 07:25  |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you want input from us I suggest you post the explain plans with and without the hint, along with row counts for each table, the number of rows returned by the query and a list of the available indexes.
We've got no idea what that query does, how much data it's selecting or anything else useful to tell us why you need to add hints to speed it up.
If you give us something to work with it'll be a lot easier to explain what you (or the database) are missing.
One obvious possible problem though is these two lines:
and ph.creation_date >= '01-MAR-2009'
and trunc(rh.creation_date) BETWEEN prep.EFFECTIVE_START_DATE AND prep.EFFECTIVE_END_DATE
If you've got an index on creation_date it won't be used due to the trunc. Functions on columns always prevent index usage unless you've got a function based index.
And you should to_date the 01-MAR-2009 to avoid any problems from implicit conversion - this can actually give you wrong answer never mind bad performance.
Also note that full table scans are not necessarily bad. Indexes are only really useful when selecting a small subset of data from a table. Otherwise full table scans are usually more efficient. Though I'm guessing they're probably not for this particular query.
|
|
|
|
| Re: Performance of a Query [message #431285 is a reply to message #431268] |
Mon, 16 November 2009 07:52 |
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
|
Member |
|
|
Hi,
Thanks for a response.
Apologies for not posting the explain plan(s).
I struggled to get a readable effort from TOAD, so i have exported to Excel and taken a print screen of each:
Explain Plan:
Explain Plan with my Hints:
Its a query which selects information about Purchase Orders, and the relating Requisition.
The user expectation of this query is that it is 'simple' and should only take seconds not minutes.
Quote:If you've got an index on creation_date it won't be used due to the trunc. Functions on columns always prevent index usage unless you've got a function based index.
And you should to_date the 01-MAR-2009 to avoid any problems from implicit conversion - this can actually give you wrong answer never mind bad performance.
I've not told it to use an index on creation date, but should the cost based optimiser not take it into consideration. Also understood about the date, this is very valid.
Quote:Also note that full table scans are not necessarily bad. Indexes are only really useful when selecting a small subset of data from a table. Otherwise full table scans are usually more efficient. Though I'm guessing they're probably not for this particular query.
I do understand this, i think it was being over simplified when explained to me, and i've got a bad habit now in terms of thought process, but what your saying is very valid.
Thanks,
Gary
|
|
|
|
| Re: Performance of a Query [message #431293 is a reply to message #431285] |
Mon, 16 November 2009 08:54 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
garylythgoe wrote on Mon, 16 November 2009 13:52
I struggled to get a readable effort from TOAD, so i have exported to Excel and taken a print screen of each:
To get nicely formatted explain plans to post here use sqlplus instead of toad. Use this example to guide you:
SQL> set pages 100
SQL> set lines 150
SQL> explain plan for select * from dual where 1=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
garylythgoe wrote on Mon, 16 November 2009 13:52
Quote:If you've got an index on creation_date it won't be used due to the trunc. Functions on columns always prevent index usage unless you've got a function based index.
And you should to_date the 01-MAR-2009 to avoid any problems from implicit conversion - this can actually give you wrong answer never mind bad performance.
I've not told it to use an index on creation date, but should the cost based optimiser not take it into consideration.
No. As I said if the column has function on it (trunc, trim, to_char, to_date, rpad etc) then the CBO can not consider it for index usage unless there is a function based index that matches - have a read up on those in the documentation.
Reprint the explain plans using the method above. Also give us the row counts. Those explain plans indicate a lot of data being read.
Finally - are the statistics up to date? Since a index hint noticeably increases performance I would assume the current stats are inaccurate or the CBO would have decided to use the indexes in the first place.
|
|
|
|
|
|
| Re: Performance of a Query [message #431298 is a reply to message #431268] |
Mon, 16 November 2009 09:26 |
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
|
Member |
|
|
cookiemonster, i really appreciate your help here btw.
Right its been a while since i've used sqlplus (toad is a luxury! teehee!) but here goes:
(I've taken out the group by, i was playing with something else earlier).
Explain Plan
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 280 | 71120 | 41835 |
| 1 | SORT ORDER BY | | 280 | 71120 | 41835 |
| 2 | NESTED LOOPS | | 280 | 71120 | 41834 |
| 3 | NESTED LOOPS | | 279 | 63891 | 41555 |
| 4 | NESTED LOOPS | | 279 | 60264 | 40939 |
| 5 | HASH JOIN | | 279 | 56079 | 40660 |
| 6 | HASH JOIN | | 47160 | 7276K| 37786 |
| 7 | HASH JOIN | | 47160 | 6125K| 34507 |
| 8 | HASH JOIN | | 47291 | 5680K| 22897 |
| 9 | HASH JOIN | | 47291 | 5218K| 18406 |
| 10 | HASH JOIN | | 49997 | 4638K| 11598 |
| 11 | TABLE ACCESS FULL | MTL_CATEGORIES_B | 861 | 36162 | 9 |
| 12 | HASH JOIN | | 49997 | 2587K| 11588 |
| 13 | TABLE ACCESS FULL | PO_HEADERS_ALL | 14416 | 492K| 3589 |
| 14 | TABLE ACCESS FULL | PO_LINES_ALL | 610K| 10M| 7994 |
| 15 | TABLE ACCESS FULL | PO_DISTRIBUTIONS_ALL | 577K| 9M| 5725 |
| 16 | TABLE ACCESS FULL | PO_REQ_DISTRIBUTIONS_ALL | 610K| 5964K| 3567 |
| 17 | TABLE ACCESS FULL | PO_REQUISITION_LINES_ALL | 602K| 5888K| 10670 |
| 18 | TABLE ACCESS FULL | PO_REQUISITION_HEADERS_ALL | 279K| 6822K| 2460 |
| 19 | TABLE ACCESS FULL | PER_ALL_PEOPLE_F | 156K| 6557K| 2085 |
| 20 | TABLE ACCESS BY INDEX ROWID| PO_VENDOR_SITES_ALL | 1 | 15 | 1 |
| 21 | INDEX UNIQUE SCAN | PO_VENDOR_SITES_U1 | 1 | | 0 |
| 22 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 13 | 2 |
| 23 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 |
| 24 | TABLE ACCESS BY INDEX ROWID | PO_VENDORS | 1 | 25 | 1 |
| 25 | INDEX UNIQUE SCAN | PO_VENDORS_U1 | 1 | | 0 |
----------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
35 rows selected.
Explain Plan with my hints:
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 280 | 71120 | 241K|
| 1 | SORT ORDER BY | | 280 | 71120 | 241K|
| 2 | NESTED LOOPS | | 280 | 71120 | 241K|
| 3 | NESTED LOOPS | | 279 | 63891 | 241K|
| 4 | NESTED LOOPS | | 279 | 60264 | 240K|
| 5 | HASH JOIN | | 279 | 56079 | 240K|
| 6 | HASH JOIN | | 47160 | 7276K| 125K|
| 7 | HASH JOIN | | 47160 | 6125K| 122K|
| 8 | HASH JOIN | | 47291 | 5680K| 110K|
| 9 | HASH JOIN | | 47291 | 5218K| 106K|
| 10 | HASH JOIN | | 49997 | 4638K| 99387 |
| 11 | TABLE ACCESS FULL | PO_HEADERS_ALL | 14416 | 492K| 3589 |
| 12 | HASH JOIN | | 610K| 34M| 95792 |
| 13 | TABLE ACCESS BY INDEX ROWID| MTL_CATEGORIES_B | 861 | 36162 | 81 |
| 14 | INDEX FULL SCAN | MTL_CATEGORIES_B_U1 | 861 | | 3 |
| 15 | TABLE ACCESS BY INDEX ROWID| PO_LINES_ALL | 610K| 10M| 95707 |
| 16 | INDEX FULL SCAN | PO_LINES_U1 | 610K| | 1212 |
| 17 | TABLE ACCESS FULL | PO_DISTRIBUTIONS_ALL | 577K| 9M| 5725 |
| 18 | TABLE ACCESS FULL | PO_REQ_DISTRIBUTIONS_ALL | 610K| 5964K| 3567 |
| 19 | TABLE ACCESS FULL | PO_REQUISITION_LINES_ALL | 602K| 5888K| 10670 |
| 20 | TABLE ACCESS FULL | PO_REQUISITION_HEADERS_ALL | 279K| 6822K| 2460 |
| 21 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 156K| 6557K| 113K|
| 22 | INDEX FULL SCAN | PER_PEOPLE_F_PK | 156K| | 962 |
| 23 | TABLE ACCESS BY INDEX ROWID | PO_VENDOR_SITES_ALL | 1 | 15 | 1 |
| 24 | INDEX UNIQUE SCAN | PO_VENDOR_SITES_U1 | 1 | | 0 |
| 25 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 13 | 2 |
| 26 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 |
| 27 | TABLE ACCESS BY INDEX ROWID | PO_VENDORS | 1 | 25 | 1 |
| 28 | INDEX UNIQUE SCAN | PO_VENDORS_U1 | 1 | | 0 |
----------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
38 rows selected.
Oh and Gather Schema Statistics is run once a week, takes a few hours.
Regards,
Gary
[Updated on: Mon, 16 November 2009 09:28] Report message to a moderator |
|
|
|
| Re: Performance of a Query [message #431300 is a reply to message #431268] |
Mon, 16 November 2009 10:10 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You still haven't supplied any row counts.
However I think the problem is all down to the PO_HEADERS_ALL table. It's the only one in your query that you are applying predicates to rather than joins but it's a full table scan in both versions of the query.
So post the results of the following queries:
SELECT COUNT(*)
from po_headers_all
where org_id = 8073
and creation_date >= to_date('01-MAR-2009', 'DD-MON-YYYY');
SELECT COUNT(*)
from po_headers_all;
and post the list of indexes and their columns on that table.
|
|
|
|
|
|
| Re: Performance of a Query [message #431302 is a reply to message #431300] |
Mon, 16 November 2009 10:21 |
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
|
Member |
|
|
cookiemonster wrote on Mon, 16 November 2009 16:10You still haven't supplied any row counts.
However I think the problem is all down to the PO_HEADERS_ALL table. It's the only one in your query that you are applying predicates to rather than joins but it's a full table scan in both versions of the query.
So post the results of the following queries:
SELECT COUNT(*)
from po_headers_all
where org_id = 8073
and creation_date >= to_date('01-MAR-2009', 'DD-MON-YYYY');
SELECT COUNT(*)
from po_headers_all;
and post the list of indexes and their columns on that table.
Query 1: 45776
Query 2: 355289
Here are the list of indexes and columns on that table:
Index Name Column Name
PO_HEADERS_F1 DECODE("CONSIGNED_CONSUMPTION_FLAG",'Y',DECODE("CLOSED_CODE", Asc
PO_HEADERS_N1 VENDOR_ID
PO_HEADERS_N1 VENDOR_SITE_ID
PO_HEADERS_N1 TYPE_LOOKUP_CODE
PO_HEADERS_N10 ENCUMBRANCE_REQUIRED_FLAG
PO_HEADERS_N11 AUTHORIZATION_STATUS
PO_HEADERS_N2 CREATION_DATE
PO_HEADERS_N20 OTM_RECOVERY_FLAG
PO_HEADERS_N3 AGENT_ID
PO_HEADERS_N4 FROM_HEADER_ID
PO_HEADERS_N5 WF_ITEM_TYPE
PO_HEADERS_N5 WF_ITEM_KEY
PO_HEADERS_N6 CLOSED_DATE
PO_HEADERS_N7 QUOTE_VENDOR_QUOTE_NUMBER
PO_HEADERS_N8 VENDOR_ORDER_NUM
PO_HEADERS_N9 LAST_UPDATE_DATE
PO_HEADERS_U1 PO_HEADER_ID
PO_HEADERS_U2 SEGMENT1
PO_HEADERS_U2 TYPE_LOOKUP_CODE
PO_HEADERS_U2 ORG_ID
As for versioning, we have :
• Oracle RDBMS 10.2.0.3
• Oracle Applications 11.5.10.2
Regards,
Gary
[Updated on: Mon, 16 November 2009 10:22] Report message to a moderator |
|
|
|
|
|
| Re: Performance of a Query [message #431306 is a reply to message #431268] |
Mon, 16 November 2009 10:50 |
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
|
Member |
|
|
If you do a Describe on the table in Toad (CTRL+click table name), and there is an index tab, i just exported the list out of there, not including any ordering.
Running the following:
SELECT INDEX_OWNER,
INDEX_NAME,
COLUMN_NAME,
COLUMN_POSITION
FROM DBA_IND_COLUMNS
WHERE TABLE_NAME='PO_HEADERS_ALL'
ORDER BY INDEX_NAME, COLUMN_POSITION
I get:
OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION
PO PO_HEADERS_F1 SYS_NC00145$ 1
PO PO_HEADERS_N1 VENDOR_ID 1
PO PO_HEADERS_N1 VENDOR_SITE_ID 2
PO PO_HEADERS_N1 TYPE_LOOKUP_CODE 3
PO PO_HEADERS_N10 ENCUMBRANCE_REQUIRED_FLAG 1
PO PO_HEADERS_N11 AUTHORIZATION_STATUS 1
PO PO_HEADERS_N2 CREATION_DATE 1
PO PO_HEADERS_N20 OTM_RECOVERY_FLAG 1
PO PO_HEADERS_N3 AGENT_ID 1
PO PO_HEADERS_N4 FROM_HEADER_ID 1
PO PO_HEADERS_N5 WF_ITEM_TYPE 1
PO PO_HEADERS_N5 WF_ITEM_KEY 2
PO PO_HEADERS_N6 CLOSED_DATE 1
PO PO_HEADERS_N7 QUOTE_VENDOR_QUOTE_NUMBER 1
PO PO_HEADERS_N8 VENDOR_ORDER_NUM 1
PO PO_HEADERS_N9 LAST_UPDATE_DATE 1
PO PO_HEADERS_U1 PO_HEADER_ID 1
PO PO_HEADERS_U2 SEGMENT1 1
PO PO_HEADERS_U2 TYPE_LOOKUP_CODE 2
PO PO_HEADERS_U2 ORG_ID 3
Hope this is more useful?
Gary
|
|
|
|
| Re: Performance of a Query [message #431309 is a reply to message #431268] |
Mon, 16 November 2009 11:08 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Ok there are two different indexes that reference the relevant columns. Org_id is in PO_HEADERS_U2 and CREATION_DATE is in PO_HEADERS_N2.
Oracle isn't going to be able to use PO_HEADERS_U2 since the column you want to search one is third in list. Oracle rarely uses multi-column indexes if it can't reference the first column in the list.
Oracle is choosing not to use PO_HEADERS_N2, presumably because it thinks it's not selective enough, or maybe because of the implicit conversion. You can see how selective it should be by doing:
SELECT COUNT(*)
from po_headers_all
where creation_date >= to_date('01-MAR-2009', 'DD-MON-YYYY');
Try creating a new index on org_id and creation_date, in that order. Then run an explain plan for your query. Make sure you add a to_date for the date I pointed out earlier.
|
|
|
|
| Re: Performance of a Query [message #431329 is a reply to message #431301] |
Mon, 16 November 2009 20:19 |
yuanqc
Messages: 2
Registered: November 2009
|
Junior Member |
|
|
hi
just curious about how often to update stats in oracle.
can help to explain? thanks in advance.
for a example:(version:10.2.0.3.0)
SQL> select LAST_ANALYZED from user_tables where rownum<5;
LAST_ANALYZED
-------------------
2009-10-27 22:08:24
2009-11-03 22:00:26
2009-03-23 22:01:16
2009-10-12 22:00:49
|
|
|
|
|
|
| Re: Performance of a Query [message #431373 is a reply to message #431268] |
Tue, 17 November 2009 03:39 |
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
|
Member |
|
|
cookiemonster wrote on Mon, 16 November 2009 17:08Ok there are two different indexes that reference the relevant columns. Org_id is in PO_HEADERS_U2 and CREATION_DATE is in PO_HEADERS_N2.
Oracle isn't going to be able to use PO_HEADERS_U2 since the column you want to search one is third in list. Oracle rarely uses multi-column indexes if it can't reference the first column in the list.
Great, that makes sense. I didnt know it wont use an index if it cant use the first column in an index. Seems a bit strange. If this is the case, i fear we have quite a few which can be improved.
Quote:Oracle is choosing not to use PO_HEADERS_N2, presumably because it thinks it's not selective enough, or maybe because of the implicit conversion. You can see how selective it should be by doing:
SELECT COUNT(*)
from po_headers_all
where creation_date >= to_date('01-MAR-2009', 'DD-MON-YYYY');
This makes sense i think. Because the date is not being selected as a 'true date', its ignoring the index? If that kind of makes sense.
Quote:
Try creating a new index on org_id and creation_date, in that order. Then run an explain plan for your query. Make sure you add a to_date for the date I pointed out earlier.
Right, i've created a new index as follows:
CREATE INDEX PO.PO_HEADERS_LGSS1 ON PO.PO_HEADERS_ALL
(ORG_ID, CREATION_DATE)
TABLESPACE POX
Index has created ok, do i need to use a hint to point to it in the code? aka:
/*+ index(ph po_headers_lgss1(org_id, creation_date)) */
Is it worth getting rid of my other hints aswell?
After this im going to try and understand the 'whys' if thats ok? 
Many thanks,
Gary
P.S Just noticed your in the UK, what a joy it is at this time of the year.. not!
|
|
|
|
| Re: Performance of a Query [message #431399 is a reply to message #431373] |
Tue, 17 November 2009 04:11 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
garylythgoe wrote on Tue, 17 November 2009 09:39cookiemonster wrote on Mon, 16 November 2009 17:08Ok there are two different indexes that reference the relevant columns. Org_id is in PO_HEADERS_U2 and CREATION_DATE is in PO_HEADERS_N2.
Oracle isn't going to be able to use PO_HEADERS_U2 since the column you want to search one is third in list. Oracle rarely uses multi-column indexes if it can't reference the first column in the list.
Great, that makes sense. I didnt know it wont use an index if it cant use the first column in an index. Seems a bit strange. If this is the case, i fear we have quite a few which can be improved.
It's not strictly true, index skip scans can kick in some cases.
Have a read of this ask tom thread for more details.
garylythgoe wrote on Tue, 17 November 2009 09:39
Quote:Oracle is choosing not to use PO_HEADERS_N2, presumably because it thinks it's not selective enough, or maybe because of the implicit conversion. You can see how selective it should be by doing:
SELECT COUNT(*)
from po_headers_all
where creation_date >= to_date('01-MAR-2009', 'DD-MON-YYYY');
This makes sense i think. Because the date is not being selected as a 'true date', its ignoring the index? If that kind of makes sense.
With the to_date there it'll definitely use the index if it thinks that'll be faster than a full table scan. However if the CBO thinks a significant percentage of the data in the table matches that predicate then it'll assume a full table scan is faster. If you were doing a = rather than a >= it'd be far more likely to use the index.
garylythgoe wrote on Tue, 17 November 2009 09:39
Quote:
Try creating a new index on org_id and creation_date, in that order. Then run an explain plan for your query. Make sure you add a to_date for the date I pointed out earlier.
Right, i've created a new index as follows:
CREATE INDEX PO.PO_HEADERS_LGSS1 ON PO.PO_HEADERS_ALL
(ORG_ID, CREATION_DATE)
TABLESPACE POX
Index has created ok, do i need to use a hint to point to it in the code? aka:
/*+ index(ph po_headers_lgss1(org_id, creation_date)) */
Is it worth getting rid of my other hints aswell?
I'd lose all the hints and see what happens. If you're having to use hints then that generally means the CBO is making some wrong assumptions, it's always better to fix that problem at source if possible instead of masking it with hints. Hints are a last resort as a fix, they can be useful to help work out what the optimiser is doing though.
garylythgoe wrote on Tue, 17 November 2009 09:39
After this im going to try and understand the 'whys' if thats ok?
Always a good idea. Have a read of the ask tom thread and some of the others he links to in there. Also have a read of the performance tuning sticky at the top this forum if you haven't already.
Quote:
P.S Just noticed your in the UK, what a joy it is at this time of the year.. not!
I keep expecting Noah to turn up.
|
|
|
|
|
|
| Re: Performance of a Query [message #431455 is a reply to message #431268] |
Tue, 17 November 2009 06:50 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If the index is not in the explain plan it ain't being used.
Check if the index has been analyzed - see the last_analyzed column on all_indexes.
If it hasn't, analyze it using dbms_stats.
If it has, run an explain for this query and see if the index is used:
SELECT COUNT(*)
from po_headers_all
where org_id = 8073
and creation_date >= to_date('01-MAR-2009', 'DD-MON-YYYY');
If it isn't used add a hint to use that index, run the explain plan again and post both plans here.
If the index is used add the hint to use that index to your proper query (no other hints mind) and get the explain plan for that and post it.
|
|
|
|
| Re: Performance of a Query [message #431465 is a reply to message #431455] |
Tue, 17 November 2009 07:16 |
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
|
Member |
|
|
cookiemonster wrote on Tue, 17 November 2009 12:50If the index is not in the explain plan it ain't being used.
Check if the index has been analyzed - see the last_analyzed column on all_indexes.
If it hasn't, analyze it using dbms_stats.
If it has, run an explain for this query and see if the index is used:
SELECT COUNT(*)
from po_headers_all
where org_id = 8073
and creation_date >= to_date('01-MAR-2009', 'DD-MON-YYYY');
Yep its been analyzed, and is used when running the above query.
Quote:
If the index is used add the hint to use that index to your proper query (no other hints mind) and get the explain plan for that and post it.
Right i added that hint, making it the the only hint, and this is the explain plan for that query, takes a while still:
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 280 | 71120 | 42251 |
| 1 | SORT ORDER BY | | 280 | 71120 | 42251 |
| 2 | NESTED LOOPS | | 280 | 71120 | 42250 |
| 3 | NESTED LOOPS | | 279 | 63891 | 41970 |
| 4 | NESTED LOOPS | | 279 | 60264 | 41355 |
| 5 | HASH JOIN | | 279 | 56079 | 41076 |
| 6 | HASH JOIN | | 47160 | 7276K| 38202 |
| 7 | HASH JOIN | | 47160 | 6125K| 34923 |
| 8 | HASH JOIN | | 47291 | 5680K| 23313 |
| 9 | HASH JOIN | | 47291 | 5218K| 18821 |
| 10 | HASH JOIN | | 49997 | 4638K| 12014 |
| 11 | TABLE ACCESS FULL | MTL_CATEGORIES_B | 861 | 36162 | 9 |
| 12 | HASH JOIN | | 49997 | 2587K| 12004 |
| 13 | TABLE ACCESS BY INDEX ROWID| PO_HEADERS_ALL | 14416 | 492K| 4005 |
| 14 | INDEX RANGE SCAN | PO_HEADERS_LGSS1 | 14435 | | 47 |
| 15 | TABLE ACCESS FULL | PO_LINES_ALL | 610K| 10M| 7994 |
| 16 | TABLE ACCESS FULL | PO_DISTRIBUTIONS_ALL | 577K| 9M| 5725 |
| 17 | TABLE ACCESS FULL | PO_REQ_DISTRIBUTIONS_ALL | 610K| 5964K| 3567 |
| 18 | TABLE ACCESS FULL | PO_REQUISITION_LINES_ALL | 602K| 5888K| 10670 |
| 19 | TABLE ACCESS FULL | PO_REQUISITION_HEADERS_ALL | 279K| 6822K| 2460 |
| 20 | TABLE ACCESS FULL | PER_ALL_PEOPLE_F | 156K| 6557K| 2085 |
| 21 | TABLE ACCESS BY INDEX ROWID | PO_VENDOR_SITES_ALL | 1 | 15 | 1 |
| 22 | INDEX UNIQUE SCAN | PO_VENDOR_SITES_U1 | 1 | | 0 |
| 23 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 13 | 2 |
| 24 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 |
| 25 | TABLE ACCESS BY INDEX ROWID | PO_VENDORS | 1 | 25 | 1 |
| 26 | INDEX UNIQUE SCAN | PO_VENDORS_U1 | 1 | | 0 |
-----------------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
36 rows selected.
Bit puzzled why its not using it?
|
|
|
|
|
|
| Re: Performance of a Query [message #431755 is a reply to message #431268] |
Thu, 19 November 2009 06:36 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Apologies - busy doing my day job.
It looks like oracle thinks the FTS is faster than the index scan.
See how the cost of accessing the PO_HEADERS_ALL goes up when you add the hint.
We need to work out if oracle is right about this.
So
Post row counts for each table.
Post row count for the actual query.
Post time taken to run query with no hints.
Post time taken to run query with new hint.
Post time taken to run query with the set of hints you were originally using.
Post time you expect it to run in.
|
|
|
|
| Re: Performance of a Query [message #432129 is a reply to message #431268] |
Sat, 21 November 2009 09:31 |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
One thing about the DATE BETWEEN:
If you look onto statistics then you might notice that it would access only data thet are >= minimal value... and the upper limit is a filter scan. If you seek opimization then you might probably optimize that part as well.
|
|
|
|
| Re: Performance of a Query [message #432325 is a reply to message #431268] |
Mon, 23 November 2009 10:09 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
Hi,
Quote:
Note
-----
- 'PLAN_TABLE' is old version
38 rows selected.
If you don't mind could you please re-create your plan table and re-post the explain plan. It will give us more information about the filter predicates applied and what it is doing.
Also if you could if you execute the query along with the mentioned hint and post the output of the query immediately after executing, it will be much useful.
For how to do it check this link.
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/
It will be help us understand what the estimate and the actuals are.
Regards
Raj
|
|
|
|
Goto Forum:
Current Time: Sat May 18 07:15:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
