OraFAQ Forum: Performance Tuning » LOV taking long time

Home » RDBMS Server » Performance Tuning » LOV taking long time

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

LOV taking long time [message #613961]

Wed, 14 May 2014 01:19

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi all,

I have LOV in that only 1 record , i ran the same LOV Query in the TOAD Database some times it is taking 10 second's and some time it is taken 5 min .

And the same in the LOV also in the Oracle forms.

Please help

Report message to a moderator

Re: LOV taking long time [message #613963 is a reply to message #613961]

Wed, 14 May 2014 01:32

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

1) In the TOAD some times it taking 10 second's & some times taking 5 min
2) In the LOV in the form also same

Please help

Report message to a moderator

Re: LOV taking long time [message #613966 is a reply to message #613963]

Wed, 14 May 2014 01:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As you provided no useful information at all, I guess that query (which is LoV's source) should be tuned. Read How to tune SQL or Identify Performance Problem and Bottleneck.

Report message to a moderator

Re: LOV taking long time [message #613968 is a reply to message #613966]

Wed, 14 May 2014 01:38

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Thanks for providing the Link , but this is the client servers.

Any properties to change in the Oracle forms 10g,if yes please provide me

Report message to a moderator

Re: LOV taking long time [message #613970 is a reply to message #613968]

Wed, 14 May 2014 01:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What is "client servers"?

What property would you change in Forms, if the same query works slowly in TOAD as well? Why wouldn't you set some property in TOAD as well (or in SQL*Plus, or elsewhere)?

Report message to a moderator

Re: LOV taking long time [message #613972 is a reply to message #613970]

Wed, 14 May 2014 01:56

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

What property would you change in Forms, if the same query works slowly in TOAD as well?

Some times it is fine & some times it is taking 6 min and above..

How can i tune the query in the Clients servers?

Report message to a moderator

Re: LOV taking long time [message #613975 is a reply to message #613972]

Wed, 14 May 2014 02:04

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

In the record group property Record group Fetch size by defaulty as 0(zero)

Report message to a moderator

Re: LOV taking long time [message #613976 is a reply to message #613972]

Wed, 14 May 2014 02:07

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't think that you actually read information written in "How to tune SQL or Identify Performance Problem and Bottleneck", did you? If you did, you'd have at least a general idea of how to tune your query.

It is difficult to discuss any subject with you (at least, that's my opinion) as your communication skills are poor. You refuse to listen what other people tell you, don't provide any information, it takes several posts to tear out anything useful, and you expect (actually, wish) that there's a magic wand which can solve all your problems.

Report message to a moderator

Re: LOV taking long time [message #613977 is a reply to message #613976]

Wed, 14 May 2014 02:16

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member


SQL> EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

SQL> SET LINESIZE 130
SQL> SELECT * 
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
ERROR: an uncaught error in function display has happened; please contact Oracle support
       Please provide also a DMP file of the used plan table PLAN_TABLE
       ORA-00904: "OTHER_TAG": invalid identifier

I never worked on this before...

Report message to a moderator

Re: LOV taking long time [message #613978 is a reply to message #613977]

Wed, 14 May 2014 02:20

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Is this a query that takes several minutes to return a single row? I suppose not. So, perhaps you could post query that bothers you.

Report message to a moderator

Re: LOV taking long time [message #613981 is a reply to message #613978]

Wed, 14 May 2014 02:27

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

Is this a query that takes several minutes to return a single row?

yes.

Please fine the below attached query

Attachment: Code.txt
(Size: 1.98KB, Downloaded 2497 times)

Report message to a moderator

Re: LOV taking long time [message #613983 is a reply to message #613981]

Wed, 14 May 2014 02:39

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It is? Are you saying that this:

  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

takes minutes? I doubt it.

CODE.TXT contains bunch of SELECT statements. I have no idea which one you used as a source for LoV.

Report message to a moderator

Re: LOV taking long time [message #613987 is a reply to message #613983]

Wed, 14 May 2014 02:52

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

That is the sample code i ran for EXPLAIN_PLAN..

Please find the below attached query.
Thanks

Attachment: query.txt
(Size: 0.78KB, Downloaded 2078 times)

Report message to a moderator

Re: LOV taking long time [message #613988 is a reply to message #613987]

Wed, 14 May 2014 02:56

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

You've been told before - some people can't / won't open attachments.
You know how to post the code in code tags, do so.

Report message to a moderator

Re: LOV taking long time [message #613990 is a reply to message #613988]

Wed, 14 May 2014 02:59

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

And then try this approach for generating the explain plan:

SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

Report message to a moderator

Re: LOV taking long time [message #613993 is a reply to message #613990]

Wed, 14 May 2014 03:46

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

SQL>  explain plan for 
  2   SELECT DISTINCT a.delivery_id, TO_NUMBER (c.source_header_number)order_number
  3              FROM wsh_new_deliveries a,
  4                   wsh_delivery_assignments b,
  5                   wsh_delivery_details c,
  6                   oe_order_headers_all d
  7             WHERE a.delivery_id = b.delivery_id
  8               AND b.delivery_detail_id = c.delivery_detail_id
  9               AND c.SOURCE_HEADER_ID=d.HEADER_ID
 10              AND TO_NUMBER (c.source_header_number)
 11                     BETWEEN NVL ('1400027',TO_NUMBER (c.source_header_number))
 12                         AND NVL ('1400029',TO_NUMBER (c.source_header_number))
 13               AND a.customer_id = '1223'
 14               AND c.source_header_type_id =NVL ('1011', c.source_header_type_id)
 15               AND b.parent_delivery_detail_id IS NULL
 16               AND c.source_code = 'OE'
 17               AND a.status_code <> 'CL'
 18          ORDER BY 1 ASC
 19  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                             |     1 |    56 | 18357 |
|   1 |  SORT UNIQUE                    |                             |     1 |    56 | 18356 |
|   2 |   NESTED LOOPS                  |                             |     1 |    56 | 18355 |
|   3 |    NESTED LOOPS                 |                             |     1 |    50 | 18354 |
|   4 |     NESTED LOOPS                |                             |  5048 |   138K|  8255 |
|   5 |      TABLE ACCESS BY INDEX ROWID| WSH_NEW_DELIVERIES          |   219 |  2847 |  7261 |
|   6 |       INDEX RANGE SCAN          | WSH_NEW_DELIVERIES_N6       |  7292 |       |    30 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   7 |      TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |    23 |   345 |     7 |
|   8 |       INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N1 |    46 |       |     2 |
|   9 |     TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_DETAILS        |     1 |    22 |     2 |
|  10 |      INDEX UNIQUE SCAN          | WSH_DELIVERY_DETAILS_U1     |     1 |       |     1 |
|  11 |    INDEX UNIQUE SCAN            | OE_ORDER_HEADERS_U1         |     1 |     6 |     1 |
-----------------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

21 rows selected.

Report message to a moderator

Re: LOV taking long time [message #613994 is a reply to message #613993]

Wed, 14 May 2014 03:55

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Doesn't look as if it should take minutes to return the result (though, I'm not an expert here).

However, you seem to be misunderstanding what NVL function does and how it works. The way you put it, it does nothing. For example, this:

NVL ('1011', c.source_header_type_id)

NVL checks the first argument. If it is NULL, it uses the second argument. '1011' is a constant, it can never be NULL, which means that you don't need NVL at all. Did you mean to use

NVL (c.source_header_type_id, '1011')

instead?

Report message to a moderator

Re: LOV taking long time [message #613995 is a reply to message #613994]

Wed, 14 May 2014 04:01

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

 14            AND c.source_header_type_id =NVL ('1011', c.source_header_type_id) is equal to Below condition
 
               and c.source_header_type_id = nvl(:ctrl.order_type_id,c.source_header_type_id)

Quote:

1011' is a constant, it can never be NULL, which means that you don't need NVL at all

Not null it is a bind variable.

Report message to a moderator

Re: LOV taking long time [message #613996 is a reply to message #613995]

Wed, 14 May 2014 04:06

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oh, I see! Sorry, I was wrong.

Report message to a moderator

Re: LOV taking long time [message #613997 is a reply to message #613996]

Wed, 14 May 2014 04:24

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Please could any one please help..

Report message to a moderator

Re: LOV taking long time [message #614016 is a reply to message #613997]

Wed, 14 May 2014 07:39

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Sort your plan table and dont replace a bind with a literal, that can change the plan.

Report message to a moderator

Re: LOV taking long time [message #614017 is a reply to message #614016]

Wed, 14 May 2014 07:47

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member


  1  explain plan for
  2  SELECT DISTINCT a.delivery_id, To_number(c.source_header_number) order_number
  3  FROM   wsh_new_deliveries a,
  4         wsh_delivery_assignments b,
  5         wsh_delivery_details c
  6  WHERE  a.delivery_id = b.delivery_id
  7         AND b.delivery_detail_id = c.delivery_detail_id
  8         AND To_number(c.source_header_number) BETWEEN
  9             Nvl(:from_order_number, To_number(c.source_header_number)) AND
 10             Nvl(:to_order_number, To_number(c.source_header_number))
 11         AND a.customer_id = Nvl(:customer_id, a.customer_id)
 12         AND c.source_header_type_id =
 13             Nvl(:order_type_id, c.source_header_type_id)
 14         AND b.parent_delivery_detail_id IS NULL
 15         AND c.source_code = 'OE'
 16         AND a.status_code <> 'CL'
 17* ORDER  BY 1 ASC
SQL> /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                        | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |     1 |    46 |   471K|
|   1 |  SORT UNIQUE                      |                             |     1 |    46 |   471K|
|   2 |   CONCATENATION                   |                             |       |       |       |
|   3 |    FILTER                         |                             |       |       |       |
|   4 |     NESTED LOOPS                  |                             |       |       |       |
|   5 |      NESTED LOOPS                 |                             |    78 |  3588 |   452K|
|   6 |       NESTED LOOPS                |                             |  1372 | 45276 |   450K|

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   7 |        TABLE ACCESS FULL          | WSH_DELIVERY_DETAILS        |  1371 | 24678 |   445K|
|   8 |        TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |     1 |    15 |     3 |
|   9 |         INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |       |     2 |
|  10 |       INDEX UNIQUE SCAN           | WSH_NEW_DELIVERIES_U1       |     1 |       |     1 |
|  11 |      TABLE ACCESS BY INDEX ROWID  | WSH_NEW_DELIVERIES          |     1 |    13 |     2 |
|  12 |    FILTER                         |                             |       |       |       |
|  13 |     NESTED LOOPS                  |                             |       |       |       |
|  14 |      NESTED LOOPS                 |                             |     1 |    46 | 18894 |
|  15 |       NESTED LOOPS                |                             |  5048 |   138K|  8795 |
|  16 |        TABLE ACCESS BY INDEX ROWID| WSH_NEW_DELIVERIES          |   219 |  2847 |  7261 |
|  17 |         INDEX RANGE SCAN          | WSH_NEW_DELIVERIES_N6       |  7292 |       |    30 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|  18 |        TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |    23 |   345 |     7 |
|  19 |         INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N1 |    46 |       |     2 |
|  20 |       INDEX UNIQUE SCAN           | WSH_DELIVERY_DETAILS_U1     |     1 |       |     1 |
|  21 |      TABLE ACCESS BY INDEX ROWID  | WSH_DELIVERY_DETAILS        |     1 |    18 |     2 |
-------------------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

31 rows selected.

Report message to a moderator

Re: LOV taking long time [message #614020 is a reply to message #613972]

Wed, 14 May 2014 09:16

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mist598 wrote on Wed, 14 May 2014 12:26

How can i tune the query in the Clients servers?

You have been consistently using the term "client servers". I am curious to know what do you mean by it and what others are supposed to interpret?

1. What is your DB version? I don't see you mentioned in the topic.
2. Is the execution plan changing at run time when you see a marginal time difference in the execution times?
3. What is the number of rows actually returned by the query? Is there any difference in the cardinality estimates and actual rows returned?

I would like to quote what Kevin Meade says about sql tuning :
"SQL tuning is all about getting correct cardinality estimates"

Probe such questions to yourself and post the details.

Edit : typo

[Updated on: Wed, 14 May 2014 09:21]

Report message to a moderator

Re: LOV taking long time [message #614021 is a reply to message #614020]

Wed, 14 May 2014 09:32

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

You have been consistently using the term "client servers". I am curious to know what do you mean by it and what others are supposed to interpret?

Is this not correct to do(tuning process) without inform to the Clients..
Quote:

1. What is your DB version? I don't see you mentioned in the topic.

10.1.0.4.2
Quote:

2. Is the execution plan changing at run time when you see a marginal time difference in the execution times?

I didn't see any execution times , means not getting any execution times(column)
Quote:

3. What is the number of rows actually returned by the query?

only 3 rows returned by the Query

I am new to do this please help...

[Updated on: Wed, 14 May 2014 09:33]

Report message to a moderator

Re: LOV taking long time [message #614022 is a reply to message #614021]

Wed, 14 May 2014 10:04

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Don't say that you don't know the actual time taken by the query to execute. If you don't know then ho would anyone know in this forum?

Please execute the query in sqlplus and set timing on. You need to tell us how much time is the query actually taking to execute. If
the time and cardinality estimates in explain way are way off w.r.t. to actuals, then there is an issue with the statistics.

[Updated on: Wed, 14 May 2014 10:09]

Report message to a moderator

Re: LOV taking long time [message #614023 is a reply to message #614021]

Wed, 14 May 2014 10:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

take the time and effort to actually read the top/Sticky post at the top of this subforum

http://www.orafaq.com/forum/f/6/

Report message to a moderator

Re: LOV taking long time [message #614024 is a reply to message #614023]

Wed, 14 May 2014 10:15

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

BlackSwan wrote on Wed, 14 May 2014 20:35

take the time and effort to actually read the top/Sticky post at the top of this subforum

http://www.orafaq.com/forum/f/6/

Unfortunately, after 750+ posts by OP, we are still reminding again and again to read the performane tuning sticky Sad

One more thing,
I recollect Kevin Meade's quote about SQL tuning : "SQL tuning is all about getting correct cardinality estimates".

Report message to a moderator

Re: LOV taking long time [message #614026 is a reply to message #614023]

Wed, 14 May 2014 10:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You can lead some folks to knowledge, but you can't make them think.

Report message to a moderator

Re: LOV taking long time [message #614044 is a reply to message #613961]

Thu, 15 May 2014 00:15

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi all, please find the below attached screen, and still running and I ran the query TOAD as well as in the SQL PLUS also ,

Elapsed: 00:14:41.64

Thanks

Attachment: image1.png
(Size: 5.92KB, Downloaded 1729 times)

Report message to a moderator

Re: LOV taking long time [message #614046 is a reply to message #614017]

Thu, 15 May 2014 00:40

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mist598 wrote on Wed, 14 May 2014 18:17

Note
-----
   - 'PLAN_TABLE' is old version

Fix it first.

Report message to a moderator

Previous Topic:	database performance issue
Next Topic:	Procedure runs slow whereas

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 23:35:46 CST 2025