Home » RDBMS Server » Performance Tuning » Query taking time to execute in front end(Oracle 10g R2) (Oracle 10g R2.)
Query taking time to execute in front end(Oracle 10g R2) [message #313868] Mon, 14 April 2008 23:53 Go to next message
anee459
Messages: 14
Registered: March 2008
Junior Member
Hi,
select inv_no from imp_bl_top 
where eta_date between to_date(:blk2.date_from,'dd-MON-yyyy')
and to_date(:blk2.date_to,'dd-MON-yyyy')
and ((:blk2.ocean_air = 'A' and fcl_lcl = 'A') or (:blk2.ocean_air = 'O' and fcl_lcl in ('F','L','C'))) 


Now this query fetches only 11 records
from total 601873.
When i execute this query in back end
i.e. Sql*plus it takes only 0.78 sec but
when actually it executes on form it takes
about 30 sec to execute.

Can anybody please tell me what can be the problem.
And provide some suggestion what further should i do.
I have created an index on fcl_lcl,inv_no,eta_date.

[Updated on: Tue, 15 April 2008 00:26] by Moderator

Report message to a moderator

Re: Query taking time to execute in front end(Oracle 10g R2) [message #313870 is a reply to message #313868] Mon, 14 April 2008 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940
Which have you done & what were the results?
Re: Query taking time to execute in front end(Oracle 10g R2) [message #313876 is a reply to message #313870] Tue, 15 April 2008 00:06 Go to previous messageGo to next message
anee459
Messages: 14
Registered: March 2008
Junior Member
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=605 Card=451               
          Bytes=9471)

   1    0  FILTER
   2    1  INLIST ITERATOR
   3    2  INDEX (RANGE SCAN) OF 'IMP_BL_TOP_INDEX' (INDEX) (Cost
           =605 Card=451 Bytes=9471)

This is the execution plan of the above query.

Re: Query taking time to execute in front end(Oracle 10g R2) [message #313936 is a reply to message #313876] Tue, 15 April 2008 04:31 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Is that the plan when it runs slow? Or when it runs fast? Post the other one for comparison.

What columns are in the index 'IMP_BL_TOP_INDEX' ? Is it the one you created? Try reordering the columns in the index: (fcl_lcl,eta_date,inv_no)

Ross Leishman
Re: Query taking time to execute in front end(Oracle 10g R2) [message #313944 is a reply to message #313936] Tue, 15 April 2008 04:48 Go to previous messageGo to next message
anee459
Messages: 14
Registered: March 2008
Junior Member
Thanks for replying.
The plan is when the query is running fast.
i.e.when it executes in back end.
I am using Front end as Oracle form builder(10g R2).
I don't know how to get a plan from there.
And imp_bl_top_index has columns (fcl_lcl,inv_no,eta_date).
I have also used optimizer hint.FIRST_ROWS(20)

[Updated on: Tue, 15 April 2008 04:51]

Report message to a moderator

Re: Query taking time to execute in front end(Oracle 10g R2) [message #313976 is a reply to message #313944] Tue, 15 April 2008 06:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Run the query from the Form.
Look in V$session and identify the session that Forms is using.

Look in v$sqlplan for the HASH_VALUE corresponding to the SQL_HASH_VALUE from that V$session record.

Failing that, have a search through v$sql_plan for queries using the tables that your query uses until you find the right one.

Then let us see what the other plan looks like.
Re: Query taking time to execute in front end(Oracle 10g R2) [message #314349 is a reply to message #313976] Wed, 16 April 2008 07:38 Go to previous messageGo to next message
anee459
Messages: 14
Registered: March 2008
Junior Member
I am sorry sir.I am not able to find all
this as i really am not getting how to find
all what you require.
I am very new with oracle.
Can you please help me out with some other thing.
Please...

Thanks & Regards
Re: Query taking time to execute in front end(Oracle 10g R2) [message #314604 is a reply to message #313868] Thu, 17 April 2008 05:00 Go to previous messageGo to next message
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
Member

Now this query fetches only 11 records
from total 601873

your problem is the query is fetching only 11 records or it is having performance, while you run it from the application side..?


U have to check your application also, performance can occur due to the problem with the applcation also.
Re: Query taking time to execute in front end(Oracle 10g R2) [message #314613 is a reply to message #314604] Thu, 17 April 2008 05:24 Go to previous messageGo to next message
anee459
Messages: 14
Registered: March 2008
Junior Member
My problem is that if it fetching only 11 records
why it is taking so much time.

Application is perfect because other
forms are executing well.

[Updated on: Thu, 17 April 2008 05:26]

Report message to a moderator

Re: Query taking time to execute in front end(Oracle 10g R2) [message #314616 is a reply to message #313868] Thu, 17 April 2008 05:27 Go to previous messageGo to next message
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
Member

how many recoreds it have to fetch actually ?
can you post the query ?
Re: Query taking time to execute in front end(Oracle 10g R2) [message #314629 is a reply to message #314616] Thu, 17 April 2008 05:49 Go to previous messageGo to next message
anee459
Messages: 14
Registered: March 2008
Junior Member
select inv_no from imp_bl_top 
where eta_date between to_date(:blk2.date_from,'dd-MON-yyyy')
and to_date(:blk2.date_to,'dd-MON-yyyy')
and ((:blk2.ocean_air = 'A' and fcl_lcl = 'A') or (:blk2.ocean_air = 'O' and fcl_lcl in ('F','L','C'))) 

This is the actual query which is fetching out 11
records from the total records in the table.(ie 607843).
And the time taken to fetch this records is more.
This is the main problem.
Re: Query taking time to execute in front end(Oracle 10g R2) [message #314639 is a reply to message #313868] Thu, 17 April 2008 06:03 Go to previous messageGo to next message
gopu_g
Messages: 54
Registered: March 2008
Location: mumbai
Member

Any index column is related to this cloumn eta_date ?
eta_date is partitioned or not ?

and this index column 'IMP_BL_TOP_INDEX' is related to which column ?

[Updated on: Thu, 17 April 2008 06:03]

Report message to a moderator

Re: Query taking time to execute in front end(Oracle 10g R2) [message #314646 is a reply to message #314639] Thu, 17 April 2008 06:13 Go to previous message
anee459
Messages: 14
Registered: March 2008
Junior Member
yes.
Index is created on fcl_lcl,eta_date,inv_no.
Eta_date is not partitioned.
I m not allowed to change the database.
Previous Topic: tuning the view (merged)
Next Topic: Enabling Trace file generation.
Goto Forum:
  


Current Time: Sat Dec 03 10:09:06 CST 2016

Total time taken to generate the page: 0.20814 seconds