title: Oracle Insert query takes long time in Production but performs in 3 seconds in lower environment
body: <p>In my Oracle stored procedure I have a SQL query which takes very long in production but in lower environment it takes only about 3-4 seconds. Please note this query is in loop and runs for 7000 times. I have no choice other than the loop as this involves lots of logic to validate the data and can't move it to simple query.</p>
  <pre><code>    INSERT INTO GTT_POSTING_XX (meter_id,
                              period_id,
                              period_duration,
                              meter_value,
                              meter_date,
                              expected_count)
  WITH
      inputs
      AS
          (SELECT :rmeter                       rmeter,
                  :oldmeter                     oldmeter,
                  :rmaxreaddate                 rmaxreaddate,
                  :parm_value                   parm_value,
                  :end_date                     end_date,
                  :swap_date                    swap_date,
                  :period_duration              period_duration
             FROM DUAL),
      im
      AS
          (SELECT 
                  meter_id,
                  meter_period_id,
                  meter_period_duration,
                  meter_value,
                  meter_date
             FROM meter_f imf, inputs i
            WHERE meter_id = i.rmeter
              AND meter_date BETWEEN i.swap_date + 1 AND i.end_date - i.parm_value
              AND meter_period_duration = NVL (i.period_duration, meter_period_duration))
  SELECT IM.METER_ID,
         IM.METER_PERIOD_ID,
         IM.METER_PERIOD_DURATION,
         IM.METER_VALUE,
         IM.METER_DATE,
         IP.PERIOD_COUNT
    FROM IM, PERIOD_D IP
   WHERE IP.PERIOD_ID       = IM.METER_PERIOD_ID
     AND IP.PERIOD_DURATION = IM.METER_PERIOD_DURATION
  </code></pre>
  <p>The above query was part of the dynamic SQL and I picked it from the TOAD's session browser so you will see those bind variables. Also note all the tables used in the query are created with parallel degree of 4 by the previous developer.</p>
  <p>My entire process takes hardly 2 minutes in Dev and more than 20 minutes in Production. I am sure when it works in Dev and not in production, there should be configuration difference and reached my DBA already. DBA identified this as low SGA and PGA, AMM disabled, and Huge Pages enabled. But still issue is not resolved. I am not a DBA and not sure of the parameters to that caused this issue and below is what I got from my DBA after the changes.</p>
  <pre><code>    show parameter 
  
  NAME TYPE VALUE
  ------------------------------------ ----------- ------------------------------
  use_large_pages string ONLY
  
  memory_max_target big integer 0
  memory_target big integer 0
  
  sga_target big integer 29G
  ga_aggregate_target big integer 12G
  
  optimizer_index_caching integer 90
  optimizer_index_cost_adj integer 10
  
  grep Huge /proc/meminfo
  AnonHugePages: 0 kB
  HugePages_Total: 14875
  HugePages_Free: 25
  HugePages_Rsvd: 0
  HugePages_Surp: 0
  Hugepagesize: 2048 kB
  </code></pre>
  <p><strong>Oracle Version</strong></p>
  <pre><code>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    0
  PL/SQL Release 12.1.0.2.0 - Production  0
  CORE    12.1.0.2.0  Production  0
  TNS for Linux: Version 12.1.0.2.0 - Production  0
  NLSRTL Version 12.1.0.2.0 - Production  0
  </code></pre>
  <p>Appreciate your help in advance and Thank You for reading!</p>