Sample Output

Why Do We Need a SQL Performance Review?

The current code review process is manual and doesn’t capture the Explain Plan for all modified queries.
Currently, lead devs, along with developers, run Explain Plans manually in Toad/SQL Developer.
To build an automated tool to capture problematic queries from an Explain Plan perspective and reduce manual oversight.
To provide performance audits with data points.

Solution

Oracle stores all the SQL database in-page memory and indexes it by SQL ID in gv$sqltext
During development (PLSQL/Java/OA Framework/XML Publisher), tag all the desired SQL queries with a code comment (Release Name/User Story Number).
Develop a PLSQL program with below features:
- Analyze the Execution Plan of queries executed by a concurrent program/Java program/OAF code/forms/reports/BI publisher reports.
- Generate a report with queries which could impact performance. For example, queries with FULL TABLE SCAN, MERGE CARTESIAN JOIN, FULL INDEX SCAN.
- Capability to categorize queries at User Story, Sprint, Release, and Scrum Team levels based on program input parameters.
- Capability to analyze queries executed by a concurrent program/package/Java modules.
- Capture data of relevant queries analyzed in a table for future analysis and dashboards.
- Store the generated Explain Plan in a database table for audit purposes.
In the Oracle E-Business Suite world, this program can be registered as an executable of concurrent programs and assigned to a request group.
Before a release migration, the SysAdmin can put together a business process to execute this concurrent program to review the Explain Plan for the SQL queries for that release and catch any trouble making queries well in advance.

Sample Query

    SQL
   
 

     
    

      

     

      

     

     

    
x

          

         

          

         

            
          
SELECT DISTINCT obj.object_name
                        ,program_line#
                        , cpu_time / 1000000 AS cpu_time_in_secs
                        , elapsed_time / 1000000 AS elapsed_time_in_secs
                        ,buffer_gets
                        ,disk_reads
                        ,end_of_fetch_count AS rows_fetched_per_execution
                        ,executions
                        ,optimizer_cost
                        ,vsql.sql_id
                        ,NULL operation
                        ,NULL options
                        ,vsplan.plan_hash_value
                        ,sql_text
                        , ( SUBSTR ( DBMS_LOB.SUBSTR ( sql_fulltext
                                                      ,4000
                                                      ,1
                                                     )
                                    , INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext
                                                               ,4000
                                                               ,1
                                                              ), '/*' ) + 2
                                    , INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext
                                                               ,4000
                                                               ,1
                                                              ), '*/' ) - INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext
                                                                                                   ,4000
                                                                                                   ,1
                                                                                                  ), '/*' ) - 2
                                   )
                          ) release_string
                        ,NULL error_flag
                        ,NULL error_message
                        ,loads
                        ,first_load_time
                        ,user_io_wait_time
                        ,rows_processed
                        ,last_load_time
                        ,vsql.module
                        ,fnd_global.user_id created_by
                        ,SYSDATE creation_date
                        ,fnd_global.user_id last_updated_by
                        ,SYSDATE last_update_date
                        ,'-1' last_update_login
         FROM            gv$sql vsql
                        ,gv$sql_plan vsplan
                        ,all_objects obj
         WHERE           1 = 1
         AND             vsql.sql_id = vsplan.sql_id
         AND             vsql.sql_fulltext NOT LIKE '%sql_text%'
         AND             vsql.program_id = obj.object_id(+)
         AND             vsql.sql_fulltext LIKE lv_pattern
         AND             TO_DATE ( last_load_time, 'YYYY-MM-DD/HH24:MI:SS' ) >= ( SYSDATE - p_hours_from / 24 )
         AND             nvl(obj.object_name, '-') = NVL(p_program,nvl(obj.object_name, '-'))
         AND             nvl(vsql.module, '-') = NVL(p_module,nvl(vsql.module, '-'))
         ORDER BY        last_load_time DESC;

      

     

fnd_file.put_line(fnd_file.output, 'String..') – This can be used to print the SQL plan in a concurrent program output file.

Lab Question

We must create a program that do the following:

Allow the user to type in a student name the coorosponds with the student's exam score.

The program will be able to accpet exam scores till the user types in the phrase "allDone".

The code will determine which student has the highest score and give the name and the score to the user.

Here is an example of what will be shown in the console....

Sample Input
Bob 82
Mary 90
James 87
alldone

Sample Output
Mary has the highest score. Her score is: 90.

Question

This line of code allows the user to type in the name of student and the grade that goes with the name:

//Allows for name and score to be type into system. for(count = 0; count < numberOfStudents; count++) { //Prompts user to type name of student. System.out.println("Type in the name of student " + count + ":"); //Allows name of student to be stored in array. studentName [count] = input.nextLine(); //Prompts user to type grade of student. System.out.println("Type in the score of student " + count + ": "); //Allows grade of student to be assigned in array. studentScore [count] = input.nextDouble(); }

I get an error message when I used this code. My question is this: In other words, how do I reformat this piece of code to allow the user to type in a name seporataly along with the corrospoindng exame score?

Tag: Sample Output

Oracle SQL Performance Plan Review Automation