Oracle Forms 6i Query Performance Issue and Oracle forms 6i performance issue (merged-djm) [message #289933] |
Wed, 26 December 2007 13:15 |
oracle1001
Messages: 4 Registered: December 2007 Location: chennnai
|
Junior Member |
|
|
Hi All,
I'm using oracle forms6i and Oracle DB 9i.
I'm facing the performance issue in query forms.
In detail block form taking long time to load the data.
Form contains 2 non data blocks
1.HDR - 3 input parameters
2.DETAILS - Grid - Details
HDR input fields
1.Company Code
2.Company ACccount No
3.Customer Name
Details Grid is displayed the details.
Here there are 2 tables involved
1.Table1 - 1 crore records
2.Table2 - 4 crore records
In form procedure one cursor bulid and fetch is done directly and assign the values to form block fields.
Below i've pasted the query
-----------
SELECT
t1.entry_dt,
t2.authoriser_code,
t1.company_code,
t1.company_ac_no
initcap(t1.customer_name) cust_name,
t2.agreement_no
t1.customer_id
FROM
table1 t1,
table2 t2
WHERE
(t2.trans_no = t1.trans_no or t2.temp_trans_no = t1.trans_no)
AND t1.company_code = nvl(:hdr.l_company_code,t1.company_code)
AND t1.company_ac_no = nvl(:hdr.l_company_ac_no,t1.company_ac_no)
AND lower(t1.customer_name) LIKE lower(nvl('%'||:hdr.l_customer_name||'%' ,t1.customer_name))
GROUP BY
t2.authoriser_code,
t1.company_code,
t1.company_ac_no,
t1.customer_name,
t2.agreement_no,
t1.customer_id;
-----------
Where Clause Analysis
---------------------
1.Condition 1 OR operator (In table2 two different columbs are compared with one column in table)
2.Like Operator
3.All the columns has index but not used properly always full table scan
4.NVL chk
5.If i run the qry in backend means coming little fast,front end very slow
Input Parameter - Query retrival data - limit
-------------------------------------------------------------------
Only compnay code means record count will be 50 - 500 records -
Only compnay code and comp ac number means record count will be 1-5
Only compnay code,omp ac number and customer name means record count will be 1 - 5 records
I have tried following ways
1.Split the query using UNIOIN (OR clause seaparted) - Nested loops COST 850 , Nested loops COST 750 - index by row id - cost
is 160 ,index by row id - cost is 152 full table access.................................
2.Dynamic SQL build - 'DBMS_SQL.DEFINE COLUMN .....
3.Given onlu one input parameter - Nested loops COST 780 , Nested loops COST 780 - index by row id - cost is 148 ,index by
row id - cost is 152 full table access.................................
Still im facing the same issue.
Please help me out on this.
Thanks and Regards,
Oracle1001
|
|
|
Oracle forms 6i performance issue [message #290207 is a reply to message #289933] |
Thu, 27 December 2007 23:42 |
oracle1001
Messages: 4 Registered: December 2007 Location: chennnai
|
Junior Member |
|
|
Hi All,
Tools - Oracle DB 9i Oracle forms 6i
I have one query form.
Im using cursor in form and fetch the data directly and assign to the form block values.
There are two table involved here.
Table1 has 1 crore record.
Table2 has 4 crore record.
The query fetch the data 50-500 records.
In backend the select query is fast.
In front end ,it is taking time to load the data in grid.
How can i fetch the data quickly in front end.
Please help me out on this.
Regards,
Oracle1001
|
|
|
|
Re: Oracle forms 6i performance issue [message #290223 is a reply to message #290209] |
Fri, 28 December 2007 01:29 |
oracle1001
Messages: 4 Registered: December 2007 Location: chennnai
|
Junior Member |
|
|
Hi,
I'm facing the performance issue in query forms.
In detail block form taking long time to load the data.
Form contains 2 non data blocks
1.HDR - 3 input parameters
2.DETAILS - Grid - Details
HDR input fields
1.Company Code
2.Company ACccount No
3.Customer Name
Details Grid is displayed the details.
Here there are 2 tables involved
1.Table1 - 1 crore records
2.Table2 - 4 crore records
In form procedure one cursor bulid and fetch is done directly and assign the values to form block fields.
Below i've pasted the query
SELECT t1.entry_dt,
t2.authoriser_code,
t1.company_code,
t1.company_ac_no,
INITCAP (t1.customer_name) cust_name,
t2.agreement_no,
t1.customer_id
FROM table1 t1, table2 t2
WHERE ( t2.trans_no = t1.trans_no
OR t2.temp_trans_no = t1.trans_no)
AND t1.company_code = NVL (:hdr.l_company_code, t1.company_code)
AND t1.company_ac_no = NVL (:hdr.l_company_ac_no, t1.company_ac_no)
AND LOWER (t1.customer_name) LIKE
LOWER (NVL ('%' || :hdr.l_customer_name || '%', t1.customer_name))
GROUP BY t2.authoriser_code,
t1.company_code,
t1.company_ac_no,
t1.customer_name,
t2.agreement_no,
t1.customer_id;
Backend query returning data quickly.
In Front End assign the data to form is very slow.
Front end i have tried dynamic sql.
Thanks
Oracle1001
[EDITED by LF: reformatted and added [code] tags]
[Updated on: Fri, 28 December 2007 01:46] by Moderator Report message to a moderator
|
|
|
|
|