Slow response of Oracle query, using functions

The second is faster because the blocks are cached in memory from the first run.

It took 30 seconds for the first.  What is the tuning goal?  Remember 'faster' is not a goal.  If you are after 1 second for any run, then stop now.  That likely isn't possible.

What does the explain plan show when executed against your database?

What does it show if you just query the columns without the calls to ge_pkt_conv_funcs.f_convert_tlq?

The plan execution says;

Plan
SELECT STATEMENT  ALL_ROWSCost: 62  Bytes: 2,744  Cardinality: 49  				
	4 SORT GROUP BY  Cost: 62  Bytes: 2,744  Cardinality: 49  			
		3 FILTER  		
			2 TABLE ACCESS BY INDEX ROWID TABLE XAJTDB.A_5MIN_033 Cost: 61  Bytes: 2,744  Cardinality: 49  	
				1 INDEX RANGE SCAN INDEX (UNIQUE) XAJTDB.A_5MIN_033_PK Cost: 3  Cardinality: 89  

Select allOpen in new window

"ge_pkt_conv_funcs.f_convert_tlq"
"xa_time_cnv.utc_to_loc"
Without those functions, the answer its much better.

Then, you may not see an improvement in query syntax?

What are you wanting me to do with those functions?

If you want me to walk through them and 'tune' them I'm afraid that isn't going to work.

You would need to hire a consultant to do that.

I have no idea what f_convert_tlq is even doing.

I can offer advice on where to look:
1:  look at the selects/cursors inside the functions.  Make sure each one of them is using an index if there is a large amount of data in the tables.

2: look into removing some of the cursors where possible.

For example in utc_to_loc you only appear to select a single row.  A simple select into with a no_data_found exception might be better.

I'm guessing Oracle date/timezone conversions won't work for you?

2. if timetran contains many records a function based index on utcstart + (offset / (24 * 60)) might improve
          WHERE local_datetime BETWEEN (utcstart + (offset / (24 * 60)))
    by using that index

You should avoid the use of FBIs whenever possible. In other situations they might "cost" a lot if you / or a new/other developer doesn't know how to use them properly...

3. if your are on 11g  and tables  tlq, xajsdb.tlq_xui, xajsdb.pov  don't change often
    consider using the  /*+ RESULT_CACHE */  hint

Depending on your configuration (e.g. check v$parameter), you can and should ommit this hint. If set up correctly, your CBO should always know how to dig through your rows properly ;-)

I would try with a code like below. But I have some problem with convertion of "UTCTIME" after "DECLARE TYPE", with the error :

ora-01722 invalid number
ORA-06512: at line 31

Is possible that conversion, when I using TYPE?
Thanks.

SET SERVEROUTPUT ON
SET TIMING ON
exec dbms_output.enable(1000000);
alter session set nls_date_format ='dd/mm/yyyy hh24:mi:ss';
DECLARE

  TYPE matriz_utctime is table of a_5min_033.utctime%TYPE;
  TYPE matriz_valor_inst is table of a_5min_033.valor_inst%TYPE;
  TYPE matriz_tlq_inst is table of a_5min_033.tlq_inst%TYPE;
  TYPE matriz_valor_prom is table of a_5min_033.valor_prom%TYPE;
  TYPE matriz_tlq_prom is table of a_5min_033.tlq_prom%TYPE;
  TYPE matriz_valor_max is table of a_5min_033.valor_max%TYPE;
  TYPE matriz_tlq_max is table of a_5min_033.tlq_max%TYPE;
  TYPE matriz_utctime_max is table of a_5min_033.utctime_max%TYPE;
  TYPE matriz_valor_min is table of a_5min_033.valor_min%TYPE;
  TYPE matriz_tlq_min is table of a_5min_033.tlq_min%TYPE;
  TYPE matriz_utctime_min is table of a_5min_033.utctime_min%TYPE;
    
  
  l_utctime matriz_utctime;
  l_valor_inst matriz_valor_inst;
  l_tlq_inst matriz_tlq_inst;
  l_valor_prom matriz_valor_prom;
  l_tlq_prom matriz_tlq_prom;
  l_valor_max matriz_valor_max;
  l_tlq_max matriz_tlq_max;
  l_utctime_max matriz_utctime_max;
  l_valor_min matriz_valor_min;
  l_tlq_min matriz_tlq_min;
  l_utctime_min matriz_utctime_min;
  
  
  
BEGIN
SELECT to_char(xa_time_cnv.utc_to_loc(UTCTIME),'DD-MM-YYYY HH24:MI:SS'),  
                    +
                    ROUND(VALOR_INST,2),  
                    +
                    ge_pkt_conv_funcs.f_convert_tlq(TLQ_INST),  
                    +
                    ROUND(VALOR_PROM,2),  +
                    ge_pkt_conv_funcs.f_convert_tlq(TLQ_PROM),  
                    +
                    ROUND(VALOR_MAX,2),  +
                    ge_pkt_conv_funcs.f_convert_tlq(TLQ_MAX),  
                    +
                    to_char(xa_time_cnv.utc_to_loc(UTCTIME_MAX),'DD-MM-YYYY HH24:MI:SS'), 
                    +
                    ROUND(VALOR_MIN,2), 
                    +
                    ge_pkt_conv_funcs.f_convert_tlq(TLQ_MIN),  
                    +
                    to_char(xa_time_cnv.utc_to_loc(UTCTIME_MIN),'DD-MM-YYYY HH24:MI:SS')
  BULK COLLECT INTO l_utctime, l_valor_inst, l_tlq_inst, l_valor_prom, l_tlq_prom,
       l_valor_max, l_tlq_max, l_utctime_max, 
       l_valor_min, l_tlq_min, l_utctime_min 
       FROM a_5min_033
  WHERE UTCTIME >= TO_DATE('21/06/2014 00:00:00','DD-MM-YYYY HH24:MI:SS') 
     AND   UTCTIME <= TO_DATE('22/06/2014 00:00:00','DD-MM-YYYY HH24:MI:SS') + 8/24 
     AND   POINTNUMBER =  330011
     ORDER BY UTCTIME;

  FOR i IN l_utctime.FIRST .. l_utctime.LAST LOOP
    dbms_output.put_line(l_utctime(i)    || ' , ' ||l_valor_inst(i)||' , '||
                            l_valor_prom(i) || ' , ' ||l_tlq_prom(i) || ' , '||
                            l_valor_max(i) || ' , ' ||l_tlq_max(i) || ' , '||l_utctime_max(i)|| ' , '||
                            l_valor_min(i) || ' , ' ||l_tlq_min(i) || ' , '||l_utctime_min(i) );
  END LOOP;

END;
/

Select allOpen in new window

What data type is a_5min_033.utctime%TYPE ?

You are converting it to a VARCHAR2 with:
to_char(xa_time_cnv.utc_to_loc(UTCTIME),'DD-MM-YYYY HH24:MI:SS'),

Double check the rest.

Thank you