Dontcheff

DBA Statements

In DBA, Oracle database, PL/SQL, SQL on September 5, 2017 at 11:51

“A statement is persuasive and credible either because it is directly self-evident or because it appears to be proved from other statements that are so.” Aristotle

In Oracle 12.2, there is a new view called DBA_STATEMENTS. It can helps us understand better what SQL we have within our PL/SQL functions, procedures and packages.

There is too little on the Internet and nothing on Metalink about this new view:

PL/Scope was introduced with Oracle 11.1 and covered only PL/SQL. In 12.2, PL/Scope was enhanced by Oracle in order to report on the occurrences of static and dynamic SQL call sites in PL/SQL units.

PL/Scope can help you answer questions such as:
– Where and how a column x in table y is used in the PL/SQL code?
– Is the SQL in my application PL/SQL code compatible with TimesTen?
– What are the constants, variables and exceptions in my application that are declared but never used?
– Is my code at risk for SQL injection and what are the SQL statements with an optimizer hint coded in the application?
– Which SQL has a BULK COLLECT or EXECUTE IMMEDIATE clause?

Details can be found in the PL/Scope Database Development Guide or at Philipp Salvisberg’s blog.

Here is an example: how to find all “execute immediate” statements and all hints used in my PL/SQL units? If needed, you can limit the query to only RULE hints (for example).

1. You need to set the PLSCOPE_SETTINGS parameter and ensure SYSAUX has enough space:


SQL> SELECT SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS 
WHERE OCCUPANT_NAME='PL/SCOPE';

SPACE_USAGE_KBYTES
------------------
              1984

SQL> show parameter PLSCOPE_SETTINGS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
plscope_settings                     string      IDENTIFIERS:NONE

SQL> alter system set plscope_settings='STATEMENTS:ALL' scope=both;

System altered.

SQL> show parameter PLSCOPE_SETTINGS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
plscope_settings                     string      STATEMENTS:ALL

2. You must compile the PL/SQL units with the PLSCOPE_SETTINGS=’STATEMENTS:ALL’ to collect the metadata. SQL statement types that PL/Scope collects are: SELECT, UPDATE, INSERT, DELETE, MERGE, EXECUTE IMMEDIATE, SET TRANSACTION, LOCK TABLE, COMMIT, SAVEPOINT, ROLLBACK, OPEN, CLOSE and FETCH.


-- start
SQL> select TYPE, OBJECT_NAME, OBJECT_TYPE, HAS_HINT, 
SUBSTR(TEXT,1,LENGTH(TEXT)-INSTR(REVERSE(TEXT), '/*') +2 ) as "HINT" 
from DBA_STATEMENTS
where TYPE='EXECUTE IMMEDIATE' or HAS_HINT='YES';

TYPE              OBJECT_NAME   OBJECT_TYPE  HAS HINT
----------------- ------------- ------------ --- ------------------
EXECUTE IMMEDIATE LASKE_KAIKKI  PROCEDURE    NO  
SELECT            LASKE_KAIKKI  PROCEDURE    YES SELECT /*+ RULE */

-- end

Check also the DBA_STATEMENTS and ALL_STATEMENTS documentation. And the blog post by Jeff Smith entitled PL/Scope in Oracle Database 12c Release 2 and Oracle SQL Developer.

But finally, here is a way how to regenerate the SQL statements without the hints:


-- start
SQL> select TEXT from DBA_STATEMENTS where HAS_HINT='YES';

TEXT
------------------------------------------------------------
SELECT /*+ RULE */ NULL FROM DUAL WHERE SYSDATE = SYSDATE

SQL> select 'SELECT '||
TRIM(SUBSTR(TEXT, LENGTH(TEXT) - INSTR(REVERSE(TEXT), '/*') + 2))
as "SQL without HINT"
from DBA_STATEMENTS where HAS_HINT='YES';

SQL without HINT
-------------------------------------------------------------
SELECT NULL FROM DUAL WHERE SYSDATE = SYSDATE

-- end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: