Looking for ideas

[ocp2b]

I'm looking for any ideas that might help me out with a problem.

Some background information:

Oracle 8.1.6.0.0
Windows NT 4.0 SP6
256 Meg Ram
1.0 ghz CPU

In my database there are four tables. Table "A" is the Parent,
tables "B","C" and "D" are the children. I have a foreign key on the children tables that references the parent table. The referential constraint is to cascade on delete. Currently, the child tables DO NOT have indexes on the foreign key fields.
The parent table does have an index on the referenced fields. Thinking I could increase performance, I added an index to each child table on the foreign keys. This slowed down performance. (By 34%) These tables have 800,000 + rows each.

Does anyone have any ideas how I can increase performance on these tables?

I'm open for any and all suggestions.

Thanks.

[sambavan]

First of all what do you mean by slows down performance, does the performance go down when you create index on the foreign key?

Have the indices created on those foreign keys and get the tables and indices analyzed. Also intereted to know what is your optimizer mode currently?

Mostly analyzing the tables periodically would solve the problem to a great extent...

Sam

[pando]

how about showing some explain plan and your queries

[ocp2b]

The indexes where created with on the child tables with "create index ... on "A" (field 1,2)". The performance when down after the indexes were created. No I did not analyze the indexes or tables. - Good thought.

Also, how can I get the optimizer mode information. The tables are being accessed via an application that I can't change. The way I gathered the performance statistics was to set SQL_TRACE = TRUE and TIMED_STATISTICS = TRUE in my init.ora. I then used TKPROF to generate a report to get the timings.

Also, the application writes to a log file the date and time(hh:mm:ss.ss) plus a message about what it is doing.

[pando]

select name, value from v$parameter where name like '%optimizer%';

or

show parameter optimizer

[Shestakov]

May you show query, explain plan and SHOW SGA.
May you say little bit about index selectivity in parent/child tables.

[ocp2b]

Unfortunately, I cannot get the code. It is in a third party application. And I'm not sure how to get an explain plan or index selectivity from outside of the application code. Does any have any suggestions? I feel like my hands are tied. I can touch,see,feel,manipulate, etc. the database, but I can't do anything with the code. Is there a log file, parameter setting, anything that I can do from an SQL> prompt or svrmgrl> prompt that will get me this information?

[pando]

you can get the SQL by tracing a session or from v$sqlarea from that you can get the execution plan, index selectivity you get it from data dictionary

[ocp2b]

Thanks Pando. I'm familiar with the SQL Trace and the v$sqlarea. but I'm not sure I understand how to get the index selectivity from the data dictionary. Could you explain with more detail.

Thanks again.

[pando]

from user_indexes or dba_indexes there is a column name like distinct or smth like that I dont know in memory but it's distinct and something more, but of course to get an accurate value from those views you have to analyze your index first