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 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
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:
http://homepage.ntlworld.com/slythgoe/car/explain_plan.JPG

Explain Plan with my Hints:
http://homepage.ntlworld.com/slythgoe/car/explain_plan_with_my_index_hints.JPG

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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 #431295 is a reply to message #431268] Mon, 16 November 2009 08:58 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why has that query got a group by? You're not selecting any aggregate functions so you shouldn't need a group by.
Re: Performance of a Query [message #431298 is a reply to message #431268] Mon, 16 November 2009 09:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 #431301 is a reply to message #431268] Mon, 16 November 2009 10:11 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which version of oracle are you running?
On 10g + gather stats is run nightly.
Re: Performance of a Query [message #431302 is a reply to message #431300] Mon, 16 November 2009 10:21 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
cookiemonster wrote on Mon, 16 November 2009 16:10
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.


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 #431303 is a reply to message #431268] Mon, 16 November 2009 10:29 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
What exact query did you use to get the index list? I ask because the order of columns within an index is important.
Re: Performance of a Query [message #431306 is a reply to message #431268] Mon, 16 November 2009 10:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
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 #431330 is a reply to message #431329] Mon, 16 November 2009 20:32 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>just curious about how often to update stats in oracle.
You should have started your own thread & not hijack this one.

DBMS_STATS job runs once a day.
Statistics are collected only for object which have changed more than 10%

Re: Performance of a Query [message #431373 is a reply to message #431268] Tue, 17 November 2009 03:39 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
cookiemonster wrote on Mon, 16 November 2009 17:08
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.


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? Embarassed

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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
garylythgoe wrote on Tue, 17 November 2009 09:39
cookiemonster wrote on Mon, 16 November 2009 17:08
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.


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? Embarassed

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 #431406 is a reply to message #431399] Tue, 17 November 2009 04:29 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
cookiemonster wrote on Tue, 17 November 2009 10:11
It's not strictly true, index skip scans can kick in some cases.
Have a read of this ask tom thread for more details.


Will do! Just what the doctor ordered.

Quote:
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.


Lost the hints, and it seems to be a bit better now. Right so if a query is running like a dog you can try and use hints to highlight the problem, and then fix it with a new index if needed.

Quote:
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.


Will do, im really keen to upskill here.

Quote:
I keep expecting Noah to turn up.


Laughing Thumbs Up

My explain plan looks like this now:

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.


I'm not sure if its using the new index as it isnt referenced in the explain plan?

Gary
Re: Performance of a Query [message #431455 is a reply to message #431268] Tue, 17 November 2009 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
cookiemonster wrote on Tue, 17 November 2009 12:50
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');


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 #431719 is a reply to message #431268] Thu, 19 November 2009 04:21 Go to previous messageGo to next message
garylythgoe
Messages: 39
Registered: February 2008
Location: Cambridge UK
Member
Have you gone quiet on me cookiemonster?

I was just getting really into it, i'm purchasing the book 'Expert one on one Oracle' by Tom Kyte after your link to that thread Razz
Re: Performance of a Query [message #431755 is a reply to message #431268] Thu, 19 November 2009 06:36 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: How to tune a update statement
Next Topic: Very high executions (Parse=Execute)
Goto Forum:
  


Current Time: Sat Dec 03 16:05:13 CST 2016

Total time taken to generate the page: 0.07736 seconds