Oracle 技術探討1

1、簡易研究UNDO方法

實驗所獲取的知識點如下：

1、檢視事務的檢視名為v$transaction
2、檢視事務變化的欄位為v$transaction 檢視的USED_UBLK欄位（每次執行後的增量就是該事務所產生的回滾量)
3、在事務提交或者說無事務的時候，v$transaction的USED_UBLK欄位為空值
4、表中，索引列比非索引列產生更大的UNDO

實驗如下：

SQL> drop table ljb_test ;

Table dropped

SQL> create table ljb_test as select object_name no_idx, object_name idx from dba_objects;

Table created

SQL> create index idx_ljb_test on ljb_test(idx);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'ljb_test');

PL/SQL procedure successfully completed

SQL> select used_ublk from v$transaction where addr =
  2  (select taddr from v$session where sid =(select sid from v$mystat where rownum = 1));

USED_UBLK
----------
SQL>

SQL> update ljb_test set no_idx=upper(no_idx);

122546 rows updated

SQL> select used_ublk from v$transaction where addr =
  2  (select taddr from v$session where sid =(select sid from v$mystat where rownum = 1));

USED_UBLK
----------
   2010

SQL> update ljb_test set idx=upper(idx);

122546 rows updated

SQL> select used_ublk from v$transaction where addr =
  2  (select taddr from v$session where sid =(select sid from v$mystat where rownum = 1));

USED_UBLK
----------
   4776

---4776-2010>2010,因此可以推斷出有索引的列開銷的UNDO更大

SQL> commit;

Commit complete

select used_ublk from v$transaction where addr =
(select taddr from v$session where sid =(select sid from v$mystat where rownum = 1));

USED_UBLK
----------

SQL>

2、簡易研究REDO方法
實驗所獲取的知識點如下：
1、研究redo的量可通過資料字典v$mystat 和v$statname關聯得到，語句為
select a.name,b.value
from v$statname a,v$mystat b
where a.statistic#=b.statistic
and a.name='redo size';
2、表中，有索引的列產生的redo將遠大於無索引列產生的redo
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb

SQL> drop table ljb_test;

Table dropped

SQL>  create table ljb_test as select object_name no_idx, object_name idx from dba_objects;

Table created

SQL> create index idx_ljb_test on ljb_test(idx);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'ljb_test');

PL/SQL procedure successfully completed

SQL>
SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and a.name='redo size';

NAME                                                                VALUE
---------------------------------------------------------------- ----------
redo size                                                       116036376

SQL> update ljb_test set no_idx=lower(no_idx);

122548 rows updated

SQL>
SQL> select a.name,b.value from v$statname a,v$mystat b where
  2  a.statistic#=b.statistic# and a.name='redo size';

NAME                                                                VALUE
---------------------------------------------------------------- ----------
redo size                                                       156722024

SQL> update ljb_test set idx=lower(idx);

122548 rows updated

SQL>
SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and a.name='redo size';

NAME                                                                VALUE
---------------------------------------------------------------- ----------
redo size                                                       265747736

SQL>
---（156722024-116036376）可推斷出索引列產生的redo將更大。

3、簡易分析索引的方法
如何看索引的高度有多高,建立什麼樣的索引效率更高如何分析

實驗獲取的知識點如下：
1、分析研究索引用的方法是 analyze index id_normal validate structure ，然後檢視index_stats
2、注意analyze index id_normal validate structure 會導致鎖表，生產慎用。
3、index_stats的資料只對當前操作過analyze index id_normal validate structure 的SESSION可見
4、HEIGHT為索引高度，值越大，索引開銷越大
5、在很多場合下，靈活使用選擇行索引（函式索引的一種),往往能提高效率。此外函式索引只在CBO有效

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb

SQL>
SQL> drop table ljb_test;

Table dropped

SQL> create table ljb_test (id int ,status varchar2(2));

Table created

SQL> create index id_normal on ljb_test(status);

Index created

SQL> insert into ljb_test select rownum ,'Y' from dual connect by rownum<=100000;

100000 rows inserted

SQL> insert into ljb_test select 1 ,'N' from dual;

1 row inserted

SQL> analyze table ljb_test compute statistics for table for all indexes for all indexed columns;

Table analyzed

SQL> explain plan for  select * from ljb_test where status='N';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3604305264
--------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |          |    1 | 10 |    2 (0)| 0
| 1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST  |    1 | 10 |    2 (0)| 0
|*  2 | INDEX RANGE SCAN       | ID_NORMAL |    1 |    |    1 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='N')

14 rows selected

SQL> analyze index id_normal validate structure;

Index analyzed

--接下來可以看索引的高度和葉子情況

SQL> select name,btree_space,lf_rows,height from index_stats;

NAME                         BTREE_SPACE LF_ROWS    HEIGHT
------------------------------ ----------- ---------- ----------
ID_NORMAL                         1976032    100001       2

SQL> drop index id_normal;

Index dropped

----以下為建立選擇性索引的情況

SQL> create index id_status on  ljb_test (Case when status= 'N' then 'N' end);

Index created

SQL> analyze table ljb_test compute statistics for table for all indexes for all indexed columns;

Table analyzed

SQL> explain plan for  select * from ljb_test where (case when status='N' then 'N' end)='N';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2720798490
--------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |          |    1 | 10 |    2 (0)| 0
| 1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST  |    1 | 10 |    2 (0)| 0
|*  2 | INDEX RANGE SCAN       | ID_STATUS |    1 |    |    1 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')

14 rows selected

SQL> analyze index id_status validate structure;

Index analyzed

SQL> select name,btree_space,lf_rows,height from index_stats;

NAME                         BTREE_SPACE LF_ROWS    HEIGHT
------------------------------ ----------- ---------- ----------
ID_STATUS                            8000       1                   1

SQL>

4、簡易預估SQL執行進度方法
實驗獲取的知識點：
1、以下情況可在v$session_longops中跟到SQL語句的進度
Table scan;
Index Fast Full Scan;
Hash join;
Sort/Merge;
Sort Output;
Rollback;
Gather Table's Index Statistics.
In next chapters I’ll look deeper into first three of the above listed types.

2、跟蹤到的SQL進度可以從sofar和total_work兩個欄位的值的差異來比較進度，理論上來說，兩者相等之時，就是任務完成之時

3、有很多情況無法跟蹤到SQL語句的進度
  3.1表掃描時間低於6秒
  3.2TABLE SCAN的塊必須超過10000個
  3.3 索引快速全掃描的塊必須超過1000個

來自：
Besides criterion that Table scan has to run more than 6 seconds there is one another criterion that is not so widely known - table has to occupy at least 10000 database blocks. Table scan has to satisfy BOTH criteria to show up in v$session_longops i.e. if the table has less than 10000 blocks but table scan runs more than 6 seconds that’s not enough.
Index FFS as well as Table Scans have another criterion for showing up in v$session_longops. Index has to occupy at least 1000 blocks i.e. 10 times less than threshold for Table Scans.

實驗如下：

SQL> select * from  v$mystat where rownum=1;

   SID STATISTIC#    VALUE
---------- ---------- ----------
   143       0       1

SQL> drop table ljb_test;

Table dropped

SQL> create table ljb_test as select * from dba_objects;

Table created

SQL> select count(*) from ljb_test t1,ljb_test  t2  where t1.owner=t2.owner ;

---開另外一視窗觀察v$session_longops情況
SQL> SELECT COUNT(*) FROM v$session_longops WHERE sid=143;

  COUNT(*)
----------
      0 --Table scan訪問的塊要超過10000個，不會顯示。

SQL> insert into ljb_test select * from ljb_test;

159466 rows inserted

SQL> insert into ljb_test select * from ljb_test;

318932 rows inserted

SQL> commit;

Commit complete

---現在就可以跟蹤到了！

SQL> SELECT COUNT(*) FROM v$session_longops WHERE sid=143;

  COUNT(*)
----------
      1

SQL> SELECT sid,opname,sofar,totalwork FROM v$session_longops WHERE sid=143;

   SID OPNAME                                                             SOFAR  TOTALWORK
---------- ---------------------------------------------------------------- ---------- ----------
   143 Table Scan                                                          15235    15235
   143 Table Scan                                                             53    15235

SQL>

5、簡易研究rowid的方法

獲取知識點

1、關於ROWID組成
8i後的rowid組成結構如下：OOOOOO FFF BBBBBB RRR
其中O是指data_object_id, F是指rfile#, B是指block_id, R是指rowid
共有10個位元組(10*8=80bit)組成：32bit+10bit rfile#+22bit+16bit

2、ROWID的查詢包dbms_rowid的簡要使用

select dbms_rowid.rowid_object('&1') data_object_id#,
   dbms_rowid.rowid_relative_fno('&1') rfile#,
   dbms_rowid.rowid_block_number('&1') block#,
   dbms_rowid.rowid_row_number('&1') row# from dual;

3、對普通表而言，表記錄的更新，是不會導致ROWID改變的

4、對分割槽表而言，表記錄的更新有可能導致ROWID改變

5、除了分割槽表，shrink或者flashback等其他操作也有可能導致ROWID改變。（所以把rowid寫成程式碼中做唯一判斷是不可靠的）

實驗如下：

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb

SQL> drop table part_tab1;

Table dropped

SQL>
SQL>  create table part_tab1 (id int)
  2    partition by range (id)
  3    (
  4    partition p1 values less than (10),
  5    partition p2 values less than (20),
  6    partition p3 values less than (maxvalue)
  7    )
  8    ;

Table created

SQL> insert into part_tab1 select rownum from dual connect by rownum <=50;

50 rows inserted

SQL> commit;

Commit complete

SQL>  select t.*,t.rowid,dbms_rowid.rowid_object(rowid) data_object_id#,
  2             dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3             dbms_rowid.rowid_block_number(rowid) block#,
  4             dbms_rowid.rowid_row_number(rowid) row# from part_tab1 partition(p1) t;

                                 ID ROWID             DATA_OBJECT_ID#    RFILE#    BLOCK#    ROW#
--------------------------------------- ------------------ --------------- ---------- ---------- ----------
                                    1 AAAw8pABNAAAAymAAA       200489       77    3238       0
                                    2 AAAw8pABNAAAAymAAB       200489       77    3238       1
                                    3 AAAw8pABNAAAAymAAC       200489       77    3238       2
                                    4 AAAw8pABNAAAAymAAD       200489       77    3238       3
                                    5 AAAw8pABNAAAAymAAE       200489       77    3238       4
                                    6 AAAw8pABNAAAAymAAF       200489       77    3238       5
                                    7 AAAw8pABNAAAAymAAG       200489       77    3238       6
                                    8 AAAw8pABNAAAAymAAH       200489       77    3238       7
                                    9 AAAw8pABNAAAAymAAI       200489       77    3238          8

9 rows selected

SQL> select t.*,t.rowid,dbms_rowid.rowid_object(rowid) data_object_id#,
  2          dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3          dbms_rowid.rowid_block_number(rowid) block#,
  4          dbms_rowid.rowid_row_number(rowid) row# from part_tab1 partition(p2) t;

                                 ID ROWID             DATA_OBJECT_ID#    RFILE#    BLOCK#    ROW#
--------------------------------------- ------------------ --------------- ---------- ---------- ----------
                                 10 AAAw8qABNAAAAyuAAA       200490       77    3246       0
                                 11 AAAw8qABNAAAAyuAAB       200490       77    3246       1
                                 12 AAAw8qABNAAAAyuAAC       200490       77    3246       2
                                 13 AAAw8qABNAAAAyuAAD       200490       77    3246       3
                                 14 AAAw8qABNAAAAyuAAE       200490       77    3246       4
                                 15 AAAw8qABNAAAAyuAAF       200490       77    3246       5
                                 16 AAAw8qABNAAAAyuAAG       200490       77    3246       6
                                 17 AAAw8qABNAAAAyuAAH       200490       77    3246       7
                                 18 AAAw8qABNAAAAyuAAI       200490       77       3246       8
                                 19 AAAw8qABNAAAAyuAAJ       200490       77       3246       9

SQL> update part_tab1 set id=12 where id=1;

update part_tab1 set id=12 where id=1

ORA-14402: 更新分割槽關鍵字列將導致分割槽的更改

SQL> alter table part_tab1 enable row movement;

Table altered

SQL> update part_tab1 set id=12 where id=1;

1 row updated

SQL> commit;

Commit complete

SQL>
SQL> select t.*,t.rowid,dbms_rowid.rowid_object(rowid) data_object_id#,
  2                dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3                dbms_rowid.rowid_block_number(rowid) block#,
  4                dbms_rowid.rowid_row_number(rowid) row# from part_tab1 partition(p1) t;

                                 ID ROWID             DATA_OBJECT_ID#    RFILE#    BLOCK#    ROW#
--------------------------------------- ------------------ --------------- ---------- ---------- ----------
                                    2 AAAw8pABNAAAAymAAB       200489       77    3238       1
                                    3 AAAw8pABNAAAAymAAC       200489       77    3238       2
                                    4 AAAw8pABNAAAAymAAD       200489       77    3238       3
                                    5 AAAw8pABNAAAAymAAE       200489       77    3238       4
                                    6 AAAw8pABNAAAAymAAF       200489       77    3238       5
                                    7 AAAw8pABNAAAAymAAG       200489       77    3238       6
                                    8 AAAw8pABNAAAAymAAH       200489       77    3238       7
                                    9 AAAw8pABNAAAAymAAI       200489       77    3238       8

8 rows selected

SQL>
SQL> select t.*,t.rowid,dbms_rowid.rowid_object(rowid) data_object_id#,
  2                dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3                dbms_rowid.rowid_block_number(rowid) block#,
  4                dbms_rowid.rowid_row_number(rowid) row# from part_tab1 partition(p2) t;

                                 ID ROWID             DATA_OBJECT_ID#    RFILE#    BLOCK#    ROW#
--------------------------------------- ------------------ --------------- ---------- ---------- ----------
                                 10 AAAw8qABNAAAAyuAAA       200490       77    3246       0
                                 11 AAAw8qABNAAAAyuAAB       200490       77    3246       1
                                 12 AAAw8qABNAAAAyuAAC       200490       77    3246       2
                                 13 AAAw8qABNAAAAyuAAD       200490       77    3246       3
                                 14 AAAw8qABNAAAAyuAAE       200490       77    3246       4
                                 15 AAAw8qABNAAAAyuAAF       200490       77    3246       5
                                 16 AAAw8qABNAAAAyuAAG       200490       77    3246       6
                                 17 AAAw8qABNAAAAyuAAH       200490       77    3246       7
                                 18 AAAw8qABNAAAAyuAAI       200490       77    3246       8
                                 19 AAAw8qABNAAAAyuAAJ       200490       77    3246       9
                                 12 AAAw8qABNAAAAyuAAK       200490       77    3246       10

11 rows selected

SQL>

----以下是普通表rowid不會改變的情況

SQL> drop table normal_tab1;

Table dropped

SQL>  create table normal_tab1(id int);

Table created

SQL> insert into normal_tab1 select rownum from dual connect by rownum<=10;

10 rows inserted

SQL> commit;

Commit complete

SQL>
SQL> select t.*,t.rowid,dbms_rowid.rowid_object(rowid) data_object_id#,
  2                   dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3                   dbms_rowid.rowid_block_number(rowid) block#,
  4                   dbms_rowid.rowid_row_number(rowid) row# from  normal_tab1 t;

                                 ID ROWID             DATA_OBJECT_ID#    RFILE#    BLOCK#    ROW#
--------------------------------------- ------------------ --------------- ---------- ---------- ----------
                                    1 AAAw8sABNAAAAy+AAA       200492       77    3262       0
                                    2 AAAw8sABNAAAAy+AAB       200492       77    3262       1
                                    3 AAAw8sABNAAAAy+AAC       200492       77    3262       2
                                    4 AAAw8sABNAAAAy+AAD       200492       77    3262       3
                                    5 AAAw8sABNAAAAy+AAE       200492       77    3262       4
                                    6 AAAw8sABNAAAAy+AAF       200492       77    3262       5
                                    7 AAAw8sABNAAAAy+AAG       200492       77    3262       6
                                    8 AAAw8sABNAAAAy+AAH       200492       77    3262       7
                                    9 AAAw8sABNAAAAy+AAI       200492       77       3262       8
                                 10 AAAw8sABNAAAAy+AAJ       200492       77    3262       9

10 rows selected

SQL> update normal_tab1 set id=999 where id=1;

1 row updated

SQL> commit;

Commit complete

SQL>
SQL> select t.*,t.rowid,dbms_rowid.rowid_object(rowid) data_object_id#,
  2                   dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3                   dbms_rowid.rowid_block_number(rowid) block#,
  4                   dbms_rowid.rowid_row_number(rowid) row# from  normal_tab1 t;

                                 ID ROWID             DATA_OBJECT_ID#    RFILE#    BLOCK#    ROW#
--------------------------------------- ------------------ --------------- ---------- ---------- ----------
                                 999 AAAw8sABNAAAAy+AAA       200492       77    3262       0
                                    2 AAAw8sABNAAAAy+AAB       200492       77    3262       1
                                    3 AAAw8sABNAAAAy+AAC       200492       77    3262       2
                                    4 AAAw8sABNAAAAy+AAD       200492       77    3262       3
                                    5 AAAw8sABNAAAAy+AAE       200492       77    3262       4
                                    6 AAAw8sABNAAAAy+AAF       200492       77    3262       5
                                    7 AAAw8sABNAAAAy+AAG       200492       77    3262       6
                                    8 AAAw8sABNAAAAy+AAH       200492       77    3262       7
                                    9 AAAw8sABNAAAAy+AAI       200492       77    3262       8
                                 10 AAAw8sABNAAAAy+AAJ       200492       77    3262       9

10 rows selected

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9932141/viewspace-665567/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 技術探討1

相關文章