-------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6529 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select num_rows from user_tables where table_name='HR_TUNDO';
NUM_ROWS
----------
SQL> analyze table hr_tundo compute statistics;
Table analyzed
SQL> select num_rows from user_tables where table_name='HR_TUNDO';
NUM_ROWS
----------
100000
统计信息的级别有basic，typical和all三种
basic代表关闭统计信息收集，typical代表只收集重要的统计信息，all则代表收集全部的统计信息
SQL> show parameter statistics;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
二：自动工作负载资料档案库
特点：针对性能内建的资料档案库；每小时进行一次快照，保留7天的快照数据，是所有自动管理功能的基础，由MMON进程负责收集信息。
创建基线，保留快照集,运行ADDM
SQL> select sql_text from v$sql where
2 plan_hash_value=292991709 and rownum <=5;
SQL_TEXT
---------------------------------------------------------
update hr.hr_tundo set uvalue=79320 where uname='a79319'
update hr.hr_tundo set uvalue=79721 where uname='a79720'
update hr.hr_tundo set uvalue=79415 where uname='a79414'
update hr.hr_tundo set uvalue=83617 where uname='a83616'
update hr.hr_tundo set uvalue=83684 where uname='a83683'
三：建议者中心，由dbms_advisor包提供
SQL> begin
2 dbms_advisor.quick_tune(
3 advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
4 task_name => 'task2',
5 attr1 => 'select * from hr.hr_tundo where uname like ''%123%''',
6 template => 'SQLACCESS_OLTP');
7* end;
8
9 PL/SQL procedure successfully completed.
本文出自 “月牙天冲” 博客