?
Solved

Meaning Of 'LAST_ANALYZED'

Posted on 2014-04-02
10
Medium Priority
?
2,622 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 600 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 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

801 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