Check access/filter predicate efficiency

      1 Comment on Check access/filter predicate efficiency

Hi there,

This some queries I am using to check access predicate efficiency.

First two small functions.

Access predicate:

CREATE OR REPLACE FUNCTION EXTRACT_ACCESS_PREDICATES (pIn  varchar2, pOwner varchar2, pIndex Varchar2) return varchar2 as
Type    T_string Is Table Of varchar2(40);
ltCols  T_string;
lResult varchar2 (500):=null;
begin
if pIn is not null then
  
  Select column_name  bulk collect into lTCols from dba_ind_columns where index_owner=pOwner and index_name = pIndex;
  
   if lTCols.FIRST is not null then
    For I In 1..lTCols.Last Loop
    if instr (pIn,lTCols(I)) <> 0 Then
      if lResult is null Then
       lResult := lTCols(I);
      else
       lResult := lResult || ';' || lTCols(I);
      End if;
     End if;
    End Loop;
  End if;
End if;
lResult := replace (lResult,'"','');
return lResult;
end;
/

And filter predicate

CREATE OR REPLACE FUNCTION EXTRACT_FILTER_PREDICATES (pIn  varchar2, pOwner varchar2, pTable Varchar2) return varchar2 as
Type    T_string Is Table Of varchar2(40);
ltCols  T_string;
lResult varchar2 (500):=null;
begin
if pIn is not null then
  
  Select column_name  bulk collect into lTCols from dba_Tab_columns where owner = pOwner and Table_name = pTable;
  
   if lTCols.FIRST is not null then
    For I In 1..lTCols.Last Loop
     if instr (pIn,lTCols(I)) <> 0 Then
      if lResult is null Then
       lResult := lTCols(I);
      else
       lResult := lResult || ';' || lTCols(I);
      End if;
     End if;
    End Loop;
  End if;
End if;
lResult := replace (lResult,'"','');
return lResult;
end;
/

Usage as follow:

Check index access predicates efficiency for a specific sql_id

Select predicats.OBJECT_OWNER, Indxs.table_name,Indxs.index_name,predicats.object_alias,predicats.object_type,
predicats.CARDINALITY,predicats.cost,predicats.Access_Pred,Indxs.colsInIndex, predicats.plan_hash_value,predicats.id
from
(
  select plan_hash_value,id,OBJECT_OWNER,object_name,object_alias,object_type,CARDINALITY,cost,Extract_Access_predicates (ACCESS_PREDICATES,OBJECT_OWNER,object_name) Access_Pred
  From v$sql_plan where sql_id='ft0tmmzf8k0qn'  and ACCESS_PREDICATES is not null and object_type like 'INDEX%'
) predicats join
(
select
      index_owner,
      table_name,
      index_name,
      listagg (column_name, ';') 
WITHIN GROUP
(ORDER BY COLUMN_POSITION) colsInIndex
FROM
   dba_ind_columns
GROUP BY
   index_owner,table_name,index_name
) Indxs
on (Indxs.index_name=predicats.object_name and Indxs.index_owner=predicats.OBJECT_OWNER)
order by plan_hash_value,id;

Result:

PMU	RACES	INDRACES_02	R@SEL$1	INDEX	611	4	RA_PLACEID	RA_PLACEID;RA_RACEID	2778439705	6
PMU	PARTICIPANTS	PARTICIPANTS_PK	P@SEL$1	INDEX (UNIQUE)	12	2	PA_RACEID	PA_RACEID;PA_POSITION	2778439705	7
PMU	PARTSTATS	PARTSTATS_PK	P@SEL$4	INDEX (UNIQUE)	87	5	PS_RACEID	PS_RACEID;PS_POSITION;PS_IDSTAT	2778439705	23
PMU	PARTSTATS	PARTSTATS_PK	P@SEL$7	INDEX (UNIQUE)	50	5	PS_RACEID	PS_RACEID;PS_POSITION;PS_IDSTAT	2778439705	33

Check index filter predicates. Index is used to filter the result as it cannot be use for access.

Select predicats.OBJECT_OWNER, Indxs.table_name,Indxs.index_name,predicats.object_alias,predicats.object_type,
predicats.CARDINALITY,predicats.cost,predicats.filter_pred,Indxs.colsInIndex, predicats.plan_hash_value,predicats.id
from
(
  select plan_hash_value,id,OBJECT_OWNER,object_name,object_alias,object_type,CARDINALITY,cost,Extract_Access_predicates (filter_predicates,OBJECT_OWNER,object_name) filter_pred
  From v$sql_plan where sql_id='ft0tmmzf8k0qn' and filter_predicates is not null and object_type like 'INDEX%'  
) predicats join
(
select
      index_owner,
      table_name,
      index_name,
      listagg (column_name, ';') 
WITHIN GROUP
(ORDER BY COLUMN_POSITION) colsInIndex
FROM
   dba_ind_columns
GROUP BY
   index_owner,table_name,index_name
) Indxs
on (Indxs.index_name=predicats.object_name and Indxs.index_owner=predicats.OBJECT_OWNER)
order by plan_hash_value,id;

Result:

PMU	PARTSTATS	PARTSTATS_PK	P@SEL$4	INDEX (UNIQUE)	87	5	PS_IDSTAT	PS_RACEID;PS_POSITION;PS_IDSTAT	2778439705	23
PMU	PARTSTATS	PARTSTATS_PK	P@SEL$7	INDEX (UNIQUE)	50	5	PS_IDSTAT	PS_RACEID;PS_POSITION;PS_IDSTAT	2778439705	33

Here we can see that the column PS_IDSTAT is used to filter while scaning the index PARTSTATS_PK.
Here we might be more efficient having and index on PS_RACEID,PS_IDSTAT.

Check table filter predicates. Potentially missing indexes.

Select OBJECT_OWNER, object_alias,object_type,CARDINALITY,cost,filter_pred, plan_hash_value,id
from
(
  select plan_hash_value,id,OBJECT_OWNER,object_name,object_alias,object_type,CARDINALITY,cost,Extract_Filter_predicates (filter_predicates,OBJECT_OWNER,object_name) filter_pred
  From v$sql_plan where sql_id='ft0tmmzf8k0qn' and filter_predicates is not null and object_type like 'TABLE%'
)  
order by plan_hash_value,id;

Result:

PMU	R@SEL$1	TABLE	316	157	RA_RACETYPE	2778439705	5
PMU	P@SEL$1	TABLE	2	10	PA_DEFER	2778439705	8

We might think about introducing thos two columns if it's the only columns that produce a "table access by rowid".

Comment are welcome
jko

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

1 thought on “Check access/filter predicate efficiency

  1. zavi

    Hi Jacques,

    have gone through access/filter predicate efficiency on your blog just want to know what is &amp,
    please share any sample input and output of the access predicate,index and table you posted.

    regards
    zavi

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *