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
buggleboy007
Messages: 205
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 messageGo to next message
John Watson
Messages: 7871
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 messageGo to next message
buggleboy007
Messages: 205
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 #675699 is a reply to message #675698] Sat, 13 April 2019 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66325
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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?

Re: Query running extremely slow [message #675700 is a reply to message #675699] Sat, 13 April 2019 10:50 Go to previous messageGo to next message
buggleboy007
Messages: 205
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 messageGo to next message
John Watson
Messages: 7871
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 messageGo to next message
buggleboy007
Messages: 205
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 #675705 is a reply to message #675704] Sun, 14 April 2019 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66325
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You need to do what John explains there.

And I add, before execute the command "set trimout on trimspool on".

Re: Query running extremely slow [message #675706 is a reply to message #675705] Sun, 14 April 2019 14:02 Go to previous messageGo to next message
buggleboy007
Messages: 205
Registered: November 2010
Location: Canada
Senior Member
Sure. I will do that first thing in the morning (tomorrow), as I too want to get the issue fixed.
Re: Query running extremely slow [message #675719 is a reply to message #675706] Mon, 15 April 2019 05:51 Go to previous messageGo to next message
cookiemonster
Messages: 13554
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 messageGo to next message
buggleboy007
Messages: 205
Registered: November 2010
Location: Canada
Senior Member
cookiemonster wrote on Mon, 15 April 2019 05:51
You'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 #675731 is a reply to message #675728] Mon, 15 April 2019 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 26483
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/SQL_Trace
Re: Query running extremely slow [message #675732 is a reply to message #675728] Mon, 15 April 2019 09:48 Go to previous messageGo to next message
cookiemonster
Messages: 13554
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 messageGo to next message
buggleboy007
Messages: 205
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
cookiemonster
Messages: 13554
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.
Previous Topic: Carriage Return Finding
Next Topic: Problem with selecting data
Goto Forum:
  


Current Time: Mon Apr 22 03:03:21 CDT 2019