Based on Oracle SR responses and vendor’s recommendation I ran few test cases to analyze global temporary table performance issues
Test Case 1:
Step 1: Create Global Temporary Table PRADIPTA.PATIENT_INTERFACE_TEMP with primary key
SQL > CREATE GLOBAL TEMPORARY TABLE PRADIPTA.PATIENT_INTERFACE_TEMP
( …………………
……………………
) ON COMMIT PRESERVE ROWS NOCACHE ENABLE ROW MOVEMENT;
SQL > ALTER TABLE PRADIPTA.PATIENT_INTERFACE_TEMP ADD (
CONSTRAINT PK_PATIENT_INTERFACE_TEMP
PRIMARY KEY
(PATIENT_INT_KEY));
Step 2: Checking table statistics information
SQL> select table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='PATIENT_INTERFACE_TEMP' and owner='PRADIPTA';
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------
PATIENT_INTERFACE_TEMP
Step 3: Gathering Status on PRADIPTA.PATIENT_INTERFACE_TEMP as ORACLE MAINTANANCE windows does every night
SQL > begin
dbms_stats.gather_table_stats(ownname => 'PRADIPTA', tabname => 'PATIENT_INTERFACE_TEMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
end;
/
Step4: Checking table statistics information after Gather stats
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------
PATIENT_INTERFACE_TEMP 0 16-FEB-12
Step 5: Run the process
Process 1: Insert into PRADIPTA.PATIENT_INTERFACE_TEMP;
105695 rows created.
Elapsed: 00:00:01.81
Process 2: Select from PRADIPTA.PATIENT_INTERFACE_TEMP using join to DD.PATIENT_DIM
105674 rows created
Elapsed: 3 – 4 hours
Test Case 2:
Step 1: Delete table statistics on PRADIPTA.PATIENT_INTERFACE_TEMP
SQL > BEGIN
dbms_stats.delete_table_stats(ownname => 'PRADIPTA', tabname => 'PATIENT_INTERFACE_TEMP');
END;
/
Step 2: Checking table statistics information after Gather stats
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------
PATIENT_INTERFACE_TEMP
Step 3: Run the process
Process 1: Insert into PRADIPTA.PATIENT_INTERFACE_TEMP;
105695 rows created.
Elapsed: 00:00:01.81 (2 seconds)
Process 2: Select from PRADIPTA.PATIENT_INTERFACE_TEMP using join to DD.PATIENT_DIM
105674 rows created
Elapsed: 00:01:36.91 (1 mins 36 seconds)
Test Case 3:
Step 1: Lock table statistics on PRADIPTA.PATIENT_INTERFACE_TEMP so ORACLE MAINTANANCE window will not gather stats.
SQL > BEGIN
dbms_stats.unlock_table_stats(ownname => 'PRADIPTA', tabname => 'PATIENT_INTERFACE_TEMP');
END;
/
Step 2: Checking table statistics information after Gather stats
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------
PATIENT_INTERFACE_TEMP
Step 3: Run the process
Process 1: Insert into PRADIPTA.PATIENT_INTERFACE_TEMP;
105695 rows created.
Elapsed: 00:00:01.81 (2 seconds)
Process 2: Select from PRADIPTA.PATIENT_INTERFACE_TEMP using join to DD.PATIENT_DIM
105674 rows created
Elapsed: 00:01:36.91 (1 mins 36 seconds)
Conclusion:
Oracle (nightly maintenance window job) is gathering statistics on global temporary table, which eventually causing the PATIENT matching performance issue.
Question 1: Why Oracle is gathering status on GTT (Global Temporary Table)?
Question 2: Are Oracle gather stats parameters efficient enough to analyze correct statistics for our application ??
