How to set session variable skip_unusable_indexes to true in a PL/SQL package to speed up a table delete/insert?

Are you issuing the ALTER SESSION statement in the same session that the stored procedure is using? Or is that ALTER SESSION executed in a separate session?

You can embed the ALTER SESSION in your PL/SQL with dynamic SQL, i.e.

BEGIN  EXECUTE IMMEDIATE 'ALTER SESSION SET skip_unusable_indexes = TRUE';  <<more code>>END;

Are some of the indexes unique (or used to enforce a unique constraint)? As the skip_unusable_indexes documentation states

Note: If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

If that is the case, can you disable the constraint and/or change the index(es) to be non-unique?

A quick sample of the difference between unique and non-unique indexes. Note when you have an unusable unique index, skip_unusable_indexes does not suppress the ORA-01502 error as it does when you have an unusable non-unique index.

SQL> create table a (  2    col1 number  3  );Table created.SQL> create unique index idx_a on a( col1 );Index created.SQL> insert into a values( 1 );1 row created.SQL> commit;Commit complete.SQL> alter index idx_a unusable;Index altered.SQL> insert into a values( 2 );insert into a values( 2 )*ERROR at line 1:ORA-01502: index 'SCOTT.IDX_A' or partition of such index is in unusable stateSQL> alter session set skip_unusable_indexes = true;Session altered.SQL> insert into a values( 2 );insert into a values( 2 )*ERROR at line 1:ORA-01502: index 'SCOTT.IDX_A' or partition of such index is in unusable stateSQL> drop index idx_a;Index dropped.SQL> create index idx_a_nonunique on a( col1 );Index created.SQL> alter index idx_a_nonunique unusable;Index altered.SQL> insert into a values( 2 );1 row created.