Home » RDBMS Server » Performance Tuning » finding Tuing problem
finding Tuing problem [message #231323] Mon, 16 April 2007 09:16 Go to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
Hello sir(Have a very nice day)

our server details:
-------------------
front end: asp.net
back end Oracle 9i-->9.2.0.7.0
OS redhat linux 2.3

-->We have create view from 20 different tables
-->mostly all table columns have indexed(index_type : normal)
-->This single view contain more than 120 columns from those 20 tables
-->optimizer_mode in cbo (dont gather stats for any more tables because ur report queries are always being updatable twice in a month)

If we ran the sql included the above view from different 20 tables it produced good results for most of the database(dedicated server) and the same sql query frozen in few database.

Eventhough it gave good results for database which contains more datas.

i don't know how to analysed the problem in which side, either sql query side or database side or OS side really i am confused on it

note: all server in same oracle verion and same linux verion

plz help and give me a good advice to me.
Re: finding Tuing problem [message #231335 is a reply to message #231323] Mon, 16 April 2007 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the session on trace and compare both execution plans and wait events.

Regards
Michel
Re: finding Tuing problem [message #231351 is a reply to message #231335] Mon, 16 April 2007 12:37 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
thank u sir

i will check
icon12.gif  Re: finding Tuing problem [message #231509 is a reply to message #231351] Tue, 17 April 2007 06:23 Go to previous messageGo to next message
kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA
Junior Member
Hi,
How about trying gathering statistics for all the tables involved in the query using DBMS_STATS. The options used in DBMS_STATS also influence the way the query behaves. U can use try the following syntax:

exec dbms_stats.gather_table_stats( ownname => '&SCHEMA_NAME',tabname => '&TB_NAME',estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto',cascade => true,degree => 7)
Re: finding Tuing problem [message #232201 is a reply to message #231509] Thu, 19 April 2007 15:29 Go to previous message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
Thanx for replying
Previous Topic: Execution plan changed automatically. How does that happen?
Next Topic: faq's in perfomance tuning
Goto Forum:
  


Current Time: Tue Dec 06 06:26:21 CST 2016

Total time taken to generate the page: 0.09962 seconds