[20180828]exadata–The God of protection of bean curd residue system.Txt
–//Yesterday saw the AWR report found as follows, 8-9 o’clock time report, this time ward business is very little, the main outpatient service business:
1.awrReport status:
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
–//exadataWhat does a machine have such a large direct path read? Does it not appear 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,Consistent with the statistics seen by IOStat by Function/Filetype summary.
–//Check IO statistics:
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 ReadsArrive at 1.5T.
–//You should check the AWR report with table EMR_BL03 to find the following SQL statement:
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.*, …
–//Just see one. Why? No similar statements were executed in another instance. I guess I didn’t use bound variables and omitted these similar SQL statements when generating an AWR report.
–//Reference link: http://blog.itpub.net/267265/viewspace-1749265/
–//In fact, development does not use binding variables. Why not use binding variables?.xxx.
–//It takes 49 seconds to burn.
2.Analysis:
–//First, look at the implementation plan:
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
–//I’ve probably tested exadata before, select /* + full (a) * / count (*) from big_table a; IO maximum throughput is about 2.5GB / s.
–//(102.436523+12.2724609)/2.5 = 45.88359356,This is very close.
–//You can find that the table has a blob field, and you can read it directly when you access the BLOB field when you read the data. No cell smart table scan wait event will occur.
–//I don’t know how the AWR report prompts you to read 1.5T on the direct path. I guess you missed a similar SQL statement as I mentioned earlier.
–//EMR_BL03There is an index IDX_EMR_BL03_BLBH. The fields include ZYMZ, BLBH, WDLX. I don’t know why index skip scan was not selected.
3.Look at the data sections designed by 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
–//5It takes 50 seconds to sample 10 seconds once. This is basically the same as the previous execution time. The main waiting event is 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
–//Look at the corresponding paragraphs.
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
–//It’s a little bit surprising. It’s all table segments, no lob segments. It’s a bit strange. There should be some lob segments. I don’t understand…
4.Problem solving:
–//The prompt is completed soon.
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”)
–//It’s strange why Oracle doesn’t choose INDEX SKIP SCAN. And it’s cheaper to take the cost of index skip scan.
–//I found that the ZYMZ field had only three values of 1,2,3. Could this be the reason for not having histogram information? Feel wrong, or reanalyze it:
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;
/
–//Not yet. First set the index IDX_EMR_BL03_BLBH attribute INVISIBLE to create the new index as follows:
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;
–//Test OK, execution plan is not posted.
–//It is difficult to control the behavior of 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’;
–//The problem is still not choosing jump index and giving up inquiry.
Summary:
–//In fact, what I’m trying to say is that exadata has a powerful IO processing capability that causes front-end users to not react to problems that arise.
–//If it’s something else, the whole IO is exhausted. It can’t run at all.
–//What surprised me most is that the execution takes 49 seconds. The front desk user has no response to this.
–//If the table has a lob type, exadata does not have a cell smart table scan wait event, but goes direct path read. I’ll have time to verify that.
–//Attach the test script to use:
$ 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’));