JDE slow, missing indexes? find it fast… fix it fast!

Here is a basic SQL that will tell you if you are missing any indexes (PK or other) for oracle based upon your current central objects.

Note that there is a difference in the naming of the unique index (_PK), so the large union.

select trim(tpobnm) || '_' || tpinid  as jdeindex
from py900.f98712 
where tpuniq <> 1
and not exists
(select 1 from all_indexes
where owner = 'CRPDTA'
and trim(tpobnm) || '_' || tpinid = index_name)
and exists
(select 1
from all_Tables
where owner = 'CRPDTA'
and table_name = trim(tpobnm))
union
select trim(tpobnm) || '_PK'  as jdeindex
from py900.f98712 
where tpuniq = 1
and not exists
(select 1 from all_indexes
where owner = 'CRPDTA'
and trim(tpobnm) || '_PK' = index_name)
and exists
(select 1
from all_Tables
where owner = 'CRPDTA'
and table_name = trim(tpobnm))
order by 1 desc;

The results will tell you quickly what you are missing.  This is a nice quick sanity check.

I can admit that this works…  For me, I see the following in the results:

F00151_PK
F0006_7
F0006_5
F0006_4
F0006_2
F00021_PK

So then I look in sqldeveloper and see

Then I look in TDA and find:

Nice one SQL – missing indexes.

I grab the definitions and create them.  Note that I’m using compression too.

CREATE INDEX "CRPDTA"."F0006_2" ON "CRPDTA"."F0006" ("MCCO")
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 "CRPINDEX2"
PARALLEL COMPRESS 1 ;

CREATE INDEX "CRPDTA"."F0006_4" ON "CRPDTA"."F0006" ("MCSTYL", "MCCO")
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 "CRPINDEX2"
PARALLEL COMPRESS 1 ;

CREATE INDEX "CRPDTA"."F0006_5" ON "CRPDTA"."F0006" ("MCSTYL", "MCFMOD")
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 "CRPINDEX2"
PARALLEL COMPRESS 1 ;

CREATE INDEX "CRPDTA"."F0006_6" ON "CRPDTA"."F0006" ("MCAN8" DESC)
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 "CRPINDEX2"
PARALLEL COMPRESS 1 ;

CREATE INDEX "CRPDTA"."F0006_7" ON "CRPDTA"."F0006" ("MCCLNU", "MCPCTN", "MCDOCO")
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 "CRPINDEX2"
PARALLEL COMPRESS 1 ;

And run the SQL again:

No more F0006 – you’d think that it works!

Why did I do this?

Take a look at my IO!  12GBs of physical reads a second…  WHAT!  I needed to track this down fast.  I managed to find the problematic SQL and then also noticed that an index was missing on the F42119…  Okay, not a problem.  But I wanted to make sure that there were no other missing indexes.