我有一个疑问，它是不久前写的.基本上是一个使用json_表函数的materialized 视图.
最近，自从我们搬到甲骨文19c，MV有时有效，有时无效.
有人能解释一下bytes, CPU, time等是什么意思吗.
这是使用json_值
EXPLAIN PLAN for
SELECT
JSON_VALUE(response, '$.ErrorRecord[0].xNumber') xNumber,
JSON_VALUE(response, '$.ErrorRecord[0]."error field"') ERROR_FIELD,
JSON_VALUE(response, '$.ErrorRecord[0]."value of field in error"') VALUE_OF_FIELD_IN_ERROR,
JSON_VALUE(response, '$.ErrorRecord[0]."error description"') ERROR_DESCRIPTION,
JSON_VALUE(request, '$.Status') STATUS,
sf.sv_code CENTER,
TO_CHAR(arr.created_date_time, 'YYYYMMDD' ) DATE_OCCURANCE
FROM
aud_request_response arr ,
person p,
rep_mapper_svc_fco sf,
rep_mapper_interface_error re
WHERE
JSON_VALUE(response, '$.ErrorRecord[0].xNumber') = p.registration_number (+)
AND arr.response.Status = 'Error'
AND arr.request.interfaceName = 'CLAIMS'
AND JSON_VALUE(request, '$.DataRecord[0].ACO') = sf.fco_code(+)
AND arr.request.interfaceName = re.interface_name
AND coalesce(sf.svc_code,'ATH')
IN ('XS','YS','XZ','ZS','ASD')
GROUP BY
sf.sv_code,
JSON_VALUE(request, '$.DataRecord[0].ACO'),
arr.request.interfaceName,
JSON_VALUE(request, '$.Status'),
JSON_VALUE(response, '$.ErrorRecord[0]."error field"'),
arr.created_date_time,
arr.updated_date_time,
JSON_VALUE(response, '$.ErrorRecord[0]."value of field in error"'),
JSON_VALUE(response, '$.ErrorRecord[0]."error description"'),
JSON_VALUE(response, '$.ErrorRecord[0].xNumber') ;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 241534218
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1094 | 871K| | 1877K (1)| 00:01:14 |
| 1 | HASH GROUP BY | | 1094 | 871K| 4688K| 1877K (1)| 00:01:14 |
| 2 | NESTED LOOPS OUTER | | 5259 | 4190K| | 1877K (1)| 00:01:14 |
|* 3 | FILTER | | | | | | |
|* 4 | HASH JOIN RIGHT OUTER| | 5259 | 4139K| | 1866K (1)| 00:01:13 |
| 5 | TABLE ACCESS FULL | REP_MAPPER_SVC_FCO | 85 | 680 | | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT SEMI| | 5259 | 4098K| | 1866K (1)| 00:01:13 |
|* 7 | TABLE ACCESS FULL | REP_MAPPER_INTERFACE_ERROR | 33 | 363 | | 5 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | AUD_REQUEST_RESPONSE | 5259 | 4041K| | 1866K (1)| 00:01:13 |
|* 9 | INDEX UNIQUE SCAN | PER_ANBR_IDX | 1 | 10 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
通过使用json_表
EXPLAIN PLAN for
SELECT
jtresponse.xNumber as xNumber,
jtresponse.error_field as ERROR_FIELD,
replace(jtresponse.value_of_field_in_error, ',interfaceName=INTERFACES','') as VALUE_OF_FIELD_IN_ERROR,
jtresponse.error_description as ERROR_DESCRIPTION,
trim(arr.response.Status) as STATUS,
sf.sv_code as CENTER,
TO_CHAR(arr.created_date_time, 'YYYYMMDD' ) as DATE_OCCURANCE
from
aud_request_response arr,
person p,
rep_mapper_svc_fco sf,
rep_mapper_interface_error re,
json_table(response, '$'
COLUMNS (
nested path '$.ErrorRecord[*]' columns (
aNumber path '$.xNumber' null on error,
error_field path '$."error field"' null on error,
value_of_field_in_error path '$."value of field in error"' null on error,
error_description path '$."error description"' null on error
))) jtresponse
,json_table(request, '$'
COLUMNS (
nested path '$.DataRecord[*]' columns (
fileControl path '$.ACO' null on error
))) jtrequest
where jtrequest.fileControl =sf.fco_code(+)
and arr.request.interfaceName = 'CLAIMS'
and arr.request.interfaceName = re.interface_name
and jtresponse.xNumber = p.registration_number (+)
and arr.response.Status='Error'
and coalesce(sf.sv_code,'ATH') in('XS','YS','XZ','ZS','ASD')
GROUP BY
sv_code,
jtrequest.fileControl,
arr.request.interfaceName,
arr.response.Status,
jtresponse.error_field,
arr.created_date_time,
arr.updated_date_time,
jtresponse.value_of_field_in_error,
jtresponse.error_description,
jtresponse.xNumber;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 834586449
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 350G| 260T| | 1908M (2)| 20:42:27 |
| 1 | HASH GROUP BY | | 350G| 260T| 290T| 1908M (2)| 20:42:27 |
| 2 | NESTED LOOPS | | 350G| 260T| | 1168M (1)| 12:40:35 |
|* 3 | FILTER | | | | | | |
|* 4 | HASH JOIN RIGHT OUTER | | 42M| 32G| | 2009K (1)| 00:01:19 |
| 5 | TABLE ACCESS FULL | REP_MAPPER_SVC_FCO | 85 | 680 | | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 42M| 32G| | 2009K (1)| 00:01:19 |
|* 7 | HASH JOIN RIGHT SEMI | | 5259 | 4098K| | 1866K (1)| 00:01:13 |
|* 8 | TABLE ACCESS FULL | REP_MAPPER_INTERFACE_ERROR | 33 | 363 | | 5 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | AUD_REQUEST_RESPONSE | 5259 | 4041K| | 1866K (1)| 00:01:13 |
| 10 | JSONTABLE EVALUATION | | | | | | |
| 11 | JSONTABLE EVALUATION | | | | | | |
--------------------------------------------------------------------------------------------------------------------
非常感谢.