Oracle database slow after schema IMPORT.
Some days ago I got called from one of my client regarding the slowness of one of the data synchronization process after performing IMPORT operation of the schema. I called the onsite DBA to understand what exactly they have done in activity and after the activity. DBA told me that they have performed the IMPORT schema and after that they have gathered the statistics of the all the tables of that schema. Then I asked them how much processes got impacted due to this, whether all other functionality were working properly or not. DBA confirmed these things with user and told me that only data synchronization process was executing slow and all other applications and processes were working fine. Earlier that process used to take 45 minutes for completing, now that process was executing from 7 hours and still running.
- I started the troubleshooting with session monitoring as impacted process was still running. There I observed that the session was executing the said package by application team.
call PKG_DATA_SYNC.SP_…..(:1, :2, :3, :4)
- Session was showing this SQL text only. I had not observed any select or DML query for this session. So I have not got the enough information required for troubleshooting from session monitoring. I was observing the events for the sessions but session was showing me the above SQL text only. So I was not able to find that which queries that session was executing. Now what to do next?
- I was thinking to start the trace for session but first I decided to generate the AWR reports for last two snapshots. From AWR reports I got the queries which were executed by that session.
- In the above snapshots, SQL_ID bp9ckadazatbr is the main process and SQL_ID’s 8aa47kpk0ts5s, 63drhzgzq9uwz and 75nhwyf7nxsgx were the queries executed by the main process. We can see that elapsed time for the sql_id 8aa47kpk0ts5s was 3528 seconds i.e. almost 1 hour and for queries 63drhzgzq9uwz and 75nhwyf7nxsgx had taken 38 Minutes and 18 Minutes respectively.
- Earlier execution time for the whole process was 45 Minutes but after IMPORT activity single query was taking that much amount of time. I queried AWR view DBA_HIST_SQLSTATS to find out how much time these queries were taking earlier (Before IMPORT activity) and I found that all the above said queries was having execution time of around 30 seconds. Now question was that what had caused to these queries as they were taking such huge time to execute.
Finding Root Cause to this problem:
- Then I had checked the execution plans for these queries before the activity and after the activity and found that execution plans got changed for all the queries. Whenever execution plan get changed first check table statistics are stale or not. I checked the table statistics for these tables. Statistics were not gathered for these tables after the IMPORT activity but before starting the troubleshooting DBA told me that they have gathered statistics for all the tables. I asked DBA how they have gathered the statistics. He replied that he created the dynamic stats gathering script from DBA_SEGMENTS view on the filter of segment type = TABLE. I checked the DBA_TABLES view for these tables which shows me that these were the partitioned tables. DBA_SEGMENTS view store segment type of partition table as “TABLE PARTITION”. DBA missed these tables to gather statistics after IMPORT as he had queried on DBA_SEGMENTS view with filter as “TABLE” and this was the root cause for this issue.
I told application team to kill the process and immediately gathered the statistics of these partitioned tables. After this application team started the process again and this time process got completed within time.
Database itself is never slow. Whenever we want to create the statistics gathering script dynamically we should query against DBA_TABLES view and not against DBA_SEGMENTS. Whenever you import table or whole schema, make sure that you have gathered the statistics for these tables.