Posted on

Oracle ERP Cloud ESS Jobs

In this previous article, we learned how to design and Develop an Oracle Cloud BI Report. In this article, we will use a simple BI report to show AP invoices for a vendor. We will register this BI report as an Enterprise Schedule Service Job.

Log in to Oracle Cloud Applications and Go to Tools in the Navigator and click on Reports and Analytics. Click on Browse Catalog to launch the BI workspace.

Posted on

Dynamic SQL Injection With Oracle ERP Cloud

In this previous article, we learned how to design and Develop an Oracle Cloud BI Report. We will use the same Report and convert it Into a Dynamic SQL Injection based Report.

Log in to Oracle Cloud Applications and Go to Tools in the Navigator and click on Reports and Analytics. Click on Browse Catalog to launch the BI workspace.

Posted on

Oracle Cloud BI Reports

Oracle Cloud applications are gaining space in the industry at a fast pace and as more implementations happen in Oracle Cloud, it has become important to query the data in the cloud and develop reports based on business requirements. Here are the steps to develop a cloud BI Report:

  • Login to Oracle Cloud Applications and Go to Tools in the Navigator and click on Reports and Analytics. Click on Browse Catalog to launch the BI workspace.
  • Select the "Custom" folder to create your own folder to keep reports.

Posted on

Oracle SQL Performance Plan Review Automation

Why Do We Need a SQL Performance Review?

  1. The current code review process is manual and doesn’t capture the Explain Plan for all modified queries.
  2. Currently, lead devs, along with developers, run Explain Plans manually in Toad/SQL Developer.
  3. To build an automated tool to capture problematic queries from an Explain Plan perspective and reduce manual oversight.
  4. 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
111


 
1 
SELECT DISTINCT obj.object_name
2 

          

          

           

            

             3
            

           

           
                        ,program_line#

          

          

           

            

             4
            

           

           

          

          

           

            

             5
            

           

           
                        , cpu_time / 1000000 AS cpu_time_in_secs

          

          

           

            

             6
            

           

           

          

          

           

            

             7
            

           

           
                        , elapsed_time / 1000000 AS elapsed_time_in_secs

          

          

           

            

             8
            

           

           

          

          

           

            

             9
            

           

           
                        ,buffer_gets

          

          

           

            

             10
            

           

           

          

          

           

            

             11
            

           

           
                        ,disk_reads

          

          

           

            

             12
            

           

           

          

          

           

            

             13
            

           

           
                        ,end_of_fetch_count AS rows_fetched_per_execution

          

          

           

            

             14
            

           

           

          

          

           

            

             15
            

           

           
                        ,executions

          

          

           

            

             16
            

           

           

          

          

           

            

             17
            

           

           
                        ,optimizer_cost

          

          

           

            

             18
            

           

           

          

          

           

            

             19
            

           

           
                        ,vsql.sql_id

          

          

           

            

             20
            

           

           

          

          

           

            

             21
            

           

           
                        ,NULL operation

          

          

           

            

             22
            

           

           

          

          

           

            

             23
            

           

           
                        ,NULL options

          

          

           

            

             24
            

           

           

          

          

           

            

             25
            

           

           
                        ,vsplan.plan_hash_value

          

          

           

            

             26
            

           

           

          

          

           

            

             27
            

           

           
                        ,sql_text

          

          

           

            

             28
            

           

           

          

          

           

            

             29
            

           

           
                        , ( SUBSTR ( DBMS_LOB.SUBSTR ( sql_fulltext

          

          

           

            

             30
            

           

           

          

          

           

            

             31
            

           

           
                                                      ,4000

          

          

           

            

             32
            

           

           

          

          

           

            

             33
            

           

           
                                                      ,1

          

          

           

            

             34
            

           

           

          

          

           

            

             35
            

           

           
                                                     )

          

          

           

            

             36
            

           

           

          

          

           

            

             37
            

           

           
                                    , INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext

          

          

           

            

             38
            

           

           

          

          

           

            

             39
            

           

           
                                                               ,4000

          

          

           

            

             40
            

           

           

          

          

           

            

             41
            

           

           
                                                               ,1

          

          

           

            

             42
            

           

           

          

          

           

            

             43
            

           

           
                                                              ), '/*' ) + 2

          

          

           

            

             44
            

           

           

          

          

           

            

             45
            

           

           
                                    , INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext

          

          

           

            

             46
            

           

           

          

          

           

            

             47
            

           

           
                                                               ,4000

          

          

           

            

             48
            

           

           

          

          

           

            

             49
            

           

           
                                                               ,1

          

          

           

            

             50
            

           

           

          

          

           

            

             51
            

           

           
                                                              ), '*/' ) - INSTR ( DBMS_LOB.SUBSTR ( sql_fulltext

          

          

           

            

             52
            

           

           

          

          

           

            

             53
            

           

           
                                                                                                   ,4000

          

          

           

            

             54
            

           

           

          

          

           

            

             55
            

           

           
                                                                                                   ,1

          

          

           

            

             56
            

           

           

          

          

           

            

             57
            

           

           
                                                                                                  ), '/*' ) - 2

          

          

           

            

             58
            

           

           

          

          

           

            

             59
            

           

           
                                   )

          

          

           

            

             60
            

           

           

          

          

           

            

             61
            

           

           
                          ) release_string

          

          

           

            

             62
            

           

           

          

          

           

            

             63
            

           

           
                        ,NULL error_flag

          

          

           

            

             64
            

           

           

          

          

           

            

             65
            

           

           
                        ,NULL error_message

          

          

           

            

             66
            

           

           

          

          

           

            

             67
            

           

           
                        ,loads

          

          

           

            

             68
            

           

           

          

          

           

            

             69
            

           

           
                        ,first_load_time

          

          

           

            

             70
            

           

           

          

          

           

            

             71
            

           

           
                        ,user_io_wait_time

          

          

           

            

             72
            

           

           

          

          

           

            

             73
            

           

           
                        ,rows_processed

          

          

           

            

             74
            

           

           

          

          

           

            

             75
            

           

           
                        ,last_load_time

          

          

           

            

             76
            

           

           

          

          

           

            

             77
            

           

           
                        ,vsql.module

          

          

           

            

             78
            

           

           

          

          

           

            

             79
            

           

           
                        ,fnd_global.user_id created_by

          

          

           

            

             80
            

           

           

          

          

           

            

             81
            

           

           
                        ,SYSDATE creation_date

          

          

           

            

             82
            

           

           

          

          

           

            

             83
            

           

           
                        ,fnd_global.user_id last_updated_by

          

          

           

            

             84
            

           

           

          

          

           

            

             85
            

           

           
                        ,SYSDATE last_update_date

          

          

           

            

             86
            

           

           

          

          

           

            

             87
            

           

           
                        ,'-1' last_update_login

          

          

           

            

             88
            

           

           

          

          

           

            

             89
            

           

           
         FROM            gv$sql vsql

          

          

           

            

             90
            

           

           

          

          

           

            

             91
            

           

           
                        ,gv$sql_plan vsplan

          

          

           

            

             92
            

           

           

          

          

           

            

             93
            

           

           
                        ,all_objects obj

          

          

           

            

             94
            

           

           

          

          

           

            

             95
            

           

           
         WHERE           1 = 1

          

          

           

            

             96
            

           

           

          

          

           

            

             97
            

           

           
         AND             vsql.sql_id = vsplan.sql_id

          

          

           

            

             98
            

           

           

          

          

           

            

             99
            

           

           
         AND             vsql.sql_fulltext NOT LIKE '%sql_text%'

          

          

           

            

             100
            

           

           

          

          

           

            

             101
            

           

           
         AND             vsql.program_id = obj.object_id(+)

          

          

           

            

             102
            

           

           

          

          

           

            

             103
            

           

           
         AND             vsql.sql_fulltext LIKE lv_pattern

          

          

           

            

             104
            

           

           

          

          

           

            

             105
            

           

           
         AND             TO_DATE ( last_load_time, 'YYYY-MM-DD/HH24:MI:SS' ) >= ( SYSDATE - p_hours_from / 24 )

          

          

           

            

             106
            

           

           

          

          

           

            

             107
            

           

           
         AND             nvl(obj.object_name, '-') = NVL(p_program,nvl(obj.object_name, '-'))

          

          

           

            

             108
            

           

           

          

          

           

            

             109
            

           

           
         AND             nvl(vsql.module, '-') = NVL(p_module,nvl(vsql.module, '-'))

          

          

           

            

             110
            

           

           

          

          

           

            

             111
            

           

           
         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.


Sample Output