Index problem and triggers (for dbas)

[fmorel]

Hi!
I have a problem with a batch which is very slow. SQL_TRACE and TKPROF show that the problem comes from an Index: It is dropped an recreated by it is not analyzed. CBO takes a wrong path in some queries.
The application vendor refuses to modify his batch.

I tried to create a trigger that would analyze the indexes as soon as they are created:



-- with sys user:
grant select on sys.dba_indexes to system;

-- with system user:
create or replace trigger tg_analyze_index after create on schema
DECLARE
cursor c_list is select index_name from dba_indexes
where owner='MYUSER' and LAST_ANALYZED is null;
BEGIN
for id_c in c_list
LOOP
execute immediate 'ANALYZE INDEX MYUSER.'||id_c.index_name||' COMPUTE STATISTICS';
end LOOP;
end;
/



When creating an index I get:

-- with myuser
SQL> create index i2 on t2(X);
create index i2 on t2(X)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 8

*****************************
I read in Oracle documentation that triggers may not perform DDL.
Does anyone has a better idea to solve my problem ?

 

[sem]

I'd recommend you to compute statistics on a regular basis rather than during creation, as with changing table data statistics becomes outdated.

As for your error, you can not use DDL in system trigger.

 

[fmorel]

I agree with you. And that's what I do: I analyze objects on a regular basis.
But the problem is that this horrible batch drops some indexes and recreate s them without analyzing them...
the batch does the following actions:
- 1 - Drop indexes
- 2 - empty tables
- 3 - load tables
- 4 - create indexes (NO STATS)
- 5 - query tables.
...

(5) --> queries are very slow because CBO takes a wrong path, because indexes were not analyzed (4).

 

[DaPi]

Hi there,

a) I don't expect this to work: have you tried calling a proc from the trigger and putting the ANALYZE in the proc?

b) a messy solution: have the trigger insert a row or write to a file somewhere and have a second process loop waiting for this, which then does the ANALYZE. I have had no probs running ANALYZE 'asynchronously' like this. Your step (5) queries will go slow to begin with, but those after the ANALYSZE should be up to speed.

 

[fmorel]

Thanks a lot DaPi.
I finally found a "clean" solution: the vendor accepted to modify his script after I proved him that there was a real problem in his batch.
It works well now.
However, I did not find a way to make this trigger work.

Tanks for help