Home » SQL & PL/SQL » SQL & PL/SQL » Query running extremely slow (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0, Windows 10 Professional)
Query running extremely slow [message #675693] |
Fri, 12 April 2019 14:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
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);
Creating the above index did not help much. Same response time.
The execution plan that I used is as follows:
set autotrace on
EXPLAIN PLAN FOR
select ird.ird_ile_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)
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],
"TP"."FISCAL_NO"[VARCHAR2,80]
16 - "IRD"."IRD_FILE_NO"[NUMBER,22], "IRD"."TAX_TYPE_NO"[NUMBER,22],
"IRD"."TAX_PAYER_NO"[NUMBER,22], "IRD"."IRD_FILE_YEAR"[NUMBER,22], "IRD"."OLD_FILE_NO"[VARCHAR2,60],
"IRD"."FILE_LOCATION_NO"[NUMBER,22]
17 - "TP"."TAX_PAYER_NO"[NUMBER,22], "TP"."FISCAL_NO"[VARCHAR2,80], "TP"."TAX_CENTRE_NO"[NUMBER,22]
18 - "DOC_NO"[NUMBER,22], "IRD_FILE_NO"[NUMBER,22], "TAX_CENTRE_NO"[NUMBER,22]
19 - "FILE_LOCATION_DESC"[VARCHAR2,400]
20 - "FL".ROWID[ROWID,10]
110 rows selected
Plan hash value: 2137789089
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 24 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
If you notice 60% and over (load) is being taken by document_all table itself.
I am wondering if you can spot anything that I am unable to or is there any other way to improve the performance of the query? Please help.
Thanks in advance
[Edit MC: right trim white spaces]
[Updated on: Sun, 14 April 2019 14:09] by Moderator Report message to a moderator
|
|
|
Re: Query running extremely slow [message #675695 is a reply to message #675693] |
Sat, 13 April 2019 01:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I can't understand this.
First, you say that one environment has 466 rows, the other has 1.8m rows. Is that not sugnificant?
Second, you are using AUTOTRACE to trace EXPLAIN PLAN. That is crazy. Use one or the other.
|
|
|
Re: Query running extremely slow [message #675698 is a reply to message #675695] |
Sat, 13 April 2019 08:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
I am not sure why you cannot understand as I have mentioned the performance difference in both these (different) environments. The reason for mentioning is: there are times on this forum that others have asked if the same query runs/behaves differently. So it was a 'fyi' kind of information.
Yes, I understand the difference between the number of rows in both the environments. Yes, it is significant. Does it mean that just because it's 1.8 million rows one needs to wait for almost 20 minutes before the results begin to download. Isn't there any better way to improve the performance using the query as-is i.e. I cannot add any other filtering conditions to the query because that is how the business requirement is.
What is the problem if one runs 'AUTOTRACE to run the EXPLAINPLAN'. By running one or the other will it make any difference to the EXECUTIONPLAN?
|
|
|
|
Re: Query running extremely slow [message #675700 is a reply to message #675699] |
Sat, 13 April 2019 10:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
Michel Cadot wrote on Sat, 13 April 2019 10:29
Quote:one needs to wait for almost 20 minutes before the results begin to download
Given that you compute data from groups ow can Oracle returns the first row before accessing all of them?
Think a little bit: if the first and the last row are in the same group, to get the count of this group
Oracle have to get all those between them, haven't it?
Agree. D'accord!
|
|
|
Re: Query running extremely slow [message #675701 is a reply to message #675698] |
Sun, 14 April 2019 00:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
buggleboy007 wrote on Sat, 13 April 2019 14:32<snip>
What is the problem if one runs 'AUTOTRACE to run the EXPLAINPLAN'. By running one or the other will it make any difference to the EXECUTIONPLAN?
he statistics shown are those for running EXPLAIN PLAN, not for running the statement itself. What is the use of that?
|
|
|
Re: Query running extremely slow [message #675704 is a reply to message #675701] |
Sun, 14 April 2019 11:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
John - To be honest, I am new to tuning. This is the 2nd time perhaps in my 12 years of Oracle career, I have been asked to look at a query of this type. That said, what ever I have applied so far is based what I have learnt so far - through sites like UDEMY and Youtube.
So if I am doing anything wrong, then kindly give a precise answer and back it up with facts. Your answers or questions that you have posed are arbitrary and cryptic in nature. I have not understood what you wish to convey.
|
|
|
|
|
Re: Query running extremely slow [message #675719 is a reply to message #675706] |
Mon, 15 April 2019 05:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You've got 4 function calls in there - 3 in the select and 1 in the where clause.
That's not going to help and explain/execution plans don't really show the effect of those.
You need to run a full sql trace to pick up the time spent in the functions.
|
|
|
Re: Query running extremely slow [message #675728 is a reply to message #675719] |
Mon, 15 April 2019 09:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
cookiemonster wrote on Mon, 15 April 2019 05:51You've got 4 function calls in there - 3 in the select and 1 in the where clause.
You need to run a full sql trace to pick up the time spent in the functions.
By turning SQL TRACE ON, won't it help? I have already turned it on when I run the execution plan. If this is not what it is, then please let me know what you meant by SQL trace.
|
|
|
|
Re: Query running extremely slow [message #675732 is a reply to message #675728] |
Mon, 15 April 2019 09:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'm talking about a full oracle session trace. autotrace doesn't do that and I'm unsure what you mean by SQL TRACE ON.
To switch it on in the current session:
alter session set sql_trace = true;
to switch it on in a different session use dbms_monitor.session_trace_enable.
It'll write a trace file to the oracle trace directory that you can then run through tkprof.
|
|
|
Re: Query running extremely slow [message #675749 is a reply to message #675732] |
Tue, 16 April 2019 12:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
I tried the options that were suggested. Unfortunately it did not reduce the response time. Yesterday, I debugged the original query line by line. The main glitch was in the f_translate that is being used in GROUP BY clause.
I refactored the query itself and then ran it in SQL DEVELOPER, it dramatically improved the response time [the COST by 100K, the time (from 01:06:39 to 00:47:37]. From 18 minutes to 4.52 minutes. However this morning our development team decided to include another filtering condition - adding a from and to dates on APEX form(because this query was from APEX form which was causing around 15+ minutes of response time) so that the response time can be decreased. I am not sure if they will retain the original query or go with my query (of course, by adding the date filtering conditions in the WHERE clause).
Just thought I should let you know. The refactored query is below:
select a.ird_file_no
,a.old_file_no
,f_translate((select FILE_LOCATION_DESC from file_location f where f.file_location_no = a.file_location_no),:APP_USER_LANG_NO) FILE_LOCATION_DESC
,a.tax_payer_no
,a.Tin
,pck_util.get_taxpayer_name_by_lang(a.tax_payer_no,1,'N',:APP_USER_LANG_NO) tax_payer_name
,f_translate(tax_centre_obj(a.tax_centre_no).tax_centre_desc,:APP_USER_LANG_NO) Tax_Division
,f_translate(tax_type_obj(a.tax_type_no).tax_type_desc,:APP_USER_LANG_NO) tax_type_desc
,a.ird_file_year
,a.no_doc
from ( select ird.ird_file_no,
ird.OLD_FILE_NO,
fl.file_location_no,
tp.tax_payer_no TAX_PAYER_NO,
tp.fiscal_no Tin,
tc.tax_centre_no,
tt.tax_type_no,
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,
fl.file_location_no,
tc.tax_centre_no,
tt.tax_type_no,
tp.tax_payer_no,
tp.fiscal_no,
ird.IRD_FILE_YEAR
) a
|
|
|
Re: Query running extremely slow [message #675756 is a reply to message #675749] |
Wed, 17 April 2019 03:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Having the functions in the group by isn't really the problem - oracle won't run the function a second time becuase it's in the group by - it'll just use the results from the select part.
What does make a difference is moving the function calls up a level since that does change the number of times they are run:
If the query without the group by and aggregate returns 1000 rows and after the group by you get 100 rows then moving the function changes it's number of executions from 1000 to 100.
However moving the function may effect the query results depending on what the query does.
If every distinct input to the function gives a distinct output then moving it will make no difference.
But if there are cases where two or more distinct inputs give the same result from the function then whaat you've done will effect the output as the number of rows that constitute a single group will change.
The better solution is to get rid of the functions entirely and do what ever they are doing direct in the SQL.
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:42:15 CDT 2024
|