??????光有执行DBMS_STATS的权限还是不够的
DBMS_STATS.GATHER_DATABASE_STATS?(ESTIMATE_PERCENT => 30);
*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Database
ORA-06512: at "SYS.DBMS_STATS", line 13323
ORA-06512: at "SYS.DBMS_STATS", line 13682
ORA-06512: at "SYS.DBMS_STATS", line 13826
ORA-06512: at "SYS.DBMS_STATS", line 13790
ORA-06512: at line 1
User did not have the right privilege:?? ANALYZE ANY DICTIONARY.
You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.
Connect to Sqlplus as sysdba and issue command:
SQL> Grant ANALYZE ANY DICTIONARY to username;
SQL>grant ANALYZE ANY system privilege to username;
NOTE:?username is the name of the user trying to gather statistics.
10G:
IMPORTANT: PLEASE NOTE:
These recommendations apply to the majority of databases.
The recommendations aim for statistics accuracy? so full samples are suggested.
For very large systems, the gathering of statistics can be a very time consuming and resource intensive activity.
In this environment sample sizes need to be carefully controlled to ensure that gathering completes within acceptable timescales and resource constraints.
For guidance on this topic See:
?
Note:237901.1?Gathering Schema or Database Statistics Automatically - Examples
In these environments, it is also recommended to utilize change based statistics gathering to avoid re-gathering information unnecessarily.
Please see:
?
Note:44961.1?Statistics Gathering: Frequency and Strategy Guidelines
Gathering statistics an individual table
exec dbms_stats.gather_table_stats( -
ownname => NULL, -
tabname => '?Table_name?', -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
N.B. replace ' Table_name ' with the name of the table to gather statistics for.
Gathering statistics for all objects in a schema
exec dbms_stats.gather_schema_stats( -
?ownname => NULL, -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
Gathering statistics for all objects in the database:
exec dbms_stats.gather_database_stats( -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
12C：
Quick Recreate Recommendation
To achieve a quick delete and recreate of the statistics on an individual table and its indexes (adding column statistics for any skewed columns) and following the recommendations in this article use:
exec dbms_stats.delete_table_stats(ownname=>'user_name',-
? tabname=>'table_name',cascade_indexes=>true);
Sample Statistic Gathering Commands
Gathering statistics an individual table
exec dbms_stats.gather_table_stats( ?-
? ? ? ?ownname => ' ?Schema_name ', -
? ? ? ?tabname => ' ?Table_name ?', -
? ? ? ?estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, ?-
? ? ? ?cascade => TRUE, ?-
? ? ? ?method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
Gathering statistics for all objects in a schema
exec dbms_stats.gather_schema_stats( -
?ownname => ' ?Schema_name ', -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
Gathering statistics for all objects in the database:
exec dbms_stats.gather_database_stats( -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
---------------------------
How to exclude external tables when you run dbms_stats.gather_database_stats procedure manually.
The following errors may be seen when you gather database or schema stats:
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SCOTT"','"TEST_EXT"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file sapasset.csv in MAXIMO_CURRENCY not found
DBMS_STATS: Too many errors... Rest of the erros are not reporded.
Lock the stats for the external table and rerun the database or schema stats:
1. EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');
2. EXEC DBMS_STATS.gather_database_stats;
or
EXEC DBMS_STATS.gather_schema_stats('SCOTT');
3. Once gather stats is complete, unlock the table stats:
EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS ('owner name', 'table name');
--------------
The document clarifies how sys and system stats are gathered by procedures DBMS_STATS.GATHER_DICTIONARY_STATS and DBMS_STATS.GATHER_DATABASE_STATS.
...
procedure gather_dictionary_stats
...
-- Gather statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of
-- RDBMS components.
...
-----------------
To describe how to force table statistics to be gathered on every execution of the default automatic statistics job and the GATHER_DATABASE_STATS job.
?
If you set the stale percentage of the particular table(s) you are interested in to 0, this will force the statistcs of this table to be gathered for every execution of default auto job regardless of the actual number modifications. You can set this using the following command:
exec dbms_stats.set_table_prefs('<user_name>','EMP','STALE_PERCENT',0);
The default value for STALE_PERCENT is 10%? (the default will be applied if NULL is specified).
For further information, refer to following:
------------------------------
No statistics are gathered for certain objects such as Materialized view logs when using dbms_stats.gather_database_stats? or dbms_stats.gather_schema_stats.
?none
Statistics are not gathered for the following objects:
?
---------------------------
SQL> SELECT table_name, index_name, stale_stats FROM dba_ind_statistics WHERE table_owner = user AND table_name = 'STALE_IDX'; TABLE_NAME INDEX_NAME STALE_STATS ----------- ------------- ----------- STALE_IDX SYS_C0013557 YES
None
When gather_database_stats or gather_schema_stats is called with LIST AUTO, it only returns stale tables, tables with no stats, and indexes with no stats. It is by design that stale index statistics are not refreshed unless the corresponding table statistics are stale as well.
A defect was filed against this symptom but it cannot be fixed in the scope of a bug fix within the current infrastructure. An enhancement was also filed to address this in a future version, subject to feasibility:
Bug 16411709?AUTO GATHER STATS IS NOT PICKING UP STALE OBJECTS
Bug 10411689?DBMS_STATS.GATHER_*_STATS SHOULD RETURN STALE INDEXES IN LISTOBJ FOR LIST AUTO
As a workaround, periodically Gather Statistics on stale objects manually.
Note: If the table is also marked as stale then it could be that the Automatic Optimizer Statistics Collection job does not have enough time available to get to these objects. According to the FAQ for Automatic Statistics Collection:
"The GATHER_DATABASE_STATS_JOB_PROC procedure called by the 'auto optimizer stats collection' job prioritizes database objects that have no statistics. This means that objects that most need statistics are processed first. Once these are done then objects with stale statistics are addressed. For these, there is no particular prioritization."
Document 1233203.1?FAQ: Automatic Statistics Collection
---------------Oracle10g 后 自动monitor? objects become stale
Intended for anyone collecting stats via the DBMS_STATS package
This is brief note to add some clarification in the area of the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_DATABASE_STATS procedures.? The 'options' parameter of these two procedures allows you to provide further
specifications on which objects to gather statistics on. Two of the values that this parameter can take are 'GATHER STALE' and 'GATHER AUTO'.
Oracle will gather statistics on objects which have statistics considered to be STALE. This is done by looking at the *_tab_modifications views. To the end user, this means that if more than 10% of the rows change, then statistics will automatically be gathered.
Oracle will gather automatically statistics on objects which currently have NO statistics (even if they have NO MONITORING set) *plus* existing objects with STALE statistics. Prior to Oracle 10g GATHER AUTO (just like GATHER STALE) required monitoring to be turned on for the objects which already have statistics. If monitoring is was not turned on, there was no way for Oracle to know which objects become stale. In 10g and above this is handled automatically.
When GATHER AUTO is specified in DBMS_STATS.GATHER_STATS syntax, Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. Thus, Gather AUTO option can be used to allow Oracle to decide how much statistics to gather.
When GATHER STALE is specified, any other parameters specified will also be taken into account.
-------------------STALE_PERCENT 设置为0 也必须有改变才gather
Execute dbms_stats.set_table_prefs(owner,table,'STALE_PERCENT',0) does not have desired effect.
?
?
This is expected behavior as the stats will gather initially with stale_percent =0,but subsequently will not be gathered unless there is modifications on the objects that are involved.
Following testcase gives an example:
?
Testcase
==========
SQL> select to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') from dba_tables where table_name = 'SHAWN';
TO_CHAR(LAST_ANALYZ
-------------------
2019/01/09 13:53:53
SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
PL/SQL procedure successfully completed.
SQL> select to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') from dba_tables where table_name = 'SHAWN';
TO_CHAR(LAST_ANALYZ
-------------------
2019/01/09 13:53:53
=============================================
Todays date 1/14/2019
Reran the below query
select to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') from dba_tables where table_name = 'SHAWN';
TO_CHAR(LAST_ANALYZ
-------------------
2019/01/09 13:53:53
SQL> select * from shawn;
FNAME LNAME ID
-------------------- -------------------- ----------
SEWY JOHN 1
DOE MIKE 2
RONALDO MESSI 3
JORDAN NIKE 4
Then Inserted a row
SQL> insert into shawn values ('seshe', 'bisi',5);
1 row created.
SQL> commit
?2 ;
Then rerun the auto stats
SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
PL/SQL procedure successfully completed.
SQL> select to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') from dba_tables where table_name = 'SHAWN';
TO_CHAR(LAST_ANALYZ
-------------------
2019/01/14 15:27:35 ==========Now its analyzed