OraFAQ Forum: Performance Tuning » Query taking time to execute in front end(Oracle 10g R2)

Home » RDBMS Server » Performance Tuning » Query taking time to execute in front end(Oracle 10g R2) (Oracle 10g R2.)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query taking time to execute in front end(Oracle 10g R2) [message #313868]

Mon, 14 April 2008 23:53

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/84315/74940
Which have you done & what were the results?

Report message to a moderator

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

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.

Report message to a moderator

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

rleishman
Messages: 3728
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

Report message to a moderator

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

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

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.

Report message to a moderator

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

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

gopu_g
Messages: 54
Registered: March 2008
Location: mumbai

Member

how many recoreds it have to fetch actually ?
can you post the query ?

Report message to a moderator

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

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.

Report message to a moderator

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

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

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.

Report message to a moderator

Previous Topic:	tuning the view (merged)
Next Topic:	Enabling Trace file generation.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 01:36:40 CST 2024