The Practical Use of PL/Scope

The Practical Use of PL/Scope

With PL/Scope Oracle gives us a powerful but not necessary instantly likeable tool to analyse the metadata of PL/SQL code.
How does it work and how can it help you in your daily business?

4378bfee946ca4ba1a942d6420e657c2?s=128

Sabine Heimsath

December 02, 2018
Tweet

Transcript

  1. The Practical Use of PL/Scope Sabine Heimsath

  2. Sabine Heimsath Client Architecture and PL/SQL and APEX Development its-people

    Portfolio Manager Database and Development Blogger DOAG Development Community (responsible for PL/SQL) sabine.heimsath@its-people.de its-people.de @oraesque
  3. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 How do

    you analyse your code? select * from user_source where upper(text) like '%MYVAR%'
  4. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 How do

    you analyse your code? select * from user_source where upper(text) like '%MYVAR%'
  5. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 What is

    PL/Scope? • Part of PL/SQL compiler since 11gR1 • Gathers information about all objects in PL/SQL and writes them into metadata tables • … and even about SQL statements (within PL/SQL)!
  6. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 What is

    PL/Scope? • Part of PL/SQL compiler since 11gR1 • Gathers information about all objects in PL/SQL and writes them into metadata tables • … and even about SQL statements (within PL/SQL)!
  7. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 What is

    PL/Scope good for? • Code Analysis Much more detail and semantics than simple text search Much more detail than DEPENDENCIES views • Check for Violation of Coding Guidelines Security Vulnerabilites • Gather Information Getting an overview of unknown code Refactoring
  8. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 How to

    Start
  9. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 How to

    Start 1. Change settings 2. Compile all your code (or just the part you are interested in) ALTER SESSION SET plscope_settings= 'IDENTIFIERS:ALL, STATEMENTS:ALL' Please refer to the docs for more exotic settings
  10. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 How to

    Start 1. Change settings 2. Compile all your code (or just the part you are interested in) ALTER SESSION SET plscope_settings= 'IDENTIFIERS:ALL, STATEMENTS:ALL'
  11. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Example The

    code through the eyes of PL/Scope
  12. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Example

  13. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Example

  14. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Example

  15. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Example

  16. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Objects compiled

    with PL/Scope SELECT * FROM ALL_PLSQL_OBJECT_SETTINGS
  17. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Objects compiled

    with PL/Scope SELECT * FROM ALL_PLSQL_OBJECT_SETTINGS
  18. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 PL/Scope –

    Where’s the information? Model by Philipp Salvisberg
  19. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 PL/Scope –

    Where’s the information? Model by Philipp Salvisberg [DBA|ALL|USER]_IDENTIFIERS • Identifier Declaration • DB Object Usage
  20. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 PL/Scope –

    Where’s the information? Model by Philipp Salvisberg [DBA|ALL|USER]_STATEMENTS • Identifier Declaration • SQL Stmt Usage • SQL Statement
  21. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Example View

    USER_IDENTIFIERS – A closer look
  22. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 View USER_STATEMENTS

    – A closer look Example
  23. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 PL/Scope -

    Use Cases DEMO
  24. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 • SQL

    Statements (only until 12.1 ) • Wrapped code • Anonymous Blocks • Dynamic SQL • Invalid Code Limits of PL/Scope
  25. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 The "Down

    Side" • Time • Only compile time is affected • Test with 61.000 lines showed an increase by a factor of 1.1 to 1.2 I didn't do any thorough testing as it didn't hurt
  26. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Check for

    yourself: SELECT SPACE_USAGE_KBYTES FROM V$SYSAUX_OCCUPANTS WHERE OCCUPANT_NAME='PL/SCOPE'; The "Down Side" • Space • The metadata does need some space • My test with 61.000 lines needed ~ 26MB • My observation: less than a KB per line of code I didn't do any thorough testing as it didn't hurt
  27. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 • Bugs

    • Very detailed analysis: https://www.salvis.com/blog/2017/10/14/limitations-of- plscope-and-how-to-deal-with-them/ • Will hopefuly go away soon  The "Down Side"
  28. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 Tools •

    PL/Scope Utils: Extension for SQL Developer https://www.salvis.com/blog/download/ • Find bad performing queries or code with PL/Scope: https://nicetheory.io/2018/11/30/find-bad-performing-queries-or-code-with-pl-scope/
  29. Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 • Database

    Development Guide: 14 Using PL/Scope http://docs.oracle.com/database/122/ADFNS/plscope.htm • Steven Feuerstein: Powerful Impact Analysis (Oracle Magazine) http://www.oracle.com/technetwork/issue-archive/2017/17-jan/o17plsql-3429342.html • Steven Feuerstein: Impact Analysis with PL/Scope (SildeShare) https://www.slideshare.net/StevenFeuerstein/impact-analysis-with-plscope • LiveSQL examples by Steven Feuerstein https://livesql.oracle.com/apex/f?p=590:49:::NO::P49_SEARCH:pl%2Fscope Reading List