EXPERT RESPONSE
Have you done an Explain Plan on the query to see what access path it is using? The most efficient access path from what you wrote below would be one using the index composed of effective_t and ar_account_obj_id0 columns. If it is not using this index, then verify that the index is not disabled or invalid. Also verify that your table and index statistics are current.
If it is using this index, then the next thing I would check is your top Wait events and average read times on your tablespaces and datafiles in your statspack or AWR reports. If your average read times in the report is high, greater than 25 ms, then you need to check your disk I/O subsystem.
Since this table contains over 30 million rows, if it were me, I would seriously consider partitioning the table and using parallel query. That might be the best way to optimize it if everything else checks out.
|
