impact analysis with pl/scope

Copyright©2015Oracleand/oritsaffiliates.Allrightsreserved.|

Impact Analysis with PL/Scope

1

StevenFeuersteinOracleDeveloperAdvocateforPL/SQL

OracleCorporaDon

Email:[email protected]:@sfonplsql

Blog:stevenfeuersteinonplsql.blogspot.comYouTube:PracDcallyPerfectPL/SQL

Copyright©2017Oracleand/oritsaffiliates.Allrightsreserved.| Page2

ResourcesforOracleDatabaseDevelopers• OfficialhomeofPL/SQL-oracle.com/plsql•  SQL-PL/SQLdiscussionforumonOTNhLps://community.oracle.com/community/database/developer-tools/sql_and_pl_sql

•  PL/SQLandEBRblogbyBrynLlewellyn-hLps://blogs.oracle.com/plsql-and-ebr

• OracleLearningLibrary-oracle.com/oll• WeeklyPL/SQLandSQLquizzes,andmore-plsqlchallenge.oracle.com•  AskTom-asktom.oracle.com–'nuffsaid•  LiveSQL-livesql.oracle.com–scriptrepositoryand12/712cdatabase•  oracle-developer.net-greatcontentfromAdrianBillington•  oracle-base.com-greatcontentfromTimHall


PL/Scope• Addedin11.1,compiler-driventoolthatcollectsinformaDonaboutidenDfiersandstatements,andstoresitindatadicDonaryviews.

• UsePL/ScopetoanswerquesDonslike:– Whereisavariableassignedavalueinaprogram?– Whatvariablesaredeclaredinsideagivenprogram?– Whichprogramscallanotherprogram(thatis,youcangetdowntoasubprograminapackage)?

– FindthetypeofavariablefromitsdeclaraDon.

• Andwith12.2,youcannowalsoanalyzeSQLstatementsinPL/SQL.


LifeWithoutPL/Scope• PriortoPL/Scope,analyzingimpactmostlymeanttextsearchesthroughfiles,orqueriesagainstALL_SOURCEandALL_DEPENDENCIESviews.

• ALL_DEPENDENCIESisfineforgivingyoudependencyinfoatthedatabaseobjectlevel,butnotbelow.– "FindallthepackagesthatreferencetableX".

• With11.1,Oraclenowsupportsfine-graineddependenciesforinvalidaDon,butthatinformaDonisnotavailableviadatadicDonaryviews.

SELECT owner , name , type

, referenced_owner || '.' || FROM all_dependencies AND referenced_type IN ('TABLE', 'VIEW') AND referenced_name = 'MY_TABLE' ORDER BY name, referenced_owner, referenced_name

11g_fgd*.sql


Ge<ngStartedwithPL/Scope

• PL/Scopemustbeenabled;itisoffbydefault.• Whenyourprogramiscompiled,informaDonaboutallidenDfiersarewriLentotheALL_IDENTIFIERSview.

•  YouthenquerythecontentsoftheviewtogetinformaDonaboutyourcode.

• ChecktheALL_PLSQL_OBJECT_SETTINGSviewforthePL/ScopeseongofaparDcularprogramunit.

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'


KeyColumnsinALL_IDENTIFIERS•  TYPE

– ThetypeofidenDfier(VARIABLE,CONSTANT,etc.)• USAGE

– ThewaytheidenDfierisused(DECLARATION,ASSIGNMENT,etc.)•  LINEandCOL

– LineandcolumnwithinlineinwhichtheidenDfierisfound•  SIGNATURE

– UniquevalueforanidenDfier.EspeciallyhelpfulwhendisDnguishingbetweenoverloadingsofasubprogramor"connecDng"subprogramdeclaraDonsinpackagewithdefiniDoninpackagebody.

• USAGE_IDandUSAGE_CONTEXT_ID– RevealhierarchyofidenDfiersinaprogramunit


Startwithsomesimpleexamples•  ShowalltheidenDfiersinaprogramunit•  Showallvariablesdeclaredinasubprogram(notatpackagelevel)•  ShowallvariablesdeclaredinthepackagespecificaDons•  ShowthelocaDonswhereavariablecouldbemodified

plscope_demo_setup.sqlplscope_all_idents.sql

plscope_var_declares.sqlplscope_gvar_declares.sql plscope_var_changes.sql


Moreadvancedexamples•  FindexcepDonsthataredefinedbutneverraised•  ShowthehierarchyofidenDfiersinaprogramunit• ValidatenamingconvenDonswithPL/Scope

plscope_unused_excepDons.sqlplscope_hierarchy.sql

plscope_naming_convenDons.sql


PL/ScopeHelperUNliNes• Clearly,"datamining"inALL_IDENTIFIERScangetcomplicated.•  SuggesDonsforpuongPL/Scopetouse:

– Buildviewstohidesomeofthecomplexity.– Buildpackagestoprovidehigh-levelsubprogramstoperformspecificacDons.

plscope_helper_setup.sqlplscope_helper.pkg


12.2EnhancementstoPL/Scope

• GathersdataonSQLstatementsinPL/SQLprogramunits•  Youcannowfind:

– wherespecificcolumnsarereferenced– allprogramunitsperformingspecificDMLoperaDonsontable(andhelpyouconsolidatesuchstatements)

– allSQLstatementscontaininghints– alldynamicSQLusages–idealforgeongridofSQLinjecDonvulnerabiliDes– locaDonsinyourcodewhereyoucommitorrollback– mulDpleappearancesofsameSQLstatement(sameSQL_ID)

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL, STATEMENTS:ALL'


NewALL_STATEMENTSView•  TheALL_STATEMENTSview(alongwithUSER_STATEMENTS)containsinformaDonabouteachSQLstatementinprogramunitscompiledwithPL/Scopeenabled.– full_text –textofSQLstatement– has_into_record –INTOplsql_record– has_current_of –UsesCURRENTOFsyntax– has_for_update –UsesFORUPDATEsyntax– has_in_binds-– has_into_bulk –UsesBULKCOLLECTINTO– usage_id –SameaswithALL_IDENTIFIERS–anduniqueacrossbothtables!– sql_id –pointertoSQLstatementinv$views


SomeExamples

•  There'ssomuchyoucando!

SELECT owner, object_name, line, full_text FROM all_statements WHERE has_hint = 'YES'

FindSQLStatementswithHintsSELECT idt.line, idt.owner || '.' || idt.object_name code_unit, RTRIM (src.text, CHR (10)) text FROM all_identifiers idt , all_statements st , all_source src WHERE idt.usage = 'REFERENCE' AND idt.TYPE = 'TABLE' AND idt.name = table_in AND idt.owner = owner_in AND idt.line = src.line AND idt.object_name = src.name AND idt.owner = src.owner AND idt.usage_context_id = st.usage_id

FindAllDMLStatementsOnTable


MoreExamples!

SELECT sql_id, text, COUNT (*) FROM all_statements WHERE sql_id IS NOT NULL GROUP BY sql_id, text HAVING COUNT (*) > 1 /

SameSQLStatementUsed>1?

SameSQL_IDbutdifferentsignature.

SELECT * FROM all_statements WHERE has_into_bulk = 'YES' /

UsesBULKCOLLECTINTO?


MoreExamples:FinddynamicSQLSELECT st.owner, st.object_name, st.line, s.text FROM all_statements st, all_source s WHERE st.TYPE IN ('EXECUTE IMMEDIATE', 'OPEN') AND st.owner = s.owner AND st.object_name = s.name AND st.line = s.line UNION ALL

SELECT idnt.owner, idnt.object_name, idnt.line, src.text FROM all_identifiers idnt, all_source src WHERE idnt.owner <> 'SYS' AND idnt.signature IN (SELECT a.signature FROM all_identifiers a WHERE a.usage = 'DECLARATION' AND a.owner = 'SYS' AND a.object_name = 'DBMS_SQL' AND a.object_type = 'PACKAGE') AND idnt.owner = src.owner AND idnt.name = src.name AND idnt.line = src.line

NaDveDynamicSQL–easy!

DBMS_SQLReferences:mustrecompilebuilt-inwithPL/Scopeenabled!


Conclusions• PL/Scopegivesyoualevelofvisibilityintoyourcodethatwasneverbeforepossible.

• With12.2enhancementsaddinganalysisofSQL,youcannowperformdetailedanalysisoftheimpactofchangingyourdatastructures.

• Checkoutmy(andother)LiveSQLscriptsdemonstraDngPL/ScopecapabiliDes.

livesql.oracle.com

Doc:12.2DatabaseDevelopmentGuide

impact analysis with pl/scope

Technology