Thanks for the question, Ingimundur.
Asked: November 07, 2019 - 11:18 am UTC
Last updated: November 20, 2019 - 6:13 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi.
We have a process that runs every night that is beginning to slow down and we need some help to find the resources to analyse the problem.
In our setup, unfortunately both transaction schemas and warehousing (statistics) schemas are kept on the same database. This due to the fact that we do not have a lot of licenses.
We have a table that statistics use, that has 163 columns and around 23 million records. This table could have been partitioned on year, if we had the licensing option for that, but we don't.
The table has 6 different indexes (all no unique) that serve different purpose for statistic selecting.
Every night, statistics runs pl/sql code that during the process, drops all indexes on the table, deletes last 3 years from the table, then fills it up with new data for the last 3 years and in the end creates the indexes again.
We have tried to do this with a materialized view, but it got to heavy to run (would sometimes take more than 24 hours to refresh).
After this is done, the last 3 years are exported into files. For this we use SQLCL in a 3 docker instances that run one after the other.
In the beginning of the year, the export would take about 6-7 minutes for 2017 and 2018 and about 1 minute for 2019 (not much data). In the second quarter, file for 2018 started taking considerably longer time of around 22 minutes (not getting bigger, just small changes in the data), while 2017 stayed the same. 2019 increased in time as well, but that would be normal with more data.
In the later part of the year, all the times have increased, and yesterday we had the following times. 2017: 31, 2018: 46, 2017: 14.
I also ran as a test a count on each year (which are very similar in size, 2019 is the smallest) and this gave me the following results: 2019: 950 sec, 2018: 850 sec, 2017: 350 sec, 2016: 180 sec, 2017: 180 sec. Doing a count on the whole table takes 85 seconds.
When I use explain plan or autotrace, I don't seem to find anything that indicates any problems. Again, we do not have tuning and diagnostic license so I can't use that. I ave also tried following the process checking longops, but nothing seems out of the ordinary.
Where else can I find info in the database on what could be the issue here and are there some simple solutions I can do.
Do I need to do something to the indexes. I though that when an index is dropped and the recreated that it would be analysed automatically in the process. Is it possible I need to do a rebuild on the table (or the indexes).
I am also wondering if it would help to put a bitmap index on the search criteria in the 3 scripts we run for export. The columns used as predicates are only 4, and the each have less than 20 unique values.
I hope this is understandable and I really hope you can help me.
Regards,
Ingimundur
and Connor said...
Thanks for your patience.
The best way would be to trace the process so you/we can accurately see the time is being lost. The way to do that is with dbms_monitor. Let's say your process looks like this:
plsql_proc1;
plsql_proc2;
plsql_proc3;
where those procs are indicative of each of the major chunks of business processing you're doing. You would then adjust this to be:
dbms_monitor.session_trace_enable(waits=>true);
alter session set tracefile_identifier = stage1;
plsql_proc1;
alter session set tracefile_identifier = stage2;
plsql_proc2;
alter session set tracefile_identifier = stage3;
plsql_proc3;
dbms_monitor.session_trace_disable
You'll then get a trace file for each major section. Throw these through tkprof and look for where your time is being lost. That will let you narrow in on where you need to focus your efforts.
Is this answer out of date? If it is, please let us know via a Comment