[20180828]exadata--豆腐渣系統的保護神.txt
[20180828]exadata--豆腐渣系統的保護神.txt
--//昨天看awr報表發現如下,時間8-9點報表,這個時間病房業務很少,主要門診的業務:
1.awr報表情況:
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 10.7K 77.9
direct path read 1,206,717 1745.2 1 12.8 User I/O
cell single block physical read 371,283 901.5 2 6.6 User I/O
log file sync 382,715 626.8 2 4.6 Commit
--//exadata機器什麼會存在這麼大direct path read呢?難道不出現cell smart table scan.
--//1745.2*1000/1206717 = 1.44623801603855750768ms
Segments by Direct Physical Reads
Total Direct Physical Reads: 210,215,077
Captured Segments account for 100.0% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Direct Reads %Total
XXXXXX_YYY XXXXXX_YYY EMR_BL03 TABLE 201,275,448 95.75
XXXXXX_ZZZ XXXXXX_ZZZ MS_CF01 TABLE 7,072,230 3.36
--//201275448*8192/1024/1024/1024/1024 = 1.49961894750595092773,與IOStat by Function/Filetype summary看到的統計一致.
--//檢視IO統計:
IOStat by Function/Filetype summary
'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
Ordered by (Data Read + Write) desc for each function
Function/File Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Tm(ms)
Direct Reads 1.5T 436.59 434.062 0M 0.00 0M 0
Direct Reads (Data File) 1.5T 436.59 434.062 0M 0.00 0M 0
Smart Scan 68.5G 20.03 19.37M 0M 0.00 0M 0
Smart Scan (Data File) 68.5G 20.03 19.37M 0M 0.00 0M 0
Buffer Cache Reads 5.3G 125.81 1.491M 0M 0.00 0M 382.1K 2.19
Buffer Cache Reads (Data File)5.3G 125.81 1.491M 0M 0.00 0M 382.1K 2.19
--//Direct Reads到達1.5T.
--//應該與表EMR_BL03,查詢awr報表,找到如下sql語句:
sql_id='crzs1c9pnjqg2'
SELECT XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
SQL ordered by User I/O Wait Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
%Total - User I/O Time as a percentage of Total User I/O Wait time
%CPU - CPU Time as a percentage of Elapsed Time
%IO - User I/O Time as a percentage of Elapsed Time
Captured SQL account for 9.4% of Total User I/O Wait Time (s): 2,775
Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): 2,775
User I/O Time (s) Executions UIO per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
...
49.51 1 49.51 1.78 54.27 9.63 91.22 crzs1c9pnjqg2 SELECT XXXXXX_YYY.EMR_BL03.*, ...
--//僅僅看到1條.為什麼?另外的例項也沒有執行類似的語句.我估計沒有使用繫結變數,在生成awr報表時漏掉這些類似的sql語句.
--//參考連結:http://blog.itpub.net/267265/viewspace-1749265/
--//實際上開發沒有使用繫結變數,為什麼不使用繫結變數....xxx.
--//消耗時間49秒.
2.分析:
--//首先看看執行計劃:
zzzzz> @ &r/dpcawr crzs1c9pnjqg2 ''
PLAN_TABLE_OUTPUT
--------------------
SQL_ID crzs1c9pnjqg2
--------------------
SELECT XXXXXX_YYY.EMR_BL03.*,XXXXXX_YYY.EMR_BL_BL01.BLMC FROM
XXXXXX_YYY.EMR_BL03 LEFT JOIN XXXXXX_YYY.EMR_BL_BL01 ON
XXXXXX_YYY.EMR_BL03.BLBH=XXXXXX_YYY.EMR_BL_BL01.BLBH WHERE
XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441'
Plan hash value: 40434530
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 215K(100)| |
| 1 | HASH JOIN | | 19 | 27645 | 215K (1)| 00:43:02 |
| 2 | JOIN FILTER CREATE | :BF0000 | 19 | 817 | 16 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 19 | 817 | 16 (0)| 00:00:01 |
| 4 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 19 | | 3 (0)| 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 3968K| 5343M| 215K (1)| 00:43:01 |
| 6 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 3968K| 5343M| 215K (1)| 00:43:01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C8875FE2
3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
6 - SEL$C8875FE2 / EMR_BL03@SEL$2
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
35 rows selected.
zzzzz> @ &r/desc XXXXXX_YYY.EMR_BL03
Name Null? Type
----- -------- ----------------------------
WDBH NOT NULL NUMBER(18)
ZYMZ NOT NULL NUMBER(2)
BLBH NOT NULL NUMBER(18)
WDLX NOT NULL NUMBER(4)
WDNR BLOB
zzzzz> select segment_name,bytes/1024/1024/1024 Gb from DBA_SEGMENTS where segment_name='EMR_BL03';
SEGMENT_NAME GB
-------------------- ----------
EMR_BL03 12.2724609
zzzzz> select segment_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in
(select segment_name from DBA_LOBS where table_name='EMR_BL03');
SEGMENT_NAME GB
------------------------------ ----------
SYS_LOB0000087717C00005$$ 102.436523
--//我以前大概測試過我們現在使用的exadata,select /*+ full(a) */ count(*) from big_table a;IO最大吞吐量大約2.5GB/s.
--//(102.436523+12.2724609)/2.5 = 45.88359356,這樣非常接近.
--//可以發現這個表存在blob欄位,讀取資料時訪問blob欄位時選擇直接路徑讀.而不會出現cell smart table scan等待事件.
--//真不知道awr報表提示直接路徑讀1.5T如何得來的,估計像我前面提到那樣漏掉類似的sql語句.
--//EMR_BL03存在索引IDX_EMR_BL03_BLBH.欄位包括ZYMZ, BLBH, WDLX.不知道為什麼沒有選擇index skip scan.
3.看看direct path read設計那些資料段:
zzzzz> select event,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY where sql_id='crzs1c9pnjqg2' group by event;
EVENT COUNT(*)
---------------------------------------- ----------
direct path read 5
--//5次,取樣10秒1次,需要50秒.這樣與前面的執行時間基本對上.主要等待事件就是direct path read.
zzzzz> @ &r/ev_name.sql 'direct path read'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
198 3926164927 direct path read file number first dba block cnt 1740759767 8 User I/O
199 861319509 direct path read temp file number first dba block cnt 1740759767 8 User I/O
zzzzz> select event,p1,p2,p3,p1text,p2text,p3text from DBA_HIST_ACTIVE_SESS_HISTORY where sql_id='crzs1c9pnjqg2' ;
EVENT P1 P2 P3 P1TEXT P2TEXT P3TEXT
---------------- --- -------- ---- ----------- --------- ---------
direct path read 56 2562432 128 file number first dba block cnt
direct path read 59 3122176 128 file number first dba block cnt
direct path read 59 3729536 128 file number first dba block cnt
direct path read 60 287744 128 file number first dba block cnt
direct path read 63 43392 128 file number first dba block cnt
--//看看對應那些段.
zzzzz> column PARTITION_NAME noprint
zzzzz> @ &r/which_obj 56 2562432
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
XXXXXX_YYY EMR_BL03 TABLE XXXXXX_YYY 151 56 2556416 67108864 8192 56
zzzzz> @ &r/which_obj 59 3122176
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
XXXXXX_YYY EMR_BL03 TABLE XXXXXX_YYY 162 59 3121664 67108864 8192 59
--//有點小小吃驚,抓取對應的都是表段,沒有lob段.有點奇怪.應該有一些lob段.不理解.....
4.問題解決:
--//加入提示很快執行完成:
SELECT /*+ INDEX_SS(EMR_BL03 ) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
SQL_ID 175cc74hfsn7k, child number 0
-------------------------------------
SELECT /*+ INDEX_SS(EMR_BL03 ) */ XXXXXX_YYY.EMR_BL03.*,
XXXXXX_YYY.EMR_BL_BL01.BLMC FROM XXXXXX_YYY.EMR_BL03 LEFT JOIN
XXXXXX_YYY.EMR_BL_BL01 ON XXXXXX_YYY.EMR_BL03.BLBH =
XXXXXX_YYY.EMR_BL_BL01.BLBH WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH =
'00366441'
Plan hash value: 2710181240
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 130 (100)| |
| 1 | NESTED LOOPS | | 19 | 27645 | 130 (0)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 19 | 817 | 16 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 19 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMR_BL03 | 1 | 1412 | 6 (0)| 00:00:01 |
|* 5 | INDEX SKIP SCAN | IDX_EMR_BL03_BLBH | 1 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMR_BL_BL01"."BRBH"='00366441')
5 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")
filter("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")
--//感覺很奇怪為什麼oracle不選擇INDEX SKIP SCAN.而且走index skip scan 的cost的成本更低.
--//我發現ZYMZ欄位僅僅存在3個值1,2,3.沒有直方圖資訊,會是這個原因嗎?感覺不對,還是重新分析看看:
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'XXXXXX_YYY'
,TabName => 'EMR_BL03'
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => 'FOR ALL COLUMNS SIZE REPEAT for colunms ZYMZ size 254'
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/
--//依舊不行.先設定索引IDX_EMR_BL03_BLBH屬性INVISIBLE,建立新索引如下:
CREATE INDEX XXXXXX_YYY.I_EMR_BL03_BLBH ON XXXXXX_YYY.EMR_BL03
(BLBH)
LOGGING
TABLESPACE XXXXXX_YYY
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
--//測試ok,執行計劃不在貼出.
--//感覺INDEX SKIP SCAN的行為難以控制.
--//ALTER INDEX XXXXXX_YYY.I_EMR_BL03_BLBH INVISIBLE;
--//ALTER INDEX XXXXXX_YYY.XXXXXX_YYY.I_EMR_BL03_BLBH VISIBLE;
SELECT /*+ cardinality(EMR_BL_BL01 1) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
--//問題依舊不會選擇跳躍索引,放棄探究...
總結:
--//實際上我想說的是正是exadata強有力的IO處理能力,導致前臺使用者對於出現的問題根本沒有反映.
--//如果是別的系統,整個IO消耗殆盡.根本跑不起來....
--//最讓我吃驚的是執行需要49秒,前臺使用者對此卻毫無反應,很無語....
--//如果表存在lob型別,exadata不會出現cell smart table scan等待事件,而是走direct path read.關於這點有時間我再驗證看看.
--//附上使用的測試指令碼:
$ cat which_obj.sql
define __FILE = &1
define __BLOCK = &2
set verify off
select * --owner,segment_name
from dba_extents
where file_id = &__FILE
and &__BLOCK between block_id and block_id + blocks - 1
-- and rownum = 1
;
$ cat ev_name.sql
column name format a40
select * from v$event_name where lower(name) like lower('%&&1%');
# cat dpcawr.sql
set verify off
select * from table(dbms_xplan.display_awr(NVL('&1',NULL),NULL,NULL,'ALL ALLSTATS LAST PEEKED_BINDS &2 cost partition -projection -outline'));
--//昨天看awr報表發現如下,時間8-9點報表,這個時間病房業務很少,主要門診的業務:
1.awr報表情況:
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 10.7K 77.9
direct path read 1,206,717 1745.2 1 12.8 User I/O
cell single block physical read 371,283 901.5 2 6.6 User I/O
log file sync 382,715 626.8 2 4.6 Commit
--//exadata機器什麼會存在這麼大direct path read呢?難道不出現cell smart table scan.
--//1745.2*1000/1206717 = 1.44623801603855750768ms
Segments by Direct Physical Reads
Total Direct Physical Reads: 210,215,077
Captured Segments account for 100.0% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Direct Reads %Total
XXXXXX_YYY XXXXXX_YYY EMR_BL03 TABLE 201,275,448 95.75
XXXXXX_ZZZ XXXXXX_ZZZ MS_CF01 TABLE 7,072,230 3.36
--//201275448*8192/1024/1024/1024/1024 = 1.49961894750595092773,與IOStat by Function/Filetype summary看到的統計一致.
--//檢視IO統計:
IOStat by Function/Filetype summary
'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
Ordered by (Data Read + Write) desc for each function
Function/File Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Tm(ms)
Direct Reads 1.5T 436.59 434.062 0M 0.00 0M 0
Direct Reads (Data File) 1.5T 436.59 434.062 0M 0.00 0M 0
Smart Scan 68.5G 20.03 19.37M 0M 0.00 0M 0
Smart Scan (Data File) 68.5G 20.03 19.37M 0M 0.00 0M 0
Buffer Cache Reads 5.3G 125.81 1.491M 0M 0.00 0M 382.1K 2.19
Buffer Cache Reads (Data File)5.3G 125.81 1.491M 0M 0.00 0M 382.1K 2.19
--//Direct Reads到達1.5T.
--//應該與表EMR_BL03,查詢awr報表,找到如下sql語句:
sql_id='crzs1c9pnjqg2'
SELECT XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
SQL ordered by User I/O Wait Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
%Total - User I/O Time as a percentage of Total User I/O Wait time
%CPU - CPU Time as a percentage of Elapsed Time
%IO - User I/O Time as a percentage of Elapsed Time
Captured SQL account for 9.4% of Total User I/O Wait Time (s): 2,775
Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): 2,775
User I/O Time (s) Executions UIO per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
...
49.51 1 49.51 1.78 54.27 9.63 91.22 crzs1c9pnjqg2 SELECT XXXXXX_YYY.EMR_BL03.*, ...
--//僅僅看到1條.為什麼?另外的例項也沒有執行類似的語句.我估計沒有使用繫結變數,在生成awr報表時漏掉這些類似的sql語句.
--//參考連結:http://blog.itpub.net/267265/viewspace-1749265/
--//實際上開發沒有使用繫結變數,為什麼不使用繫結變數....xxx.
--//消耗時間49秒.
2.分析:
--//首先看看執行計劃:
zzzzz> @ &r/dpcawr crzs1c9pnjqg2 ''
PLAN_TABLE_OUTPUT
--------------------
SQL_ID crzs1c9pnjqg2
--------------------
SELECT XXXXXX_YYY.EMR_BL03.*,XXXXXX_YYY.EMR_BL_BL01.BLMC FROM
XXXXXX_YYY.EMR_BL03 LEFT JOIN XXXXXX_YYY.EMR_BL_BL01 ON
XXXXXX_YYY.EMR_BL03.BLBH=XXXXXX_YYY.EMR_BL_BL01.BLBH WHERE
XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441'
Plan hash value: 40434530
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 215K(100)| |
| 1 | HASH JOIN | | 19 | 27645 | 215K (1)| 00:43:02 |
| 2 | JOIN FILTER CREATE | :BF0000 | 19 | 817 | 16 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 19 | 817 | 16 (0)| 00:00:01 |
| 4 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 19 | | 3 (0)| 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 3968K| 5343M| 215K (1)| 00:43:01 |
| 6 | TABLE ACCESS STORAGE FULL | EMR_BL03 | 3968K| 5343M| 215K (1)| 00:43:01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C8875FE2
3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
6 - SEL$C8875FE2 / EMR_BL03@SEL$2
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
35 rows selected.
zzzzz> @ &r/desc XXXXXX_YYY.EMR_BL03
Name Null? Type
----- -------- ----------------------------
WDBH NOT NULL NUMBER(18)
ZYMZ NOT NULL NUMBER(2)
BLBH NOT NULL NUMBER(18)
WDLX NOT NULL NUMBER(4)
WDNR BLOB
zzzzz> select segment_name,bytes/1024/1024/1024 Gb from DBA_SEGMENTS where segment_name='EMR_BL03';
SEGMENT_NAME GB
-------------------- ----------
EMR_BL03 12.2724609
zzzzz> select segment_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in
(select segment_name from DBA_LOBS where table_name='EMR_BL03');
SEGMENT_NAME GB
------------------------------ ----------
SYS_LOB0000087717C00005$$ 102.436523
--//我以前大概測試過我們現在使用的exadata,select /*+ full(a) */ count(*) from big_table a;IO最大吞吐量大約2.5GB/s.
--//(102.436523+12.2724609)/2.5 = 45.88359356,這樣非常接近.
--//可以發現這個表存在blob欄位,讀取資料時訪問blob欄位時選擇直接路徑讀.而不會出現cell smart table scan等待事件.
--//真不知道awr報表提示直接路徑讀1.5T如何得來的,估計像我前面提到那樣漏掉類似的sql語句.
--//EMR_BL03存在索引IDX_EMR_BL03_BLBH.欄位包括ZYMZ, BLBH, WDLX.不知道為什麼沒有選擇index skip scan.
3.看看direct path read設計那些資料段:
zzzzz> select event,count(*) from DBA_HIST_ACTIVE_SESS_HISTORY where sql_id='crzs1c9pnjqg2' group by event;
EVENT COUNT(*)
---------------------------------------- ----------
direct path read 5
--//5次,取樣10秒1次,需要50秒.這樣與前面的執行時間基本對上.主要等待事件就是direct path read.
zzzzz> @ &r/ev_name.sql 'direct path read'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
198 3926164927 direct path read file number first dba block cnt 1740759767 8 User I/O
199 861319509 direct path read temp file number first dba block cnt 1740759767 8 User I/O
zzzzz> select event,p1,p2,p3,p1text,p2text,p3text from DBA_HIST_ACTIVE_SESS_HISTORY where sql_id='crzs1c9pnjqg2' ;
EVENT P1 P2 P3 P1TEXT P2TEXT P3TEXT
---------------- --- -------- ---- ----------- --------- ---------
direct path read 56 2562432 128 file number first dba block cnt
direct path read 59 3122176 128 file number first dba block cnt
direct path read 59 3729536 128 file number first dba block cnt
direct path read 60 287744 128 file number first dba block cnt
direct path read 63 43392 128 file number first dba block cnt
--//看看對應那些段.
zzzzz> column PARTITION_NAME noprint
zzzzz> @ &r/which_obj 56 2562432
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
XXXXXX_YYY EMR_BL03 TABLE XXXXXX_YYY 151 56 2556416 67108864 8192 56
zzzzz> @ &r/which_obj 59 3122176
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
XXXXXX_YYY EMR_BL03 TABLE XXXXXX_YYY 162 59 3121664 67108864 8192 59
--//有點小小吃驚,抓取對應的都是表段,沒有lob段.有點奇怪.應該有一些lob段.不理解.....
4.問題解決:
--//加入提示很快執行完成:
SELECT /*+ INDEX_SS(EMR_BL03 ) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
SQL_ID 175cc74hfsn7k, child number 0
-------------------------------------
SELECT /*+ INDEX_SS(EMR_BL03 ) */ XXXXXX_YYY.EMR_BL03.*,
XXXXXX_YYY.EMR_BL_BL01.BLMC FROM XXXXXX_YYY.EMR_BL03 LEFT JOIN
XXXXXX_YYY.EMR_BL_BL01 ON XXXXXX_YYY.EMR_BL03.BLBH =
XXXXXX_YYY.EMR_BL_BL01.BLBH WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH =
'00366441'
Plan hash value: 2710181240
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 130 (100)| |
| 1 | NESTED LOOPS | | 19 | 27645 | 130 (0)| 00:00:02 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01 | 19 | 817 | 16 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_EMR_BL_BL01_BRBH_CJSJ | 19 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMR_BL03 | 1 | 1412 | 6 (0)| 00:00:01 |
|* 5 | INDEX SKIP SCAN | IDX_EMR_BL03_BLBH | 1 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMR_BL_BL01"."BRBH"='00366441')
5 - access("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")
filter("EMR_BL03"."BLBH"="EMR_BL_BL01"."BLBH")
--//感覺很奇怪為什麼oracle不選擇INDEX SKIP SCAN.而且走index skip scan 的cost的成本更低.
--//我發現ZYMZ欄位僅僅存在3個值1,2,3.沒有直方圖資訊,會是這個原因嗎?感覺不對,還是重新分析看看:
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'XXXXXX_YYY'
,TabName => 'EMR_BL03'
,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
,Method_Opt => 'FOR ALL COLUMNS SIZE REPEAT for colunms ZYMZ size 254'
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/
--//依舊不行.先設定索引IDX_EMR_BL03_BLBH屬性INVISIBLE,建立新索引如下:
CREATE INDEX XXXXXX_YYY.I_EMR_BL03_BLBH ON XXXXXX_YYY.EMR_BL03
(BLBH)
LOGGING
TABLESPACE XXXXXX_YYY
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
--//測試ok,執行計劃不在貼出.
--//感覺INDEX SKIP SCAN的行為難以控制.
--//ALTER INDEX XXXXXX_YYY.I_EMR_BL03_BLBH INVISIBLE;
--//ALTER INDEX XXXXXX_YYY.XXXXXX_YYY.I_EMR_BL03_BLBH VISIBLE;
SELECT /*+ cardinality(EMR_BL_BL01 1) */ XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
FROM XXXXXX_YYY.EMR_BL03
LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
ON XXXXXX_YYY.EMR_BL03.BLBH = XXXXXX_YYY.EMR_BL_BL01.BLBH
WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';
--//問題依舊不會選擇跳躍索引,放棄探究...
總結:
--//實際上我想說的是正是exadata強有力的IO處理能力,導致前臺使用者對於出現的問題根本沒有反映.
--//如果是別的系統,整個IO消耗殆盡.根本跑不起來....
--//最讓我吃驚的是執行需要49秒,前臺使用者對此卻毫無反應,很無語....
--//如果表存在lob型別,exadata不會出現cell smart table scan等待事件,而是走direct path read.關於這點有時間我再驗證看看.
--//附上使用的測試指令碼:
$ cat which_obj.sql
define __FILE = &1
define __BLOCK = &2
set verify off
select * --owner,segment_name
from dba_extents
where file_id = &__FILE
and &__BLOCK between block_id and block_id + blocks - 1
-- and rownum = 1
;
$ cat ev_name.sql
column name format a40
select * from v$event_name where lower(name) like lower('%&&1%');
# cat dpcawr.sql
set verify off
select * from table(dbms_xplan.display_awr(NVL('&1',NULL),NULL,NULL,'ALL ALLSTATS LAST PEEKED_BINDS &2 cost partition -projection -outline'));
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2213256/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180810]exadata--豆腐渣系統的保護神.txt
- windows10系統開啟系統保護的方法Windows
- 保護企業系統中的程式碼安全
- 19-作業系統安全保護作業系統
- 保護你的 Fedora 系統免受這個 DHCP 漏洞
- Win10系統如何設定護眼色 Win10系統設定保護色的方法Win10
- HMAC演算法：資料傳輸的保護神Mac演算法
- [20180828]關於引數cursor_space_for_time(10g).txt
- win10如何禁用增強保護_win10怎麼禁用系統保護Win10
- 高併發架構下的系統限流保護策略架構
- Fortinet運用前沿IT思維，保護OT系統
- windows10系統保護的關閉方法【圖文教程】Windows
- 視訊通訊保障系統為‘救護車安全保駕護航’
- 小心設定啟動項保護Windows系統安全Windows
- 交易所開發系統最主要的要能保護交易安全
- 預言機智慧計算系統全程保駕護航
- win10系統如何關閉密碼保護共享Win10密碼
- 形似神非： 網路遊戲類電作品保護的前世今生遊戲
- windows10系統關閉dep資料執行保護的方法Windows
- Win10系統怎樣關閉Windows檔案保護 win10關閉windows檔案保護的步驟Win10Windows
- Win10系統如何啟用OneDrive資料夾保護Win10
- ASLR 是如何保護 Linux 系統免受緩衝區溢位攻擊的Linux
- Zoho CRM系統保護使用者隱私和資料安全
- Win10系統禁用或啟用Defender的 PUA (PUP) 保護的圖文教程Win10
- 保護期限
- 保護模式模式
- lua保護的前世今生
- win10系統如何開啟windows defender實時保護【圖文】Win10Windows
- 如何檢查Mac上是否啟用了SIP系統完整性保護Mac
- 聖天諾LDK加密鎖（加密狗）如何保護Linux系統下的軟體加密Linux
- 運維守護神——數十萬線上機器的守護【門神】運維
- 保護創新、促進仿製：對中國建立藥品專利保護體系的政策建議
- 【等級保護】等級保護共分為幾級？保護物件是指什麼？物件
- 【科普】等級保護與分級保護的區別和聯絡!
- win10怎麼啟用DEP資料執行保護_win10系統資料執行保護怎麼開啟Win10
- [自制作業系統] 第07回 認識保護模式之地址對映作業系統模式
- w10命令提示符啟用系統保護操作方法
- Mac清理保護電腦必備軟體：系統最佳化工具 MacBoosterMac