oracle10g Statistics View, collection

1. Statistical Information view

1.1 Global statistics for individual tables, viewing of statistical effects

2. Statistical information Analysis (collection)

2.1 Analysis Tool Selection

2.2 To do index reconstruction before analysis

2.3 Analyze a data table that can be executed under the PL/SQL command window

2.4 Analysis schema, performed in Sqlplus

2.5 Dbms_stats.gather_schema_stats Detailed

2.6 Initialization parameter Statistics_level relationship to Oracle default statistics collection job

3. Improved, auxiliary Oracle default statistics collection job

3.1 Create new statistics collection proc, JOB, executed every weekend

3.2 View the status of the newly created statistics collection job

3.3 Batch generation of the received statistics (by table) of SQL

4. The most forceful statistical information collection command (failure and success documentary)

4.1 Failed Statistics Collection commands

4.2 attempted command, each command cannot change the execution plan of the core SQL

4.3 Collect statistics by table: changed the execution Plan of core SQL---OK

4.4 Important conclusions of statistical information collection (Oracle Technician recommendation) 1. Statistics View 1.1 single table global statistics, statistical effects view

Sql> Select Column_name,num_distinct,density,sample_size,avg_col_len from Dba_tab_col_statistics where table_name = ' TB1 ';

Sql> Select Global_stats from dba_tables where table_name= ' TB1 ';

If it is no, analyze the statistics immediately.

To view the last statistics collection time for a table:

Sql> Select To_char (last_analyzed, ' Fmyyyy-mm-dd HH24:MI:SS ') from Dba_tables where wner= ' Platform_dev ' and Table_ Name= ' T_user ';

View the last statistics collection time for an index: check dba_indexes, User_indexes last_analyzed. 2. Statistical information Analysis (collection) 2.1 Analysis tool selection

Dbms_stats can be analyzed in parallel

Dbms_stats features automatic analysis (ALTER TABLE monitor)

Analyze analysis statistics, inaccurate----some times 2.2 analysis before doing index reconstruction

Rebuild Indexes by Table:

Sql> select ' Alter index PLATFORM_DEV. ' | | index_name| | ' rebuild online; ' from dba_indexes where wner= ' Platform_dev ' and table_name= ' t_sso_session ';

Rebuild the index by user (schema):

Sql> select ' Alter index PLATFORM_DEV. ' | | index_name| | ' rebuild online; ' from dba_indexes where wner= ' Platform_dev '; 2.3 Analyze a data table that can be executed under the PL/SQL command window

The default way----The most common way:

Sql> EXECUTE dbms_stats. Gather_table_stats (

Ownname = ' LK ',

tabname = ' TB1 ',

Estimate_percent = NULL,

Method_opt = ' For all INDEXED COLUMNS ',

CASCADE = TRUE);

------Full-scale analysis (full-granularity), note that cascade=true must be added, otherwise the relevant index may not be counted.

Sql> begin

Dbms_stats. Gather_table_stats (ownname = ' Platform_dev ', tabname = ' T_operation_log ', CASCADE = TRUE);

End

/2.4 Analysis schema, performed in Sqlplus

PL/sql:

Begin

Dbms_stats.gather_schema_stats (ownname = 'platform_dev', estimate_percent = 5,cascade=>true, DEGREE=>5);

End

/

SQLPLUS:

----resource consumption is large and can only be performed in server native Sqlplus.

sql> exec dbms_stats.gather_schema_stats (ownname = ' LK ', estimate_percent = 5,cascade=>true,degree=> 5); 2.5 Dbms_stats.gather_schema_stats Detailed

estimate_percent option : Allow Oracle's dbms_stats to automatically estimate the best percentage of a segment to be sampled when collecting statistics:
Estimate_percent = Dbms_stats.auto_sample_size---This is its default value

ESTIMATE_PERCENT=>100---Complete statistics

Cascade options: Recursive statistics, statistics related table corresponding index

Cascade=> TRUE

degree Options : determines the degree of parallelism. Default value is null

Options Parameters

Options=> "Gather"--re-analysis of the entire architecture (schema).

Options=> "Gather empty"-----collect statistics for tables that have not been analyzed

Options=> "Gather stale"----to re-analyze the revised table of more than 10% (i.e., add, delete, change).

Options=> "Gather Auto"----to re-parse objects that do not currently have statistics, as well as objects that have statistics that expire (become dirty). Note that using gather auto is similar to combining the use of gather stale and gather empty.

method_opt Parameters

Ideal for refreshing statistics when tables and index data change, or for determining which columns require histograms (histograms).

Method_opt=> ' For all columns size skewonly '

Method_opt=> ' For all columns size repeat '----use it when you periodically re-analyze the statistics, and less resources are consumed to re-analyze the task. It will only re-parse the index for the existing histogram and no longer search for other histogram opportunities. -----triggered the bug!. Do not use this item!

Method_opt=> ' For all columns size auto '-----Oracle company recommended setting value 2.6 initialization parameters statistics_level relationship to Oracle default statistics collection job

? Sys user, find default statistics collection JOB :

Select Owner,program_name,job_action,job_name, state from Dba_scheduler_jobs where job_name = ' Gather_ Stats_job '

? Initialize Parameters Statistics_level three states ( BASIC , Typical or All )

Statistics_level=basic, Oracle turns off the collection of all performance data from the default statistics collection job, that is, to turn off Awr or Statspack collection, as long as you set the ALTER system set Statistics_level =basic; it's all right.

Statistics_level=typical, in addition to plan_executetion_statistics and OS statistics cannot be collected, others can be collected, if you want to collect the two items, you must set the Statistics_ Level=all; 3. Improved, auxiliary Oracle default statistics collection JOB 3.1 Create new statistics collection proc, job, executed every weekend

///// the optional method --- Disable the default statistics collection job , but does not advocate

SYS user, disable the default statistics collection job:
Sql> BEGIN
Dbms_scheduler. DISABLE (' gather_stats_job ');
END; ----Re-enable it with Dbms_scheduler. Enable command
Review is really disabled:

Sql> SELECT owner,job_name,enabled from dba_scheduler_jobs WHERE job_name = ' gather_stats_job '; ----The enabled should be false at this time

///// the optional method --- Disable the default statistics collection job , but not advocated, ended

? Sys users create weekend statistics collection stored procedures

Create or replace procedure Proc_statistics_gather_weekend is
Begin
---gather statistics of schema:lk
Dbms_stats.gather_schema_stats (ownname = "LK",

Estimate_percent = 30,

CASCADE=>TRUE,DEGREE=>5);

----Repeat above sentence for the other schema.
End

----parameter method_opt=> ' for all columns size repeat ' is problematic, not explicitly specified but best with default values.

? Sys user creates weekend statistics collection job

Begin
Dbms_scheduler.create_job (
job_name = ' Job_statistics_gather_weekend ',
Job_type = ' stored_procedure ',
Job_action = ' SYS. Proc_statistics_gather_weekend ',
start_date = To_date (' 2010-08-22 04:00:00 ', ' fmyyyy-mm-dd HH24:MI:SS '),
Repeat_interval = ' freq=daily;interval=7 ',
Comments = ' Author:liukan ');
End;

----NOTE: Once every 7 days, the first time is 2010-08-22 04:00:00,

? Sys Turn on Enable the job above

Begin
Dbms_scheduler.enable (' job_statistics_gather_weekend ');
End

? Sys manually try to run the job above

Begin
Dbms_scheduler.run_job (job_name = ' job_statistics_gather_weekend ',
Use_current_session = TRUE);
End; 3.2 View the status of the newly created statistics collection job

SYS User:

Select Job_name,job_action,state,to_char (start_date, ' fmyyyy-mm-dd HH24:MI:SS ') start_date, To_char (Next_run_date, ' Fmyyyy-mm-dd HH24:MI:SS ') next_run_date from User_scheduler_jobs; 3.3 Batch generation of the received statistics (by table) of SQL

Select ' EXEC dbms_stats. Gather_table_stats (ownname = ' | | Chr (39) | | Platform_dev ' | | Chr | | ', tabname = | | Chr (39) | | table_name| | Chr | | ', CASCADE = True,degree = 5); ' From User_tables; 4. The most forceful statistical information collection command (failure and success documentary) 4.1 Failed statistics Collection command

Create or replace procedure Proc_statistics_gather_weekend is
Begin
Dbms_stats.gather_schema_stats (ownname = ' Platform_dev ',

Estimate_percent = 50,

Method_opt=> ' For all columns size repeat ',

CASCADE=>TRUE,DEGREE=>5);

End

----Its execution leads to the most core of the main station library (user login) SQL can not go the best execution plan. 4.2 attempted command, each command cannot change the execution plan of the core SQL

Command 1:

exec dbms_stats.gather_schema_stats (ownname = ' Platform_dev ', estimate_percent = 30,cascade=>true,degree= >5);

Command 2:

EXEC dbms_stats. Gather_schema_stats (ownname=> ' Platform_dev ', estimate_percent = 30,method_opt=> ' For all indexed columns ', Options=> ' GATHER ', cascade=>true);

Command 3:

exec dbms_stats.gather_schema_stats (ownname = ' Platform_dev ', estimate_percent + =, method_opt = ' for all CO Lumns size skewonly ', cascade=>true,degree = 5);

Command 4:

exec dbms_stats.gather_schema_stats (ownname = ' Platform_dev ', estimate_percent + =, method_opt = ' for all CO Lumns size skewonly ', cascade=>true,degree = 5);

These commands are useless, and it is useless to execute the statements after rebuilding all the indexes, and the core (user logged in) SQL is unable to go through the best execution plan. 4.3 Collect statistics by table: changed the execution Plan of core SQL---OK

EXEC dbms_stats. Gather_table_stats (ownname = ' Platform_dev ', tabname = ' T_user ', CASCADE = True,degree = 5);

EXEC dbms_stats. Gather_table_stats (ownname = ' Platform_dev ', tabname = ' T_corporation_user ', CASCADE = True,degree = 5);

......

The core (user login) SQL goes the best execution plan! 4.4 Important conclusions of statistical information collection (recommended by Oracle Technician)

Dbms_stats.gather_schema_stats may not be good, or better:

1. Every three months or half a year (depending on the frequency of data changes) Use routine update downtime to organize the storage structure of the primary table (move tablespace can be the same TBS), rebuild all indexes (rebuild no online option), Based on this, the statistics are collected manually by schema, and then the database performance is closely tracked. +

2. Regular statistical information collection is performed by Oracle's own automated statistical information collection Mechanism +

3. Discover slow SQL, which can focus on rebuilding indexes on slow SQL-related tables and re-collecting statistics for related tables (dbms_stats. Gather_table_stats) +

/////////////////////

Select ' EXEC dbms_stats. Gather_table_stats (ownname = ' | | chr (39) | |
' Platform_dev ' | | Chr (39) | | ', tabname = ' | | Chr (39) | | table_name | |
Chr (39) | | ', estimate_percent =>30,cascade = True,degree = 5); '
From User_tables t where t.table_name isn't like '%$% ';

////////////////////

4. The most core, most frequently executed, and not the complex assembly of the SQL, should be added hint to enforce its implementation plan.

5. Critical, throat, and core SQL is stored in an Oracle database in a process or view format to facilitate fast SQL tuning and troubleshooting.

oracle10g Statistics View, collection