Toad Code Analysis

    Aug 28, 2017 1:12:11 PM by Dan Hotka

    Hi,

    Code Analysis has been in Toad for a while now.  If you are using a slightly older version of Toad, it might be called Code Review, found under the same menu path.

    Code Analysis gives you or your project team useful information about a particular routine or set of routines. 

    I find this information useful when starting to work on a PL/SQL routine.  I can quickly assess the complexity of the routine, any issues that the review notices, and the complexity of any SQL within the routine. 

    A project manager can use this same information to make sure they have the right resources for each part of the project. 

    CREATE OR REPLACE PROCEDURE looping_example
    IS
       cursor emp_cur is
            select ename
            from emp;
       loop_counter   NUMBER := 0;
       ename   varchar2(10);
      
    BEGIN
       open emp_cur;
     
       fetch emp_cur into ename;
     
       while emp_cur%FOUND
       LOOP
          loop_counter := loop_counter + 1;
          DBMS_OUTPUT.put_line
              ('Record ' || loop_counter || ' is Employee ' || ename );
          fetch emp_cur into ename;
       END LOOP;
     
       DBMS_OUTPUT.put_line ('Procedure Looping Example is done');
      
       close emp_cur;
      
    END;

    This is my simple looping routine that I’ve used for years to demonstrate many features of Toad, including the debugger and the profilers. 

    I will run Code Analysis on this simple routine for my examples below.

    Accessing Code Analysis

    To use:  Database -> Diagnose -> Code Analysis.

     

     Accessing PLSQL Code

    Now, click on Open from File system or Open from the database.  Select your module or modules for evaluation.  You can multi-select.  You can select functions, procedures, and/or packages.

    Code Analysis Panel

    On the dropdown menu next to the green button, make sure you have selected ‘Run Review and SQL Scan’.

    You run this report on one item at a time.  Once run, you can then click and see the reports at any time.  Rerun the report after making code changes on the same item to update the reports.  Make sure to save the metrics from the prior run for easy comparison to make sure you improved things and didn’t make anything worse (such as code violations or invalid SQL).  I would also look to make sure I didn’t accidentally make the routine more difficult to maintain next time it needs changes.

     

    Useful Code Analysis Metrics

    This report shows six main areas across the top of the interface that I would like as a manager:

    • Number of statements in the code
    • Toad Code Rating
    • Halstead Volume – a rating of computational complexity
    • McCabes Cyclomatic – a rating of overall module complexity
    • Maintainability Index – a simple scale of maintainable code
    • SQL Scan summary – SQL complexity has four areas:
      • Problematic SQL
      • Complex SQL
      • Simple SQL
      • Invalid SQL

    A manager can use this simple scale when assigning resources to the project and to quickly see the status of a current project or an overall rating of a completed project.

    The Toad rating is a simple 1 through 4 rating; ‘1’ being simple and ‘4’ being quite complex.  At a minimum, any manager can see the complexity of this routine, using this simple rating, without going much further.

    If I were a programmer, I would pay particular attention to the SQL Scan areas, making sure that there were no invalid SQL statements for sure, then maybe reviewing what this analysis deemed as complex. 

    Again, if I were a programmer, I’d review the lower left panel if there were any rule violations.  I would be sure to fix these.  The violations would be listed out if there were any.

    And last, if I were a programmer, I would feel good about raising the maintainability index.

    If I were the DBA or person in charge of code checkin/checkout… I would want to see this report for any module going into production.

    This type of analysis might benefit those who are doing application evaluations.  This analysis will give the person doing the evaluation very useful information about the code very quickly.  I would look at the code violations, the complexity, and the results of the SQL Scan for sure.

    The main interface allows you to open and analyze individual program units or packages.  You can open a variety of program units but the Report (on the Report tab, lower left panel) will be more general. 

    When these reports are run against single program units or single packages, the report contains considerably more detail.

    The report identifies program modules that contain SQL and what that SQL does: ‘CRUD’…create/retrieve/update/delete from the database.

    The Halstead Volume is calculation/code function complexity.  Items considered are any calculation, length of the calculation, flow logic, depth of flow logic, etc.  The Report tab shows all the detail being considered.

    0-1000 – an average programmer should be able to comprehend and maintain

    1001-3000 – code is more challenging and would take more skills to comprehend and change content

    >3000 – too complex.  Redesign this module.

     

    McCabe’s Cyclomatic Complexity is a different and simpler metric for identifying program unit complexity:

    1-10 – Simple program

    11-20 – More complex program

    21-50 – complex program

    >50 – untestable program

     

    My personal favorite: The Maintainability Index (MI).  How maintainable is the code? This metric considers the output of both the Halstead and McCabe’s complexity scales.  I would always want to raise this number while working on code.  Making code more difficult to maintain (lowering this number) would not be a good thing. 

    0-64 – difficult to maintain

    65-84 – moderate maintainability

    >85 – easy to maintain

     

    I used the Toad Help and then found Code Analysis in the index for these descriptions and additional information on how to use the interface. 

    I find these reports useful.  If your shop does code walkthroughs (not common, but I’ve been in shops that have used this technique), these reports might be invaluable for those reviewing the code, especially if they are new to the code or application.

    As always, you can ask me for the scripts and sample code.

    I hope you find these tips useful in your day-to-day use of the Oracle RDBMS.

     

    Dan Hotka

    Author/Instructor/Oracle Expert

    www.DanHotka.com

    Dan@DanHotka.com

     

    Tags: Oracle

    Dan Hotka

    Written by Dan Hotka

    Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari. Dan's most recent book is Toad for Oracle Unleashed