Hi there,
IDOUtils queries differ quite a lot – some of the are just executed during startup, while others happen all the time. By analyzing the performance on our Oracle database with grid it came to the top queries just like for
- servicechecks, servicestatus
- hostchecks, hoststatus
- timedevents
- programstatus
But how to improve the performance of those queries when they are called all the time?
Well, the query as is is always the same, only the values happen to change. So the basic idea is to prepare the statements with value place holders and if it comes to the query, just to bind the paramaters (values) to the prepared statement and execute that. This is a real performance boost compared to putting the query within the rdbm cache all the time.
Generally speaking the query statements are prepared after database connection and the statement handle is stored within the global dbinfo object (where the connection handler resides too).
dbinfo.oci_statement_programstatus = OCI_StatementCreate(dbinfo.oci_connection);
OCI_Prepare(dbinfo.oci_statement_programstatus, MT("MERGE INTO table USING DUAL ON (v1=:X1) WHEN MATCHED THEN UPDATE SET v2=:X2 WHEN NOT MATCHED THEN INSERT (v1, v2) VALUES (:X1, :X2)"))
When a query should be executed, all values will be binded (X1, X2) to the statement.
OCI_BindUnsignedBigInt(dbinfo.oci_statement_programstatus, MT(":X1"), (big_uint *) value1)
OCI_BindString(dbinfo.oci_statement_programstatus, MT(":X2"), (char *) value2)
Then the query gets executed.
OCI_Execute(dbinfo.oci_statement_programstatus);
Well it sounds quite simple but regarding the architecture of *DOUtils it was a hard nut to crack. The most common problem was the query buffer building – each unixtimestamp conversion is done before query building and sending the query. That does not fit for prepared statements where the whole query is pushed into the database cache.
Within the code, there is an char* array which gets the SQL-code from ndo2db_db_timet_to_sql and this is then printed to the whole statements. Not very useful since you may paste that right within each query. For the prepared statements, I’ve added all plain unixtimestamps to the data[] array and then binding the values directly.
(SELECT unixts2date(:X3) FROM DUAL)
So the bind param task has been done for the initial steps, improved delete statements and other improvements need to be implemented.
Another thing which was quite nasty is that Oracle support was dependant on libdbi, but it was not even used. So I decided to split the code completely and change configure. If you use –enable-oracle it will only require ocilib to work, it does not complain about a missing libdbi. The other way around it also works fine just like it was.
Conclusion to that – you won’t need libdbi to get Oracle support for Icinga IDOUtils – just ocilib.
Those improvements have been pushed to actual GIT master und you are very welcome to test and report bugs! =)
After…
