Tuesday, April 22, 2014

Install PHP OCI8 extension for Codeigniter on Mac OSX

  Working on a DBA dashboard using Codeigniter and Bootstrap , the most popular platform to build professional looking web apps. To make easy development, I installed MAMP on my MAC, so I can have an environment with me on the go. Being primarily focused on LAMP development, MAMP installed ready for MYSQL connection, however if you need connect to Oracle database, you need a little bit of work. oci8 is not fully configured and you need to do it manually.
  To save you some time I compiled a list that works for me so you don't have to crawl all over internet to find them.
  Among all the blogs and posts I checked, this one enabling-oracle-oci8-php-extension-on-os-x came closest to a full list, it only missing the part how to get PECL and autoconf works on your MAC.

Install PEAR on your MAC 

curl -O http://pear.php.net/go-pear.phar sudo php -d detect_unicode=0 go-pear.phar

Download these and unzip into same Dir
Create a bunch of symbolic link for compilesudo cp instantclient_11_2/sdk/include/*.h /usr/include
sudo cp instantclient_11_2/sqlplus /usr/bin

sudo cp instantclient_11_2/*.dylib /usr/lib
sudo cp instantclient_11_2/*.dylib.* /usr/lib

Now move to the /usr/lib directory and create the following link:sudo ln -s libclntsh.dylib.11.1 libclntsh.dylib

At this point, when you runsudo pecl install oci8You likely to get these errors:
Cannot find autoconf. Please check your autoconf installation and the $PHP_AUTOCONF environment variable.

autoconf need to manually installed on MAC
curl http://ftp.gnu.org/gnu/autoconf/autoconf-latest.tar.gz > autoconf.tar.gz

Untar it
tar -xvzf autoconf.tar.gz
cd autoconf-2.69 ./configure 
make sudo make install 
export PHP_AUTOCONF=/usr/local/bin/autoconf

The oci8 installation should be successful after this. These are the messages for successful installation.
sudo pecl install oci8
Installing '/usr/lib/php/extensions/no-debug-non-zts-20100525/oci8.so'install ok: channel://pecl.php.net/oci8-2.0.8configuration option "php_ini" is not set to php.ini locationYou should add "extension=oci8.so" to php.ini

Monday, March 31, 2014

Something about SQL Server Isolation Level

  As we all know Oracle's default transaction isolation level is Read Committed. That means you can read data from changes made by other session as long as it's committed. Readers are not blocking writers and vice versa.

   The default isolation level of MS SQL Server is also Read Committed, however, from time to time our users saw their select blocked by other sessions and after they added NOWAIT hint, you get these errors
  Lock request time out period exceeded
  I did a quick testing and confirmed the observation. A select statement will be blocked by uncommitted transaction on underlying table and if put NOWAIT hint, the query will exit will the error. 
  There's another hint NOLOCK in SQL Server, however, that's definitely not preferable option, because the behavior of NOLOCK is same as setting transaction isolation level to READ UNCOMMITTED. Which mean you could read dirty data that changed by other session but not committed yet.
  After a little digging, I found SQL Server documented this behavior. Basically you need to set database option READ_COMMITTED_SNAPSHOT to ON, by default it was set to OFF.

  select * from sys.databases

  The SNAPSHOT serving the similar function as Oracle Rollback segment, holding pre-image of changes made by transaction. I am surprised SQL Server by default setting it to 0 (OFF) which essentially disabled default isolation level READ Committed. Means all read will require shared lock on table and if there's open transaction, the read is blocked.


  Of course the quick fix is set to READ_COMMITTED_SNAPSHOT to ON which can be done at database level of SQL Server.

Quick follow up on this, as expected there's performance penalty by turning on Read Committed Snapshot. A blog talked about this a while ago. Apparently, SQL Server does not improve much in the last few years. 
Clearly SQL Server is not suitable for high volume read/write load.  Small Biz and office use please.

Performance Impact: The Potential Cost of Read_Committed_Snapshot