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

As long as you already have function-based index on the column, you need to provide an execution plan. Otherwise we cannot guess what is slow — astentx, May 20 '23 at 14:46
Please provide an Active SQL Monitor report. However you could try a covering index; ie include COL1 and COL2 along with upper(COL3) — BobC, May 20 '23 at 15:18
OT, but upper('abc') can just be 'ABC'. Also have the table statistics been updated? — OldProgrammer, May 20 '23 at 15:39
Attached the execution plan. I had to hide real names and values. (Of course now (Saturday) the query worked fast) — canonier, May 20 '23 at 18:46
then there is something else going on if you say it works fast when nobody else is working? May be a million other reasons, and that is not something we can debug here. — OldProgrammer, May 20 '23 at 18:51

score 1 · Answer 1 · answered May 24 '23 at 03:18

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

Oracle Database performance tuning (on database level)

1 Answers1