Tirando um RAIO-X do desempenho do código PL/SQL

[oracle@odbsrv01 oracle]$ mkdir /oracle/SH
[oracle@odbsrv01 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 23 10:43:49 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL>
SQL> SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> create directory PLSHPROF_DIR as '/oracle/SH';


Directory created.

SQL> -----------------------------------------------
SQL> -- Atribuir os privilégios para usuário SH
SQL> -----------------------------------------------
SQL>
SQL> grant read,write on directory PLSHPROF_DIR to SH;

Grant succeeded.

SQL> grant execute on DBMS_HPROF to SH;

Grant succeeded.

SQL> -----------------------------------------------
SQL> -- Criar as tabelas para HPROF no schema SH
SQL> -----------------------------------------------
SQL> conn sh/sh
Connected.
SQL> @?/rdbms/admin/dbmshptab.sql;

Table dropped.


Table dropped.


Table dropped.


Sequence dropped.


Table created.


Comment created.


Table created.


Comment created.


Table created.


Comment created.


Sequence created.

SQL>
SQL> -----------------------------------------------
SQL> -- Criar as tabelas auxiliares
SQL> -----------------------------------------------
SQL>
SQL> CREATE TABLE SH.CUSTOMERS_COUNTRIES
  2     (       COUNTRY_NAME            VARCHAR2(40 BYTE)       NOT NULL ENABLE,
  3     COUNTRY_REGION          VARCHAR2(20 BYTE)       NOT NULL ENABLE,
  4     CUST_ID                 NUMBER                  NOT NULL ENABLE,
  5     CUST_GENDER             CHAR(1 BYTE)            NOT NULL ENABLE,
  6     CUST_STATE_PROVINCE     VARCHAR2(40 BYTE)       NOT NULL ENABLE,
  7     CONSTRAINT cust_countr_pk PRIMARY KEY (CUST_ID)
  8     USING INDEX TABLESPACE USERS )
  9     TABLESPACE USERS ;

Table created.

SQL>
SQL> CREATE TABLE SH.SALES_SUM
  2     (       CUST_ID                 NUMBER                  NOT NULL ENABLE,
  3     AMOUNT_SOLD             NUMBER(10,2)            NOT NULL ENABLE,
  4     CONSTRAINT sale_sum_pk PRIMARY KEY (CUST_ID)
  5     USING INDEX TABLESPACE USERS )
  6     TABLESPACE USERS ;

Table created.

SQL>
SQL> -----------------------------------------------
SQL> -- Criar uma procedure no schema SH
SQL> -----------------------------------------------
SQL>
SQL> create or replace PROCEDURE PRC_REPORT_01(
  2  in_country_name    IN varchar2
  3  ) IS
  4
  5  CURSOR c_cust_count IS
  6          Select cu.cust_id, co.country_name, co.country_region, cu.cust_gender, cu.cust_state_province
  7            From customers cu, countries co
  8           Where cu.country_id = co.country_id
  9             and co.country_name = in_country_name
 10        Order By cu.cust_id;
 11
 12  CURSOR c_sales_sum IS
 13          Select s.cust_id, sum(s.amount_sold) amount_sold
 14            From sales s
 15  --          From sales s, customers_countries cc
 16  --         Where s.cust_id = cc.cust_id
 17        Group By s.cust_id
 18        Order By s.cust_id;
 19
 20  CURSOR c_report IS
 21          Select cc.country_region, cc.country_name, cc.cust_state_province, cc.cust_gender, sum(ss.amount_sold) amount_sold
 22            From customers_countries cc,
 23                 sales_sum ss
 24           Where cc.cust_id = ss.cust_id
 25        Group By cc.country_region, cc.country_name, cc.cust_state_province, cc.cust_gender
 26        Order By cc.country_region, cc.country_name, cc.cust_state_province;
 27
 28
 29  reg_cust_count   c_cust_count%ROWTYPE;
 30
 31  reg_sales_sum    c_sales_sum%ROWTYPE;
 32
 33  reg_report       c_report%ROWTYPE;
 34
 35  BEGIN
 36
 37     EXECUTE IMMEDIATE 'truncate table customers_countries';
 38     EXECUTE IMMEDIATE 'truncate table sales_sum';
 39
 40     OPEN c_cust_count;
 41
 42        LOOP
 43            FETCH c_cust_count
 44             INTO reg_cust_count;
 45
 46            EXIT WHEN c_cust_count%NOTFOUND;
 47
 48            INSERT INTO CUSTOMERS_COUNTRIES (country_name, country_region, cust_id, cust_gender, cust_state_province)
 49                                      VALUES(reg_cust_count.country_name, reg_cust_count.country_region, reg_cust_count.cust_id, reg_cust_count.cust_gender, reg_cust_count.cust_state_province);
 50            COMMIT;
 51
 52        END LOOP;
 53
 54     CLOSE c_cust_count;
 55
 56     OPEN c_sales_sum;
 57
 58        LOOP
 59            FETCH c_sales_sum
 60             INTO reg_sales_sum;
 61
 62            EXIT WHEN c_sales_sum%NOTFOUND;
 63
 64            INSERT INTO SALES_SUM (cust_id, amount_sold)
 65                            VALUES(reg_sales_sum.cust_id, reg_sales_sum.amount_sold);
 66            COMMIT;
 67
 68        END LOOP;
 69
 70     CLOSE c_sales_sum;
 71
 72     OPEN c_report;
 73
 74        LOOP
 75            FETCH c_report
 76             INTO reg_report;
 77
 78            EXIT WHEN c_report%NOTFOUND;
 79
 80            dbms_output.put_line(reg_report.country_region||'_'||reg_report.country_name||'_'||reg_report.cust_state_province||'_'||reg_report.cust_gender||' => '|| reg_report.amount_sold);
 81
 82            COMMIT;
 83
 84        END LOOP;
 85
 86     CLOSE c_report;
 87
 88
 89
 90  EXCEPTION
 91     WHEN OTHERS THEN
 92
 93        DBMS_OUTPUT.PUT_LINE('ERRO :' || SQLCODE || ' - ' || SQLERRM);
 94
 95        CLOSE c_cust_count;
 96        CLOSE c_sales_sum;
 97        CLOSE c_report;
 98
 99  END PRC_REPORT_01;
100  /

Procedure created.

SQL>