Home » RDBMS Server » Server Administration » Restoring statistics in oracle10g
Restoring statistics in oracle10g [message #430058] Sat, 07 November 2009 20:46 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
I am using oracle10g. I have emp table in scott schema. I analyzed the table today and
last_analyzed field is showing today's date. it make sense.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select last_analyzed from dba_tables where table_name='EMP';

LAST_ANALYZED
--------------------------------------------------------------------------------
07-NOV-09

I have statistics history since from 6-oct-2009.

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
06-OCT-09 09.52.45.351000000 PM -05:00


Now i want to restore the statistics as of date 10-OCT-2009

SQL> execute dbms_stats.restore_table_stats('SCOTT','EMP','10-OCT-09 09.52.45.351000000 PM -05:00')

PL/SQL procedure successfully completed.

SQL>

Let me check the last_analyzed column....

SQL> select last_analyzed from dba_tables where table_name='EMP';

LAST_ANALYZED
--------------------------------------------------------------------------------
15-SEP-09

SQL>


It is showing 15-SEP-2009. But is is supposed to show 10-OCT-09.

Any clarification is highly appreciated..

Report message to a moderator

Re: Restoring statistics in oracle10g [message #430101 is a reply to message #430058] Sun, 08 November 2009 06:09 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
I checked the dba_tab_stats_history view and i do not see that emp table statistics was not updated on 15-SEP-09.


SQL> select table_name,stats_update_time from dba_tab_stats_history
  2  where table_name='EMP'
  3  /

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------
EMP                            19-OCT-09 10.01.27.954000 PM -04:00
EMP                            19-OCT-09 10.31.32.347000 PM -04:00
EMP                            19-OCT-09 10.26.45.057000 PM -04:00
EMP                            19-OCT-09 10.33.57.068000 PM -04:00
EMP                            19-OCT-09 10.37.23.627000 PM -04:00
EMP                            19-OCT-09 10.38.14.437000 PM -04:00
EMP                            19-OCT-09 10.39.49.269000 PM -04:00
EMP                            19-OCT-09 10.48.10.138000 PM -04:00
EMP                            19-OCT-09 10.50.52.815000 PM -04:00
EMP                            19-OCT-09 10.51.29.085000 PM -04:00
EMP                            19-OCT-09 10.54.42.094000 PM -04:00
EMP                            19-OCT-09 10.57.11.589000 PM -04:00
EMP                            30-OCT-09 06.25.54.809000 PM -04:00
EMP                            07-NOV-09 07.23.23.504000 AM -05:00
EMP                            07-NOV-09 07.27.31.709000 AM -05:00
EMP                            07-NOV-09 07.29.54.324000 AM -05:00

16 rows selected.

SQL>

Report message to a moderator

Re: Restoring statistics in oracle10g [message #430125 is a reply to message #430101] Mon, 09 November 2009 00:21 Go to previous message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
 
SQL> conn system/tejajun20
Connected.
SQL> select to_timestamp(sysdate) from dual;

TO_TIMESTAMP(SYSDATE)
---------------------------------------------------------------------------
09-NOV-09 12.00.00 AM

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
08-NOV-09 03.28.39.996000000 PM +05:30

SQL> select last_analyzed from dba_tables where table_name='EMP';

LAST_ANAL
---------
04-NOV-09
22-OCT-09
08-NOV-09

SQL> ed
Wrote file afiedt.buf

  1* select last_analyzed from dba_tables where table_name='EMP' and owner='SCOTT'
SQL> /

LAST_ANAL
---------
08-NOV-09

SQL> ED
Wrote file afiedt.buf

  1* select TOTIMESTAMP(last_analyzed) from dba_tables where table_name='EMP' and owner='SCOTT'
SQL> /
select TOTIMESTAMP(last_analyzed) from dba_tables where table_name='EMP' and owner='SCOTT'
       *
ERROR at line 1:
ORA-00904: "TOTIMESTAMP": invalid identifier


SQL> ED
Wrote file afiedt.buf

  1* select TO_TIMESTAMP(last_analyzed) from dba_tables where table_name='EMP' and owner='SCOTT'
SQL> /

TO_TIMESTAMP(LAST_ANALYZED)
---------------------------------------------------------------------------
08-NOV-09 12.00.00 AM

SQL> begin
  2     dbms_stats.gather_table_stats('SCOTT','EMP');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select TO_TIMESTAMP(last_analyzed) from dba_tables where table_name='EMP' and owner='SCOTT';

TO_TIMESTAMP(LAST_ANALYZED)
---------------------------------------------------------------------------
09-NOV-09 12.00.00 AM

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
08-NOV-09 03.28.39.996000000 PM +05:30

SQL> select table_name,stats_update_time from dba_tab_stats_history
  2   where table_name='EMP' AND OWNER='SCOTT'
  3   /

TABLE_NAME
------------------------------
STATS_UPDATE_TIME
---------------------------------------------------------------------------
EMP
08-NOV-09 04.34.44.896000 PM +05:30

EMP
09-NOV-09 11.30.00.325000 AM +05:30


SQL>  begin
  2      dbms_stats.gather_table_stats('SCOTT','EMP');
  3   end;
  4   /

PL/SQL procedure successfully completed.

SQL>  select TO_TIMESTAMP(last_analyzed) from dba_tables where table_name='EMP' and owner='SCOTT';

TO_TIMESTAMP(LAST_ANALYZED)
---------------------------------------------------------------------------
09-NOV-09 12.00.00 AM

SQL>  select table_name,stats_update_time from dba_tab_stats_history
  2    where table_name='EMP' AND OWNER='SCOTT'
  3    /

TABLE_NAME
------------------------------
STATS_UPDATE_TIME
---------------------------------------------------------------------------
EMP
08-NOV-09 04.34.44.896000 PM +05:30

EMP
09-NOV-09 11.30.00.325000 AM +05:30

EMP
09-NOV-09 11.32.31.073000 AM +05:30


SQL> execute dbms_stats.restore_table_stats('SCOTT','EMP','09-NOV-09 11.30.00.325000 AM +05:30');

PL/SQL procedure successfully completed.

SQL> select TO_TIMESTAMP(last_analyzed) from dba_tables where table_name='EMP' and owner='SCOTT';

TO_TIMESTAMP(LAST_ANALYZED)
---------------------------------------------------------------------------
09-NOV-09 12.00.00 AM

SQL>  execute dbms_stats.restore_table_stats('SCOTT','EMP','08-NOV-09 04.34.44.896000 PM +05:30');

PL/SQL procedure successfully completed.

SQL>  select TO_TIMESTAMP(last_analyzed) from dba_tables where table_name='EMP' and owner='SCOTT';

TO_TIMESTAMP(LAST_ANALYZED)
---------------------------------------------------------------------------
08-NOV-09 12.00.00 AM

SQL>  select table_name,stats_update_time from dba_tab_stats_history
  2    where table_name='EMP' AND OWNER='SCOTT'
  3    /

TABLE_NAME
------------------------------
STATS_UPDATE_TIME
---------------------------------------------------------------------------
EMP
08-NOV-09 04.34.44.896000 PM +05:30

EMP
09-NOV-09 11.30.00.325000 AM +05:30

EMP
09-NOV-09 11.32.31.073000 AM +05:30


TABLE_NAME
------------------------------
STATS_UPDATE_TIME
---------------------------------------------------------------------------
EMP
09-NOV-09 11.33.33.747000 AM +05:30

EMP
09-NOV-09 11.34.15.096000 AM +05:30


SQL>

Hope the following may help you....

http://itknowledgeexchange.techtarget.com/eye-on-oracle/your-oracle-wish-list-2/

http://www.freelists.org/post/oracle-l/silly-dbms-stats-question,3

http://yong321.freeshell.org/oranotes/Dbms_statsThenAnalyze.txt

http://hemantoracledba.blogspot.com/2008/08/testing-gather-stats-behaviour-based-on.html

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm


Quote:
I checked the dba_tab_stats_history view and i do not see that emp table statistics was not updated on 15-SEP-09.


"STATS_UPDATE_TIME" gives you when the stats are updated....

Check your stats_availability


sriram.

[Updated on: Mon, 09 November 2009 00:21]

Report message to a moderator

Previous Topic: 9i to 10g upgrade - Materialized views
Next Topic: Reading Trace(.trc) file
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 11:29:40 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.