Home » RDBMS Server » Performance Tuning » Query running slow in Forms
Query running slow in Forms [message #212771] Mon, 08 January 2007 05:05 Go to next message
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 #212930 is a reply to message #212771] Mon, 08 January 2007 19:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Trace both sessions and use TK*Prof to check why they are different. Post both TK*Prof results here if you still have questions.

Ross Leishman
Re: Query running slow in Forms [message #213053 is a reply to message #212930] Tue, 09 January 2007 04:31 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What have you given me here? ./fa/1659/0/ 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
Previous Topic: with out enforcing index i want use how to do this
Next Topic: Log Switches
Goto Forum:
  


Current Time: Thu Dec 08 10:40:21 CST 2016

Total time taken to generate the page: 0.13412 seconds