Home » RDBMS Server » Performance Tuning » LOV taking long time
LOV taking long time [message #613961] Wed, 14 May 2014 01:19 Go to next message
mist598
Messages: 1029
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
Re: LOV taking long time [message #613963 is a reply to message #613961] Wed, 14 May 2014 01:32 Go to previous messageGo to next message
mist598
Messages: 1029
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
Re: LOV taking long time [message #613966 is a reply to message #613963] Wed, 14 May 2014 01:36 Go to previous messageGo to next message
Littlefoot
Messages: 19809
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.
Re: LOV taking long time [message #613968 is a reply to message #613966] Wed, 14 May 2014 01:38 Go to previous messageGo to next message
mist598
Messages: 1029
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
Re: LOV taking long time [message #613970 is a reply to message #613968] Wed, 14 May 2014 01:52 Go to previous messageGo to next message
Littlefoot
Messages: 19809
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)?
Re: LOV taking long time [message #613972 is a reply to message #613970] Wed, 14 May 2014 01:56 Go to previous messageGo to next message
mist598
Messages: 1029
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?

Re: LOV taking long time [message #613975 is a reply to message #613972] Wed, 14 May 2014 02:04 Go to previous messageGo to next message
mist598
Messages: 1029
Registered: February 2013
Location: Hyderabad
Senior Member
In the record group property Record group Fetch size by defaulty as 0(zero)
Re: LOV taking long time [message #613976 is a reply to message #613972] Wed, 14 May 2014 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 19809
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.
Re: LOV taking long time [message #613977 is a reply to message #613976] Wed, 14 May 2014 02:16 Go to previous messageGo to next message
mist598
Messages: 1029
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...
Re: LOV taking long time [message #613978 is a reply to message #613977] Wed, 14 May 2014 02:20 Go to previous messageGo to next message
Littlefoot
Messages: 19809
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.
Re: LOV taking long time [message #613981 is a reply to message #613978] Wed, 14 May 2014 02:27 Go to previous messageGo to next message
mist598
Messages: 1029
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 36 times)
Re: LOV taking long time [message #613983 is a reply to message #613981] Wed, 14 May 2014 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 19809
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.
Re: LOV taking long time [message #613987 is a reply to message #613983] Wed, 14 May 2014 02:52 Go to previous messageGo to next message
mist598
Messages: 1029
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 28 times)
Re: LOV taking long time [message #613988 is a reply to message #613987] Wed, 14 May 2014 02:56 Go to previous messageGo to next message
cookiemonster
Messages: 11201
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.
Re: LOV taking long time [message #613990 is a reply to message #613988] Wed, 14 May 2014 02:59 Go to previous messageGo to next message
cookiemonster
Messages: 11201
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> 

Re: LOV taking long time [message #613993 is a reply to message #613990] Wed, 14 May 2014 03:46 Go to previous messageGo to next message
mist598
Messages: 1029
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.
Re: LOV taking long time [message #613994 is a reply to message #613993] Wed, 14 May 2014 03:55 Go to previous messageGo to next message
Littlefoot
Messages: 19809
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?
Re: LOV taking long time [message #613995 is a reply to message #613994] Wed, 14 May 2014 04:01 Go to previous messageGo to next message
mist598
Messages: 1029
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.
Re: LOV taking long time [message #613996 is a reply to message #613995] Wed, 14 May 2014 04:06 Go to previous messageGo to next message
Littlefoot
Messages: 19809
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, I see! Sorry, I was wrong.
Re: LOV taking long time [message #613997 is a reply to message #613996] Wed, 14 May 2014 04:24 Go to previous messageGo to next message
mist598
Messages: 1029
Registered: February 2013
Location: Hyderabad
Senior Member
Please could any one please help..
Re: LOV taking long time [message #614016 is a reply to message #613997] Wed, 14 May 2014 07:39 Go to previous messageGo to next message
Roachcoach
Messages: 1224
Registered: May 2010
Location: UK
Senior Member
Sort your plan table and dont replace a bind with a literal, that can change the plan.
Re: LOV taking long time [message #614017 is a reply to message #614016] Wed, 14 May 2014 07:47 Go to previous messageGo to next message
mist598
Messages: 1029
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.
Re: LOV taking long time [message #614020 is a reply to message #613972] Wed, 14 May 2014 09:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2521
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 Go to previous messageGo to next message
mist598
Messages: 1029
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2521
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 Go to previous messageGo to next message
BlackSwan
Messages: 23055
Registered: January 2009
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/
Re: LOV taking long time [message #614024 is a reply to message #614023] Wed, 14 May 2014 10:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2521
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".
Re: LOV taking long time [message #614026 is a reply to message #614023] Wed, 14 May 2014 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 23055
Registered: January 2009
Senior Member
You can lead some folks to knowledge, but you can't make them think.
Re: LOV taking long time [message #614044 is a reply to message #613961] Thu, 15 May 2014 00:15 Go to previous messageGo to next message
mist598
Messages: 1029
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 40 times)
Re: LOV taking long time [message #614046 is a reply to message #614017] Thu, 15 May 2014 00:40 Go to previous message
Lalit Kumar B
Messages: 2521
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.
Previous Topic: database performance issue
Next Topic: Procedure runs slow whereas
Goto Forum:
  


Current Time: Wed Nov 26 08:36:30 CST 2014

Total time taken to generate the page: 0.10411 seconds