Hi, Connor
According to your detailed explanation, I've eventually understood
"ROLL_INVALID_MISMATCH" is the concept of Oracle optimizer statistics work.
SQL ID : <b>bg8pk89nc71x1</b>
SQL Text :
SELECT
COUNT(*)
FROM
(
SELECT
t.title,
t.subject_id,
t.cre_user_id,
t.view_count,
t.area_id,
t.case_id
FROM
sd_res_id_case PARTITION ( year_mark4 ) t
WHERE
1 = 1
AND
t.delete_flag = '0'
AND
t.forbidden_flag = '1'
AND
t.is_school_check = '2'
AND
t.from_type = 'fromType.1s1k'
AND
t.city_id =:1
AND
t.subject_id =:2
ORDER BY t.view_count DESC
) a
WHERE
ROWNUM < 501
;
Nevertheless if you check that
3 number of execution plans, you'll find that the
original execution plan has used
"INDEX RANGE SCAN" at line id
"6" and its index is
"ID1_ID_CASE_DF_ISC_SI_VI_TC" (including
5 columns) , this index's circumstance is as follows,
set linesize 200
set pagesize 200
col column_name for a20
select column_name, column_position, column_length from dba_ind_columns where index_name = 'ID1_ID_CASE_DF_ISC_SI_VI_TC';
COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH
-------------------- --------------- -------------
DELETE_FLAG 1 22
IS_SCHOOL_CHECK 2 22
SUBJECT_ID 3 40
VERSION_ID 4 40
TEXTBOOK_CODE 5 40
select column_name, num_distinct, last_analyzed from dba_tab_cols
where owner = 'SZD_RESOURCE_V2'
and table_name = 'SD_RES_ID_CASE'
and column_name in ('DELETE_FLAG', 'IS_SCHOOL_CHECK', 'SUBJECT_ID', 'VERSION_ID', 'TEXTBOOK_CODE')
order by 2 desc,1
;
COLUMN_NAME NUM_DISTINCT LAST_ANALYZED
-------------------- ------------ -------------------
TEXTBOOK_CODE 128736 2018-07-05 22:00:24
VERSION_ID 881 2018-07-05 22:00:24
SUBJECT_ID 88 2018-07-05 22:00:24
IS_SCHOOL_CHECK 4 2018-07-05 22:00:24
DELETE_FLAG 2 2018-07-05 22:00:24
Next the
second execution plan also adopts
"INDEX RANGE SCAN" at line id
"6" but it has used another index
"IDX1_RES_ID_CITY_ID" (only one column) this time and its situation is below,
set linesize 200
set pagesize 200
col column_name for a20
select column_name, column_position, column_length from dba_ind_columns where index_name = 'IDX1_RES_ID_CITY_ID';
COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH
-------------------- --------------- -------------
CITY_ID 1 40
select column_name, num_distinct, last_analyzed from dba_tab_cols where owner = 'SZD_RESOURCE_V2' and table_name = 'SD_RES_ID_CASE' and column_name = 'CITY_ID';
COLUMN_NAME NUM_DISTINCT LAST_ANALYZED
-------------------- ------------ -------------------
CITY_ID 411 2018-07-05 22:00:24
The
third execution plan is about
"TABLE ACCESS FULL" and its Cost(%CPU) is
"173K".
The
last execution plan comes from recommending method with
SQLT Report and Oracle doesn't use it currently. Its index is
"IDX$$_53800001" and you can also find some clues as follows,
Recommendation (estimated benefit: 99.71%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index
"SZD_RESOURCE_V2"."IDX1_RES_ID_CITY_ID" because it is a prefix of the
recommended index.
create index SZD_RESOURCE_V2.IDX$$_53800001 on
SZD_RESOURCE_V2.SD_RES_ID_CASE("CITY_ID","SUBJECT_ID","IS_SCHOOL_CHECK","DE
LETE_FLAG","FORBIDDEN_FLAG","FROM_TYPE");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
I think those two execution plan has been apparently different from each other (although they're all INDEX RANGE SCAN), maybe two different indexes have resulted in this root cause? Could I need to create that recommending index for improving performance (I see Cost(%CPU) will decrease to 11)?
Best Regards
Quanwen Zhao