Home » RDBMS Server » Performance Tuning » Performance Forms 6i - Where clause in block
Performance Forms 6i - Where clause in block [message #212761] Mon, 08 January 2007 03:49 Go to next message
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 #212765 is a reply to message #212761] Mon, 08 January 2007 04:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you collect the statistics on base tables and indexes? If not
first collect stats and try again.
If yes, then how did you collect the stats? post the exact command
Re: Performance Forms 6i - Where clause in block [message #212778 is a reply to message #212765] Mon, 08 January 2007 05:35 Go to previous messageGo to next message
ora_dev
Messages: 27
Registered: December 2006
Junior Member
Hi

How do u collect the statistics. What command do I use? I am a developer.

Thankx
Re: Performance Forms 6i - Where clause in block [message #212780 is a reply to message #212778] Mon, 08 January 2007 05:44 Go to previous messageGo to next message
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
Re: Performance Forms 6i - Where clause in block [message #212782 is a reply to message #212780] Mon, 08 January 2007 05:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
use sql*plus or OEM or whatever tool you use to analyze collect stats.
In sqlplus
sql> exec dbms_stats.gather_schema_stats('SCOTT',METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 250',CASCADE=>TRUE);
Re: Performance Forms 6i - Where clause in block [message #212793 is a reply to message #212761] Mon, 08 January 2007 07:09 Go to previous messageGo to next message
ora_dev
Messages: 27
Registered: December 2006
Junior Member
Hi Mahesh

I have run the command via sql*plus and the session did not come back and I ended up cancelling the job. Does it write the stats to a table???

Re: Performance Forms 6i - Where clause in block [message #212796 is a reply to message #212793] Mon, 08 January 2007 07:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If you are talking about a high volume database, this could take a while. The CBO needs a certain statistics on table and indexes. These statistics are collected and stored in dictionary.
The said procedure will collect stats for all objects in schema.
You can try for only the tables that are involved in your query. If the tables are huge, it may take a while
sql> exec dbms_stats.gather_table_stats('SCOTT','THIS_TABLE',METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 250',CASCADE=>TRUE);
sql> exec dbms_stats.gather_table_stats('SCOTT','ANOTHER_TABLE',METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 250',CASCADE=>TRUE);
Re: Performance Forms 6i - Where clause in block [message #212819 is a reply to message #212761] Mon, 08 January 2007 08:05 Go to previous messageGo to next message
ora_dev
Messages: 27
Registered: December 2006
Junior Member
Hi Mahesh

I ran them separately. Attached are the stats.Here is request_headers.
Re: Performance Forms 6i - Where clause in block [message #212821 is a reply to message #212761] Mon, 08 January 2007 08:05 Go to previous message
ora_dev
Messages: 27
Registered: December 2006
Junior Member
And here is request_lines.

Thankx
Previous Topic: Question about parsing and Bind variables
Next Topic: Merge over dblink - too slow..
Goto Forum:
  


Current Time: Sun Dec 01 10:29:13 CST 2024