| Forms Performance [message #227041] |
Tue, 27 March 2007 03:32  |
ora_dev
Messages: 27
Registered: December 2006
|
Junior Member |
|
|
Hi
I have a form (6i) which takes a minute when querying a block.
The code in the where clause of the block has:
aocl.status <> 'P'
AND aocl.type = 'DELNOTE'
AND aocl.req = 'L'
AND aocl.ID IN (SELECT bdoc.ID
FROM bdoc
WHERE bdoc.status = 'P'
AND bdoc.TYPE = 'DELNOTE'
AND bdoc.control_code is not null)
If I remove the last 5 lines ie. the IN clause it comes back within seconds. The problem seems to be with the IN clause. Changed code from using the IN clause to EXISTS clause and response didnt improve. There are indexes on both the tables AOCL and BDOC.
In SQL*PLUS when the above code is run.
There are 179890 records in AOCL table that meet the the first 3 lines of the above code. And there are no records in bdoc that meet the inner select statement criteria.
Any suggestions???
Thanks
|
|
|
|
| Re: Forms Performance [message #227051 is a reply to message #227041] |
Tue, 27 March 2007 03:50  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
A couple of question first:
1. How many rows are in BDOC table?
2. Is bdoc.ID an UNIQUE identifier of row in that table?
3. How many rows are in BDOC table corresponds to
bdoc.status = 'P' AND bdoc.TYPE = 'DELNOTE' AND doc.control_code is not null
4. Is there an index on AOCL table having aocl.ID as it's first column?
It's quite possible that the difference(with and without IN clause) that without the clause Oracle starts returning the rows faster, but the execution time will be almost the same.
Post EXPLAIN plan for all queries:
1. as is,
2. EXISTS,
3. without IN
HTH.
|
|
|
|
| Re: Forms Performance [message #227075 is a reply to message #227051] |
Tue, 27 March 2007 04:45 |
ora_dev
Messages: 27
Registered: December 2006
|
Junior Member |
|
|
Hi
1) 1132182
2) Yes ID is unique.
3) Returns none.
4) Yes
5) Explain plan - Rule
As is with the IN clause:
SELECT STATEMENT, GOAL = RULE
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID Object owner=RB Object name=AOCL
INDEX RANGE SCAN Object owner=RB Object name=AOCL_SORTED_IDX_007
TABLE ACCESS BY INDEX ROWID Object owner=RB Object name=BDOC
INDEX UNIQUE SCAN Object owner=RB Object name=BDOC_PK
With exists clause;
SELECT STATEMENT, GOAL = RULE
FILTER
TABLE ACCESS BY INDEX ROWID Object owner=RB Object name=AOCL
INDEX RANGE SCAN Object owner=RBM Object name=AOCL_SORTED_IDX_007
TABLE ACCESS BY INDEX ROWID Object owner=RB Object name=BDOC
INDEX UNIQUE SCAN Object owner=RB Object name=BDOC_PK
Also noticed that when i remove the code " bdoc.status = 'P' "
from the inner select the records are retrieved moch more faster. So I built indexes for status on DBOC table, but the performance is still the same.
Thanks
|
|
|
|
| Re: Forms Performance [message #227098 is a reply to message #227075] |
Tue, 27 March 2007 06:03 |
ora_dev
Messages: 27
Registered: December 2006
|
Junior Member |
|
|
Could someone explain the following:
With the bdoc.status = 'P' in the inner query no records are retrieved and it takes about a minute to come back.
I have noticed that when I remove code bdoc.status = 'P' in the inner select statement the query retrieves rows within seconds. If I changed the code to bdoc.status = 'V' again the query returns rows within seconds.
Bearing in mind that status = 'P' does not return any rows and takes a minute, I thereafter added a union :
aocl.status <> 'P'
AND aocl.type = 'DELNOTE'
AND aocl.req = 'L'
AND aocl.ID IN (SELECT bdoc.ID
FROM bdoc
WHERE bdoc.status = 'P'
AND bdoc.TYPE = 'DELNOTE'
AND bdoc.control_code is not null
UNION
select 0 from dual)
The form comes back within seconds.
Anyone can explain???
Thanks
|
|
|
|
| Re: Forms Performance [message #227153 is a reply to message #227098] |
Tue, 27 March 2007 07:55 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Not sure what the UNION is all about, but I think I understand your other problem.
When matching rows are plentiful, it returns fast. When matching rows are rare, it returns slow.
What is happening is that forms is showing only the first page of matching rows. If you have 1,000,000 rows of which 99% match the query, you will find a page-worth very quickly. If <1% of rows match, you will have to read the whole 1,000,000 rows to find just a page of results.
Work out what is the most restrictive filtering clause in the query. Is it the sub-query that is responsible for filtering the most rows? Or one of the other predicates. Once you know that, you want to drive the query off that predicate using an index or partition prune.
Ross Leishman
|
|
|
|
| Re: Forms Performance [message #227445 is a reply to message #227041] |
Wed, 28 March 2007 04:59 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
Ross explained what's happening with your query very well.
I would like you to try something else:
1. CREATE INDEX BDOC$IDX ON BDOC ( TYPE, STATUS, CONTROL_CODE ) ...
2. Change the query :
SELECT ...
FROM AOCL, BDOC
WHERE
aocl.status <> 'P'
AND aocl.type = 'DELNOTE'
AND aocl.req = 'L'
AND aocl.ID = bdoc.ID
AND bdoc.status = 'P'
AND bdoc.TYPE = 'DELNOTE'
AND bdoc.control_code > ' ' /* OR bdoc.control_code > 0 - If it's NUMERIC column */
Post results and EXPLAIN of a new query.
HTH. Michael
[Updated on: Wed, 28 March 2007 05:06] Report message to a moderator |
|
|
|
|
|
| Re: Forms Performance [message #227871 is a reply to message #227447] |
Thu, 29 March 2007 22:45 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Michael, did you forget to include bdoc.ID as the last column in the new index, as this would avoid a table lookup.
Ross Leishman
|
|
|
|
| Re: Forms Performance [message #227902 is a reply to message #227041] |
Fri, 30 March 2007 02:13 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi, Ross.
Actually, I was thinking about it, but when I asked
| Quote: | 3. How many rows are in BDOC table corresponds to
bdoc.status = 'P' AND bdoc.TYPE = 'DELNOTE' AND doc.control_code is not null
?
|
and the answer wal NULL, I decided not to overload the index.
I intended to make BDOC the driving table, so in that case only very small number of rows will be accessed.
P.S. However it still may be good idea.
Michael
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
