OraFAQ Forum: Performance Tuning » Queries not running

Home » RDBMS Server » Performance Tuning » Queries not running - pls help

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

Queries not running - pls help [message #216832]

Tue, 30 January 2007 11:47

sajut
Messages: 69
Registered: January 2007

Member

Hi

I have few sql queries which has been running for about two years. Since last few days, these queries almost stopped executing. Is this due to any storage parameters like PCT_FREE or PCT_USED or freelists of the related tables.

Kindly advice

Report message to a moderator

Re: Queries not running - pls help [message #216833 is a reply to message #216832]

Tue, 30 January 2007 11:53

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> Since last few days, these queries almost stopped executing
What do you mean by that?
When was the last time the statistics is been updated?

Report message to a moderator

Re: Queries not running - pls help [message #216837 is a reply to message #216832]

Tue, 30 January 2007 11:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>these queries almost stopped executing.
Do you realize how many hundreds or thousands of different ways a query can fail?
Posting actual error codes & messages might get you closer to a solution.

Report message to a moderator

Re: Queries not running - pls help [message #216839 is a reply to message #216837]

Tue, 30 January 2007 12:05

sajut
Messages: 69
Registered: January 2007

Member

Not giving any error messages, but just does'nt gives any results

Report message to a moderator

Re: Queries not running - pls help [message #216840 is a reply to message #216839]

Tue, 30 January 2007 12:06

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>but just does'nt gives any result
May be it is not finding the records!.

Report message to a moderator

Re: Queries not running - pls help [message #216841 is a reply to message #216832]

Tue, 30 January 2007 12:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

1) Collect new statistics
2) post EXPLAIN_PLAN
3) enable SQL_TRACE at level 12, run query, run results thru TKPROF & post results

Report message to a moderator

Re: Queries not running - pls help [message #216974 is a reply to message #216841]

Wed, 31 January 2007 02:21

sajut
Messages: 69
Registered: January 2007

Member

Hi
With your hint about updating STATISTICS, I could solve the problem. I have not updated the statistics of some tables used in the query. Now I updated all of them and query is running fine.

I used
analyze table <tablename> compute statistics
analyze index <index names> compute statistics

Is is better to use analyze or dbms_stats.get_table_stats cmpute statistics. and how often do I need to update the statistics on objects?

Thank you very much

Report message to a moderator

Re: Queries not running - pls help [message #216975 is a reply to message #216832]

Wed, 31 January 2007 02:28

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Oracle 9 and UP you have to use DBMS_STATS.

Issue ALTER TABLE <table_name> MONITORING; for all your tables. You will be able to use DBA_TAB_MODIFICATIONS view to see the number of DML for each table and it may help you to decide about DBMS_STATS usage.

HTH.

Report message to a moderator