Solved

Meaning Of 'LAST_ANALYZED'

Posted on 2014-04-02
10
957 Views
Last Modified: 2014-04-03
In OEM under schema->tables there is a column 'last_analyzed'.  What is the process or job that produces this column?   I had thought it came from the Oracle job dbms_stats.gather......, but I'm seeing that job has been disabled yet these tables were analyzed yesterday and today.
0
Comment
Question by:xoxomos
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
Comment Utility
Yes, it does come from there..

it simply means when did the last time the statistics were collected for that particular object..

refer to analyze command in oracle documentation

http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_4005.htm

this question was already asked in one of the forums, doing a copy and paste from there

It means statistics was collected. In older versions statistics was collected using ANALYZE statement, that's why LAST_ANALYZED. In newer versions you can still use ANALYZE but DBMS_STATS package provides much more comprehensive interface to collect stats.

to view the question, you can go here

https://community.oracle.com/thread/981094?start=0&tstart=0
0
 
LVL 23

Expert Comment

by:David
Comment Utility
xo, which version of Oracle?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
Comment Utility
Even though that job is disabled what about the automatic maintenance window?  I tihnk it does some stats collection as well.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
Are you using some custom job to collect stats or copying stats from another database if Gather Stats Job is disable?
It could also be possible that some of the batch jobs written by (developers etc) creates some tables and gathers stats on them.
0
 

Author Comment

by:xoxomos
Comment Utility
---- 'Even though that job is disabled what about the automatic maintenance window?  I tihnk it does some stats collection as well.'
The vendor, Blackboard Learn, had us to set the maintenance plan to ''.

"---It could also be possible that some of the batch jobs written by (developers etc) creates some tables and gathers stats on them."

The developers do have a dbms_stats.gather_schema_stats, but that runs on Saturday.
These tables show last analyzed as yesterday, Tuesday.

Now it's 11.2.0.4 version
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
Comment Utility
On which tables were you able to see stats collected?
0
 

Author Comment

by:xoxomos
Comment Utility
I'm seeing some of the user tables have recent stats others do not.
Sys and system almost none have recent stats.  Most go back to 2012.
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 150 total points
Comment Utility
In general sys and system schema comprises of data dictionary views, if you want to(though you may or may not have the need) update the stats of those schemas, you should do gather data dictionary stats and database stats

for eg:

EXEC DBMS_STATS.gather_dictionary_stats;

refer to this Tim Hall link for more information on database statistics,

http://www.oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics.php
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 250 total points
Comment Utility
you can do below:-
Find timing of last analyzed for set of tables you are interested in.
using this time, find snap_id
using snap_id, find all SQLs get executed using DBA_HIST_SQLSTAT. You can look for 'ANALYZE TABLE/INDEX' or 'DBMS_STATS' sqls
You can find bind variables and try to find values or which modules causes this.
Also you can try to find more information from DBA_HIST_ACTIVE_SESSION_HIST table.

To me it looks like, stats are not getting gathered using GATHER JOB, but manually/some job.
0
 

Author Closing Comment

by:xoxomos
Comment Utility
Thanks all.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now