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?

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