| Query running slow in Forms [message #212771] |
Mon, 08 January 2007 05:05  |
neo_oracle
Messages: 2
Registered: January 2007
Location: London
|
Junior Member |
|
|
Hi,
I have a query selecting from a view which i build dynamically at run time. In forms this query is taking almost 6 secs to return the results.
However when i run the same query in Toad it take 3ms.
We are on Forms 6i and Oracle 8i.
Any help will be much appreciated.
|
|
|
|
|
|
| Re: Query running slow in Forms [message #213053 is a reply to message #212930] |
Tue, 09 January 2007 04:31  |
neo_oracle
Messages: 2
Registered: January 2007
Location: London
|
Junior Member |
|
|
Hi Ross,
Thanks for getting back to me. I have managed to work out where it is taking a long time to execute but no idea why.
What we are basically doing is performing a search in a form, if the search results are > 1 then we store the default where clause in a package and call a new form which then populates a global temp table , selecting from a view using the where clause. Long winded i know.
Now due to the table set-up the insert statement itself is not very pretty.
INSERT INTO ch_temp_table
SELECT * FROM V_QP -- nasty view made up from 8 tables
WHERE qp_id IN (SELECT d1.qp_id
FROM d_qp_quote_policies d1
WHERE d1.qp_policy = 151
UNION
SELECT d2.qp_id
FROM d_qp_quote_policies d2
WHERE EXISTS (SELECT 'X'
FROM d_qp_quote_policies d3
WHERE d2.qp_renewed_from_qp_id = d3.qp_id
AND d3.qp_policy = 151));
After checking this with autotrace I get the following stats:
Statistics
----------------------------------------------------------
112 recursive calls
8 db block gets
155 consistent gets
0 physical reads
136 redo size
394 bytes sent via SQL*Net to client
1003 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
8 rows processed
Any help will be appreciated. Also new to this area so if this is really bad practice let me know and I will look into other ways of writing the code I have inherited.
Thanking you in Advance
Neo
[Updated on: Tue, 09 January 2007 04:34] Report message to a moderator |
|
|
|
| Re: Query running slow in Forms [message #213230 is a reply to message #213053] |
Tue, 09 January 2007 20:44 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
What have you given me here?  You originally asked why one source ran faster than another. I asked for TK*Prof output from both sources so that we could COMPARE them. Instead, you give AUTOTRACE output from a third source, and don't tell me whether this run represents the "fast" or the "slow".
Follow the link in my earlier post and trace BOTH sessions.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
