#StackBounty: #oracle #performance #wait-types #oracle-19c Reduce file header block

Bounty: 50

I’m in the middle of my first Statspack analysis, and one of the most time consuming events is Buffer Busy Waits (around 30% time). I checked out the Buffer Wait statistics, and it seems like the most time it’s waiting in File Header Block (about 10x more time than the second wait event).
Could you give me a hint how can I approach this issue? The only advice I found online in this site saying

File Header Block – Most likely extent allocation problems, look at extent size on tablespace and increase the extent size to there are few extent allocations and less contention on the File Header Block.

I was googling again about increasing extent size and I’m bit lost here. Seems that extent size is assigned to particular tablespace, and I would need to replace my tablespaces in order to change that, but that is only for the initial one. How can I increase it without replacing all tablespaces in my DB?

Is there maybe any other stuff I should look into when I see lot of File Header Block waiting time?

Cheers


Update1:

Tablespace
------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
AA_AAA
       438,217     220    3.8     1.0      788,012      396      2,007   14.3
UNDOTBS1
             0       0    0.0               21,104       11     21,647  993.6
TBS_PERFSTAT
           143       0   40.1     1.0        3,340        2          0    0.0
SYSTEM
            26       0   14.6     1.0          114        0          0    0.0
SYSAUX
            14       0    4.3     1.0           98        0          0    0.0
AA_AAA_IDX
            74       0   15.7     1.0            2        0          4    7.5
BB_BBB
             0       0    0.0                   54        0        820  993.0
BB_BBB_IDX
             0       0    0.0                   40        0      1,527  993.5
          -------------------------------------------------------------
File IO Stats  DB/Inst: ORCL/orcl  Snaps: 9833-9834
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
                        Av   Mx                                             Av
                 Av     Rd   Rd    Av                    Av        Buffer BufWt
         Reads Reads/s (ms)  Bkt Blks/Rd       Writes Writes/s      Waits  (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
AA_AAA                   /oradata/data_files/ORCL/eim.dbf
             0       0                             54        0        820  993.0

AA_AAA_IDX               /oradata/data_files/ORCL/eim_idx.dbf
             0       0                             40        0      1,527  993.5

BB_BBB                   /oradata/data_files/ORCL/uel.dbf
       438,217     220   3.8   1     1.0      788,012      396      2,007   14.3

BB_BBB_IDX               /oradata/data_files/ORCL/uel_idx.dbf
            74       0  15.7   1     1.0            2        0          4    7.5

SYSAUX                   /oradata/data_files/ORCL/sysaux01.dbf
            14       0   4.3   1     1.0           98        0          0

SYSTEM                   /oradata/data_files/ORCL/system01.dbf
            26       0  14.6   1     1.0          114        0          0

TBS_PERFSTAT             /oradata/data_files/ORCL/statspack_data01.dbf
           143       0  40.1   1     1.0        3,340        2          0

UNDOTBS1                 /oradata/data_files/ORCL/undotbs01.dbf
             0       0                         21,104       11     21,647  993.6

          -------------------------------------------------------------
File Read Histogram Stats  DB/Inst: ORCL/orcl  Snaps: 9833-9834
->Number of single block reads in each time range
->Tempfiles are not included
->ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
    0 - 2 ms     2 - 4 ms    4 - 8 ms     8 - 16 ms   16 - 32 ms       32+ ms
------------ ------------ ------------ ------------ ------------ ------------
AA_AAA_IDX               /oradata/data_files/ORCL/uel_idx.dbf
          19            0            0            0            0            0

SYSAUX                   /oradata/data_files/ORCL/sysaux01.dbf
          10            0            0            0            0            0

AA_AAA                   /oradata/data_files/ORCL/uel.dbf
     392,355            0            0            0            0            0

TBS_PERFSTAT             /oradata/data_files/ORCL/statspack_data01.dbf
          29            0            0            0            0            0

SYSTEM                   /oradata/data_files/ORCL/system01.dbf
          18            0            0            0            0            0

          -------------------------------------------------------------


Get this bounty!!!

#StackBounty: #azure #database #oracle #azure-networking Integration of Azure with Oracle Cloud Infrastructure (OCI) ORA-03113: end-of-…

Bounty: 100

I’m trying to integrate Azure and OCI using this approach and this article.

Now, I have the infrastructure up and running. It consists of a VM in Azure, an Autonomous Database (ATP) Oracle Cloud Infrastructure (OCI), and a Java application on the VM. The application successfully connected to the database.

However, after some period of time application fails with:

ORA-03113: end-of-file on communication channel

Process ID: 86437

Session ID: 57114 Serial number: 29955

How can I identify where is the problem (Azure, OCI, etc.) in order to have an idea of how to fix it?


Get this bounty!!!

#StackBounty: #oracle #linux #sqlplus #oracle-sqlcl oracle sqlcl and sqlplus – seperate login.sql on linux

Bounty: 50

I feel I’m probably missing something obvious here, but I can’t see an easy way to have a separate login.sql for sqlplus and sqlcl

This is problematic for me as the errors thrown up by sqlplus for the set commands it doesn’t understand interfere with various scripts.

I think I could do it with aliases that change the SQLPATH prior to starting sqlcl, but would prefer a cleaner way if possible.

Is there a separate file name that only sqlcl will look for? Or separate location?


Get this bounty!!!

#StackBounty: #oracle #oracle-11g-r2 #oracle-11g #sqlplus How to turn on the expanded mode in Oracle SQL*Plus?

Bounty: 50

As in the switch x of PostgreSQL.

For more information on the PostgreSQL expanded mode, fetch https://serverfault.com/questions/618642/psql-expanded-mode-equivalency-for-mysql.

Both PostgreSQL and MySQL have mechanisms for getting an expanded mode, I guess Oracle should have it too.

I am using the instant client 11.2 by the way.


Get this bounty!!!

#StackBounty: #oracle #join #oracle-12c #view CREATE VIEW WITH LEFT JOIN – FILTER RIGHT DEPENDING ON LEFT

Bounty: 50

I am using Oracle 12.1.
I have the following tables, simplified for the question :
(Note that the XXX for entity ID does represent the same ID being repeated)

Events

date_event | entity_id | used
-------------------------------
2020-09-01 | XXX       | ...
2020-08-15 | XXX       | ...
2020-07-01 | XXX       | ...
...        | ...       | ...

Contract

date_contract | entity_id | capacity
-------------------------------------
2020-08-25    | XXX       | 1000
2020-07-20    | XXX       | 1000
2020-06-22    | XXX       | 1000
...

Modifications

date_modification | entity_id | capacity | month_capacity
-------------------------------------------------------------
2020-08-10        | XXX       | 2000     | 500

I would like to create a view like this :

date_event | entity_id | date_situation | capacity | month_capacity | used
--------------------------------------------------------------------------
2020-09-01 | XXX       | 2020-08-25     | 1000     | NULL           | 200
2020-08-15 | XXX       | 2020-08-10     | 2000     | 500            | 200
2020-07-01 | XXX       | 2020-06-22     | 1000     | NULL           | 200

For one entity_id :

  • Events table has one record per day
  • Contracts has one record per month, but technically could be whenever, there is no business rule about being monthly
  • Modifications table has one record when an edit is made ; can be once a month, once a year, once a week, never, …

The fields date_situation, capacity, and month_capacity come from either Contracts or Modifications depending on the following conditions :

  • The first (ordering by date_contract) record in Contracts that has a date_contract more recent than date_event, if there is no record in Modifications with a more recent date_modification in the same month. If there is such a record, then it comes from Modifications.
  • If there is neither a more recent Contracts or Modifications record, then we want the most recent one between the most recent Contracts record and the most recent Modifications record. But it can be the most recent Modifications record only if it is in the same month as date_event.

So, to sum it up, data comes from the most recent record in Contracts for the same entity_id, unless there is a more recent Modifications record, in the same month as the record from Events.
And if so, we want the most recent Modifications record of the same month.

I hope the explaination and the example view are clear enough. So far we have something with (SELECT) LEFT JOIN (SELECT) LEFT JOIN (SELECT) but it takes the most recent Contracts and Modifications records in the table. I need them relative to date_event, and I can’t seem to "inject" that in the subqueries.

How would one proceed to represent such a view ?


Get this bounty!!!

#StackBounty: #oracle-linux #oracle Oracle Linux logon screen without login box

Bounty: 100

today my Oracle Linux Server 7.3 (installed on a Proxmox VM) just shows a gray window, where normaly the login prompt would be. The gray background is the regular "noisy" Oracle Server 7 wallpaper. I tried using ctrl+alt+FX … after some seconds the whole server reboots.

Pinging the machine does not work. Can’t connect to the oracle server either.

Has anyone an idea what could be wrong?!

Edit: I managed to start a single user shell but have no idea where to start honestly.


Get this bounty!!!

#StackBounty: #oracle #oracle-12c Oracle – Monitoring tablespace warnings and critical events via Microsoft Windows Event log

Bounty: 50

I would like to monitor tablespace warning and critical events of my Oracle database (12c Standard Release 12.2.0.1.0) via Microsoft Windows Event Viewer and attach tasks to it.

Right now only a few event log entries of the Oracle db are available in Event Viewer – i assume this is because audit_trail is currently set to DB.

Is it the correct way to set audit_trail to OS to get the desired events in Windows Event Viewer?

Anything i have to consider regarding this change?


Get this bounty!!!

#StackBounty: #oracle #stored-procedures Update multiple columns based on percentage wise calculation in Oracle

Bounty: 50

NOTE: Updating total question with sample data and output.

I need to update some columns by comparing from another table and update the columns percentage wise. So here I go.

first of all , the query to get CMM approved length is below with other columns which I need

select CIRCLE,regexp_substr(MP,'[^/]+',1,1)MPNAME,regexp_substr(MP,'[^/]+',1,2)MPCODE,
 SPAN_TYPE,SPAN_LINK_ID,NE_LENGTH AS NE_LEN,
 ROUTE_APPROVED_BY_CMM as CMM_APPROVED_LENGTH from 
 TBL_FIBER_INV_CMP_REPORT_MV
 where CMM_APPROVED_DATE IS NOT NULL OR ROUTE_APPROVED_BY_CMM > 0 OR 
 JOB_PROGRESS_FLAG = 1;

and the output of above query looks like this below image

enter image description here

Now comes the second part which is calculation of the percentage wise ratio

CASE 1: If The CMM approved length in above image is 70km and NE length from (NE,UG, AR len from NE.MV_SPAN@DB_LINK_NE_VIEWER)comes out to be 100Km then divide (UG=80Km and AR=20Km)

Then percentage of bifurcation would be 80% UG_length and 20% AR_length. So, against 70km of CMM approved kilometer, TBL_FIBER_INV_SIGN_OFF_SHEET should update the column as UG = 56Km and AR = 14Km.
the bifurcation should be updated in below table TBL_FIBER_INV_SIGN_OFF_SHEET

and the table sample data looks like below:-

enter image description here

CASE 2: IF CMM approved length and NE length is same. for ex: 70km (UG = 60 Km and AR=10Km) then in TBL_FIBER_INV_SIGN_OFF_SHEET table should update UG = 60Km and AR = 10Km.

Below are the table description of both the tables.

Table:- NE.MV_SPAN@DB_LINK_NE_VIEWER

Name                           Null Type           
------------------------------ ---- -------------- 
OBJECTID                            NUMBER         
SPAN_NAME                           VARCHAR2(20)   
CATEGORY_NAME                       VARCHAR2(20)   
TYPE_NAME                           VARCHAR2(20)   
SPAN_REF_NAME                       VARCHAR2(20)   
RJ_NETWORK_ENTITY_ID                VARCHAR2(39)   
RJ_SPAN_RJID                        VARCHAR2(50)   
RJ_ROUTE_NAME                       VARCHAR2(200)  
RJ_NETWORK_CATEGORY                 VARCHAR2(50)   
RJ_LINK_ID                          VARCHAR2(25)   
RJ_SPAN_ID                          VARCHAR2(25)   
RJ_STATE_NAME                       VARCHAR2(50)   
RJ_STATE_CODE                       VARCHAR2(2)    
RJ_R4G_STATE_NAME                   VARCHAR2(50)   
RJ_R4G_STATE_CODE                   VARCHAR2(2)    
RJ_MAINTENANCE_ZONE_NAME            VARCHAR2(50)   
RJ_MAINTENANCE_ZONE_CODE            VARCHAR2(25)        
RJ_CONSTRUCTION_METHODOLOGY         VARCHAR2(50)   
SHAPE                               ST_GEOMETRY()  
CALCULATED_LENGTH                   NUMBER(38,8)





Table:- TBL_FIBER_INV_SIGN_OFF_SHEET

Name                Null Type           
------------------- ---- -------------- 
JOB_ID                   NUMBER         
CIRCLE                   NVARCHAR2(100) 
MAINTENANCEZONENAME      NVARCHAR2(100) 
MAINTENANCEZONECODE      NVARCHAR2(50)  
SPAN_LINK_ID             NVARCHAR2(21)  
SPAN_TYPE                NVARCHAR2(20)  
NE_LENGTH                NUMBER         
HOTOOFFERDATE            DATE           
FSA_UG                   NUMBER         
FSA_AERIAL               NUMBER    

Please help me to achieve this procedure of what ever will be easy

UPDATE To get UG_length and AR_length from NE.MV_SPAN@DB_LINK_NE_VIEWER here is the logic

SELECT ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH,
 ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
 ,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH
 FROM NE.MV_SPAN@DB_LINK_NE_VIEWER)

Also some common columns from all the tables are

SPAN_LINK_ID from TBL_FIBER_INV_SIGN_OFF_SHEET table

SPAN_LINK_ID from TBL_FIBER_INV_CMP_REPORT_MV table

AND

RJ_LINK_ID, RJ_SPAN_ID, CALCULATED_LENGTH, RJ_CONSTRUCTION_METHODOLOGY FROM NE.MV_SPAN@DB_LINK_NE_VIEWER table

THE LOGIC WHICH I TRIED TILL NOW

CREATE OR REPLACE PROCEDURE UPD_UG_AR_BY_CMM AS 
   BEGIN


    for cur_r in (
    select circle, 
                   regexp_substr(MP,'[^/]+',1,1)MAINTENANCE_ZONE_NAME,
                   regexp_substr(MP,'[^/]+',1,2)MAINTENANCE_ZONE_CODE,
                   SPAN_TYPE,
                   SPAN_LINK_ID,
                   NE_LENGTH,
                   ROUTE_APPROVED_BY_CMM
                   from TBL_FIBER_INV_CMP_REPORT_MV    
                  where CMM_APPROVED_DATE IS NOT NULL 
                  OR ROUTE_APPROVED_BY_CMM > 0 
                  OR JOB_PROGRESS_FLAG = 1
        )
        
  LOOP

        IF cur_r.ROUTE_APPROVED_BY_CMM >  SELECT ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH,
                                           ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
                                           ,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH
                                           FROM NE.MV_SPAN@DB_LINK_NE_VIEWER
        THEN
        
          BEGIN
              UPDATE TBL_FIBER_INV_SIGN_OFF_SHEET 
              SET  FSA_UG = UG_LENGTH,  --- divide 80% of NE_LENGTH
                   FSA_AERIAL = AR_LENGTH    --- divide 20% of NE_LENGTH
              WHERE CUR_R.SPAN_LINK_ID =  RJ_SPAN_ID
                
          END
        
        

    NULL;
  END UPD_UG_AR_BY_CMM;

but iam stuck at calculation of percentage wise ratio.


Get this bounty!!!

#StackBounty: #oracle #trigger #oracle-11g #plsql #debugging Why is an Oracle Package Variable Intermittently Incorrect after Multiple …

Bounty: 200

I am supporting an application that runs on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production.

We have a table that stores hourly data that uses triggers call a package to sync to a monthly table that stores a total amount.

When the hourly table is updated, the on update trigger saves the old amount to a table package variable in this format: primary key fields, amount.

TYPE HOURLY_CHANGE_TAB IS TABLE OF HOURLY_REC INDEX BY BINARY_INTEGER;

TYPE HOURLY_REC IS RECORD (
      KEY       NUMBER(10),
      SUB_KEY   NUMBER,
      MONTH     VARCHAR2(6),
      AMOUNT NUMBER);

Amount is set to -old_amount. It then adds the new amount to amount. It does this for each hour record that gets updated. So if there are 120 hours in a month, it would subtract the old amount 120 times from the variable, and add the new amount 120 times to get the total change. An after statement trigger or manual call would update the months record with this total and clear the package variable. When we are updating a large number of hours, we set a flag to disable the month update and then manually call the update method once at the end for performance. Regardless, the issue has already happened by the time this method gets called.

When I update a deal that runs for multiple years, the total amount is off by new amount – old amount in a random number of months. So if old amount was 100 per hour and new amount is 10 per hour then the amount we push to the month record is -90 what it should be. The majority of the months would be correct. I have seen zero, one, two, and four months broken after each update / rollback testing run. Each time I run the exact same update and then rollback after checking for the error.

I logged every time the package variable changes to a new table and saw the following:
LOGGED DATA

KEY and SUB_KEY are keys in this example. Month is the monthly record our hour is part of. The amount column shows the amount to be added to the package variable. Index is the index of the table variable that we are updating for this row. Starting amount it the value of the amount column at the start of logging the update. Calc Amount is what the package variable should store after we add the amount to the starting amount. Ending Amount is the value retrieved from the package variable after the update is saved. Row index is the incrementing unique sequence in the logging table to keep track of the order in which the updates ran. The correct amount is a window function summing the amount ordered by row_index, partitioned by the keys, to show what the package variable should store on every row.

Notice what happens between rows 2 and 3. The ending amount on 2 is the correct value of 540. The starting amount on 3 is 550, shifted by new volume(20) – old volume(10). This relationship held true for an update of 100 to 10, the shifted amount was -90, not 10. And yet there is no gap in the row index. We go straight from 16188 to 16189 without a gap. I created a copy of the package and removed everything but the method to update the package variable. There is no other path to update the table variable except via the method that is logged. The package variable is defined within the package body so no other package can update it, and I deleted all other methods in the package but the one to update it. I can’t fathom how this variable is changing between the end of one method and the start of the next.

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS

   PKG_HOUR_COUNT  NUMBER := 0;
   HOUR_TAB            HOURLY_CHANGE_TAB ;
...

Again, the number of months affected by this issue is random, and they are all broken by the same amount, one month isn’t offsetting another by some off by one error.

Note that I have endeavored to reduce the following code to a minimum version of the code. Table names and columns have been changed. If I attempt to run the trigger code in a loop versus running an update the issue does not happen.

I was able to create a new version of the package, delete everything not related to these calls and reproduce the issue and was successful. I tried to create a mini copy of the table with just the rows I needed and may have been able to reproduce the issue once. I know that’s vague but it hasn’t worked since on the mini copied table.

SUB_KEY is not used in the package variable since it only defines the hours in this table so all hours with the same KEY and SUB_KEy will be in the same month, regardless of SUB_SUB_KEY.

Table Definition:

CREATE TABLE ORDER_HOURS
(KEY NUMBER(12,0) NOT NULL ENABLE, 
    SUB_KEY NUMBER(6,0) NOT NULL ENABLE, 
    SUB_SUB_KEY NUMBER(3,0) NOT NULL ENABLE, 
    GMT_TIME DATE NOT NULL, 
    DAY DATE NOT NULL ENABLE, 
    HOUR VARCHAR2(4 BYTE) NOT NULL ENABLE, 
    AMOUNT NUMBER(10,3) NOT NULL ENABLE, 
    CONFIRMED_FLAG VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,        
     CONSTRAINT HOUR_PK PRIMARY KEY (KEY, SUB_KEY, SUB_SUB_KEY, GMT_TIME)
  USING INDEX 
   ) PARTITION BY RANGE ("DAY") INTERVAL (NUMTOYMINTERVAL(3,'MONTH'))
   ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) ); 

Trigger:

CREATE OR REPLACE TRIGGER UPDATE_HOUR BEFORE
  UPDATE OF AMOUNT
    ON ORDER_HOURS
    FOR EACH ROW    
    BEGIN 
        IF :NEW.CONFIRMED_FLAG = 'Y' AND :OLD.CONFIRMED_FLAG = 'Y' 
            THEN RAISE_APPLICATION_ERROR(-20914, 'You cannot Update.');
        ELSE
            IF :NEW.CONFIRMED_FLAG = 'A' THEN
                :NEW.CONFIRMED_FLAG := 'Y';
            END IF;
            IF TEST_PKG.GET_UPDATE_HOURS_FLAG = 'Y' THEN
                TEST_PKG.UPDATE_HOURS(:OLD.KEY, :NEW.KEY, :OLD.SUB_KEY, :NEW.SUB_KEY, :OLD.SUB_SUB_KEY, :NEW.SUB_SUB_KEY, :OLD.DAY, :NEW.DAY, :OLD.AMOUNT, :NEW.AMOUNT);
            END IF;
        END IF;
    END;
/

Logging Table:

CREATE TABLE HOURS_DATA(
KEY   number,
SUB_KEY   number,
MONTH   VARCHAR2(4000),
AMOUNT NUMBER,
VARIABLE_INDEX NUMBER,
STARTING_AMOUNT NUMBER,
CALC_AMOUNT NUMBER,
ENDING_AMOUNT NUMBER,
ROW_INDEX NUMBER,
CREATE_DATE DATE
);

Trimed down package, only portion remaining that touches HOUR_TAB:

CREATE OR REPLACE PACKAGE TEST_PKG AS

UPDATE_HOURS_FLAG VARCHAR2(1) DEFAULT 'Y';

FUNCTION GET_UPDATE_HOURS_FLAG RETURN VARCHAR2;

PROCEDURE UPDATE_HOURS(
   OLD_KEY NUMBER,
   NEW_KEY NUMBER,
   OLD_SUB_KEY NUMBER,
   NEW_SUB_KEY NUMBER,
   OLD_SUB_SUB_KEY NUMBER,
   NEW_SUB_SUB_KEY NUMBER,
   OLD_DAY DATE,
   NEW_DAY DATE,
   OLD_AMOUNT NUMBER,
   NEW_AMOUNT NUMBER);

 PROCEDURE UPDATE_HOUR_TAB (
   KEY  NUMBER,
   SUB_KEY NUMBER,
   MONTH VARCHAR2,
   AMOUNT NUMBER);

PROCEDURE FLUSH_HOUR_TAB;

TYPE HOURLY_REC IS RECORD (
      KEY       NUMBER(10),
      SUB_KEY   NUMBER,
      MONTH     VARCHAR2(6),
      AMOUNT NUMBER);   

TYPE HOURLY_CHANGE_TAB IS TABLE OF HOURLY_REC INDEX BY BINARY_INTEGER;      

END TEST_PKG;
/

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS

   PKG_HOUR_COUNT  NUMBER := 0;
   HOUR_TAB            HOURLY_CHANGE_TAB ;

FUNCTION GET_UPDATE_HOURS_FLAG RETURN VARCHAR2 IS

BEGIN
   RETURN UPDATE_HOURS_FLAG;
END GET_UPDATE_HOURS_FLAG;

PROCEDURE UPDATE_HOUR_TAB (
   KEY  NUMBER,
   SUB_KEY NUMBER,
   MONTH VARCHAR2,
   AMOUNT NUMBER) IS

   CNT NUMBER;
   STARTING_AMOUNT number := 0;
   CALC_AMOUNT number := 0;

BEGIN

   CNT := HOUR_TAB.FIRST;

   WHILE CNT IS NOT NULL LOOP
      EXIT WHEN HOUR_TAB(CNT).KEY = KEY AND HOUR_TAB(CNT).SUB_KEY = SUB_KEY AND HOUR_TAB(CNT).MONTH = MONTH;
      CNT := HOUR_TAB.NEXT(CNT);
   END LOOP;

   IF CNT IS NULL THEN
      PKG_HOUR_COUNT := PKG_HOUR_COUNT + 1;
      HOUR_TAB(PKG_HOUR_COUNT).KEY := KEY;
      HOUR_TAB(PKG_HOUR_COUNT).SUB_KEY := SUB_KEY;
      HOUR_TAB(PKG_HOUR_COUNT).MONTH := MONTH;
      HOUR_TAB(PKG_HOUR_COUNT).AMOUNT := AMOUNT;
   ELSE
      STARTING_AMOUNT := HOUR_TAB(CNT).AMOUNT;
      CALC_AMOUNT := HOUR_TAB(CNT).AMOUNT + AMOUNT;
      HOUR_TAB(CNT).AMOUNT := HOUR_TAB(CNT).AMOUNT + AMOUNT;      
   END IF;

   IF CNT IS NULL THEN
    CNT := PKG_HOUR_COUNT;
    END IF;
   INSERT INTO HOURS_DATA
   VALUES(KEY,
   SUB_KEY,
   MONTH,
   AMOUNT,
   CNT,
   STARTING_AMOUNT,
   CALC_AMOUNT,
   HOUR_TAB(CNT).AMOUNT,
   (SELECT COUNT(*) FROM HOURS_DATA),
   SYSDATE);

END UPDATE_HOUR_TAB;

PROCEDURE UPDATE_HOURS(
   OLD_KEY NUMBER,
   NEW_KEY NUMBER,
   OLD_SUB_KEY NUMBER,
   NEW_SUB_KEY NUMBER,
   OLD_SUB_SUB_KEY NUMBER,
   NEW_SUB_SUB_KEY NUMBER,
   OLD_DAY DATE,
   NEW_DAY DATE,
   OLD_AMOUNT NUMBER,
   NEW_AMOUNT NUMBER) IS

BEGIN

      UPDATE_HOUR_TAB (
         OLD_KEY,
         OLD_SUB_KEY,
         TO_CHAR(OLD_DAY,'YYYYMM'),
         -OLD_AMOUNT);      

      UPDATE_HOUR_TAB (
         NEW_KEY,
         NEW_SUB_KEY,
         TO_CHAR(NEW_DAY,'YYYYMM'),
         NEW_AMOUNT); 

END UPDATE_HOURS;

PROCEDURE FLUSH_HOUR_TAB IS
   CNT NUMBER;

BEGIN

   CNT := HOUR_TAB.FIRST;

   WHILE CNT IS NOT NULL LOOP   
      CNT := HOUR_TAB.NEXT(CNT);
      --UPDATE MONTHS, DATA ALREADY BROKEN AT THIS POINT
   END LOOP;

   HOUR_TAB.DELETE;
   PKG_HOUR_COUNT := 0;

END FLUSH_HOUR_TAB;

END TEST_PKG;
/

Insert Script for test data:

DECLARE
    ORDER_KEY NUMBER := 10;
    SUB_KEY NUMBER := 0;
    SUB_SUB_KEY NUMBER := 1;
    V_START_TIME TIMESTAMP WITH TIME ZONE := '01-FEB-19 09.00.00 AM UTC';
    V_END_TIME TIMESTAMP WITH TIME ZONE := '31-DEC-22 08.00.00 AM UTC';
    V_CURRENT_TIME TIMESTAMP WITH TIME ZONE;
    V_DAY DATE;
    V_HOUR NUMBER(2);
BEGIN    
    V_CURRENT_TIME := V_START_TIME;
    WHILE V_CURRENT_TIME <= V_END_TIME LOOP
    --DBMS_OUTPUT.PUT_LINE(V_CURRENT_TIME);

    V_DAY := CAST(V_CURRENT_TIME
                AT TIME ZONE ('US/PACIFIC') AS DATE);                
    V_HOUR := TO_CHAR(V_DAY, 'HH24') + 1;

    --EXCLUDE WEEKENDS                
    IF MOD(TO_CHAR(V_DAY, 'J'), 7) + 1 NOT IN(6, 7) THEN
        --DBMS_OUTPUT.PUT_LINE(V_DAY);
        INSERT INTO ORDER_HOURS
        VALUES(ORDER_KEY, SUB_KEY, SUB_SUB_KEY, V_CURRENT_TIME, TRUNC(V_DAY), V_HOUR, 10, 'N');
    END IF;

    V_CURRENT_TIME := V_CURRENT_TIME + INTERVAL '1' HOUR;

END LOOP;    
    COMMIT;
END;
/

Update I ran – only causes error on original table:

DECLARE
    P_AMOUNT NUMBER := 20;
    P_KEY NUMBER := 10;
BEGIN

FOR UPDATE_HOUR IN(SELECT * FROM ORDER_HOURS
                    WHERE KEY = P_KEY                       
                    AND DAY >= '1-MAR-20'
                    ) LOOP                           
                                    UPDATE ORDER_HOURS
                                    SET AMOUNT = P_AMOUNT
                                    WHERE KEY = P_KEY
                                        AND DAY = UPDATE_HOUR.DAY
                                        AND HOUR = UPDATE_HOUR.HOUR
                                        AND (AMOUNT <> P_AMOUNT);
--Running the trigger code directly has never worked
--            IF :NEW.CONFIRMED_FLAG = 'Y' AND :OLD.CONFIRMED_FLAG = 'Y' 
--                THEN RAISE_APPLICATION_ERROR(-20914, 'You cannot Update.');
--            ELSE
--                IF :NEW.CONFIRMED_FLAG = 'A' THEN
--                    :NEW.CONFIRMED_FLAG := 'Y';
--                END IF;
--                IF TEST_PKG.GET_UPDATE_HOURS_FLAG = 'Y' THEN
--                    TEST_PKG.UPDATE_HOURS(:OLD.KEY, :NEW.KEY, :OLD.SUB_KEY, :NEW.SUB_KEY, :OLD.SUB_SUB_KEY, :NEW.SUB_SUB_KEY, :OLD.DAY, :NEW.DAY, :OLD.AMOUNT, :NEW.AMOUNT);
--                END IF;
--            END IF;                                                        
    END LOOP;
    --DOES THE MONTHLY UPDATE BASED ON PACKAGE VARIABLE AND CLEARS THE VARIABLE
    TEST_PKG.FLUSH_HOUR_TAB;
END;
/

Code to check logs for errors:

SELECT HD.*
FROM (
SELECT HD.*, SUM(AMOUNT) OVER(PARTITION BY KEY, SUB_KEY, MONTH ORDER BY ROW_INDEX) CORRECT_AMOUNT, COUNT(*) OVER(PARTITION BY SUB_KEY) SEQ_COUNT
FROM HOURS_DATA HD
) HD
WHERE HD.ENDING_AMOUNT <> CORRECT_AMOUNT
ORDER BY ROW_INDEX;

I have checked to see if there are more rows than there should be with an the same index or sub_key but the number of sub_key rows and index_rows is always the same. Right now my only hypothesis is a bug in Oracle. I am happy to provide more data or code if needed, but since I have not been able to duplicate this on another table, I am not sure how to help someone else reproduce it themselves.

The Order Hours table has 294,574,145 rows, filling 138 partitions, and using 7045 MB for data. When I run the attached code against the mini table ORDER_HOUR, it does not error out. When I only change the table name to the actual table with all the data, I do get the error. This makes me suspect that this is a bug with Oracle around triggers on large partitioned tables since the only difference is the table the trigger runs against. This makes it more difficult for someone to test against and try to reproduce if the issue is table specific. Any suggestions around cause or additional traces to run is appreciated.

While I may be able to solve this by changing how we update the monthly table, or update the month table after every update to an hour despite the performance cost(there are triggers on the month table that do a monthly cost calculation so each update is expensive), this issue is strange enough that I would like to solve it if possible.

Thank you for your help and patience reading through this wall of text.


Get this bounty!!!

#StackBounty: #oracle #oracle-12c Oracle 12c query performance varies based on schema/user

Bounty: 50

I have a strange (to me) performance issue. Based on the schema that runs the query I received very different performance. Here’s my setup.

Oracle 12c DB 
    SAMPLE schema/user
       WIDGET table (300,000 rows/10 columns of varchars, nvarchars, dates, and numbers)
    USER1 schema/user
    USER2 schema/user
    DBA1 schema/user
--SELECT granted on WIDGET table to USER1 and USER2. 
--DBA1 already has access to all table accross all schemas and has many more privs. 

When I execute either of the following:

select * from SAMPLE.WIDGET;
select count(1) from SAMPLE.WIDGET;

It takes about 0.5 seconds in SAMPLE schema or DBA1 schema. It takes about 10 seconds in USER1 and USER2 schema.

So I’m looking for the following:

Under what condition(s) can query performance in Oracle differ based on the user you are logged in to when you execute a query?


Get this bounty!!!