Home » RDBMS Server » Performance Tuning » Forms Performance
Forms Performance [message #227041] Tue, 27 March 2007 03:32 Go to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 #227447 is a reply to message #227041] Wed, 28 March 2007 05:00 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Made a mistake - corrected it.

[Updated on: Wed, 28 March 2007 05:03]

Report message to a moderator

Re: Forms Performance [message #227871 is a reply to message #227447] Thu, 29 March 2007 22:45 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
michael_bialik
Messages: 611
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
Previous Topic: Sizing a DB
Next Topic: oracle error 35153 temporary tablespace is empty
Goto Forum:
  


Current Time: Wed Dec 07 14:25:04 CST 2016

Total time taken to generate the page: 0.10788 seconds