OraFAQ Forum: Performance Tuning » Query tuning

Home » RDBMS Server » Performance Tuning » Query tuning (Oracle ,10.2.0.2.0,Unix)

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

Query tuning [message #507835]

Wed, 18 May 2011 03:40

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi,

I am facing query taking longer time to execute ie 1hr in UAT database
whereas in other development database the same query executes in 2 mins.

I checked the cost of the query .it is 1500 in UAT database and 3500 in
dev database.i noticed the query going for full table scan for 2 tables Tab1 &tab2
in dev database whereas in UAT database the tables used by query goes for
TABLE ACCESS BY INDEX ROWID .

I also checked the indexes of 2 tables going full tables scan were not analyzed
up to date in dev database whereas in UAT database the indexes were analyzed up to date.

I also noticed the difference in database parameter .

_optimizer_cost_based_transformation string ON in UAT database whereas this parameter
is not found in Dev database.

FYI
----
The query is using 12 tables.

your help will be appreciated.

Thanks

Report message to a moderator

Re: Query tuning [message #507836 is a reply to message #507835]

Wed, 18 May 2011 03:45

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

Senior Member

Does Dev and UAT have the same amount of data?

Report message to a moderator

Re: Query tuning [message #507846 is a reply to message #507836]

Wed, 18 May 2011 04:27

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

How to check that ?

Report message to a moderator

Re: Query tuning [message #507848 is a reply to message #507846]

Wed, 18 May 2011 04:33

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

Senior Member

SELECT COUNT(*) from each table involved. How else would you check?

Report message to a moderator

Re: Query tuning [message #507849 is a reply to message #507846]

Wed, 18 May 2011 04:34

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Query dba_segments.bytes and dba_tables.num_rows.

Regards
Michel

Report message to a moderator

Re: Query tuning [message #507851 is a reply to message #507849]

Wed, 18 May 2011 04:39

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

Senior Member

Ok so there is another way, still count(*) is obvious (and more accurate than num_rows).
However you really ought to know already if they have the similar amounts of data or not. Presumably you know how the tables are populated in each case?
This is really basic information for tuning.

Report message to a moderator

Re: Query tuning [message #507865 is a reply to message #507851]

Wed, 18 May 2011 05:41

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

The amount data varies in both the databases.

Thanks

Report message to a moderator

Re: Query tuning [message #507867 is a reply to message #507865]

Wed, 18 May 2011 05:51

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

Senior Member

By how much?

If, as I suspect, UAT has a lot more data, then you would expect the query there to take longer and use a different plan.

It'll still need tuning, but comparing it to dev is fairly pointless in that case.

Report message to a moderator

Re: Query tuning [message #507868 is a reply to message #507867]

Wed, 18 May 2011 05:59

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

What plan do you want me to use ?

Thanks

Report message to a moderator

Re: Query tuning [message #507870 is a reply to message #507868]

Wed, 18 May 2011 06:03

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

(Your original post is a shining example of why cost should not be used...)

I digress: Cookiemonsters reply is basically saying you cannot compare the run times as the situation is not the same.

It is akin to saying a 400 mile drive takes longer than a 40 mile drive, what is wrong with my route?

[Updated on: Wed, 18 May 2011 06:04]

Report message to a moderator

Re: Query tuning [message #507884 is a reply to message #507868]

Wed, 18 May 2011 06:49

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

Senior Member

balaji14 wrote on Wed, 18 May 2011 11:59

What plan do you want me to use ?

Thanks

I'm talking about the plan oracle uses - the explain plan (or execution plan). If you don't know what that is I suggest you have a read of the Oracle Database Performance Tuning Guide

Report message to a moderator

Re: Query tuning [message #507885 is a reply to message #507884]

Wed, 18 May 2011 06:50

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

Senior Member

Though having re-read your original post it appears you do know what an explain is, so I really have no idea what prompted that question.

Report message to a moderator

Re: Query tuning [message #507921 is a reply to message #507885]

Wed, 18 May 2011 08:45

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

I already seen the explain plan and based on that only i told 2 tables are going full table scan .

Report message to a moderator

Re: Query tuning [message #507924 is a reply to message #507921]

Wed, 18 May 2011 08:52

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

Senior Member

We know. So what?

You asked why it was running faster in dev than UAT - answer is that UAT has a lot more data. That was easy to work out.
If you want us to help tune the query, that's not so easy and we'll require a lot more information - read the sticky at the top of this forum and supply all the requested information.

Report message to a moderator

Re: Query tuning [message #507928 is a reply to message #507924]

Wed, 18 May 2011 09:10

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

OK.What else information you neeed to tune this query ?

Report message to a moderator

Re: Query tuning [message #507930 is a reply to message #507928]

Wed, 18 May 2011 09:22

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

balaji14 wrote on Wed, 18 May 2011 15:10

OK.What else information you neeed to tune this query ?

I'll copy & paste the link for you...

http://www.orafaq.com/forum/t/84315/153040/

Report message to a moderator

Re: Query tuning [message #507931 is a reply to message #507924]

Wed, 18 May 2011 09:22

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

Senior Member

cookiemonster wrote on Wed, 18 May 2011 14:52

read the sticky at the top of this forum and supply all the requested information.

Report message to a moderator

Re: Query tuning [message #507933 is a reply to message #507931]

Wed, 18 May 2011 09:30

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

You want the explain plan of the query ?

Report message to a moderator

Re: Query tuning [message #507936 is a reply to message #507933]

Wed, 18 May 2011 09:38

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

Senior Member

Yes, and the query, and descriptions of the tables involved.

Report message to a moderator

Re: Query tuning [message #507937 is a reply to message #507936]

Wed, 18 May 2011 09:42

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

I have one doubt here.

Database parameter is set ON

SQL> show parameter opti

NAME TYPE VALUE
------------------------------------ ----------- -------
_optimizer_cost_based_transformation string ON

I think this could be the reason why query is taking longer time to execute
whereas i did not find this parameter in other dev database where it takes only 2 mins to execute.

Report message to a moderator

Re: Query tuning [message #507938 is a reply to message #507835]

Wed, 18 May 2011 09:44

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

This db parameter is something related to query performance.So i suspect
this could be the reason for time consuming.

Report message to a moderator

Re: Query tuning [message #507939 is a reply to message #507937]

Wed, 18 May 2011 09:44

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Rather than guess, perhaps you could do what cookiemonster requested?

I must commend his(her) patience, I'd have went nuts by now (but I'm a grumpy so and so) Smile

Smile

Report message to a moderator

Re: Query tuning [message #507941 is a reply to message #507939]

Wed, 18 May 2011 09:49

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

I have attached the query with explain plan.

Attachment: SQL tune.txt
(Size: 6.10KB, Downloaded 1525 times)

Report message to a moderator

Re: Query tuning [message #507942 is a reply to message #507937]

Wed, 18 May 2011 09:49

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

Senior Member

A quick look at metalink shows that switching that parameter on causes oracle to spend more time parsing a query in order to try and find a better plan. It is also a hidden parameter and as such should only be set under Oracle Supports recommendation. Maybe you should find out who set it in your UAT environment and ask them why. Especially since that parameter will affect all queries and not just your problem one.
I would be very surprised if changing it got you a 2 second response time on UAT though.

EDIT: typo

[Updated on: Wed, 18 May 2011 09:50]

Report message to a moderator

Re: Query tuning [message #507943 is a reply to message #507941]

Wed, 18 May 2011 09:51

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

I have attached the count of rows in a table involved in the query of both databases.

Attachment: counts.txt
(Size: 0.85KB, Downloaded 1182 times)

Report message to a moderator

Re: Query tuning [message #507944 is a reply to message #507942]

Wed, 18 May 2011 09:52

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

Senior Member

1) Post thq query and explain plan directly in your post in [code] tags. A lot of people won't download attachments.
2) Use the following method for generating the explain, it's easier for us to read:

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>

3) Make sure you give the explain from UAT.

Report message to a moderator

Re: Query tuning [message #507947 is a reply to message #507944]

Wed, 18 May 2011 10:03

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

Senior Member

How many rows does the query return in each DB?

Report message to a moderator

Re: Query tuning [message #507950 is a reply to message #507947]

Wed, 18 May 2011 10:11

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Dev

TABLES ROWS
------------ --------
DISTRIBUTED_MATERIALS -- 1282029
RECEIVED_MATERIALS ---- 995301
REQUISITION_LINES -------- 830939
TASKS -------------- 17582228
CREWS -------------- 4681
MATERIAL_TYPES --------- 66015
EWO EWO, --------- 1123845
YARDS ---------- 3353
GEOLOCS ---------- 1011233
LOCATIONS ---------- 2022369
ACAS_CONTRACTORS --- 2167
MTL_SIGN_OUT -- 165348

UAT
---------

TABLES ROWS
------------ --------

DISTRIBUTED_MATERIALS, -- 1251277
RECEIVED_MATERIALS RM, ---- 930124
REQUISITION_LINES -------- 770827
TASKS TSK, -------------- 18245448
CREWS CRW, -------------- 3733
MATERIAL_TYPES --------- 68166
EWO EWO, --------- 1205687
YARDS YARD ---------- 3367
GEOLOCS GLOC ---------- 1275965
LOCATIONS LOC ---------- 1275965
ACAS_CONTRACTORS CONT --- 2885
MTL_SIGN_OUT SIGN_OUT -- 249704

[Updated on: Wed, 18 May 2011 10:13]

Report message to a moderator

Re: Query tuning [message #507951 is a reply to message #507950]

Wed, 18 May 2011 10:12

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Still unable to format?
If you are unable to do such simple thing, you should forget to try to optimize Oracle and choose another job.

Regards
Michel

Report message to a moderator

Re: Query tuning [message #507953 is a reply to message #507951]

Wed, 18 May 2011 10:19

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Dev
TABLES ROWS
------------ --------
DISTRIBUTED_MATERIALS -- 1282029
RECEIVED_MATERIALS ---- 995301
REQUISITION_LINES -------- 830939
TASKS -------------- 17582228
CREWS -------------- 4681
MATERIAL_TYPES --------- 66015
EWO EWO, --------- 1123845
YARDS ---------- 3353
GEOLOCS ---------- 1011233
LOCATIONS ---------- 2022369
ACAS_CONTRACTORS --- 2167
MTL_SIGN_OUT -- 165348

UAT
---------
TABLES ROWS
------------ --------
DISTRIBUTED_MATERIALS, -- 1251277
RECEIVED_MATERIALS RM, ---- 930124
REQUISITION_LINES -------- 770827
TASKS TSK, -------------- 18245448
CREWS CRW, -------------- 3733
MATERIAL_TYPES --------- 68166
EWO EWO, --------- 1205687
YARDS YARD ---------- 3367
GEOLOCS GLOC ---------- 1275965
LOCATIONS LOC ---------- 1275965
ACAS_CONTRACTORS CONT --- 2885
MTL_SIGN_OUT SIGN_OUT -- 249704

Is this OK now ? I dont need to choose another job.If you can answer my question
help me else dont post anything here.

[Updated on: Wed, 18 May 2011 10:19]

Report message to a moderator

Re: Query tuning [message #507955 is a reply to message #507953]

Wed, 18 May 2011 10:22

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Is this OK now ?

What is your opinion?

Regards
Michel

Report message to a moderator

Re: Query tuning [message #507956 is a reply to message #507953]

Wed, 18 May 2011 10:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>If you can answer my question help me else dont post anything here.
so it was said & so it was written; so let it be done.
post no additional responses to this thread.

Report message to a moderator

Re: Query tuning [message #507957 is a reply to message #507955]

Wed, 18 May 2011 10:24

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

i gave the command but it did not generate explain plan

[Updated on: Wed, 18 May 2011 10:27]

Report message to a moderator

Re: Query tuning [message #507959 is a reply to message #507957]

Wed, 18 May 2011 10:25

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Pretty good formatting! Easy to read!

Regards
Michel

Report message to a moderator

Re: Query tuning [message #507960 is a reply to message #507957]

Wed, 18 May 2011 10:25

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I

c
a
n

t
o
t
a
l
l
y

r
e
a
d

t
h
a
t

@Blackswan: methinks you are correct.
@Michel: You want to patent those glasses if you can read that!

[Updated on: Wed, 18 May 2011 10:27]

Report message to a moderator

Re: Query tuning [message #507962 is a reply to message #507957]

Wed, 18 May 2011 10:32

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

Senior Member

balaji14 wrote on Wed, 18 May 2011 16:24

i gave the command but it did not generate explain plan

Copy and paste the execution here. Use [code] tags as described here

And if you don't start formatting things properly I'm going to lose patience with you as well - you have been pointed to the guidelines before.

Report message to a moderator

Re: Query tuning [message #507964 is a reply to message #507962]

Wed, 18 May 2011 10:51

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
----------------------

| Id | Operation | Name | R
ows | Bytes | Cost |

--------------------------------------------------------------------------------
----------------------

| 0 | SELECT STATEMENT | |
1 | 351 | 1882 |

| 1 | SORT GROUP BY | |
1 | 351 | 1882 |

| 2 | NESTED LOOPS | |
| | |

| 3 | NESTED LOOPS | |
1 | 351 | 1881 |

| 4 | NESTED LOOPS | |
1 | 308 | 1879 |

| 5 | NESTED LOOPS | |
1 | 292 | 1878 |

| 6 | NESTED LOOPS | |
1 | 257 | 1877 |

| 7 | NESTED LOOPS | |
1 | 241 | 1875 |

| 8 | NESTED LOOPS OUTER | |
1 | 226 | 1874 |

| 9 | NESTED LOOPS OUTER | |
1 | 190 | 1873 |

| 10 | NESTED LOOPS | |
1 | 161 | 1872 |

| 11 | NESTED LOOPS | |
1 | 92 | 1871 |

| 12 | NESTED LOOPS | |
392 | 24304 | 1061 |

| 13 | NESTED LOOPS | |
392 | 17248 | 667 |

| 14 | INDEX RANGE SCAN | CRW_PK |
17 | 204 | 2 |

| 15 | TABLE ACCESS BY INDEX ROWID| JAM_TASKS |
23 | 736 | 39 |

| 16 | INDEX RANGE SCAN | TSK_CRW_FK_I |
1 | | 38 |

| 17 | TABLE ACCESS BY INDEX ROWID | JAM_EWO |
1 | 18 | 1 |

| 18 | INDEX UNIQUE SCAN | EWO_PK |
1 | | 0 |

| 19 | TABLE ACCESS BY INDEX ROWID | JAM_DISTRIBUTED_MATERIALS |
1 | 30 | 3 |

| 20 | INDEX RANGE SCAN | DM_TSK |
1 | | 2 |

| 21 | TABLE ACCESS BY INDEX ROWID | JAM_RECEIVED_MATERIALS |
1 | 69 | 1 |

| 22 | INDEX UNIQUE SCAN | RM_PK |
1 | | 0 |

| 23 | TABLE ACCESS BY INDEX ROWID | JAM_MTL_SIGN_OUT |
1 | 29 | 1 |

| 24 | INDEX UNIQUE SCAN | JAM_MTL_SIGN_OUT_TSK_PK |
1 | | 0 |

| 25 | TABLE ACCESS BY INDEX ROWID | JAM_ACAS_CONTRACTORS |
1 | 36 | 1 |

| 26 | INDEX UNIQUE SCAN | JAM_ACAS_CONTRACTOR_PK |
1 | | 0 |

| 27 | TABLE ACCESS BY INDEX ROWID | JAM_YARDS |
1 | 15 | 1 |

| 28 | INDEX UNIQUE SCAN | YARD_PK |
1 | | 0 |

| 29 | TABLE ACCESS BY INDEX ROWID | JAM_GEOLOCS |
1 | 16 | 2 |

| 30 | INDEX RANGE SCAN | GLOC_UK2 |
1 | | 1 |

| 31 | TABLE ACCESS BY INDEX ROWID | JAM_REQUISITION_LINES |
1 | 35 | 1 |

| 32 | INDEX UNIQUE SCAN | RL_PK |
1 | | 0 |

| 33 | TABLE ACCESS BY INDEX ROWID | JAM_MATERIAL_TYPES |
1 | 16 | 1 |

| 34 | INDEX UNIQUE SCAN | MT_PK |
1 | | 0 |

| 35 | INDEX UNIQUE SCAN | LOC_PK |
1 | | 1 |

| 36 | TABLE ACCESS BY INDEX ROWID | JAM_LOCATIONS |
1 | 43 | 2 |

--------------------------------------------------------------------------------
----------------------

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
495482 consistent gets
45354 physical reads
0 redo size
1104 bytes sent via SQL*Net to client
1290 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Report message to a moderator

Re: Query tuning [message #507965 is a reply to message #507964]

Wed, 18 May 2011 10:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do we need to enhance the Posting Guidelines to make them easier to follow?

Report message to a moderator

Re: Query tuning [message #507967 is a reply to message #507965]

Wed, 18 May 2011 11:16

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Down to this level? it is not possible.
Some people just can't read and learn.
Here's a good example of someone that is just able to copy and paste others' work without understanding it.

Regards
Michel

Report message to a moderator

Re: Query tuning [message #507987 is a reply to message #507967]

Wed, 18 May 2011 14:18

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

Senior Member

@balaji14 - take a long hard look at the explain plan you posted. Then take a long hard look at the example I posted. Realise that mine is easy to follow and yours isn't at all.
We have pointed you, repeatedly, to really simple and easy to follow instructions as to how to format your post so it looks like my exmaple.
This leads to one of 2 conclusions:
1) You can't understand the really simple instructions - in which case you have no hope what so ever of being a programmer and need to find a new job.
2) You're too lazy to follow them - in which case no here is going to bother to try and help you again.

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	Multiple SELECT statement help
Next Topic:	Auto Stats gathering not including my schema?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 05:55:06 CDT 2024