Peter Ramm's Oracle performance analysis stuff

A few years ago I wrote a blog post about Identify non-relevant indexes for secure deletion . One additional aspect that is possibly worth to consider before deleting an index is: Are there any optimizer hints in SQL statements, SQL Patches, SQL Profiles or SQL Plan Baselines pointing to that index? In reality, they should not be relevant because otherwise this index would not be marked as unused since longer time. But, for clean structures it would be good to know that the index name is referenced somewhere. You can use this SQL to scan for occurrences of the index name in SQL Profiles, Patches or SQL Plan Baselines. In Baselines you'll usually not find the index name itself, because they mostly address the index by its column names. SELECT DECODE(so.Obj_Type, 1, 'SQL Profile', 2, 'SQL Plan Baseline', 3, 'SQL Patch', 'Unknown') Type, so.Name, sod.Comp_Data, sod.Category FROM sys.sqlobj$data sod JOIN sys.sqlobj$ so ON so.Category = sod.C...

 If you want avoid blocking locks while using referential integrity constraints in an Oracle-DB, it is suggested to protect a foreign key constraint by an index. Not always it is useful and necessary to protect a foreign key constraint with an index. But this question was considered separately in this blog post. The question today is: What column structure must an index have to be accepted by the Oracle DB as protection for a multi-column foreign key constraint? To answer this question, several scenarios are tested. Test setup: CREATE TABLE Dim(ID1 NUMBER, ID2 NUMBER, ID3 NUMBER, Name VARCHAR2(200)); ALTER TABLE Dim ADD CONSTRAINT Dim_PK PRIMARY KEY (ID1, ID2, ID3); INSERT INTO Dim VALUES(1,1,1, 'First'); INSERT INTO Dim VALUES(2,2,2, 'Second'); CREATE TABLE Fact(ID1 NUMBER, ID2 NUMBER, ID3 NUMBER, Name VARCHAR2(200)); ALTER TABLE Fact ADD CONSTRAINT Fact_Dim_FK FOREIGN KEY (ID1, ID2, ID3) REFERENCES Dim(ID1, ID2, ID3); Scenario 1: No index second session is blo...