I am facing an issue where in the performance of a query is very very slow. Database being used is: Oracle 11g
It happens only to 2 specific environments. The environments are: Mali Preprod(MPP) and Mali QA(MQA). I don't have access to run the execution plan on MPP but did run the execution plan and the query itself. It takes about 18 minutes to retrieve the first set of rows. The same query when I ran in development, it was quick; less than 3 seconds. The difference is that in Mali's environments, there are 1.8 million rows and in development there are only 466 rows.
I checked the indexes on the tables being used and they appear appropriate. The only index that I found missing was on : IRD_FILE table and a non unique index was created:
CREATE INDEX TAX_TYPE_PK_I
ON ird_file(tax_type_no);
The execution plan that I used is as follows:
set autotrace on
EXPLAIN PLAN FOR
select ird.ird_file_no,
ird.OLD_FILE_NO,
f_translate(FILE_LOCATION_DESC, :APP_USER_LANG_NO) FILE_LOCATION_DESC,
tp.tax_payer_no TAX_PAYER_NO,
tp.fiscal_no Tin,
pck_util.get_taxpayer_name_by_lang(ird.tax_payer_no,1,'N', :APP_USER_LANG_NO) tax_payer_name,
f_translate(tc.tax_centre_desc, :APP_USER_LANG_NO) Tax_Division,
f_translate(tt.tax_type_desc, :APP_USER_LANG_NO) tax_type_desc,
ird.IRD_FILE_YEAR,
count(doc.doc_no) no_doc
from document doc, ird_file ird, tax_centre tc, tax_payer tp, tax_type tt, file_location fl
where tp.tax_centre_no = tc.tax_centre_no(+)
and ird.tax_payer_no = tp.tax_payer_no(+)
and ird.TAX_TYPE_NO = tt.TAX_TYPE_NO(+)
and ird.FILE_LOCATION_NO = fl.FILE_LOCATION_NO(+)
and ird.ird_file_no = doc.ird_file_no(+)
and ( (':'||:P2100_CENTER||':' like '%:' || doc.tax_centre_no ||':%' ) OR doc.tax_centre_no is null) -- Activité : 5100050 See documents created by the user pas spécifiée ici mais a servi à renseigner P2100_CENTER
and (DOCUMENT_OBJ(doc.doc_no).GET_ENTRY_USER() = :P2100_IRD_EMPLOYEE_LOGIN_ID OR :P2100_ACTIVITY_5100120 = 1 /*Activité : See documents created by all users*/)
group by ird.ird_file_no,
ird.OLD_FILE_NO,
f_translate(FILE_LOCATION_DESC, :APP_USER_LANG_NO),
tp.tax_payer_no,
tp.fiscal_no,
pck_util.get_taxpayer_name_by_lang(ird.tax_payer_no,1,'N', :APP_USER_LANG_NO),
f_translate(tc.tax_centre_desc, :APP_USER_LANG_NO),
f_translate(tt.tax_type_desc, :APP_USER_LANG_NO),
ird.IRD_FILE_YEAR;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'ALL'));
set autotrace off;
NOTE: DOCUMENT is a VIEW and the underlying table is DOCUMENT_ALL. Replacing the view with the table name did not make much of a difference.
Output was:
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
plan FOR succeeded.
Statistics
-----------------------------------------------------------
19 CPU used by this session
20 CPU used when call started
21 DB time
21 HSC Heap Segment Block Changes
1 IMU Flushes
1 IMU ktichg flush
15 Requests to/from client
15 SQL*Net roundtrips to/from client
204 buffer is not pinned count
1941 bytes received via SQL*Net from client
26960 bytes sent via SQL*Net to client
111 calls to get snapshot scn: kcmgss
1 calls to kcmgas
7 calls to kcmgcs
8192 cell physical IO interconnect bytes
39 consistent changes
316 consistent gets
300 consistent gets - examination
316 consistent gets from cache
13 consistent gets from cache (fastpath)
5 cursor authentications
63 db block changes
55 db block gets
1 db block gets direct
54 db block gets from cache
15 enqueue releases
19 enqueue requests
25 execute count
2 file io wait time
7 free buffer requested
3 index crx upgrade (positioned)
100 index fetch by key
3 index scans kdiixs1
6 lob reads
1 lob writes
1 lob writes unaligned
3031040 logical read bytes from cache
2 no work - consistent read gets
17 non-idle wait count
3 non-idle wait time
6 opened cursors cumulative
2 opened cursors current
9 parse count (hard)
6 parse count (total)
1 physical write IO requests
8192 physical write bytes
1 physical write total IO requests
8192 physical write total bytes
1 physical writes
1 physical writes direct
1 physical writes direct (lob)
1 physical writes direct temporary tablespace
1 physical writes non checkpoint
1 pinned cursors current
804 recursive calls
19 recursive cpu usage
22 redo entries
3276 redo size
100 rows fetched via callback
1 session cursor cache count
1 session cursor cache hits
371 session logical reads
131072 session pga memory
458384 session uga memory
3 shared hash latch upgrades - no wait
1 sorts (memory)
679 sorts (rows)
2 sql area evicted
102 table fetch by rowid
3145728 temp space allocated (bytes)
1552 undo change vector size
3 user I/O wait time
16 user calls
3 workarea executions - optimal
299 workarea memory allocated
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 761366760
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1882K| 560M| | 333K (1)| 01:06:39 |
| 1 | HASH GROUP BY | | 1882K| 560M| 588M| 333K (1)| 01:06:39 |
|* 2 | HASH JOIN RIGHT OUTER | | 1882K| 560M| | 207K (2)| 00:41:26 |
| 3 | VIEW | index$_join$_005 | 76 | 1444 | | 2 (0)| 00:00:01 |
|* 4 | HASH JOIN | | | | | | |
| 5 | INDEX FAST FULL SCAN | TAX_TYP_PK | 76 | 1444 | | 1 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN | TAX_TYP_TAX_TYP2_UK | 76 | 1444 | | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 1882K| 525M| | 207K (2)| 00:41:26 |
| 8 | VIEW | index$_join$_003 | 67 | 938 | | 2 (0)| 00:00:01 |
|* 9 | HASH JOIN | | | | | | |
| 10 | INDEX FAST FULL SCAN | TAX_CENTRE2_UK | 67 | 938 | | 1 (0)| 00:00:01 |
| 11 | INDEX FAST FULL SCAN | TAX_CENTRE_PK | 67 | 938 | | 1 (0)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 1882K| 500M| | 207K (2)| 00:41:26 |
|* 13 | FILTER | | | | | | |
|* 14 | HASH JOIN OUTER | | 1882K| 114M| 9400K| 207K (2)| 00:41:26 |
|* 15 | HASH JOIN OUTER | | 174K| 7348K| 6152K| 2728 (2)| 00:00:33 |
| 16 | TABLE ACCESS FULL | IRD_FILE | 174K| 4101K| | 145 (3)| 00:00:02 |
| 17 | TABLE ACCESS FULL | TAX_PAYER | 433K| 8035K| | 1642 (2)| 00:00:20 |
|* 18 | TABLE ACCESS FULL | DOCUMENT_ALL | 1882K| 37M| | 200K (2)| 00:40:12 |
| 19 | TABLE ACCESS BY INDEX ROWID| FILE_LOCATION | 1 | 215 | | 0 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | FILE_LOCATION_PK | 1 | | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
3 - SEL$EA66F271 / TT@SEL$1
4 - SEL$EA66F271
5 - SEL$EA66F271 / indexjoin$_alias$_001@SEL$EA66F271
6 - SEL$EA66F271 / indexjoin$_alias$_002@SEL$EA66F271
8 - SEL$3EA0B395 / TC@SEL$1
9 - SEL$3EA0B395
10 - SEL$3EA0B395 / indexjoin$_alias$_001@SEL$3EA0B395
11 - SEL$3EA0B395 / indexjoin$_alias$_002@SEL$3EA0B395
16 - SEL$F5BB74E1 / IRD@SEL$1
17 - SEL$F5BB74E1 / TP@SEL$1
18 - SEL$F5BB74E1 / DOCUMENT_ALL@SEL$2
19 - SEL$F5BB74E1 / FL@SEL$1
20 - SEL$F5BB74E1 / FL@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("IRD"."TAX_TYPE_NO"="TT"."TAX_TYPE_NO"(+))
4 - access(ROWID=ROWID)
7 - access("TP"."TAX_CENTRE_NO"="TC"."TAX_CENTRE_NO"(+))
9 - access(ROWID=ROWID)
13 - filter((':'||:P2100_CENTER||':' LIKE '%:'||TO_CHAR("TAX_CENTRE_NO")||':%' OR "TAX_CENTRE_NO" IS
NULL) AND ("DOCUMENT_OBJ"."GET_ENTRY_USER"("DOCUMENT_OBJ"."DOCUMENT_OBJ"("DOC_NO"))=:P2100_IRD_EMPLOYEE
_LOGIN_ID OR TO_NUMBER(:P2100_ACTIVITY_5100120)=1))
14 - access("IRD"."IRD_FILE_NO"="IRD_FILE_NO"(+))
15 - access("IRD"."TAX_PAYER_NO"="TP"."TAX_PAYER_NO"(+))
18 - filter("IRD_FILE_NO"(+) IS NOT NULL AND "DOC_SIGTAS_GROUP"(+)='STANDARD')
20 - access("IRD"."FILE_LOCATION_NO"="FL"."FILE_LOCATION_NO"(+))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=9) "IRD"."IRD_FILE_NO"[NUMBER,22], "IRD"."OLD_FILE_NO"[VARCHAR2,60],
"F_TRANSLATE"("FILE_LOCATION_DESC",:APP_USER_LANG_NO)[4000], "TP"."TAX_PAYER_NO"[NUMBER,22],
"TP"."FISCAL_NO"[VARCHAR2,80], "PCK_UTIL"."GET_TAXPAYER_NAME_BY_LANG"("IRD"."TAX_PAYER_NO",'1','N',:APP
_USER_LANG_NO)[4000], "F_TRANSLATE"("TC"."TAX_CENTRE_DESC",:APP_USER_LANG_NO)[4000],
"F_TRANSLATE"("TT"."TAX_TYPE_DESC",:APP_USER_LANG_NO)[4000], "IRD"."IRD_FILE_YEAR"[NUMBER,22],
COUNT("DOC_NO")[22]
2 - (#keys=1) "TT"."TAX_TYPE_DESC"[VARCHAR2,400], "TC"."TAX_CENTRE_DESC"[VARCHAR2,400],
"IRD"."IRD_FILE_NO"[NUMBER,22], "IRD"."TAX_PAYER_NO"[NUMBER,22], "TP"."TAX_PAYER_NO"[NUMBER,22],
"TP"."FISCAL_NO"[VARCHAR2,80], "IRD"."IRD_FILE_YEAR"[NUMBER,22], "IRD"."OLD_FILE_NO"[VARCHAR2,60],
"FILE_LOCATION_DESC"[VARCHAR2,400], "DOC_NO"[NUMBER,22]
3 - "TT"."TAX_TYPE_DESC"[VARCHAR2,400], "TT"."TAX_TYPE_NO"[NUMBER,22]
4 - (#keys=1) "TT"."TAX_TYPE_NO"[NUMBER,22], "TT"."TAX_TYPE_DESC"[VARCHAR2,400]
5 - ROWID[ROWID,10], "TT"."TAX_TYPE_NO"[NUMBER,22]
6 - ROWID[ROWID,10], "TT"."TAX_TYPE_DESC"[VARCHAR2,400]
7 - (#keys=1) "TC"."TAX_CENTRE_DESC"[VARCHAR2,400], "IRD"."IRD_FILE_NO"[NUMBER,22],
"IRD"."TAX_PAYER_NO"[NUMBER,22], "TP"."TAX_PAYER_NO"[NUMBER,22], "TP"."FISCAL_NO"[VARCHAR2,80],
"IRD"."TAX_TYPE_NO"[NUMBER,22], "IRD"."IRD_FILE_YEAR"[NUMBER,22], "IRD"."OLD_FILE_NO"[VARCHAR2,60],
"FILE_LOCATION_DESC"[VARCHAR2,400], "DOC_NO"[NUMBER,22]
8 - "TC"."TAX_CENTRE_NO"[NUMBER,22], "TC"."TAX_CENTRE_DESC"[VARCHAR2,400]
9 - (#keys=1) "TC"."TAX_CENTRE_DESC"[VARCHAR2,400], "TC"."TAX_CENTRE_NO"[NUMBER,22]
10 - ROWID[ROWID,10], "TC"."TAX_CENTRE_DESC"[VARCHAR2,400]
11 - ROWID[ROWID,10], "TC"."TAX_CENTRE_NO"[NUMBER,22]
12 - (#keys=0) "IRD"."IRD_FILE_NO"[NUMBER,22], "IRD"."TAX_PAYER_NO"[NUMBER,22],
"TP"."TAX_PAYER_NO"[NUMBER,22], "TP"."FISCAL_NO"[VARCHAR2,80], "IRD"."TAX_TYPE_NO"[NUMBER,22],
"IRD"."IRD_FILE_YEAR"[NUMBER,22], "IRD"."OLD_FILE_NO"[VARCHAR2,60], "TP"."TAX_CENTRE_NO"[NUMBER,22],
"DOC_NO"[NUMBER,22], "FILE_LOCATION_DESC"[VARCHAR2,400]
13 - "IRD"."IRD_FILE_NO"[NUMBER,22], "IRD"."TAX_PAYER_NO"[NUMBER,22],
"TP"."TAX_PAYER_NO"[NUMBER,22], "TP"."FISCAL_NO"[VARCHAR2,80], "IRD"."TAX_TYPE_NO"[NUMBER,22],
"IRD"."FILE_LOCATION_NO"[NUMBER,22], "IRD"."IRD_FILE_YEAR"[NUMBER,22],
"IRD"."OLD_FILE_NO"[VARCHAR2,60], "TP"."TAX_CENTRE_NO"[NUMBER,22], "DOC_NO"[NUMBER,22]
14 - (#keys=1) "IRD"."IRD_FILE_NO"[NUMBER,22], "IRD"."TAX_PAYER_NO"[NUMBER,22],
"TP"."TAX_PAYER_NO"[NUMBER,22], "TP"."FISCAL_NO"[VARCHAR2,80], "IRD"."TAX_TYPE_NO"[NUMBER,22],
"IRD"."FILE_LOCATION_NO"[NUMBER,22], "IRD"."IRD_FILE_YEAR"[NUMBER,22],
"IRD"."OLD_FILE_NO"[VARCHAR2,60], "TP"."TAX_CENTRE_NO"[NUMBER,22], "DOC_NO"[NUMBER,22],
"TAX_CENTRE_NO"[NUMBER,22]
15 - (#keys=1) "IRD"."TAX_PAYER_NO"[NUMBER,22], "TP"."TAX_PAYER_NO"[NUMBER,22],
"IRD"."IRD_FILE_NO"[NUMBER,22], "IRD"."TAX_TYPE_NO"[NUMBER,22], "IRD"."FILE_LOCATION_NO"[NUMBER,22],
"IRD"."IRD_FILE_YEAR"[NUMBER,22], "IRD"."OLD_FILE_NO"[VARCHAR2,60], "TP"."TAX_CENTRE_NO"[NUMBER,22],