CODEWITHSUNDEEP
sqloracle-databaseoracle11g
María Belén Esteban Menéndez
María Belén Esteban Menéndez

Reputation: 397

Query acting extremely slow

I have this query on a Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production that is extremely slow (Fetched 50 rows in 3.859 seconds) and I don't know what else to do to optimize it! t_operation has only 12610 rows !

CREATE OR REPLACE FORCE VIEW "IOT"."V_DEVICES_LIST" ( "PRODUCT", "DEVICE_LIST") AS 
select
  PRODUCT ,
  RTRIM(XMLAGG(XMLELEMENT(E, NAME,', ').EXTRACT('//text()')  ).GetClobVal(),',') AS DEVICE_LIST
from
   t_operation 
where   
   discriminator = 'ANDROID'  and product is not null 
group by
  PRODUCT;

EXPLAIN PLAN :

EXPLAIN PLAN 
  SET statement_id = 'ex_plan1' FOR
 select
      PRODUCT ,
      RTRIM(XMLAGG(XMLELEMENT(E, NAME,', ').EXTRACT('//text()')  ).GetClobVal(),',') AS DEVICE_LIST
    from
       t_operation 
    where   
       discriminator = 'ANDROID'  and product is not null 
    group by
      PRODUCT;

result: 

Plan hash value: 795775875

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             | 11484 |   246K|    11  (19)| 00:00:01 |
|   1 |  SORT GROUP BY     |             | 11484 |   246K|    11  (19)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T_OPERATION | 11992 |   257K|     9   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("PRODUCT" IS NOT NULL AND "DISCRIMINATOR"='ANDROID')

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

AND T_OPERATION:

 CREATE TABLE "IOT"."T_OPERATION" 
   (    "ID" NUMBER(38,0) NOT NULL ENABLE, 
    "DISCRIMINATOR" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "COUNTRY" NUMBER(38,0), 
    "NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
    "COMPUTER" NUMBER(38,0), 
    "PRODUCT" NUMBER(38,0), 
    "ADDRESS" NUMBER(38,0), 
     CONSTRAINT "PK_OPR_ID" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 655360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "IOT"  ENABLE, 
     CONSTRAINT "FK_OPR2ADR_INF" FOREIGN KEY ("ADDRESS")
      REFERENCES "IOT"."T_ADDRESS_INFO" ("ID") ENABLE, 
     CONSTRAINT "FK_OPR2CTR" FOREIGN KEY ("COUNTRY")
      REFERENCES "IOT"."T_COUNTRY" ("ID") ENABLE, 
     CONSTRAINT "FK_OPR2PRD" FOREIGN KEY ("PRODUCT")
      REFERENCES "IOT"."T_TABLET" ("ID") ENABLE, 
     CONSTRAINT "FK_OPR2SRV" FOREIGN KEY ("COMPUTER")
      REFERENCES "IOT"."T_COMPUTER" ("ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "IOT" ;

  CREATE INDEX "IOT"."V_DEVICES_LIST_3" ON "IOT"."T_OPERATION" (UPPER("NAME")) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "IOT" ;

Upvotes: 0

Views: 1604

Answers (2)

Mark Stewart
Mark Stewart

Reputation: 2098

Put an index on discriminator and product columns:

create index i_operation_disc_prod on t_operation(discriminator, product);

Assuming that discriminator='ANDROID' yields less than 10% of the number of rows in table t_operation.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

I don't think you can. Retrieving the data from the table is going as fast as it can - as your filter doesn't seem to be very selective a full table scan is probably going to be suitable anyway, and adding an index is unlikely to help. With that volume of data that is going to be relatively fast anyway.

The time is being spent in the aggregation. I tried using XMLQuery instead of extract() (as that is deprecated) but it was slower, if anything. I also tried with a user-defined CLOB aggregator, and the collect() method (see this popular list of techniques), and they were also a bit slower. Of course that's on my system and with the test data I made up; your results might vary but these alternatives don't seem likely to give you significant gains.

If your aggregated lists of names can exceed 4K - which might only take 17 names per product - then you can't use listagg(), and presumably what's why you're trying to use XMLAgg in the first place.

Unfortunately it seems you just have to live with the overhead that involves, form the XMLType and CLOB manipulation.

Upvotes: 1

Related Questions