Hi,
I'm on 12c PDB with a performance issue. Got a query that takes to run ~40-45 seconds for the first time and then all the later executions takes ~0.1 second.
QUERY: SELECT categoryid,count(categoryid),customertype,count(customertype) FROM subscription WHERE dtime IS null AND companyorganisationnumber IN ('12345') GROUP BY categoryid,customertype;
Explain plan says it doesn't do a full table scan, rather index range scan on companyorganisationnumber:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962970421
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 312 | 3797 (1) | 00:00:01 |
| 1 | HASH GROUP BY | | 6 | 312 | 3797 (1) | 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED | SUBSCRIPTION | 33918 | 1722K | 3795 (1) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | S_CORG | 48606 | | 90 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------
The index ddl is:
CREATE INDEX "XTAS"."S_CORG" ON "XTAS"."SUBSCRIPTION" ("COMPANYORGANISATIONNUMBER")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED LOW
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 "XTAS"
My question is if it's normal that it runs that long the first time? Am I missing something else or should I simply increase the library cache to avoid aging and have blocks in cache for a longer period of time?