Printable View
DBAs,
We have a troublesome application whose performance is very poor.
I turned on the monitoring for all the indexes belonging to that application.
After 24 hours monitoring, I found 210 out 425 indexes have not been used.
60% of those unused indexes are foreign key related.
Can I get rid of those unused indexes?
Especially those on the big tables.
Is it a good idea?
Thanks!
NO.
Indexes on FK are needed otherwise you will have locking issues.
Tamil
Thanks!Quote:
Originally Posted by tamilselvan
But after 24 hours monitoring, they are not used at all.
so?
I agree, leave FK's indexed.
Your space saving benefits would soon be overshadowed.
That was true for pre-9i databases and only, if you deleted a parent key (with DELETE CASCADE defined on FK) or if you updated a PK column of the parent that was referenced by child's FK.Quote:
Originally Posted by tamilselvan
But since 9.0, even when any of those two (rather unusual) situation occurs, Oracle doesn't need index on FK to prevent locking of child table. In short, indexes on FKs are not needed any more to prevent locking.
Modic, I disagree with your points.Quote:
Originally Posted by jmodic
I do not know how you tested.
Here is my test.
The following demo was tested in Oracle 9.2.0.5
The easiest way to check FK Indexes are needed is disable
table lock on the child table.
SQL> get cr_p
1 CREATE TABLE supplier
2 ( supplier_id numeric(10) not null,
3 supplier_name varchar2(10) not null,
4 contact_name varchar2(10),
5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
6 );
7 CREATE TABLE products
8 ( product_id numeric(10) not null,
9 supplier_id numeric(10) not null,
10 CONSTRAINT fk_supplier
11 FOREIGN KEY (supplier_id)
12 REFERENCES supplier(supplier_id)
13 ON DELETE CASCADE
14* );
15
SQL> @cr_p
Table created.
Table created.
SQL> desc supplier
Name Null? Type
-------------------------------- -------- ---------------------------
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(10)
CONTACT_NAME VARCHAR2(10)
SQL> insert into supplier values (1, 'Name-1','Scott');
1 row created.
SQL> insert into supplier values (2 , 'Name-2','Tom');
1 row created.
SQL> commit;
Commit complete.
SQL> desc products
Name Null? Type
--------------------------------------- -------- ---------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SQL> insert into products values (1,1);
1 row created.
SQL> insert into products values (2,2) ;
1 row created.
SQL> commit;
Commit complete.
REM The next command is very important to understand how Oracle works
SQL> alter table products disable table lock;
Table altered.
SQL> delete supplier where supplier_id = 1 ;
delete supplier where supplier_id = 1
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for PRODUCTS
Why should I get an error here?
Why should oracle try to acquire "Table Lock" on products?
I am just deleting one row from the parent table and one row from the child by
way cascade delete.
SQL> rollback;
Rollback complete.
SQL> alter table products enable table lock;
Table altered.
SQL> delete supplier where supplier_id = 1 ;
1 row deleted.
SQL> commit;
Commit complete.
Once Oracle table lock was enabled, Oracle was able to delete rows on both table.
Let us see how an Index on FK plays an important role.
SQL> select * from supplier ;
SUPPLIER_ID SUPPLIER_N CONTACT_NA
----------- ---------- ----------
2 Name-2 Tom
SQL> select * from products ;
PRODUCT_ID SUPPLIER_ID
---------- -----------
2 2
I inserted deleted rows again.
SQL> insert into supplier values (1,'Name-1','Scott');
1 row created.
SQL> insert into products values (1,1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table products enable table lock;
Table altered.
SQL> create index products_fk_idx on products(supplier_id);
Index created.
SQL> alter table products disable table lock;
Table altered.
SQL> select * from supplier;
SUPPLIER_ID SUPPLIER_N CONTACT_NA
----------- ---------- ----------
2 Name-2 Tom
1 Name-1 Scott
SQL> select * from products ;
PRODUCT_ID SUPPLIER_ID
---------- -----------
2 2
1 1
SQL> delete supplier where supplier_id = 1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from supplier;
SUPPLIER_ID SUPPLIER_N CONTACT_NA
----------- ---------- ----------
2 Name-2 Tom
SQL> select * from products ;
PRODUCT_ID SUPPLIER_ID
---------- -----------
2 2
Even when the "table lock" is disabled but an index on FK is present,
Oracle is able to delete rows in both parent and child table.
Tamil
so what, drop those unused indexes and if you run something like an erp system wait for the end of the quarter of year end when those processes that run only once and awhile and see what happens. Are they realy hurting you? can you prove it?
Almost by definition, dropping objects that are not used is never going to help improve performance. The trouble here is that you're guessing abouyt the cause of your poor performance instead of measuring the system activity.
Gather a 15 minute interval statspack run tas a starting point, and see what the major waits on the system are and what the most resource intensive SQL statements are. You'll then have a starting point for actual analysis, instead of guesswork.
Nothing wrong with that, Selvan. ;)Quote:
Originally Posted by tamilselvan
Well, maybe the above command was appropriate for yor demonstration, but in reality this is one of the most obscure command that you'll never find in any of the serious application on the oracle database.Quote:
REM The next command is very important to understand how Oracle works
SQL> alter table products disable table lock;
Table altered.
I didn't say oracle doesn't need any lock on the table when you delete the row from the primary that has ON DELET CASCADE on the referencing foreign key. I just said you doesn't have locking isues any more if you don't have forign key indexed. And I still stick to it, despite your demonstration. The fact that you don't need an index on FK to prevent DML operations on child when you delete a row or update a PK column on the parent was one of the more prominent features advertised when Oracle 9i came out. And it wasn't just marketing hype, it realy works at it was advertised.
Because you disabled Oracle from doing it the way it normaly does it with your DISABLE TABLE LOCK command. But in reality you will never use that command, would you? So would you have a locking isues if you don't have an index on that foreign key? No, of course not, if you would allow Oracle to do the things as it was deseigned for.Quote:
SQL> alter table products disable table lock;
Table altered.
SQL> delete supplier where supplier_id = 1 ;
delete supplier where supplier_id = 1
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for PRODUCTS
Why should I get an error here?
It is well known that since Oracle 9i, if you delete a row from the parent table and if the foreign key was defined with DELETE CASCADE, Oracle will have to obtain a shared table lock on the child table for a very short period of time, "but then releases it immediately after obtaining it", as it says in the documentation. So yes, it still needs a shared table lock, but only momentarily - after it gets it, it releases it immediately and other transactions are happily permited to perform DML operations on the child table. They arent blocked, they don't have any locking issues at all.
So, in your demonstration, ommit the "ALTER TABLE DISABLE TABLE LOCK" statement and after deleting a row from the parent table, try to insert/update/delete rows from your child table - you'll se that you are permited to do so, even when your FK is not indexed. You have no locking issues on child table whatsoever! That is a big difference compared from pre-9i releases. If you would try to do so with the pre-9i database, your DML on child table will fail, because the delete on the parent table will cause a permanent (for the duration of the parent transaction) lock on the child table.
Now, I didn't say you don't need indexes on FK columns at all in 9i and above. Perhaps your queries need them. Also, if you have an ON DELETE CASCADE and you delete rows from your parent table, you'd probably wan't to have an index on it for performance reasons. What I said was: you don't need them simply because of the locking issues. That was true until 8i, but since 9i you can perform DML operations on the unindexed child table even while the transactions on your parent table are modifying rferenced primary/unique key.
Demonstration with your example (also performed on 9.2.0):
Now, in another session let's try to perform some DML on the child table:Code:SQL> select * from supplier;
SUPPLIER_ID SUPPLIER_N CONTACT_NA
----------- ---------- ----------
1 Name-1 Scott
2 Name-2 Tom
SQL> select * from products;
PRODUCT_ID SUPPLIER_ID
---------- -----------
1 1
2 2
SQL> select constraint_name, r_constraint_name, delete_rule, status
2 from user_constraints where table_name = 'PRODUCTS' and constraint_type = 'R';
CONSTRAINT_NAME R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------ ------------------------------ --------- --------
FK_SUPPLIER SUPPLIER_PK CASCADE ENABLED
SQL> select * from user_indexes where table_name = 'PRODUCTS';
no rows selected
SQL> delete from supplier where supplier_id = 1;
1 row deleted.
SQL>
So, we vere able to perform DML on child table even though we doesn't have any index on the foreign key columns of the child table. Did we encountered any locking isues? Certanly not.Code:SQL> insert into products values (3,2);
1 row created.
In case we tried that on 8.1.7 or below, our child transaction would be blocked by the permanent child table lock obtained by the transaction on the parent table.
Thanks Modic.
I got the true picture. I missed the insert part that you demonstrated.
In an OPS environment, I used DISABLE TABLE LOCK, that's different story.Quote:
Because you disabled Oracle from doing it the way it normaly does it with your DISABLE TABLE LOCK command. But in reality you will never use that command, would you? So would you have a locking isues if you don't have an index on that foreign key? No, of course not, if you would allow Oracle to do the things as it was deseigned for.
Tamil
Tamilselvan,
I understand, it locks the child table record when we delete partent record(ON DELETE CASCADE). Why we need index on FK's? I am not really understand why we need index on FK's. Can you please explain.
Govind
Quote:
Originally Posted by tamilselvan
Please read the post from the beginning.Quote:
Originally Posted by pranavgovind
Modic and I explained. Prior to 9i, Oracle would lock the child table for the duration of the transaction if there is NO FK index, so other transaction waits until the previous transaction commits/rollbacks. As Modic explained, 9i and 10g place shared lock on the child table but do not prevent other transactions to do DML on other rows on the child table.
Tamil
they might also be needed for performance reasons as well