Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3749
  • Last Modified:

Meaning Of 'LAST_ANALYZED'

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
xoxomos
Asked:
xoxomos
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
Wasim Akram ShaikCommented:
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
 
DavidSenior Oracle Database AdministratorCommented:
xo, which version of Oracle?
0
 
slightwv (䄆 Netminder) Commented:
Even though that job is disabled what about the automatic maintenance window?  I tihnk it does some stats collection as well.
0
Technology Partners: 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!

 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
xoxomosAuthor Commented:
---- '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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
On which tables were you able to see stats collected?
0
 
xoxomosAuthor Commented:
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
 
Wasim Akram ShaikCommented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
xoxomosAuthor Commented:
Thanks all.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now