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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 12306 技術難點大探討
- 熱更新技術探討，該如何選型
- oracle 雙機部署模式探討Oracle模式
- Android技術棧(三)依賴注入技術的探討與實現Android依賴注入
- Linux挖礦木馬的技術演進探討Linux
- [技術分析]探討大世界遊戲的製作流程及技術——前期流程篇遊戲
- 非技術探討：文章定時釋出功能如何實現
- ?【Java技術專區】「探針Agent專題」Java Agent探針的技術介紹（1）Java
- “技術沙龍”來襲，邀您一同探討 Serverless 資料庫技術最佳實踐Server資料庫
- 探討AIGC的崛起歷程，淺析其背後技術發展AIGC
- Promise探討Promise
- 探討大世界遊戲的製作流程及技術——大場景製作技術概況篇遊戲
- 從“微盟刪庫“事件探討銀行資料安全保護技術事件
- OPCUA 探討（一）
- 深入探討HBASE
- 深入探討 UndefinedUndefined
- 探討計算機視覺前沿技術，加速智慧城市落地應用 | CNCC 2019計算機視覺
- 由IDC餘熱回收創新技術實踐引出的跨界合作探討
- 記錄一次nodejs爬取《17吉他》所有吉他譜（只探討技術）NodeJS
- 深入雲電腦PC Farm技術探討，以阿里雲、華為雲、ToDesk為例阿里
- Oracle Freelist和HWM原理探討及相關效能優化(轉)Oracle優化
- Springboot 加密方案探討Spring Boot加密
- px em rem 探討REM
- 10、DNS隧道技術再探DNS
- 面向金融業的分散式交易型資料庫關鍵技術及發展探討分散式資料庫
- Oracle資料庫升級或資料遷移的方法探討Oracle資料庫
- JavaScript原型鏈汙染探討JavaScript原型
- 深入探討單例模式單例模式
- Netty URL路由方案探討Netty路由
- 聽技術探討一對一原始碼與視訊聊天系統分銷代理機制！原始碼
- Amazon Corretto技術細節探祕
- 1024程式設計師節/探討ORACLE環境故障的解決方法程式設計師Oracle
- 共同探討資料庫技術與行業數字化轉型過程的融合創新資料庫行業
- 深入探討ROP 載荷分析
- python建立elasticsearch索引的探討PythonElasticsearch索引
- SQL優化器探討(zt)SQL優化
- 探討.NET Core的未來
- 從 UI 設計角度探討如何製作 UI 音訊 - Part 1UI音訊
- Oracle實時同步技術Oracle