Thanks for the question, kishan.
Asked: July 05, 2019 - 2:11 am UTC
Last updated: July 05, 2019 - 6:09 am UTC
Version: 12.1.0.2.0
Viewed 1000+ times
You Asked
I am facing performance issue in oracle database when application user load bulk DML queries into the production database.The job gets hung for some reason.We have added additional redolog files as we observed log switch for every 1 minute.We have extended temp tablespace size,dont find any space related issue,ran the oradebug hang analyser,statspack report and lost somewhere chasing nothing.We dont have license enabled for awr diagnostics pack which is very difficult to identify whats exactly happening.This is an exadata server.Then in logs we found out that
"Index some [sub]partitions of the index have been marked unusable".Other than the error we dont find any useful information in logs during load.Gather statistics and rebuild index has no improvement. So need some suggestions on this to tunnel the bottleneck.
and Connor said...
The best way is to trace the component, ie
- dbms_monitor.session_trace_enable(waits=>true)
- run the code
- dbms_monitor.session_trace_disable
and look at the tkprof formatted trace.
Start with that - once you have a trace, we can help you with it to analyze it.
Is this answer out of date? If it is, please let us know via a Comment