1 / 34

Toad for Oracle’s CodeXpert: High-quality SQL & PL/SQL Code Validation

Toad for Oracle’s CodeXpert: High-quality SQL & PL/SQL Code Validation. Steven Feuerstein Bert Scalzo. Agenda. Why should we use a Code Validation Process? What is Code Validation? Discussion of Code Validation Processes Demonstration of CodeXpert. Why Use a Code Validation Process?.

guinevere-castro
Download Presentation

Toad for Oracle’s CodeXpert: High-quality SQL & PL/SQL Code Validation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Toad for Oracle’s CodeXpert:High-quality SQL & PL/SQL Code Validation Steven Feuerstein Bert Scalzo

  2. Agenda • Why should we use a Code Validation Process? • What is Code Validation? • Discussion of Code Validation Processes • Demonstration of CodeXpert

  3. Why Use a Code Validation Process? • Software Defects are extremely costly • We all agree: program liabilities must go! But how best to do this? • "Manual" code review is necessary but not sufficient. • It's hard to see them through consistently. • Traditional quality-oriented tools for Developers are designed for Debugging • Can be used as a Coding Coach • Programmers can learn from the validation process and over time minimize the feedback loops from validation.

  4. Why use a Code Validation Process? • In 2002, National Annual cost of inadequate software testing cost up to $59.5 Billion • Developers spend 40% of their time fixing software defects • Between 60% and 70% of the cost of software is attributable to maintenance

  5. What is Code Validation? • Code Validation is not the same as a typical Code Review • Components of Code Validation include... • Efficiency • Correctness • Maintainability • Readability • Structure • Complexity • CRUD Matrix

  6. Code Validation - Efficiency • There are many aspects, small and large, to SQL & PL/SQL code efficiency • Querying data from PL/SQL • Dynamic SQL and Dynamic PL/SQL • Loop Processing • Using built-in functions in SQL • Scalability of program

  7. Code Validation - Correctness • Syntactically correct code may not be functionally correct • Transaction Management • Variables and Data Structures • Use of Built in Packages • Scripts and SQL*Plus reports

  8. Code Validation - Maintainability • We spend 40% of our time fixing defects • Up to 70% of the cost of software is attributable to Maintenance • Is the intent of the code shown by the code itself? • Transaction Management • Querying Data • Exception Processing

  9. Code Validation - Readability • Readability is where Code Reviews typically focus, but with smaller scope • Coding style and conventions • Program construction • Loop Processing • SELECT list items qualified

  10. Code Validation - Structure • Well-formed structure in PL/SQL helps avoid resource leaks and unexpected behavior • Close those cursors! • Variables and Data Structures • Declaring and using Package Variables • Global variable side effects can be especially problematic. • Exception Processing • SQL*Plus script options

  11. Code Validation - Complexity • Complex code is difficult to maintain • High probability of introducing defects with changes due to maintenance • Software Engineering Institute (SEI) and Capability Maturity Model (CMM) metrics : • Number of Lines of Code • Number of Statements • Halstead Complexity – measure of module complexity • McCabe's Cyclomatic Complexity – measure of soundness and confidence • Maintainability Index – predictor of a module’s maintainability

  12. Critical Success Factors • Emphasize success, not failure • Provide detailed analysis and solutions to code validation problems • Avoid code violation “shock” with Prioritization • Set measurable goals

  13. CodeXpert – Where is it? • Currently: • SQL Editor • Proc Editor • Project Manager • Upcoming Toad 9.0: • File Menu • Schema Browser • Has its own screen

  14. CodeXpert - Rule Filters • Clicking on the filter down arrow lists the default rule filters. • CodeXpert comes with predefined filters based on severity or review objectives

  15. CodeXpert - Rule Details Summary • Clicking on the rule sets icon displays the default rule sets and allows users to create their own filters. Note: user-defined filters are not the same as user-defined rule sets.

  16. CodeXpert – Rules List • Users and review all the rules in CodeXpert by clicking on the Rules tab. Rules are organized according to the drop-down selection.

  17. CodeXpert – Analysis Options • First button runs CodeXpert • 2nd button enables/disables running CodeXpert rules • 3rd button enables/disables scanning SQL statements for problematic SQL.

  18. CodeXpert -- Result Set • The Results tree will be displayed when the CodeXpert scan completes. • Each objective is broken down into sub-categories and shows a count of the rules that failed. • The Properties category displays a list of statistics about the code. This is comparable to the information available in the Formatter profile statistics.

  19. CodeXpert – Flagged Rules in Result Set • Clicking on a flagged rule highlights the code in the Editor.

  20. CodeXpert – Flagged Rule Options • Right-clicking on a flagged rule allows the user to see a detailed explanation of the rule and its purpose (Show Tip) • User can also override, or ignore, a rule. A rule can be overridden completely or just a specific occurrence. • Once a rule has been overridden, it can also be accepted back if the user changes their mind.

  21. CodeXpert – Rule Insights • Double-clicking on the rule description in the Result Summary or selecting the Show Tip option in the right-click menu displays insights for that rule.

  22. CodeXpert – Report Summary Click on the full screen toggle to better show the full report • The Report Summary is an excellent management tool or report to take to code review meetings. It summarizes the number of flagged and overridden rules, displays statistical analysis by severity and objective, as well as summarizations of CodeXpert’s complexity analysis.

  23. CodeXpert – CRUD Matrix • The CRUD Matrix displays a tabular representation of the data access for the SQL included in the scanned code.

  24. CodeXpert – Code Metrics Report • The Code Metrics Report: • Based on the Software Engineering Institute (SEI) Capability Maturity Model (CMM) • Displays a summary of industry standard, software complexity analysis reports • Metrics reported include: • Number of Statements • Halstead Complexity Measure (Computational Complexity) • McCabe’s Cyclomatic Complexity (soundness & confidence of code) • SEI Maintainability Index (measurement reduce code entropy) Here we have a very short program with a relatively high level of complexity…

  25. There’s real science behind all this 

  26. Example of fixing complex code • Before: • Halstead = 160 • Very convoluted logic • Nearly impossible to read • Maintenance nightmare!!! • I’ve seen code like this  • After: • Halstead = 48 • Equivalent end-result • Much simpler logic • Much easier to read • Possible to maintain

  27. CodeXpert – SQL Scan • Answers the key developers’ question – so which SQL statements should I try to tune? • CodeXpert SQL Scan auto-magically finds all the performance needles in the haystack! • SQL Scan option is new with Toad8.5. • Scans all SQL in code for problematic SQL. User can customize definition of problematic SQL by clicking on the customization toolbar option. • Statements identified by SQL Scan can then be passed to Toad’s SQL Tuner for possible optimization (process shown in the next couple of slides).

  28. Send Problematic SQL to Toad Xpert Tuning …

  29. Let Toad Xpert Tuning Auto-Tune it for you …

  30. Toad Xpert Tuning find lots of possible rewrites

  31. Run them to find the winner …

  32. Send the best SQL back to Toad …

  33. The Toad Community • More than 500,000 users • Market-leading tool • User groups and events • Access to development team • World-class support • Toad is now available for SQL Server, DB2 and MySQL

  34. Thank You for Attending • Download a free evaluation of Toad for OracleDownload Toad • Attend a Toad for Oracle DemoToad for Oracle Events • Learn how Toad for Oracle has helped organizations just like yoursCustomer Success Stories Thank You for Your Time!

More Related