ORACLE 如何查看索引重建進度情況

来源:http://www.cnblogs.com/kerrycode/archive/2016/07/15/5673224.html

在ORACLE資料庫中,如果一個比較大的索引在重建過程中耗費時間比較長,那麼怎麼查看索引重建耗費的時間,以及完成了多少(比例)了呢,我們可以通過V$SESSION_LONGOPS視圖來查看索引重建的時間和進度。 官方文檔關於V$SESSION_LONGOPS的介紹如下 V$SESSION_LONGO... ...


   在ORACLE資料庫中,如果一個比較大的索引在重建過程中耗費時間比較長,那麼怎麼查看索引重建耗費的時間,以及完成了多少(比例)了呢,我們可以通過V$SESSION_LONGOPS視圖來查看索引重建的時間和進度。

 

官方文檔關於V$SESSION_LONGOPS的介紹如下

V$SESSION_LONGOPS

This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release

To monitor query execution progress, you must be using the cost-based optimizer and you must:

Set the TIMED_STATISTICS or SQL_TRACE parameter to true

Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

 

這個視圖顯示運行時間超過6秒的各類資料庫操作的狀態,這些操作包括備份、恢復功能,統計信息收集,以及查詢操作等。

 

要監控查詢執行進展情況,你必須是CBO優化器模式,並且滿足下麵條件:

  •    TIMED_STATISTICS或SQL_TRACE參數為true。
  •    使用DBMS_STATS包或ANLYZE語句收集、分析過對象的統計信息。

 

 

Column

DateType

Description

Description(中文)

SID

NUMBER

Session identifier

Session標識

SERIAL#

NUMBER

Session serial number

Session串號

OPNAME

VARCHAR2(64)

Brief description of the operation

操作簡要說明

TARGET

VARCHAR2(64)

The object on which the operation is carried out

操作的對象

TARGET_DESC

VARCHAR2(32)

Description of the target

目標對象說明

SOFAR

NUMBER

The units of work done so far

迄今為止完成的工作量

TOTALWORK

NUMBER

The total units of work

總工作量

UNITS

VARCHAR2(32)

The units of measurement

工作量單位

START_TIME

DATE

The starting time of operation

操作開始時間

LAST_UPDATE_TIME

DATE

Time when statistics last updated

統計項最後更新時間

TIMESTAMP

DATE

Timestamp

TIME_REMAINING

NUMBER

Estimate (in seconds) of time remaining for the operation to complete

預計完成操作的剩餘時間(秒)

ELAPSED_SECONDS

NUMBER

The number of elapsed seconds from the start of operations

從操作開始總花費時間(秒)

CONTEXT

NUMBER

Context

上下文關係

MESSAGE

VARCHAR2(512)

Statistics summary message

統計項的完整描述

USERNAME

VARCHAR2(30)

User ID of the user performing the operation

操作用戶

SQL_ADDRESS

RAW(4 | 8)

Used with the value of the SQL_HASH_VALUEcolumn to identify the SQL statement associated with the operation

SQL_HASH_VALUE

NUMBER

Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation

SQL_ID

VARCHAR2(13)

SQL identifier of the SQL statement associated with the operation

QCSID

NUMBER

Session identifier of the parallel coordinator

 

下麵我們來演示一下,首先構造了一個大表TEST_INDX,表TEST_INDX上建有一個索引IDX_TEST_INDX。我們開啟兩個會話視窗:

 

會話視窗1,執行下麵SQL語句:

SQL>  SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');
 
       SID    SERIAL# STATUS
---------- ---------- --------
       827        746 ACTIVE
 
SQL>  ALTER INDEX IDX_TEST_INDX REBUILD;
 
Index altered.
 
SQL> 

clip_image001[4]

 

在會話視窗2,執行下麵SQL

SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');
 
       SID    SERIAL# STATUS
---------- ---------- --------
       808       1003 ACTIVE
 
SQL> col opname format a32
SQL> col target format a32
SQL> col perwork format a12
SQL> set linesize 1200
SQL> select sid
  2        ,opname
  3        ,target
  4        ,sofar
  5        ,totalwork
  6        ,trunc(sofar/totalwork*100,2)||'%' as perwork
  7  from v$session_longops where sofar!=totalwork and sid=&sid;
Enter value for sid: 827
old   7: from v$session_longops where sofar!=totalwork and sid=&sid
new   7: from v$session_longops where sofar!=totalwork and sid=827
 
       SID OPNAME                        TARGET        SOFAR  TOTALWORK PERWORK
---------- --------------------- ------------------ ---------- ---------- --------
       827 Index Fast Full Scan      TEST.TEST_INDX    27914     157907 17.67%
 
SQL> /
Enter value for sid: 827
old   7: from v$session_longops where sofar!=totalwork and sid=&sid
new   7: from v$session_longops where sofar!=totalwork and sid=827
 
       SID OPNAME                        TARGET       SOFAR  TOTALWORK PERWORK
---------- -------------------- ------------------ ---------- ---------- -------
       827 Index Fast Full Scan   TEST.TEST_INDX      105075     157907 66.54%
 
SQL> /
Enter value for sid: 827
old   7: from v$session_longops where sofar!=totalwork and sid=&sid
new   7: from v$session_longops where sofar!=totalwork and sid=827
 
       SID OPNAME                   TARGET          SOFAR  TOTALWORK PERWORK
---------- ---------------  ------------------- ---------- ---------- --------
       827 Sort Output                              41728     175125 23.82%
 
SQL> 

clip_image002[4]

 

註意,這個SQL有時候需要一點時間才能看到結果,因為v$session_longpos中記錄的是執行時間超過6秒的操作,另外,你有時候會看到在Index Fast Full Scan之後,出現Sort Output操作。這個是索引重建時的排序操作,對這個Sort OutPut有點不清楚,在https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3114287916999 這個鏈接裡面,看到Tom大師的這麼一個回覆:

 

It is not showing you the end to end time of the index create, it is showing you the end to end time of the STEPS within the index create.

For example, I had a sort_area_size of 1m. I created a table t with 1,000,000 rows based on all_objects. On this table, I put an index on object_id. This took many TABLE SCAN followed by SORT/MERGE followed by SORT/OUTPUT steps. Each was timed independently.

Next, I dropped that index and set my sort_area_size to 300m (large enough to avoid a sort to disk). This time, the ONLY thing in v$session_longops was a TABLE SCAN, no sort/merge, no sort/output. Since we didn't swap to disk, these steps were avoided.

So, I'll guess -- your single key index was done in memory, your concatenated key was not.

 

也就是說,如果sort_area_size足夠大,就不會出現Sort Merge或Sort Output操作,因為在sort_area_size不夠大的時候,就會使用臨時表空間的臨時段來排序。由於沒有查到較權威的官方資料,猜測是在索引重建過程中,由於sort_area_size不夠大,所以要使用磁碟排序,即使用了臨時表空間來排序,所以出現了Sort Output操作,它表示記憶體排序輸出到磁碟進行排序(當然僅僅是個人猜測,如有不正確的地方,敬請指正),另外在metalink上也看到這樣一段介紹:

 

First, there are the temporary segments that are used to store partial sort

data when the SORT_AREA_SIZE is too small to process the complete sort set

These segments are built in the user's default TEMPORARY tablespace.

Second, as the index is being rebuilt, it uses a segment which is defined as

a temporary segment until the rebuild is complete. Once this segment is fully

populated, the old index can be dropped and this temporary segment is redefined

as a permanent segment with the index name.

 

 

下麵我們對索引重建做一個10046跟蹤

 
SQL> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
SQL> ALTER INDEX TEST.IDX_TEST_INDX REBUILD;
 
 
Index altered.
SQL> alter session set events '10046 trace name context off';
 
Session altered.

此時在trc文件裡面,你會看到大量的'direct path read temp'等待事件,表示重建索引時用到了臨時表空間做磁碟排序操作,由於索引非常大,所以產生了這個等待事件。

clip_image003[4]

clip_image004[4]

 

如果跟蹤一個非常小的索引重建,你在跟蹤文件裡面是看不到這個信息的。

 
SQL> alter session set events '10046 trace name context forever, level 12';
 
Session altered.
 
SQL> alter index scott.pk_emp rebuild;
 
Index altered.
 
SQL> alter session set events '10046 trace name context off';
 
Session altered.
 
SQL> 

clip_image005[4]

 

如果你根本不知道會話信息,如果我只知道是在那個表上重建索引,也可以根據表名來查詢,如下所示,我們還增加了開始時間等欄位

 

SQL> col opname format a32
SQL> col target format a32
SQL> col start_time format a24
SQL> col elapsed_seconds format 99
SQL> col perwork format a12
SQL> select sid

更多相關文章
  • 今天終於鼓起所有的勇氣,吹散了所有的懶惰去參加了一家小公司的面試,面試方向是PHP工程師,作為從二線城市過來的二線PHPer可謂是備受打擊。在以前憑藉著回答“GET和POST的區別”的面試方式以及不存在了,在整個面試過程中幾乎沒有涉及PHP的基礎知識,問的基本上都是高併發、緩存、大數據量等方面的知識 ...
  • user_tab_columns來源於user_tab_cols,user_tab_cols where hidden_column='NO',引自:http://blog.csdn.net/gumengkai/article/details/50823140 user_all_tables 是 u ...
  • 我執行下邊的sql語句 得到下麵結果 誰能告訴我那一步沒有去重嗎? ...
  • 工作中的問題總結: 問題一:scala 之向下轉型 引言:假如在複雜的業務邏輯中,變數的類型不能確認,只能給個介面類型,這樣數據類型推導不會錯誤,但是後面要使用實現類的類型時,你卻發現轉不過來了? 對於這樣的一個問題,scala可以這樣解決: 首先建造一個介面,People: 這樣定義了一個介面,接 ...
  • sql語言進階 典型操作 order by - select * from play_list order by createtime; - select * from play_list order by bookedcount desc,createtime asc; order by 語句用於 ...
一周排行
  • 上一章介紹了配置的多種數據源被註冊、載入和獲取的過程,本節看一下這個過程系統是如何實現的。(ASP.NET Core 系列目錄) 一、數據源的註冊 在上一節介紹的數據源設置中,appsettings.json、命令行、環境變數三種方式是被系統自動載入的,這是因為系統在webHost.CreateDe ...
  • Controller繼承ControllrBase,ControllerBase繼承IController,而IController里只有一個Execute方法 1、ControllrBase里的Execute(),裡面調用了ExecuteCore(),而ExecuteCore()是個抽象方法,抽象 ...
  • @[toc] 前言 中秋過完不知不覺都已經快兩周沒動這個工程了,最近業務需要總算開始搞後臺雲服務了,果斷直接net core搞起,在做的中間遇到了不少問題,這個後續會一點點列出來包括解決方法,今天就先把之前挖的坑填一個。 Redis 之前在緩存那篇提到過, Cookie , Session , Ca ...
  • 對try catch finally的理解1.finally 總是會運行的,即使在catch中thorw拋出異常了。2.finally 在 return後沒有結束,而是繼續運行finally2.順序的話就是對try-》finally 或者 try-》catch-》finally不管怎樣finally ...
  • 原文地址:https://blog.csdn.net/FL1623863129/article/details/89013137 VS2019於昨日正式發佈,博主立馬下載一個專業版嘗嘗鮮,但是發現項目打開都沒反應,而且VS2019都死在進程,怎麼也打不開,這不是安裝包而是沒有以管理員運行,只要在屬性 ...
  • 單例類public class SnappingClass : ISnappingEnvironment, IExtension { // private static readonly SnappingClass instance = null; static SnappingClass() { ... ...
  • 前提 入行已經7,8年了,一直想做一套漂亮點的自定義控制項,於是就有了本系列文章。 GitHub:https://github.com/kwwwvagaa/NetWinformControl 碼雲:https://gitee.com/kwwwvagaa/net_winform_custom_contr ...
  • 期待已久的.NET Core 3.0即將發佈! .NET Core 3.0在.NET Conf上發佈。大約還有9個多小時後,.NET Conf開始啟動。 為期3天的大概日程安排如下: 第1天-9月23日 9:00-10:00 Microsoft Studios播出的主題演講10:00-17:00從M ...
  • 場景 Winform中實現讀取xml配置文件並動態配置ZedGraph的RadioGroup的選項: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/100540708 在上面實現了將RadioGroup的選項根據配置文件動態配置後 ...
  • 原文地址: https://blog.csdn.net/chadcao/article/details/7859394 ASP.NET的安全認證,共有“Windows”、“Form”、“Passport”、“None”四種驗證模式。下麵來說說From認證,內容分三部分: 第一部分—— 怎樣實現Fro ...
x