OraFAQ Forum: Performance Tuning » Differences in query execution between two similar environments

Home » RDBMS Server » Performance Tuning » Differences in query execution between two similar environments

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

Differences in query execution between two similar environments [message #197845]

Thu, 12 October 2006 20:29

pria79
Messages: 8
Registered: June 2006
Location: Chicago US

Junior Member

Hi All

I am a PL/Sql developer and stuck with a DB related problem.

I have two environments env1 and env2 for an application. I have a query which used to take huge amount of time, 15 hours to be precise. We finally optimised it to a minute by changing logic in our code/process.

The strange thing is that when i run my process in env1, the query is using more than 7GB of TEMP (Temporary) tablespace (ultimately throwing ORA 1652) but when i run in env2, the query is not even using 1GB of temp tablespace.

The setups, code and the way to run the process for env1 and env2 are the same. I want to know what could be the possible reasons for the same.
I can only think two possible reasons:

1. Some db parameters difference in the two environments, but again dont know which parameters might have effect on usage of TEMP tablespace.
2. Difference in the way query is being executed, i had checked the explain plan for the two environments differ.

I dont know if my thinking is right or not. Please guide me through this

Any help is appreciated.

Thanks in advance
pria

Report message to a moderator

Re: Differences in query execution between two similar environments [message #197904 is a reply to message #197845]

Fri, 13 October 2006 04:21

orausern
Messages: 826
Registered: December 2005

Senior Member

Are the objects being referred in your query analyzed recently?
(see the last_analyzed column for all the objects (referred in ur query) in user_tables and user_indexes views)
Also one way to investigage would be to get execution plan of the query in both the environments and compare.

[Updated on: Fri, 13 October 2006 04:21]

Report message to a moderator

Re: Differences in query execution between two similar environments [message #197969 is a reply to message #197845]

Fri, 13 October 2006 09:18

srinivas4u2
Messages: 66
Registered: June 2005

Member

Is the data same in the two environments? what are the two environments?

Check for any missing indexes as they are presorted.

Compare the sort_area_size of the two environments.

Also, check when the objects were last analyzed as the other reader suggested.

Let us know if it helped.

Thanks,

Report message to a moderator

Re: Differences in query execution between two similar environments [message #197988 is a reply to message #197904]

Fri, 13 October 2006 11:48

pria79
Messages: 8
Registered: June 2006
Location: Chicago US

Junior Member

Hi Nirav and srinivas..Thnx for the suggestions.. i was able to find the reason finally. Our targets were hash joins and sort operations as the two really effect the temp tablespace. After checking the sort_area_size (which was same in the two environments) , we did checked the explain plan for the same query in the two environments (which was different). We really dig down to each difference of the objects we had. Note here that our data set was exactly the same. The only difference was that in one of the environments we had an index with unique option, leading to use of lot of hash joins in execution plan. In the other environment we had the same index with non-unique option. I really dont know the reason for this happening. Please do share or throw some light on this if you come to know the reason.

Thanks again!!!
Pria

Report message to a moderator

Previous Topic:	Query Takes More time To Run
Next Topic:	sql tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 06 13:31:52 CDT 2024