Check access/filter predicate efficiency

Hi there,
This some queries I am using to check access predicate efficiency.
First two small functions.
Access predicate:
[code language=”sql”]
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;
/
[/code]
And filter predicate
[code language=”sql”]
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;
/
[/code]
Usage as follow:
Check index access predicates efficiency for a specific sql_id

[code language="sql"]
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;
[/code]
Result:
[code language="sql"]
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
[/code]
Check index filter predicates. Index is used to filter the result as it cannot be use for access.
[code language="sql"]
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;
[/code]
Result:
[code language="sql"]
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
[/code]
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.
[code language="sql"]
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;
[/code]
Result:
[code language="sql"]
PMU R@SEL$1 TABLE 316 157 RA_RACETYPE 2778439705 5
PMU P@SEL$1 TABLE 2 10 PA_DEFER 2778439705 8
[/code]
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


     

Leave a Reply

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