Display_cursor(null,null,'allstats Last') Command With Statistics_level=all Does Not Display Buffer Information For SQL With Illogical Predicate

(Doc ID 2333906.1)

Last updated on DECEMBER 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.2.0.1 [Release 10.2 to 12.2]
Information in this document applies to any platform.

Goal

The purpose of the document is to explain the situation of the incomplete display of display_cursor with Statistics_level=all.

The DBMS_XPLAN.DISPLAY_CURSOR is important for the diagnostic of SQL performance, and it will provide useful information.
But sometimes the output command is not complete, why? The reason is the SQL with illogical predicate, as the following example testcase shows.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms