你的ANALYZE怎么写的?你的要求我觉得可以这么来做:
SQL> set pages 999
SQL> select 'ANALYZE TABLE ' || table_name || ' COMPUTE STATISTICS;'
2 from user_tables;'ANALYZETABLE'||TABLE_NAME||'COMPUTESTATISTICS;'
----------------------------------------------------------------
ANALYZE TABLE ACTIVITY COMPUTE STATISTICS;
ANALYZE TABLE ACTIVITY_CHANGE_REQUEST COMPUTE STATISTICS;
ANALYZE TABLE ACTIVITY_WORKLOAD_SHARE COMPUTE STATISTICS;
ANALYZE TABLE APPLICATION COMPUTE STATISTICS;
ANALYZE TABLE APP_RELEASE COMPUTE STATISTICS;
ANALYZE TABLE CHANGE_REQUEST COMPUTE STATISTICS;
ANALYZE TABLE ITEM_ATTACHMENT COMPUTE STATISTICS;
ANALYZE TABLE ITEM_COMMENT COMPUTE STATISTICS;
ANALYZE TABLE ITEM_LOG COMPUTE STATISTICS;
ANALYZE TABLE PERSON COMPUTE STATISTICS;
ANALYZE TABLE SCORE COMPUTE STATISTICS;
ANALYZE TABLE STUDENTS COMPUTE STATISTICS;
ANALYZE TABLE TIGER_TABLE_STU COMPUTE STATISTICS;
ANALYZE TABLE USER_ROLE COMPUTE STATISTICS;
ANALYZE TABLE USR COMPUTE STATISTICS;
ANALYZE TABLE WEEKLY_WORKLOAD COMPUTE STATISTICS;16 rows selected.
***** 在这里copy/paste上面的结果,或者存到一个sql脚本中做batch run。SQL> ANALYZE TABLE ACTIVITY COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ACTIVITY_CHANGE_REQUEST COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ACTIVITY_WORKLOAD_SHARE COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE APPLICATION COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE APP_RELEASE COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE CHANGE_REQUEST COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ITEM_ATTACHMENT COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ITEM_COMMENT COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ITEM_LOG COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE PERSON COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE SCORE COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE STUDENTS COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE TIGER_TABLE_STU COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE USER_ROLE COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE USR COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE WEEKLY_WORKLOAD COMPUTE STATISTICS;Table analyzed.SQL> select table_name, num_rows from user_tables;TABLE_NAME NUM_ROWS
------------------------------ ----------
ACTIVITY 4
ACTIVITY_CHANGE_REQUEST 1
ACTIVITY_WORKLOAD_SHARE 2
APPLICATION 7
APP_RELEASE 3
CHANGE_REQUEST 4
ITEM_ATTACHMENT 0
ITEM_COMMENT 0
ITEM_LOG 0
PERSON 0
SCORE 578
STUDENTS 1
TIGER_TABLE_STU 1
USER_ROLE 11
USR 11
WEEKLY_WORKLOAD 016 rows selected.
SQL> set pages 999
SQL> select 'ANALYZE TABLE ' || table_name || ' COMPUTE STATISTICS;'
2 from user_tables;'ANALYZETABLE'||TABLE_NAME||'COMPUTESTATISTICS;'
----------------------------------------------------------------
ANALYZE TABLE ACTIVITY COMPUTE STATISTICS;
ANALYZE TABLE ACTIVITY_CHANGE_REQUEST COMPUTE STATISTICS;
ANALYZE TABLE ACTIVITY_WORKLOAD_SHARE COMPUTE STATISTICS;
ANALYZE TABLE APPLICATION COMPUTE STATISTICS;
ANALYZE TABLE APP_RELEASE COMPUTE STATISTICS;
ANALYZE TABLE CHANGE_REQUEST COMPUTE STATISTICS;
ANALYZE TABLE ITEM_ATTACHMENT COMPUTE STATISTICS;
ANALYZE TABLE ITEM_COMMENT COMPUTE STATISTICS;
ANALYZE TABLE ITEM_LOG COMPUTE STATISTICS;
ANALYZE TABLE PERSON COMPUTE STATISTICS;
ANALYZE TABLE SCORE COMPUTE STATISTICS;
ANALYZE TABLE STUDENTS COMPUTE STATISTICS;
ANALYZE TABLE TIGER_TABLE_STU COMPUTE STATISTICS;
ANALYZE TABLE USER_ROLE COMPUTE STATISTICS;
ANALYZE TABLE USR COMPUTE STATISTICS;
ANALYZE TABLE WEEKLY_WORKLOAD COMPUTE STATISTICS;16 rows selected.
***** 在这里copy/paste上面的结果,或者存到一个sql脚本中做batch run。SQL> ANALYZE TABLE ACTIVITY COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ACTIVITY_CHANGE_REQUEST COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ACTIVITY_WORKLOAD_SHARE COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE APPLICATION COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE APP_RELEASE COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE CHANGE_REQUEST COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ITEM_ATTACHMENT COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ITEM_COMMENT COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE ITEM_LOG COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE PERSON COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE SCORE COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE STUDENTS COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE TIGER_TABLE_STU COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE USER_ROLE COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE USR COMPUTE STATISTICS;Table analyzed.SQL> ANALYZE TABLE WEEKLY_WORKLOAD COMPUTE STATISTICS;Table analyzed.SQL> select table_name, num_rows from user_tables;TABLE_NAME NUM_ROWS
------------------------------ ----------
ACTIVITY 4
ACTIVITY_CHANGE_REQUEST 1
ACTIVITY_WORKLOAD_SHARE 2
APPLICATION 7
APP_RELEASE 3
CHANGE_REQUEST 4
ITEM_ATTACHMENT 0
ITEM_COMMENT 0
ITEM_LOG 0
PERSON 0
SCORE 578
STUDENTS 1
TIGER_TABLE_STU 1
USER_ROLE 11
USR 11
WEEKLY_WORKLOAD 016 rows selected.
1、不能对系统的数据字典做ANALYZE
2、不能对临时表做ANALYZE
3、有REFs,varrays, nested tables,long或者object类型的字段不参与ANALYZE
但我有些表的num_rows却没值。有REFs,varrays, nested tables,long或者object类型的字段不参与ANALYZE
这句有点不懂。
是有这种字段的表不参与analyze还是这种字段不参与analyze?
我把select 后的结果在sqlplus中粘贴的时候,居然没有粘全。
但我已经全选了。
不知道什么问题。
你的意思是说oracle自己会根据analyze的结果调整自身的性能?
rule base和cost base中文含义怎么说?
(基于规则?基于代价?)
性能调整确实很抽象。
COST BASE是基于成本的优化,每次执行SQL前会先分析这句SQL的成本(主要是数据读写的I/O量),然后根据成本最小的原则选择最优化的方案。应该说,COST BASE对大型数据库来说更合理,所以现在一般都采用COST BASE。这个问题要说清楚牵涉到很多方面,建议你看看优化方面的资料,网站上应该有下载的。