• <fieldset id="8imwq"><menu id="8imwq"></menu></fieldset>
  • <bdo id="8imwq"><input id="8imwq"></input></bdo>
    最新文章專題視頻專題問答1問答10問答100問答1000問答2000關鍵字專題1關鍵字專題50關鍵字專題500關鍵字專題1500TAG最新視頻文章推薦1 推薦3 推薦5 推薦7 推薦9 推薦11 推薦13 推薦15 推薦17 推薦19 推薦21 推薦23 推薦25 推薦27 推薦29 推薦31 推薦33 推薦35 推薦37視頻文章20視頻文章30視頻文章40視頻文章50視頻文章60 視頻文章70視頻文章80視頻文章90視頻文章100視頻文章120視頻文章140 視頻2關鍵字專題關鍵字專題tag2tag3文章專題文章專題2文章索引1文章索引2文章索引3文章索引4文章索引5123456789101112131415文章專題3
    問答文章1 問答文章501 問答文章1001 問答文章1501 問答文章2001 問答文章2501 問答文章3001 問答文章3501 問答文章4001 問答文章4501 問答文章5001 問答文章5501 問答文章6001 問答文章6501 問答文章7001 問答文章7501 問答文章8001 問答文章8501 問答文章9001 問答文章9501
    當前位置: 首頁 - 科技 - 知識百科 - 正文

    高水位線引起的查詢變慢解決方法

    來源:懂視網 責編:小采 時間:2020-11-09 14:16:36
    文檔

    高水位線引起的查詢變慢解決方法

    高水位線引起的查詢變慢解決方法:眾所周知,隨著不斷地進行表記錄的DML操作,會不斷提高表的高水位線(HWM),DELETE操作之后雖然表的數據刪除了,但是并沒有降低表的高水位,除非你使用TRUNCATE操作,進行表查詢的時候,Oracle會掃表高水位以下的數據塊,也就是說,掃描的時間并不會有所減少
    推薦度:
    導讀高水位線引起的查詢變慢解決方法:眾所周知,隨著不斷地進行表記錄的DML操作,會不斷提高表的高水位線(HWM),DELETE操作之后雖然表的數據刪除了,但是并沒有降低表的高水位,除非你使用TRUNCATE操作,進行表查詢的時候,Oracle會掃表高水位以下的數據塊,也就是說,掃描的時間并不會有所減少

    眾所周知,隨著不斷地進行表記錄的DML操作,會不斷提高表的高水位線(HWM),DELETE操作之后雖然表的數據刪除了,但是并沒有降低表的高水位,除非你使用TRUNCATE操作,進行表查詢的時候,Oracle會掃表高水位以下的數據塊,也就是說,掃描的時間并不會有所減少。所以DELETE刪除數據以后并不會提高表的查詢效率。

    相關mysql視頻教程推薦:《mysql教程》

    下面通過這個例子,用來解決高水位引起的查詢變慢問題:

    --例子中測試表占用表空間大小為:128M
    SQL> SELECT a.bytes/1024/1024 || 'M' FROM user_segments a WHERE a.segment_name = 'TC_RES_PHY_EQP_PRO_MID_517';
    
    A.BYTES/1024/1024||'M'
    -----------------------------------------
    128M
    
    --查詢一條記錄成本為:4357,一致性讀為:15730 耗時 0.53 秒
    SQL> set autotrace on
    SQL> SELECT 1 FROM TC_RES_PHY_EQP_PRO_MID_517 a WHERE a.obj_id = 17202000000001;
    
     1
    ----------
     1
    
    
    執行計劃
    ----------------------------------------------------------
    Plan hash value: 854298875
    
    ------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 175 | 2275 | 4357 (2)| 00:00:53 |
    |* 1 | TABLE ACCESS FULL| TC_RES_PHY_EQP_PRO_MID_517 | 175 | 2275 | 4357 (2)| 00:00:53 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
     1 - filter("A"."OBJ_ID"=17202000000001)
    
    Note
    -----
     - dynamic sampling used for this statement (level=2)
    
    
    統計信息
    ----------------------------------------------------------
     0 recursive calls
     0 db block gets
     15730 consistent gets
     0 physical reads
     0 redo size
     520 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
     2 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
     1 rows processed
    
    --現在刪除大部分數據,只剩下一條測試數據:
    SQL> DELETE FROM TC_RES_PHY_EQP_PRO_MID_517 a WHERE a.obj_id <> 17202000000001;
    
    已刪除1172857行。
    
    --查詢該段占用的表空間仍然為128M
    SQL> set autotrace off
    SQL> SELECT a.bytes/1024/1024 || 'M' FROM user_segments a WHERE a.segment_name = 'TC_RES_PHY_EQP_PRO_MID_517';
    
    A.BYTES/1024/1024||'M'
    -----------------------------------------
    128M
    SQL> COMMIT;
    
    提交完成。
    
    SQL> SELECT a.bytes/1024/1024 || 'M' FROM user_segments a WHERE a.segment_name = 'TC_RES_PHY_EQP_PRO_MID_517';
    
    A.BYTES/1024/1024||'M'
    -----------------------------------------
    128M
    
    --查詢一條記錄消耗的成本為:4316,一致性讀為:15730 耗時 0.52 秒
    SQL> set autotrace on
    SQL> SELECT 1 FROM TC_RES_PHY_EQP_PRO_MID_517 a WHERE a.obj_id = 17202000000001;
    
     1
    ----------
     1
    
    
    執行計劃
    ----------------------------------------------------------
    Plan hash value: 854298875
    
    ------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 13 | 4316 (1)| 00:00:52 |
    |* 1 | TABLE ACCESS FULL| TC_RES_PHY_EQP_PRO_MID_517 | 1 | 13 | 4316 (1)| 00:00:52 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
     1 - filter("A"."OBJ_ID"=17202000000001)
    
    Note
    -----
     - dynamic sampling used for this statement (level=2)
    
    
    統計信息
    ----------------------------------------------------------
     0 recursive calls
     0 db block gets
     15730 consistent gets
     0 physical reads
     0 redo size
     520 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
     2 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
     1 rows processed
    
    --一般情況下,表的rowid是不會變的,我們通過ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;來打開行遷移
    SQL> ALTER TABLE TC_RES_PHY_EQP_PRO_MID_517 ENABLE ROW MOVEMENT;
    
    表已更改。
    
    --整理碎片并回收空間
    --此操作相比于ALTER TABLE MOVE:
    --1.不會消耗更多的表空間
    --2.可以在線執行,不會使索引失效
    --3.可以使用參數CASCADE,同時收縮表上的索引
    --4.ALTER TABLE MOVE之后表空間的位置肯定會發生變化,而SHRINK表空間的位置沒有發生變化
    SQL> ALTER TABLE TC_RES_PHY_EQP_PRO_MID_517 SHRINK SPACE;
    
    表已更改。
    --查詢一條記錄消耗的成本為:2,一致性讀為:4 耗時 0.01 秒
    SQL> SELECT 1 FROM TC_RES_PHY_EQP_PRO_MID_517 a WHERE a.obj_id = 17202000000001;
    
     1
    ----------
     1
    
    
    執行計劃
    ----------------------------------------------------------
    Plan hash value: 854298875
    
    ------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
    |* 1 | TABLE ACCESS FULL| TC_RES_PHY_EQP_PRO_MID_517 | 1 | 13 | 2 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
     1 - filter("A"."OBJ_ID"=17202000000001)
    
    Note
    -----
     - dynamic sampling used for this statement (level=2)
    
    
    統計信息
    ----------------------------------------------------------
     0 recursive calls
     0 db block gets
     4 consistent gets
     0 physical reads
     0 redo size
     520 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
     2 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
     1 rows processed
    --此時占用表空間只有4M
    SQL> SELECT a.bytes/1024/1024 || 'M' FROM user_segments a WHERE a.segment_name = 'TC_RES_PHY_EQP_PRO_MID_517';
    
    A.BYTES/1024/1024||'M'
    -----------------------------------------
    4M

    當然ENABLE ROW MOVEMENT對系統性能也有影響,在TOM的博客中找到這個關于ROW MOVEMENT的問答:

    You Asked
    Hi Tom 
    I have seen your posting on ENABLE ROW MOVEMENT which is available in 10g. It looks a 
    very nice option since we can relocate and reorganize the heap tables without any outage 
    since it does not invalidate indexes. But is there any performance hit or any other 
    disadvantages for using this. I would like to use this in our new application.
    Rgds
    Anil 
    and we said...
    Well, the tables have to be in an ASSM (Automatic Segment Space Managment) tablespace for 
    this to work (so if they are not, you have to move them there first in order to do this 
    over time).
    It will necessarily consume processing resources on your machine while running (it will 
    read the table, it will delete/insert the rows at the bottom of the table to move them 
    up, it will generate redo, it will generate undo).
    I would suggest benchmarking -- collect performance metrics about the table before and 
    after performing the operation. You would expect full scans to operate more efficiently 
    after, you would expect index range scans to either be unchanged or "better" as you have 
    more rows per block packed together (less data spread). You would be looking for that to 
    happen -- statspack or the tools available in dbconsole would be useful for measuring 
    that (the amount of work performed by your queries over time)

    也就是說,ENABLE ROW MOVEMENT也會有副作用,因為表打開行遷移之后,如果對數據進行UPDATE操作,那么系統會對數據進行DELETE操作

    之后再進行INSERT操作,導致產生更多的redo和undo,并且rowid也會發生變化。
    附行遷移和行連接的解釋:

    row chain:When a row is too large to fit into any block, row chaining occurs. In this case, the Oracle devide the row into smaller chunks. each chunk is stored in a block along
     with the necessary poiters to retrive and assemble the entire row.
    row migration:when a row is to be updated and it cannot find the necessary free space in its block, the Oracle will move the entire row into a new block and leave a pointer from the orginal block to the new location. This process is called row migration.

    聲明:本網頁內容旨在傳播知識,若有侵權等問題請及時與本網聯系,我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

    文檔

    高水位線引起的查詢變慢解決方法

    高水位線引起的查詢變慢解決方法:眾所周知,隨著不斷地進行表記錄的DML操作,會不斷提高表的高水位線(HWM),DELETE操作之后雖然表的數據刪除了,但是并沒有降低表的高水位,除非你使用TRUNCATE操作,進行表查詢的時候,Oracle會掃表高水位以下的數據塊,也就是說,掃描的時間并不會有所減少
    推薦度:
    標簽: 查詢 方法 處理
    • 熱門焦點

    最新推薦

    猜你喜歡

    熱門推薦

    專題
    Top
    主站蜘蛛池模板: 国产香蕉国产精品偷在线观看| 四虎精品影院4hutv四虎| 国产精品自在线拍国产| 久久无码精品一区二区三区| 国产精品网址你懂的| 亚洲一区二区三区国产精品| 91麻豆精品一二三区在线| 国产精品无码一区二区三级| 亚洲国产精品成人网址天堂| 国产精品自在在线午夜福利| 久久夜色精品国产亚洲| 国语自产少妇精品视频| 亚洲精品97久久中文字幕无码| 国产女人18毛片水真多18精品| 免费91麻豆精品国产自产在线观看 | 亚洲国产精品久久| 国产欧美精品AAAAAA片| 久久99精品久久久久婷婷| 亚洲AV乱码久久精品蜜桃| 精品久久久久久成人AV| 亚洲国产成人乱码精品女人久久久不卡 | 欧美日韩精品一区二区三区不卡| 白浆都出来了视频国产精品 | 欧美精品黑人粗大| 精品第一国产综合精品蜜芽| 国产91精品黄网在线观看| 亚洲国产精品久久久久网站| 久久99精品久久久久久| 国产精品视频网站你懂得| 国产精品久久久久影视不卡| 国产精品美女久久久| 久久99精品国产99久久| 精品国精品国产| 午夜精品成年片色多多| 一区二区三区精品| 亚洲精品国产成人专区| 四虎国产精品免费久久久| 欧美777精品久久久久网| 青青草国产精品久久久久| 日韩精品免费视频| 国产成人1024精品免费|