tag:blogger.com,1999:blog-8677649049588007585.post8576359448295537025..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: How Reliable are PL/Scope Results? (9403)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-8677649049588007585.post-50417632145178666972011-11-14T18:52:57.491+00:002011-11-14T18:52:57.491+00:00Hello JHall, All,
This finding is very interestin...Hello JHall, All,<br /><br />This finding is very interesting.<br /><br />In fact, when looking at the PL/SCOPE feature, it includes some aspects that "overlap" in a certain meaning with the Oracle regular dependency tracking, that is, the one between database objects which is recorded<br />in the USER_DEPENDENCIES and similar views.<br /><br />When a procedure calls another procedure using a static procedure call, then this dependency is tracked at both the database level (always) and at PL/SCOPE level (on demand, entirely or partially, as you have observed).<br /><br />In my opinion, making the data set recorded for one object (the calling procedure) dependent <br />on whether the called procedure has PL/SCOPE data collected or not looks as "kind of a bug".<br /><br />Why do I say "kind of" ?<br />Because it seems that Oracle has implemented this behavior on purpose.<br /><br />The documentation says the following:<br />http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25518/adfns_plscope.htm#g1010526<br /><br />"The packages STANDARD and DBMS_STANDARD declare and define base types, such as VARCHAR2 and NUMBER, and subprograms such as RAISE_APPLICATION_ERROR. If your database has PL/Scope identifier data for these packages, PL/Scope can track your usage of the identifiers that these packages create."<br /><br />I checked in my database and found that both these two standard packages do have PL/SCOPE identifiers recorded.<br /><br />The behavior is probably the same for regular schema pl/sql objects.<br /><br /><br />I think that this feature will probably become more customizable in the future versions of PL/SQL, allowing for example to collect only some types of the data, instead of the current "all or nothing" approach, in a similar way in which the PLSQL_WARNINGS are customizable up to a single specific warning message.<br /><br /><br />Thanks a lot & Best Regards,<br />Iudith MentzelAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-53800049708325652792011-11-14T05:25:21.785+00:002011-11-14T05:25:21.785+00:00PL/Scope results appear to only include references...PL/Scope results appear to only include references to identifiers for which the associated declaration has been recorded (I suspect this is because the signature is determined when the declaration is processed). The output from the following sequence of statements demonstrates the affect of not storing PL/Scope details for referenced identifiers:<br /><br />ALTER session SET plscope_settings='IDENTIFIERS:NONE'<br />/<br />CREATE OR REPLACE PROCEDURE plch_plscope_test1 AS<br />BEGIN<br /> RETURN;<br />END plch_plscope_test1;<br />/<br />ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'<br />/<br />CREATE OR REPLACE PROCEDURE plch_plscope_test2 AS<br />BEGIN<br /> plch_plscope_test1;<br />END plch_plscope_test2;<br />/<br />SELECT *<br />FROM sys.user_identifiers<br />WHERE object_name LIKE 'PLCH\_PLSCOPE\_TEST%' ESCAPE '\'<br />ORDER BY object_name, line, col<br />/<br />ALTER PROCEDURE plch_plscope_test1 COMPILE<br />/<br />ALTER PROCEDURE plch_plscope_test2 COMPILE<br />/<br />SELECT *<br />FROM sys.user_identifiers<br />WHERE object_name LIKE 'PLCH\_PLSCOPE\_TEST%' ESCAPE '\'<br />ORDER BY object_name, line, col<br />/<br />DROP PROCEDURE plch_plscope_test2<br />/<br />DROP PROCEDURE plch_plscope_test1<br />/<br /><br /><br />The following are the output from the queries:<br /><br />NAME SIGNATURE TYPE OBJECT_NAME OBJECT_TYPE USAGE USAGE_ID LINE COL USAGE_CONTEXT_ID <br />------------------------------ -------------------------------- ------------------ ------------------------------ ------------- ----------- ---------------------- ---------------------- ---------------------- ---------------------- <br />PLCH_PLSCOPE_TEST2 75DE7E708891CE1B5F9297A20D839A16 PROCEDURE PLCH_PLSCOPE_TEST2 PROCEDURE DECLARATION 1 1 11 0 <br />PLCH_PLSCOPE_TEST2 75DE7E708891CE1B5F9297A20D839A16 PROCEDURE PLCH_PLSCOPE_TEST2 PROCEDURE DEFINITION 2 1 11 1 <br /><br />NAME SIGNATURE TYPE OBJECT_NAME OBJECT_TYPE USAGE USAGE_ID LINE COL USAGE_CONTEXT_ID <br />------------------------------ -------------------------------- ------------------ ------------------------------ ------------- ----------- ---------------------- ---------------------- ---------------------- ---------------------- <br />PLCH_PLSCOPE_TEST1 2C9891C7A26B1B39EC3B5894664ECE69 PROCEDURE PLCH_PLSCOPE_TEST1 PROCEDURE DEFINITION 2 1 11 1 <br />PLCH_PLSCOPE_TEST1 2C9891C7A26B1B39EC3B5894664ECE69 PROCEDURE PLCH_PLSCOPE_TEST1 PROCEDURE DECLARATION 1 1 11 0 <br />PLCH_PLSCOPE_TEST2 75DE7E708891CE1B5F9297A20D839A16 PROCEDURE PLCH_PLSCOPE_TEST2 PROCEDURE DECLARATION 1 1 11 0 <br />PLCH_PLSCOPE_TEST2 75DE7E708891CE1B5F9297A20D839A16 PROCEDURE PLCH_PLSCOPE_TEST2 PROCEDURE DEFINITION 2 1 11 1 <br />PLCH_PLSCOPE_TEST1 2C9891C7A26B1B39EC3B5894664ECE69 PROCEDURE PLCH_PLSCOPE_TEST2 PROCEDURE CALL 3 3 3 2 <br /><br />procedure PLCH_PLSCOPE_TEST2 dropped.<br />procedure PLCH_PLSCOPE_TEST1 dropped.<br /><br /><br />As can be seen, when PLCH_PLSCOPE_TEST1 is compiled without recording its identifiers, calls to it are also not recorded.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-44000603167136155432011-11-10T18:45:20.524+00:002011-11-10T18:45:20.524+00:00Hello Steven,
Wow, that would be far shooting ind...Hello Steven,<br /><br />Wow, that would be far shooting indeed :) :)<br /><br />Maybe it's time to have a "permanent representation" of the PL/SQL Challenge team<br />at Oracle Headquarters, before they release new features ? ) :)<br /><br />This just shows that the software producers themselves are also humans ... they also can err<br />just like us, the mere mortals ...<br /><br />Thank you so much & Best Regards,<br />IudithAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-51844260403109289992011-11-10T17:21:52.373+00:002011-11-10T17:21:52.373+00:00I think that it is likely you have uncovered bugs ...I think that it is likely you have uncovered bugs in PL/Scope. I don't think this has anything to do with the optimization process.<br /><br />I have sent a note to Bryn Llewellyn with an example of what you have uncovered. I will let you all know what he says.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-49847570958472151272011-11-10T17:18:51.199+00:002011-11-10T17:18:51.199+00:00Hello Steven, All,
I just checked and the output ...Hello Steven, All,<br /><br />I just checked and the output is the same under the two optimization levels.<br /><br />TYPE USAGE NAME<br />--------------- ----------- --------------------<br />ITERATOR DECLARATION INDX<br />ITERATOR REFERENCE INDX<br />PACKAGE DECLARATION PLCH_PKG<br />PACKAGE DEFINITION PLCH_PKG<br />PROCEDURE DECLARATION DO_STUFF<br />PROCEDURE DEFINITION DO_STUFF<br />SYNONYM CALL PLITBLM<br />SYNONYM REFERENCE DBMS_SQL<br />VARIABLE DECLARATION L_ITEMS<br />VARIABLE REFERENCE L_ITEMS<br />VARIABLE REFERENCE L_ITEMS<br /><br />11 rows selected.<br /><br />All my tests are performed in Oracle 11.1.0.7.0.<br /><br /><br />We see however that the output DOES or DOES NOT contain the LABEL rows and also other rows<br />like the SYSDATE call or DBMS_OUTPUT reference, subject to how we "play around" with the labels.<br /><br />So, even under PLSQL_OPTIMIZE_LEVEL=0, looks like the optimizer still performs some "code analysis" that "filters out" some of the elements from the source code, before generating the user_identifiers data.<br />Or, to say it differently, there maybe exist "internal optimizing compiler" actions<br />that are not controlled by this parameter.<br /><br />Otherwise we are only left with the conclusion that maybe the PL/SQL Scope feature has some serious bugs not yet discovered ?!?<br /><br />It would be nice if this feature would indeed analyze the source code exactly "as is",<br />without any intervening corrections, optimizations, a.s.o, that way we would indeed have a completely accurate image of our code ... of course if we know exactly how to interpret and how to look after <br />what we are willing to check.<br /><br />I'd be glad to hear other players opinion and experience.<br /><br />Thanks a lot & Best Regards,<br />IudithAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-65770743633505050012011-11-10T16:39:58.721+00:002011-11-10T16:39:58.721+00:00Iudith wonders if the outcome of a PL/Scope analys...Iudith wonders if the outcome of a PL/Scope analysis is affected by the PL/SQL optimizer. I don't believe this is the case. Try running the script below. You get the same result when the optimization level is 0 (disabled) and 2.<br /><br />[Sorry about the poor code formatting. Blogger doesn't allow pre tags in comments. Ridiculous!]<br /><br />ALTER SESSION SET plscope_settings='identifiers:all'<br />/<br /><br />ALTER session set plsql_optimize_level=0<br />/<br /><br />CREATE TABLE plch_stuff<br />(<br /> amount NUMBER<br /> , rating INTEGER<br />)<br />/<br /><br />CREATE OR REPLACE PACKAGE plch_pkg<br />IS<br /> PROCEDURE do_stuff;<br />END plch_pkg;<br />/<br /><br />CREATE OR REPLACE PACKAGE BODY plch_pkg<br />IS<br /> PROCEDURE do_stuff<br /> IS<br /> l_items DBMS_SQL.number_table;<br /> BEGIN<br /> FORALL indx IN 1 .. l_items.COUNT<br /> UPDATE plch_stuff<br /> SET amount = l_items (indx);<br /><br /> GOTO all_done;<br /> NULL;<br /><br /> <><br /> DBMS_OUTPUT.put_line (SYSDATE);<br /> END do_stuff;<br />END plch_pkg;<br />/<br /><br /> SELECT TYPE, usage, name<br /> FROM user_identifiers<br /> WHERE object_name = 'PLCH_PKG'<br />ORDER BY 1, 2<br />/<br /><br />ALTER session set plsql_optimize_level=2<br />/<br /><br />ALTER PACKAGE plch_pkg COMPILE BODY<br />/<br /><br /> SELECT TYPE, usage, name<br /> FROM user_identifiers<br /> WHERE object_name = 'PLCH_PKG'<br />ORDER BY 1, 2<br />/<br /><br />DROP TABLE plch_stuff<br />/<br /><br />DROP PACKAGE plch_pkg<br />/Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.com