CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Laks
Laks

Reputation: 85

what is the SYS_AUDIT in the filter predicate in oracle execution

i am running 1 query in 2 different environment(lower env and prod) but found plan difference. volume looks to be almost same. before i analyse further just wanted to understand what is SYS_AUDIT in predicates.

Env 1: query takes around 50min.

Predicates as show below. I tried searching for SYS_AUDIT did not find much help in google. Wanted to understand what is SYS_AUDIT. 

3 - filter(SYS_AUDIT(1,'schema','SUPPLY_table','SUPPLY_ATTR',3) IS NULL)

5 - access("SUPPLY_DIM"."SUPPLY_SET_ID"="SUPPLY_SET_ID")
    filter((GREATEST("EFF_ASOF_DT","SUPPLY_DIM"."EFF_ASOF_DT")<=TO_DATE(' 2019-08-19 13:29:29', 
             'syyyy-mm-dd hh24:mi:ss') AND LEAST("EFF_UNTIL_DT","SUPPLY_DIM"."EFF_UNTIL_DT")>=TO_DATE(' 2019-08-19 
              13:29:29', 'syyyy-mm-dd hh24:mi:ss')))

Env 2: Query takes 15min, predicates as show below

3 - access("SUPPLY_DIM"."SUPPLY_SET_ID"="SUPPLY_table"."SUPPLY_SET_ID")
       filter((GREATEST("SUPPLY_table"."EFF_ASOF_DT","SUPPLY_DIM"."EFF_ASOF_DT")<=TO_DATE(' 2019-08-19 13:29:29', 'syyyy-mm-dd hh24:mi:ss') AND 
              LEAST("SUPPLY_table"."EFF_UNTIL_DT","SUPPLY_DIM"."EFF_UNTIL_DT")>=TO_DATE(' 2019-08-19 13:29:29', 'syyyy-mm-dd hh24:mi:ss')))

Upvotes: 1

Views: 256

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

According to the My Oracle Support document "FGA Policies Disable Plan Stability Baselines (Doc ID 2272107.1)":

FGA is enforced using transparently generated SYS_AUDIT filter predicates, filters that alter the structure of the query plans. Some of the query blocks composing the query execution plan change as a result of the FGA activation

Similar to Virtual Private Database, looks like fine grained auditing silently adds predicates to queries, to enforce custom security rules.

Unfortunately I can't provide much advice for how to investigate further or how to tune your query. You might need to post on an FGA specific forum, or create a ticket for My Oracle Support.

Upvotes: 1

Related Questions