The colleagues ask me: How to find the history of the statistics in Oracle?

My answer is: well, it is not very difficult, just see the instructions below.

You could find this with the following SQL:

SELECT *

FROM DBA_TAB_STATISTICS

WHERE TABLE_NAME = ‘ My_Table_Name ‘;

My_Table_Name is the name of the table which you would like to check whether the statistics are locked.

There are three views you could use depends on your desire:

• DBA_TAB_STATISTICS displays optimizer statistics for all tables in the database.
• USER_TAB_STATISTICS displays optimizer statistics for the tables owned by the current user. This view does not display the OWNER column.
• ALL_TAB_STATISTICS displays optimizer statistics for the tables accessible to the current user.

In an SAP system you can use the report RSORADJV for this purpose or use SQL Plus at OS level.

From SAP system it could be checked with a transaction DB20.

Posted by admin | No Comments »

The colleagues ask me: How to find the history of the statistics in Oracle?

My answer is: well, it is not very difficult, just see the instructions below.

You could find this with the following SQL:

SELECT *

FROM USER_TAB_STATS_HISTORY

WHERE TABLE_NAME = ‘My_Table_Name‘;

My_Table_Name is the name of the table which you would like to check the history of the statistics for.

There are dictionary views that display the time of statistics modifications. These views are useful in determining the time stamp to be used for statistics restoration.

• Catalog view DBA_OPTSTAT_OPERATIONS contain history of statistics operations performed at schema and database level using DBMS_STATS.
• The views *_TAB_STATS_HISTORY views (ALL, DBA, or USER) contain a history of table statistics modifications.

In an SAP system you can use the report RSORADJV for this purpose or use SQL Plus at OS level.

Posted by admin | No Comments »