| Performance Forms 6i - Where clause in block [message #212761] |
Mon, 08 January 2007 03:49  |
ora_dev
Messages: 27
Registered: December 2006
|
Junior Member |
|
|
Hi !
I have a form that takes 15 seconds to retrieve a single record in the test environment. In the live environment the performance is worse. The problem is the code in the WHERE clause of the block.The block is based on a details table called request_lines. The performance improved just a litttle after I created indexes.
The code in the where clause is as follows:
id = '2'
and req in ('L')
and req_status <> 'P'
and doc_type = 'DELIVERY'
and doc_id = (select h.doc_id
from request_headers h
where h.doc_id = request_lines.doc_id
and h.req_status = 'P'
and h.upd_code not null)
Records in the headers table - REQUEST_HEADERS = 877409
Records in the details table - REQUEST_LINES = 882095
In the live environment there is much more records in both tables.
How can I restructure this code to improve performance.
Thanks!
[Updated on: Mon, 08 January 2007 03:50] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Performance Forms 6i - Where clause in block [message #212780 is a reply to message #212778] |
Mon, 08 January 2007 05:44  |
ora_dev
Messages: 27
Registered: December 2006
|
Junior Member |
|
|
I went into Pl/sql developer and ran the statement and it gave me the following stats:
Name: Last: Total:
physical reads 40078 768123
physical writes 0 0
table scans (short tables) 0 22
table scans (long tables) 0 0
table scan rows gotten 0 22
table scan blocks gotten0 22
table fetch by rowid 277428 5274440
sorts (memory) 0 17
sorts (disk) 0 0
sorts (rows) 0 1037
session logical reads 436330 8569014
CPU used by this session0 0
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
