Database Software

Franck

This is a nice idea and an elegant way to work around this lack of information from AWR. This gap is still not filled in 12cR1 (have not tested in 12cR2)

http://hourim.wordpress.com/2014/02/17/12c-display_awr/

The same conclusion can be made for the wonderful Real Time SQL monitoring which lacks the predicate part as well.

One of my favourite blogs and reads is Dominic Brooks which has also mentioned another interesting AWR display lack which is the Note about SQL plan baseline:

http://orastory.wordpress.com/2014/02/05/awr-was-a-baselined-plan-used/

It is probably the same case for the new 12c Note (not tested thought)

   - this is an adaptive plan (rows marked '-' are inactive)

Which might also not been reported by the display_awr function. It depends from where this Note comes, other_xml or v$sql?

Best regards

Mohamed Houri

Franck

This is a nice idea and an elegant way to work around this lack of information from AWR. This gap is still not filled in 12cR1 (have not tested in 12cR2)

http://hourim.wordpress.com/2014/02/17/12c-display_awr/

The same conclusion can be made for the wonderful Real Time SQL monitoring which lacks the predicate part as well.

One of my favourite blogs and reads is Dominic Brooks which has also mentioned another interesting AWR display lack which is the Note about SQL plan baseline:

http://orastory.wordpress.com/2014/02/05/awr-was-a-baselined-plan-used/

It is probably the same case for the new 12c Note (not tested thought)

   - this is an adaptive plan (rows marked '-' are inactive)

Which might also not been reported by the display_awr function. It depends from where this Note comes, other_xml or v$sql?

Best regards

Mohamed Houri

Hi Mohamed,

Real Time SQL monitoring has the predicates displayed in the 'Plan' tab when having the mouse over the filter or access icon in the predicates column. Not easy to copy/paste howver. They are available in the html source (except that in 12c you have to unencode/uncompress it - http://www.dbi-services.com/index.php/blog/entry/oracle-sql-monitoring-reports-in-flash-html-text)

Regards,

Franck.

We, Oracle are working on our internal process as to how to evaluate and prioritize the IDEAS submitted.  But the more votes obviously the more priority we will put on the request.  However votes/popularity alone will not determine the priority.

As we move through the process the IDEA will change stages: (not in flow order)

- Active

- Already Offered

- Archived

- Coming Soon

- For Future Consideration

- in Progress

- Partially Implemented

- Under Review

Absolutely needed. No reason why it should not be present.

In my view this feature avoids additional trace files generation and AWR alone gives the required information.

Definitely. +42.

Looking forward to this being implemented.

Note that this has following opened bug created in 2008:

This enhancement would be extremely helpful. Hopefully it get's implemented.

I think would be very interesting enhancement.

This would be helpful in case of night batch processing performance degradation.

I think this would be represent an overhead if done for all statements, this can be done for example for specified sql_id or sql_text , may be via

DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('98n7902rytpxn');

Thanks,

Cherif BEN HENDA

Hi Mohamed,

The same behavior under 12.2.0.1.0

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.

Thanks,

Cherif BEN HENDA.

This should absolutely be done.

Christian Klammer

Hello!

I also missed this piece of information quite a few times.

RobK

voted up

The issue is being assessed and evaluated in the enhancement request:

Bug 7493519 - PERF_DIAG: ACCESS_PREDICATES AND FILTER_PREDICATES MISSING IN DBA_SQL_PLAN_HIST

BR,

Ahmed AbuRob

Now we have 2021 and release 19.10 and nothing has changed regarding access and filter predicates in AWR.
It's absolutely a shame for Oracle to be unable to fill this gap for decades.
If you absolutely need access and filter predicates for SQL plan history there's a workaround using Panorama-Sampler (http://rammpeter.github.io/panorama_sampler.html) in Addition to AWR.
But this sampling by Panorama-Sampler has primarily been intended as AWR-substitute for Standard Edition.
Best regards
Peter

Hi Peter,
As mentioned above, this has been fixed in 20c and is there in 21c:
```
DEMO@o21c_tp> select banner_full from v$version;

                                                                            BANNER\_FULL  

____________________________________________________________________________________________
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.2.0.0.0

DEMO@o21c_tp> select count(distinct access_predicates),count(distinct filter_predicates) from dba_hist_sql_plan;

COUNT(DISTINCTACCESS_PREDICATES) COUNT(DISTINCTFILTER_PREDICATES)
___________________________________ ___________________________________
3,452 1,754

DEMO@o21c_tp>
```