Oracle Database performance tuning (on database level)

Question

I have a big table in oracle database (it's almost 400 million rows) and a program which connects to that table and executes a query.

I don't have any possibility to modify the program neither the database structure.

The problem is that the query execution takes long time. Sometimes 11 seconds, which in my case is really bad. But the query is always the same. It's something like:

SELECT COL1, COL2 FROM TABLE1 WHERE upper(COL3) = upper('abc')

and the result is ~5 rows.

These are the indexes in the table from COL3:

  CREATE INDEX "DATABASE1"."PIPTABLE1_2" ON "DATABASE1"."PTABLE1" (UPPER("COL3")) 
  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 "DATABASE1_INDX";
  
  
  CREATE INDEX "DATABASE1"."PIPTABLE1_3" ON "DATABASE1"."PTABLE1" ("COL3") 
  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 "DATABASE1_INDX";

Is there any way to optimize it on database level?

(Oracle Database Version = 19c)

In the execution plan below I had to hide real names and values. (Of course now (Saturday) the query worked fast)

execution plan

Answer 1

Even if you have an index on a column, if the query is going to return many values, then its possible that those values are scattered across many physical data blocks. Thus if you were getting (say) 10,000 rows back, you might have read 10,000 different blocks which, even via an index, is going to cost you time.

You might be able to look at clustering the data to reduce this cost. You can use attribute clustering on the table for this, followed by the an online move.

alter table xxx add clustering by ...
alter table xxx move online;

More details and demo in this video (including pros and cons)

https://youtu.be/UndmvLZ4KSI