Home » RDBMS Server » Performance Tuning » Queries not running - pls help
Queries not running - pls help [message #216832] Tue, 30 January 2007 11:47 Go to next message
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
Re: Queries not running - pls help [message #216833 is a reply to message #216832] Tue, 30 January 2007 11:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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?
Re: Queries not running - pls help [message #216837 is a reply to message #216832] Tue, 30 January 2007 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 25047
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.
Re: Queries not running - pls help [message #216839 is a reply to message #216837] Tue, 30 January 2007 12:05 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
Not giving any error messages, but just does'nt gives any results
Re: Queries not running - pls help [message #216840 is a reply to message #216839] Tue, 30 January 2007 12:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>but just does'nt gives any result
May be it is not finding the records!.
Re: Queries not running - pls help [message #216841 is a reply to message #216832] Tue, 30 January 2007 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25047
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
Re: Queries not running - pls help [message #216974 is a reply to message #216841] Wed, 31 January 2007 02:21 Go to previous messageGo to next message
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
Re: Queries not running - pls help [message #216975 is a reply to message #216832] Wed, 31 January 2007 02:28 Go to previous messageGo to next message
michael_bialik
Messages: 611
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.


Re: Queries not running - pls help [message #221033 is a reply to message #216975] Fri, 23 February 2007 05:28 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
Thanks for the reply

Do I need to alter all tables in my schema, to monitoring for proper statistics collection

Thanks
Re: Queries not running - pls help [message #221077 is a reply to message #216832] Fri, 23 February 2007 12:29 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO - yes.
Previous Topic: db statistics after db abnormal shutdown
Next Topic: Optimize mode first_rows returns full table
Goto Forum:
  


Current Time: Wed Dec 07 14:31:56 CST 2016

Total time taken to generate the page: 0.11327 seconds