RE: ORACLE Statements in a PLSQL Block

  • From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
  • To: <rajendra.pande@xxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
  • Date: Mon, 24 Feb 2014 08:23:14 -0600

Hint are NOT removed.

I ran a simple test with a simple Select in a PLSLQ block I ran the
block 4 times once all lower case, then upper, then split on two line
and last one with a hint.  Here is what shows up in v$sqlarea

SQL> select action, executions, version_count, hash_value, sql_text
  2    from v$sqlarea
  3   where module = 'PLSQL' and command_type != 47
  4   order by hash_value;

ACTION      EXEC VERSIONS   HASH_VALUE SQL_TEXT
---------- ----- -------- ------------
--------------------------------------------
HINT           1        1   2245097906 SELECT /*+ full (e) */ COUNT(*)
FROM EMP E
LOWER          3        1   2728343546 SELECT COUNT(*) FROM EMP E

2 rows selected.

Notice the simple query is run 3 times as the same code, the PL/SQL
engine converted it all formatting as noted.  The one with the hint does
show up as a different statement, the hint is NOT removed.   A regular
comment is removed. 

If you'd like the full code on this test let me know. 

+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
Ric Van Dyke
Education Director
Hotsos Ltd.

Hotsos Symposium March 2-6 2014
Make your plans to be there now!


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
rajendra.pande@xxxxxxx
Sent: Monday, February 24, 2014 9:12 AM
To: Oracle-L@xxxxxxxxxxxxx
Subject: ORACLE Statements in a PLSQL Block

Came across this MOS note

How to Determine the SQL_ID of a SQL Statement in a PL/SQL Block (Doc ID
741724.1)
As part of the note it goes on to say that 

"The SQL statement within the PL/SQL block is actually stored
separately, but you cannot see it because:

* every sql statement in a PL/SQL block is stored as capital letters
* every comment and INTO clause are removed

If that is the case does it not have implications for the optimizer
related to performance and statement tuning. .. Given that all hints are
also removed 

Wondering if someone has done some research and blogging on this topic

Regards
Please visit our website at
http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html
for important disclosures and information about our e-mail policies. For
your protection, please do not transmit orders or instructions by e-mail
or include account numbers, Social Security numbers, credit card
numbers, passwords, or other personal information.
--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: