10g的一些新特性就不在这边讨论了，flashback、ash、awr等等都在newfeature中可以看到。在这里讨论下在平常的开发和维护的过程中，容易忽视的差别点。如果您在日常工作中也遇到了别的差别，值得需要注意的地方，也欢迎您告诉我。
一、9i的group by会排序，10g的不会。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
--9i： SQL> select * from tab2; A B ---------- ---------- 1 22 3 22 1 23 2 24 9 24 9 29 5 29 7 rows selected. SQL> SQL> select a,sum(b) from tab2 group by a; A SUM(B) ---------- ---------- 1 45 2 24 3 22 5 29 9 53 SQL> --10g： SQL> select * from tab2; A B ---------- ---------- 1 22 3 22 1 23 2 24 9 24 9 29 5 29 7 rows selected. Elapsed: 00:00:00.67 SQL> select a,sum(b) from tab2 group by a; A SUM(B) ---------- ---------- 1 45 2 24 5 29 3 22 9 53 Elapsed: 00:00:00.42 SQL> |
|
1 |
10g的这个不排序的表现是收到隐含参数"_gby_hash_aggregation_enabled"影响，10g中，该参数默认是true。 |
二、9i的dbms_stats不会收集直方图，10g的会。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
--9i： SQL> select a,count(*) from tab2 group by a; A COUNT(*) ---------- ---------- 1 55960 99 1384 999 832 SQL> exec dbms_stats.gather_table_stats(user,'TAB2',cascade=>true); PL/SQL procedure successfully completed. SQL> select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from USER_HISTOGRAMS where TABLE_NAME='TAB2' and COLUMN_NAME='A'; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------------ --------------- -------------- A 0 1 A 1 999 SQL> --10g： SQL> select a,count(*) from tab2 group by a; A COUNT(*) ---------- ---------- 1 55960 999 832 99 1384 Elapsed: 00:00:01.20 SQL> SQL> SQL> exec dbms_stats.gather_table_stats(user,'TAB2',cascade=>true); PL/SQL procedure successfully completed. Elapsed: 00:00:03.32 SQL> select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from USER_HISTOGRAMS where TABLE_NAME='TAB2' and COLUMN_NAME='A'; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------------ --------------- -------------- A 5270 1 A 5409 99 A 5498 999 Elapsed: 00:00:00.04 SQL> |
|
1 |
在9i中，dbms_stats的method_opt的默认值是for all column size 1，即不收集直方图；在10g中，默认值是for all column size auto。 |
三、同样属性的表的全表扫描，10g的cost会多1：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
9i： SQL> explain plan for 2 select /*+ full(t) */ a from tab2; Explained. SQL> set line 1000 SQL> set pages 1000 SQL> SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 82 | 1066 | 3 (34)| | 1 | TABLE ACCESS FULL | TAB2 | 82 | 1066 | 3 (34)| ------------------------------------------------------------------------- 7 rows selected. SQL> 10g： SQL> explain plan for 2 select /*+ full(t) */ a from tab2; Explained. Elapsed: 00:00:00.25 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------- --------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5000 | 65000 | 4 (0)| | 1 | TABLE ACCESS FULL| TAB2 | 5000 | 65000 | 4 (0)| --------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version 11 rows selected. Elapsed: 00:00:01.57 SQL> |
|
1 |
这是受到隐含参数"_table_scan_cost_plus_one"影响，10g中这个参数默认为true。 |
四、plan_table的变化，9i是个table，10g是temporary table，且字段也发生了改变：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
--9i： CREATE TABLE "SYS"."PLAN_TABLE" ( "STATEMENT_ID" VARCHAR2(30), "TIMESTAMP" DATE, "REMARKS" VARCHAR2(80), "OPERATION" VARCHAR2(30), "OPTIONS" VARCHAR2(255), "OBJECT_NODE" VARCHAR2(128), "OBJECT_OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(30), "OBJECT_INSTANCE" NUMBER(*,0), "OBJECT_TYPE" VARCHAR2(30), "OPTIMIZER" VARCHAR2(255), "SEARCH_COLUMNS" NUMBER, "ID" NUMBER(*,0), "PARENT_ID" NUMBER(*,0), "POSITION" NUMBER(*,0), "COST" NUMBER(*,0), "CARDINALITY" NUMBER(*,0), "BYTES" NUMBER(*,0), "OTHER_TAG" VARCHAR2(255), "PARTITION_START" VARCHAR2(255), "PARTITION_STOP" VARCHAR2(255), "PARTITION_ID" NUMBER(*,0), "OTHER" LONG, "DISTRIBUTION" VARCHAR2(30), "CPU_COST" NUMBER(*,0), "IO_COST" NUMBER(*,0), "TEMP_SPACE" NUMBER(*,0), "ACCESS_PREDICATES" VARCHAR2(4000), "FILTER_PREDICATES" VARCHAR2(4000) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" --10g： CREATE GLOBAL TEMPORARY TABLE "SYS"."PLAN_TABLE$" ( "STATEMENT_ID" VARCHAR2(30), "PLAN_ID" NUMBER, "TIMESTAMP" DATE, "REMARKS" VARCHAR2(4000), "OPERATION" VARCHAR2(30), "OPTIONS" VARCHAR2(255), "OBJECT_NODE" VARCHAR2(128), "OBJECT_OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(30), "OBJECT_ALIAS" VARCHAR2(65), "OBJECT_INSTANCE" NUMBER(*,0), "OBJECT_TYPE" VARCHAR2(30), "OPTIMIZER" VARCHAR2(255), "SEARCH_COLUMNS" NUMBER, "ID" NUMBER(*,0), "PARENT_ID" NUMBER(*,0), "DEPTH" NUMBER(*,0), "POSITION" NUMBER(*,0), "COST" NUMBER(*,0), "CARDINALITY" NUMBER(*,0), "BYTES" NUMBER(*,0), "OTHER_TAG" VARCHAR2(255), "PARTITION_START" VARCHAR2(255), "PARTITION_STOP" VARCHAR2(255), "PARTITION_ID" NUMBER(*,0), "OTHER" LONG, "OTHER_XML" CLOB, "DISTRIBUTION" VARCHAR2(30), "CPU_COST" NUMBER(*,0), "IO_COST" NUMBER(*,0), "TEMP_SPACE" NUMBER(*,0), "ACCESS_PREDICATES" VARCHAR2(4000), "FILTER_PREDICATES" VARCHAR2(4000), "PROJECTION" VARCHAR2(4000), "TIME" NUMBER(*,0), "QBLOCK_NAME" VARCHAR2(30) ) ON COMMIT PRESERVE ROWS |
具体的可见我前段时间写的《9i和10g的plan table》。
五、对失效索引，9i提示不能用，10g直接跳过索引走全表扫描：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 |
--9i： SQL> create table test.tab8 as select * from dba_users; Table created. SQL> create unique index p_id on test.tab8 (user_id); Index created. SQL> SQL> SQL> SQL> explain plan for 2 select username from test.tab8 where user_id=2; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS BY INDEX ROWID| TAB8 | | | | |* 2 | INDEX UNIQUE SCAN | P_ID | | | | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TAB8"."USER_ID"=2) Note: rule based optimization 15 rows selected. SQL> SQL> SQL> alter table test.tab8 move; Table altered. SQL> select status from dba_indexes where index_name='P_ID'; STATUS -------- UNUSABLE SQL> SQL> SQL> explain plan for 2 select username from test.tab8 where user_id=2; explain plan for * ERROR at line 1: ORA-01502: index 'SYS.P_ID' or partition of such index is in unusable state --10g： SQL> create table test.tab8 as select * from dba_users; Table created. Elapsed: 00:00:01.93 SQL> create unique index p_id on test.tab8 (user_id); Index created. Elapsed: 00:00:00.67 SQL> SQL> SQL> SQL> explain plan for 2 select username from test.tab8 where user_id=2; Explained. Elapsed: 00:00:00.10 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| TAB8 | 1 | 30 | 1 (0)| |* 2 | INDEX UNIQUE SCAN | P_ID | 1 | | 0 (0)| ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("USER_ID"=2) Note ----- - 'PLAN_TABLE' is old version 17 rows selected. Elapsed: 00:00:00.03 SQL> SQL> SQL> SQL> alter table test.tab8 move; Table altered. Elapsed: 00:00:01.23 SQL> SQL> SQL> select status from dba_indexes where index_name='P_ID'; STATUS -------- UNUSABLE Elapsed: 00:00:00.51 SQL> SQL> explain plan for 2 select username from test.tab8 where user_id=2; Explained. Elapsed: 00:00:01.96 SQL> SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- --------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 180 | 3 (0)| |* 1 | TABLE ACCESS FULL| TAB8 | 6 | 180 | 3 (0)| --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("USER_ID"=2) Note ----- - 'PLAN_TABLE' is old version 16 rows selected. Elapsed: 00:00:02.53 SQL> |
|
1 |
该变化是受到10g中的一个新参数"skip_unusable_indexes"影响，10g默认是true，9i中对于不可用的index，则报错。 |
另外小记：查看隐含参数，
|
1 2 3 4 5 6 7 |
当前系统的： SQL> select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppsv b 2 where a.indx=b.indx and KSPPINM like '\_%' escape '\'; 看当前session的： SQL> select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppcv b 2 where a.indx=b.indx and KSPPINM like '\_%' escape '\'; |
x$ksppsv，s是表示system；x$ksppcv，c表示current session。一般alter session修改后，查看当前session是否启用了修改后的隐含参数，用x$ksppcv。
4条评论
10G自动收集histogram是很大的一个进步. 但是不是真的收集了histogram.要看user_tab_col_statistics比较准确
您的blog很精彩，更新也快，学了不少东西，谢谢
good
rman全备时先参数文件、控制文件、数据文件顺序不同