Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Temporarily Switch off "Optimizer Statistics Gathering"

MaMerker
Contributor

Question: Is it possible to switch of "Optimizer Statistics Gathering" temporarily in a session?

Background of Question:

We finished a complete "continuous syncronisation" of our Oracle database with > 1000 tables based on autoamtically created triggers (insert,update,delete) and automatic use of primary keys (some are multifield primary keys). To synchronize data from Oracle to Exasol we currently use Database Link via ODBC and Oracle stored procedure. It works fine now and stable.

Initial migration (and therfore our desaster recovery), before we start continous synchronization, is done by Exasol Import-Script: https://github.com/exasol/database-migration/blob/master/oracle_to_exasol.sql

(By the way, is there a special Oracle Database Link available from Exasol (so that we do not need to use ODBC, see https://docs.oracle.com/database/121/OTGIW/configodbc.htm#OTGIW1041 )

For this purpose it is necessary to run DML-scripts against Exasol (inserts and deletes via database link). 

Everything works fine now, but we found out that the execution times of a single SQL-Insert into Exasol takes approx. 5 to 10 times longer than using any other target database.

We think, that this is due to the idea of Exasol, beeing fast in select, and accepting slowness in DML jobs. In general it is ok.

But we see, that with Exasol "Import ..."-Statements, thousands of DML-tasks can be done very fast. And we guess, that the "Optimizer Statistics Gathering" is switched off at the beginning of "import ..." and switched on at the end of "Import ...".

So, what we would like to do to speed up this synchronization is to switch of "Optimizer Statistics Gathering" before running a batch of DML-jobs (mixed deletes and inserts) and switch it on afterwards (or let run "Optimizer Statistics Gathering" afterwards by any command.

Due to this fact, we currently are able to handle about 20 DML statements per second, that might be enough for our change volume, but we did expect higher performance.

Snyhronisation procedure is currently started every 2 seconds. That means, Exaslo is synchron to Oracel with just a very little delay.

 

Thanks for your feedback (especially to @exa-SebastianB, because this is a follow up request to previous communications on continous sync). 

11 REPLIES 11

mwellbro
Xpert

Hi MaMerker,

I´m not quite sure what you try to gain from disabling Autocommit ? If I understood your architecture correctly ( from the script a few topics back ) then you´re going for different pquerys for every insert, so you´d necessarily incur a commit at the end of each individual statement - or did I get this wrong ?

The second thing I don´t get is how the settings at ora should impact you at all, since the commit you profiled is purely exa.
Could you provide some kind of snipped that could give us a closer look on what is happening ?

Also: "I do not have a possibilty to run command like "ALTER SYSTEM|SESSION SET PROFILE=’ON’;"" => how did you get your previous profiles ? Different DB-Instance ?

exa-SebastianB
Team Exasol
Team Exasol

The user guide says this about index generation:


    Exasol creates and maintains required indexes automatically. It is not recommended to interfere in this.

 

Now to the question of whether manual committing can improve performance. The commit overhead on single row inserts is huge -- hence the hint to accumulate them in one insert programmatically. You could check EXA_SQL_LAST_DAY and compare the situation with auto-commit for every single row insert and manually commiting after a 1000 or so inserts. The later case should be faster. How much you will have to measure in your concrete case.

I am not an ODBC expert, but the documentation says you can switch auto-commit off via connection string.

All in all its always better to improve the algorithms you use for data migration than to try tweaking database parameters.