Yuan Yao, An Oracle ACE's Blog

When Oracle displays top SQL statements in the “SQL Statistics” sections of an Oracle AWR report, it truncates long SQL texts for readability— showing only the first few words next to each SQL ID. At the end of this section, the “Complete List of SQL Text” provides the full SQL text for every SQL ID. This enable DBAs to view the entire, untruncated SQL statement by click on the SQL ID in the reprot—whether in any of previous SQL lists or in the ASH report appended to the AWR report since Oracle 12c.

The complete list includes SQL statements from both user applications and background processes, whether they are executed individually or as part of PL/SQL blocks. By examining its SQL text, it is often possible to infer the origin of a SQL statement.

If a SQL statement is invoked by PL/SQL, the PL/SQL interpreter transforms the text in a distinctive way. The entire SQL text is converted to uppercase, with input variables shown as:Bn（ e.g.,:B1,:B2,:B3) and output variables:On( e.g.,:O0,:O1,:O2). A typical example looks like this:

INSERT INTO ORDERS ( ORDER_ID,  ORDER_DATE,  ORDER_MODE,  CUSTOMER_ID,  ORDER_STATUS,  WAREHOUSE_ID,  DELIVERY_TYPE,  DELIVERY_ADDRESS_ID,  COST_OF_DELIVERY,  WAIT_TILL_ALL_AVAILABLE,  CUSTOMER_CLASS,  CARD_ID,  INVOICE_ADDRESS_ID ) VALUES ( ORDERS_SEQ.NEXTVAL + :B8 ,  SYSTIMESTAMP ,  'online',  :B7 ,  1,  :B6 ,  'Standard',  :B1 ,  DBMS_RANDOM.VALUE(:B5 ,  :B4 ),  'ship_asap',  :B2 ,  :B3 ,  :B1 ) RETURNING ORDER_ID INTO :O0 

For SQL generated internally by Oracle, the text is typically in lowercase, though this alone is not a definitive indicator. A more reliable marker is Oracle’s long-standing naming convertions: many internal objects contain the dollar sign ($) in their names. Examples include X$ tables, V$ views , and system base tables such as OBJ$, TAB$, USER$, and UNDO$. Another naming pattern is the use of # in column name to store numeric identifiers. An example of such an internal SQL statement is shown below:

select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), nvl(i.evaledition#, 1), nvl(i.unusablebefore#, 0), nvl(i.unusablebeginning#, 0),  ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i,  ind_stats$ ist,  (select enabled,  min(intcols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

It is also worth noting the influence of the initialization parameterCURSOR_SHARING. When set toFORCE, Oracle rewrites SQL statements that contain literals by replacing those literals with system-generated bind variables in the form:SYS_B_n, where n represents 0, 1, 2, 3, and so on. For example:

SELECT UNIQUE_BANK_ID FROM TABADM.MODT WHERE BANK_ID = :"SYS_B_0" AND CUM_SOURCE = :"SYS_B_1" AND 
PRIMARY_REF = :"SYS_B_2" AND ROWNUM = :"SYS_B_3" AND DEL_FLG != :"SYS_B_4" AND ENTITY_CRE_FLG = :"SYS_B_5"

This transformation allows SQL statements that differ only in literal values to share the same cursor, thereby reducing hard parsing and improving cursor cache efficiency.