OraFAQ Forum: Performance Tuning » finding Tuing problem

Home » RDBMS Server » Performance Tuning » finding Tuing problem

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

finding Tuing problem [message #231323]

Mon, 16 April 2007 09:16

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.

Report message to a moderator

Re: finding Tuing problem [message #231335 is a reply to message #231323]

Mon, 16 April 2007 10:30

Michel Cadot
Messages: 68651
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

Report message to a moderator

Re: finding Tuing problem [message #231351 is a reply to message #231335]

Mon, 16 April 2007 12:37

vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY

Senior Member

thank u sir

i will check

Report message to a moderator

Re: finding Tuing problem [message #231509 is a reply to message #231351]

Tue, 17 April 2007 06:23

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)

Report message to a moderator

Re: finding Tuing problem [message #232201 is a reply to message #231509]

Thu, 19 April 2007 15:29

vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY

Senior Member

Thanx for replying

Report message to a moderator

Previous Topic:	Execution plan changed automatically. How does that happen?
Next Topic:	faq's in perfomance tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 04:14:46 CDT 2024