Oracle judges and manually collects statistics scripts

CREATE OR REPLACE PROCEDURE  current_userisBEGIN    SYS. Dbms_stats. Gather_table_stats ('schname'TableName ' CASCADE = TRUE); END ; /

Select Owner,table_name,last_analyzed,num_rows from dba_tables where owner= ' SYSTEM ' and Table_name= ' aq$_internet_agents '

Last_analyzed: The last time the information of related tables is counted;

Num_rows: Number of records in the table stored in the optimizer (may not match the actual situation )

When you EXEC the DBMS_STATS procedure directly, it's running as an anonymous block, and those always run with Invoker's right s-honouring roles.

I. Description

In the previous blog:

Summary of Oracle statistic statistical information

Http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx

The collection of statistics, Oracle's automatic Statistics gathering is collected and maintained through scheduler. The job name is Gather_stats_job, which collects 2 statistics about all objects in the database:

(1) Missing Statistics (statistical information missing)

(2) Stale statistics (statistical information obsolete)

The job is created automatically when the database is created and managed by scheduler. Scheduler run the gather job when maintenance windows open. By default, jobs are opened every night from 10 to 6 in the morning and on weekends throughout the day. The process first detects missing statistics and obsolete objects. Then prioritize and start the statistics.

The Stop_on_window_close property of the Scheduler JOB controls whether Gather_stats_job continues. The default value of this property is true. If the value is set to False, the gather_stats_job is interrupted, and objects that are not collected will continue to be collected the next time they are started.

Gather_stats_job calls the Dbms_stats.gather_database_stats_job_proc procedure to collect statistics information. The process collects objects statistics the following conditions:

(1) The statistics for the object have not been collected before.

(2) When the object has more than 10% rows modified, the object's statistics are also called stale statistics.

In Oracle 10g, dynamic sampling is used when querying tables without statistical analysis.

Oracle Analytics and Dynamic sampling

Http://blog.csdn.net/tianlesoftware/archive/2010/08/28/5845028.aspx

The above instructions are for Oracle to automatically collect this piece, but sometimes, automatic collection is not very reliable, because by default, only in the evening 10 o'clock to 6 o ' morning. If updates are frequent on other timesheets, the data is inaccurate. The resulting implementation plan may differ from the actual one.

Two. Manual collection of statistical information scripts

You can view the collection of statistics by using the following SQL:

[SQL]View Plain Copy

1. /* Formatted on 2011/11/24 12:03:16 (QP5 v5.185.11230.41888) */
2. SELECT/*+ UNNEST */
3. DISTINCT table_name, last_analyzed, Stale_stats
4. From Dba_tab_statistics
5. WHERE last_analyzed is NULL OR stale_stats = ' YES ' and OWNER = ' XEZF '

In general, when the table analysis, in view of the Num_rows column of the Dba_tables table will show the number of records in the table, we can take this value and count (*) to compare the results, if the 2 is a large difference, it indicates that the table of statistical information is obsolete, need to collect statistical information.

`

2.1 Stored Procedures

CREATE OR REPLACE PROCEDURE proc_manual_gather_stats

As

T_count number;

T_num_rows number;

T_tablename VARCHAR2 (50);

T_sql VARCHAR2 (200);

CURSOR C1 is a select * from Dba_tables where owner= ' DAVE ';

BEGIN

/**

Process content: to determine whether the statistics are synchronized, such as not synchronized, manually collect statistical information

tianlesoftware

Time:2011-5-25

*/

For X in C1

LOOP

T_tablename: = X.table_name;

T_num_rows: = x.num_rows;

T_sql: = ' SELECT count (*) from ' | | T_tablename;

Execute immediate t_sql into T_count;

--Dbms_output. Put_Line(' t_tablename:--' | | t_tablename | | '   --' | | ' t_num_rows is:t_count:--' | |  t_num_rows | | ': ' | | T_count);

If ABS (t_count-t_num_rows) >=10000 Then

--When the number of records in the statistics is greater than 10000 of the actual number of records in the table, the table is analyzed

Dbms_stats.gather_table_stats (' DAVE ', t_tablename);

End If;

END LOOP;

EXCEPTION

When No_data_found

Then

Dbms_output. Put_Line (' No_data_found ');

RETURN;

When OTHERS

Then

Dbms_output. Put_Line (' OTHERS ');

RETURN;

END;

/

2.2 Using Scheduler Job Deployment

Oracle 10g Scheduler Features

Http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4715218.aspx

2.2.1 Create job

BEGIN

Dbms_scheduler. Create_job (

job_name = ' Job_manual_gather_stats ',

Job_type = ' stored_procedure ',

job_action = ' proc_manual_gather_stats ',--the procedure name of the call

start_date = Sysdate,

Repeat_interval = ' freq=hourly;interval=1 ');

--once every hour

END;

/

This time is judged by your business, because the analysis itself consumes CPU resources. So try to avoid peak business execution, preferably when the business is low.

Attention:

The JOB was created successfully, but it was not executed. Because the enabled parameter is not explicitly specified, the default value of this parameter is false.

2.2.2 Enable job

exec dbms_scheduler.enable (' job_manual_gather_stats ');

2.2.3 Stop Job

exec dbms_scheduler.disable (' job_manual_gather_stats ');

http://blog.csdn.net/tianlesoftware/article/details/6445868

Need to use the gather_table_stats process of dbms_stats This package, where the owner and the table name must be filled in.

PROCEDURE Gather_table_stats
Argument Name Type in/out Default?
------------------------------ ----------------------- ------ --------
Ownname VARCHAR2 in
TabName VARCHAR2 in
PartName VARCHAR2 in DEFAULT
Estimate_percent number in DEFAULT
Block_sample BOOLEAN in DEFAULT
Method_opt VARCHAR2 in DEFAULT
Degree number in DEFAULT
Granularity VARCHAR2 in DEFAULT
CASCADE BOOLEAN in DEFAULT
Stattab VARCHAR2 in DEFAULT
Statid VARCHAR2 in DEFAULT
Statown VARCHAR2 in DEFAULT
No_invalidate BOOLEAN in DEFAULT
StatType VARCHAR2 in DEFAULT
Force BOOLEAN in DEFAULT

exec dbms_stats.gather_table_stats (' SCOTT ', ' D ');

The statistics of the table are not taken in real time. So sometimes just delete the data in the table, not real-time from the user_tables in the num_rows reaction. This is the time to collect the statistics from the table below.

Sql> Select table_name,num_rows from user_tables where table_name= ' D ';

TABLE_NAME Num_rows
--------------- ----------
D 4

Sql> select * from D;

DEPTNO dname
---------- ----------------------------
Ten ACCOUNTING
About
SALES
OPERATIONS

Sql> Delete from d where deptno=30;

1 row deleted.

Sql> commit;

Commit complete.

Sql> select * from D;

DEPTNO dname
---------- ----------------------------
Ten ACCOUNTING
About
OPERATIONS

Sql> Select table_name,num_rows from user_tables where table_name= ' D ';

TABLE_NAME Num_rows
--------------- ----------
D 4

The number of rows is still 4. We collect the statistical information.

sql> exec dbms_stats. Gather_table_stats (' SCOTT ', ' D ');//You can also use the call command

PL/SQL procedure successfully completed.

Sql> Select table_name,num_rows from user_tables where table_name= ' D ';

TABLE_NAME Num_rows
--------------- ----------
D 3

==============================================================================================

Restore the data you just deleted ...

Sql> alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';

Session altered.

Sql> sql>
Sql> select Sysdate from dual;

Sysdate
-------------------
2012-02-28 05:01:49

Sql> SELECT * from D as of timestamp to_timestamp (' 2012-02-28 04:50:00 ', ' yyyy-mm-dd hh24:mi:ss ') where deptno=30;

DEPTNO dname
---------- ----------------------------
SALES

sql> INSERT INTO D select * from D as of timestamp to_timestamp (' 2012-02-28 04:50:00 ', ' yyyy-mm-dd hh24:mi:ss ') where de ptno=30;

1 row created.

Sql> select * from D;

DEPTNO dname
---------- ----------------------------
Ten ACCOUNTING
About
OPERATIONS
SALES

Sql> commit;

Commit complete.

Http://www.linuxidc.com/Linux/2012-12/76912.htm

Oracle judges and collects statistics scripts manually