Dba_hist_ SQL _plan is an important view for querying the execution plan of oraclesql.
I would like to express my gratitude for the help of elders who have prepared this article.
The applicable oracle database version is oracle 10 Gb or later.
This view is important because one column in this view is not found in the awrsqrpt report. This column is the filter_predicates column.
SELECT plan_hash_value,
TO_CHAR (RAWTOHEX (child_address), TO_NUMBER (child_number), id, LPAD ('', DEPTH) | operation, options, object_owner, object_name, optimizer, cost, access_predicates, filter_predicates from v $ SQL _PLAN WHERE SQL _id = 'bkcyk7bf1_t6 'ORDER BY 1, 3, 2, 4; Focus on the optimizer column and the filter_predicates column. If this SQL statement is not in the shared pool, run the following SQL statement: set linesize 500 set pagesize 500 col plan_hash_value format 9999999999 col id format 999999 col operation format a30 col options format a15 col object_owner format a15 col object_name format a20 col optimizer format a15 col cost format 9999999999 col access_predicates format a15 col filter_predicates format a15
SELECT plan_hash_value, id, LPAD ('', DEPTH) | operation, options, object_owner, object_name, optimizer, cost, access_predicates, filter_predicates FROM dba_hist_ SQL _plan WHERE SQL _id = 'fahv8x6ngrb50 'ORDER BY plan_hash_value, id; Focus on the filter_predicates column. -- This column can help you determine why indexes are not taken away. If the query result of this column is null, it is normal. If it is not null, you need to pay attention to the reason for not being null, as shown in: