Slow response of Oracle query, using functions

Question

I have the next query:

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')  
                    FROM  a_5min_033 
             WHERE utctime >= xa_time_cnv.loc_to_utc(TO_DATE('27/06/2014 00:00:00','DD-MM-YYYY HH24:MI:SS'),null) 
             AND   utctime <= xa_time_cnv.loc_to_utc(TO_DATE('28/06/2014 00:00:00','DD-MM-YYYY HH24:MI:SS') + 8/24,null) 
             AND   POINTNUMBER =  330000 
             ORDER BY utctime;

The first time it runs after returns records:

Elapsed: 00:00:30.87

Then, In a second run (query in cache):

Elapsed: 00:00:01.17

I tried grouping, but without results:

GROUP BY utctime, valor_inst, tlq_inst, valor_prom, tlq_prom, valor_max, tlq_max, utctime_max, valor_min, tlq_min, utctime_min

a_5min_033 have 3million of rows, aprox.

I would like to improve the response, 1st time.

Could you advise me on how to get better performance?

In the attachment, I send the script of creating the table, indexes.

Please add an execution plan (see docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm). How is the table defined? Any indexes? — Trinimon
– Trinimon, Commented Jul 29, 2014 at 19:37
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 — user2048092
– user2048092, Commented Jul 29, 2014 at 19:53
CREATE TABLE A_5MIN_033 ( UTCTIME DATE NOT NULL, POINTNUMBER INTEGER NOT NULL, SITEID INTEGER, VALOR_INST FLOAT(126) DEFAULT (0.0), TLQ_INST INTEGER DEFAULT (32), VALOR_PROM FLOAT(126) DEFAULT (0.0), TLQ_PROM INTEGER DEFAULT (32), VALOR_MAX FLOAT(126) DEFAULT (0.0), TLQ_MAX INTEGER DEFAULT (32), UTCTIME_MAX DATE, VALOR_MIN FLOAT(126) DEFAULT (0.0), TLQ_MIN INTEGER DEFAULT (32), UTCTIME_MIN DATE); — user2048092
– user2048092, Commented Jul 29, 2014 at 19:55
CREATE UNIQUE INDEX A_5MIN_033_PK ON A_5MIN_033 (POINTNUMBER, UTCTIME) LOGGING NOPARALLEL; CREATE INDEX A_5MIN_033_VALOR_INST ON A_5MIN_033 (UPPER(TO_CHAR("VALOR_INST"))) LOGGING NOPARALLEL; — user2048092
– user2048092, Commented Jul 29, 2014 at 19:56
Hard to read in a comment :( ... have you tried to use two separate indexes for POINTNUMBER and utctime? Depending on cardinalities this might result in better performance. Is your query much faster if you omit the ORDER BY? — Trinimon
– Trinimon, Commented Jul 29, 2014 at 21:52

Gordon Linoff · Accepted Answer · 2014-07-29 19:39:01Z

1

I would start by creating an index on (POINTNUMBER, utctime):

create index idx_a_5min_033_pointnumber_utctime on a_5min_033(POINTNUMBER, utctime);

The extra time initially is probably for loading the data into memory. The timing suggests that the query is doing a fully table scan. Note that this is speculation. To really understand the execution path, you need to use explain plan or similar functionality.

answered Jul 29, 2014 at 19:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Slow response of Oracle query, using functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related