Oracle Exadata performance issue with simple select query

484 views Asked by At

I am using Oracle Exadata cloud environment. I have a table which I am using to store raw data from excel import through an application. All the columns are VARCHAR2(100 CHAR). I have a simple select statement which returns all the records which are failed validation. In the application I am using OracleDataReader to fetch the records. This query takes about 15 minutes to return 308K records. I ran the statistics & are as below. I have a combine index on Batch_Id & User_Id columns. What can be done to improve the performance, this is a very simple select statement without any joins.

SELECT /*+ gather_plan_statistics */
      ExcelRowNumber,
       Program_Number,
       Program_Number_Source,
       Invoice_Number,
       Invoice_Amount,
       Unit_Number,
       Customer_Number,
       ErrorText
  FROM MKTG.STG_UNIT_INVC_CALC
 WHERE BATCH_ID = 2038326851 AND USER_Id = 'JAY' AND ErrorText IS NOT NULL                    

enter image description here

0

There are 0 answers