Solved

Meaning Of 'LAST_ANALYZED'

Posted on 2014-04-02
10
1,158 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
ID: 39973496
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
ID: 39973506
xo, which version of Oracle?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 39973553
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
ID: 39973588
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
ID: 39973612
---- '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
ID: 39973694
On which tables were you able to see stats collected?
0
 

Author Comment

by:xoxomos
ID: 39973753
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
ID: 39974049
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
ID: 39976171
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
ID: 39976480
Thanks all.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Database creation fails 5 49
Encryption Decryption in Oracle 12 107
statspack purge automate 7 53
Difference in number of minutes between 2 timestamps 16 39
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 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

25 Experts available now in Live!

Get 1:1 Help Now