» 小议9i和10g的差别点 OracleBlog.cn -天堂向左 DBA向右

小议9i和10g的差别点

· 作者小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/study-note/different-from-9i-and-10g/

10g的一些新特性就不在这边讨论了，flashback、ash、awr等等都在newfeature中可以看到。在这里讨论下在平常的开发和维护的过程中，容易忽视的差别点。如果您在日常工作中也遇到了别的差别，值得需要注意的地方，也欢迎您告诉我。

一、9i的group by会排序，10g的不会。

--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>

10g的这个不排序的表现是收到隐含参数"_gby_hash_aggregation_enabled"影响，10g中，该参数默认是true。

二、9i的dbms_stats不会收集直方图，10g的会。

--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>

在9i中，dbms_stats的method_opt的默认值是for all column size 1，即不收集直方图；在10g中，默认值是for all column size auto。

三、同样属性的表的全表扫描，10g的cost会多1：

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>

这是受到隐含参数"_table_scan_cost_plus_one"影响，10g中这个参数默认为true。

四、plan_table的变化，9i是个table，10g是temporary table，且字段也发生了改变：

--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直接跳过索引走全表扫描：

--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>

该变化是受到10g中的一个新参数"skip_unusable_indexes"影响，10g默认是true，9i中对于不可用的index，则报错。

另外小记：查看隐含参数，

当前系统的：
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。

· 【文章发布信息】发表于： 2009-02-19 @ 23:45:52 · ||分类： Study note

4 条评论 »

robin.ma 于 2009-02-20 @ 11:53:18 留言：

10G自动收集histogram是很大的一个进步. 但是不是真的收集了histogram.要看user_tab_col_statistics比较准确
carcase 于 2009-02-20 @ 13:46:17 留言：

您的blog很精彩，更新也快，学了不少东西，谢谢
老熊于 2009-02-24 @ 12:27:09 留言：

good
hh 于 2009-03-09 @ 22:05:31 留言：

rman全备时先参数文件、控制文件、数据文件顺序不同

RSS 为此帖反馈评论 · 反向跟踪网站

OracleBlog.cn -天堂向左 DBA向右