Tweet
Tweet

Slide 1

Slide 1 text

The Practical Use of PL/Scope Sabine Heimsath

Slide 2

Slide 2 text

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) [email protected] its-people.de @oraesque

Slide 3

Slide 3 text

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%'

Slide 4

Slide 4 text

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%'

Slide 5

Slide 5 text

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)!

Slide 6

Slide 6 text

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)!

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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'

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

Sabine Heimsath  PL/Scope UKOUG Tech 2018 2018-12-02 PL/Scope - Use Cases DEMO

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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"

Slide 28

Slide 28 text

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/

Slide 29

Slide 29 text

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