Oracle DB slow performanse

[savoir]

Hi,

i installed 2 OTRS 3.2.10 instances.

One on Oracle DB 11.2.0.1 database backand
Second on Mysql

Both instances are empty.

The problem is the performance as OTRS Mysql is much, much faster then Oracle DB. On the Oracle side there is enough memory, CPU so the OTRS should "fly", but its not like that.

Regards,
Savo

[jojo]

Oracle will always be slower than MySQL with OTRS as it is slower on plain SQL operations

[savoir]

Hi, thanks for the response.

It seems that it was not problem with Oracle, but with Perl/apache configuration.

So here is the thing. As i had problem with the connection ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS settings, permissions, etc[..] , i found somewhere on this forum solution how to solve this.

The resolution was this:
mv zzz_otrs.conf otrs.conf

Then reconfigure variables in otrs.conf
my example:
SetEnv ORACLE_HOME /oraclient/oracle/orahome
SetEnv ORACLE_SID OCP
SetEnv NLS_LANG AMERICAN_AMERICA.UTF8
SetEnv NLS_DATE_FORMAT 'YYYY-MM-DD HH24:MI:SS'

After that:
service httpd restart

and voila. OTRS on Oracle is working, but SLOW!

So, more googleing and i found that when you rename zzz_otrs.conf to otrs.conf, actually you are bypassing mod_perl which is a performance booster!

So if you want better performance, then you need mod_perl.

How to enable it with Oracle:

1. Edit your DB connection in /opt/otrs/Kernel/Config.pm
2. Add your variables to /etc/sysconfig/httpd

export ORACLE_HOME=/oraclient/oracle/orahome
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG=american_america.utf8
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

3. Modify apache2-perl-startup.pl

add/uncomment:

use Apache::DBI;
Apache::DBI->connect_on_init('DBI:Oracle:sid=OCP;host=ocpserver.com;port=1521','user','pass');
use DBI ();

# enable this if you use oracle
use DBD::Oracle ();
use Kernel::System::DB::oracle;

After that restart httpd, login, and you will see that OTRS is much, much faster.

Regards!