Gena01 Blog

Leveraging Oracle connection metadata functionality

Fri, 21 May 2010 23:14:22 GMT

One little known feature of Oracle is connection metadata functionality. It might not sound as important as it actually is.

Here is a bit of a back story. When Oracle 10g came out one of the biggest features that it added was web-based Oracle Enterprise Manager (OEM) interface. For the first time in years developers and dbas got a web interface dashboard that finally shows what's going on inside the database. This one feature is a great breakthrough. Finally no need to download some cryptic looking scripts and trying to understand the multi-page reports to get a glimpse at the status of your database and which query just spiked the database load. Now with OEM finding what the query is is really easy (you might need to buy Oracle Performance pack to monitor vitals and performance information). So you know which query caused the spike. Do you know where that query is in your application code? Do you even know which of 10 different applications this one is coming from? You might have an idea based on table names to at least identify the application, and then you are very lucky if you can do that. A common way to solve this sort of query identity crisis is to start embedding comment information identifying where that query can be found in your code. But you already have a ton of applications written, having to go through every query to add this meta information is a ton of dev effort. What do you do?

With pecl oci8 1.4.0 release a couple of new functions were introduced that weren't available before. From changelog ( http://pecl.php.net/package-info.php?package=oci8&version=1.4.0 ):

Quote

1. Introduce connection attribute functions:

oci_set_module_name
oci_set_action
oci_set_client_info
oci_set_client_identifier

These set values that are visible/used by the database. They are useful for tracing, authentication and auditing.

So what can you do with all these new functions?

oci_set_client_info -> this is your application identification information. Something like App V1.2
oci_set_client_identifier -> as far as PHP manual explains it it's a possible application username that is logged into your applcation.
oci_set_module_name -> which module of your application is this related to
oci_set_action -> this one idenfies which action/function is performing this set of queries.

All these functions provide a lightweight way of attaching more information to your client connection without executing any more queries on the server. Actually this information is actually getting sent on next round-trip request to the server. So it's very lightweight. These functions are also great at providing tracing functionality that allows dbas to come back to developers and work with them on getting their application to be much better than before. It also makes things a lot easier to find in big or numerous applications within the enterprise. On server side this information can be queried from V$session view among other places. It will also show up in various places within the OEM interface. Also optionally tracing and logging can be turned on the server side based on some of specific identifiers set by your application. This would provide server-side tracing logs that would let you trace down problems you might be having with some pieces of your application.

Personally using these functions all over your code might be an overkill. It's also more of your own call of how much of it you want to integrate into your application. One thing I would at least recommend is to at least identify your application and/or possible module information and possibly the client identifier to see who is causing all the problems for you.

Gena01

Oracle query validation

Fri, 21 May 2010 22:39:10 GMT

So we got an interesting use case at work where we have an admin interface to setup filters in our system. Our staff is able to enter expressions in the web interface and we have a way of checking that whatever they enter is valid or not.

While trying to optimize the validation code to do stricter checking with lower overhead I ran into a little gotcha. oci_parse() function doesn't actually do validation. This was a huge surprise to me and something I didn't grok. I went and pulled up the PHP manual page that has a note (from http://php.net/oci_parse ):

Quote

Note: This function does not validate sql_text. The only way to find out if sql_text is a valid SQL or PL/SQL statement is to execute it.

This was a big surprise to me. I thought that you use oci_parse to parse the statement and then oci_execute() to actually execute/run the query with bind variables. It seems that oci_parse does basic sql parsing on client side without going to the server. There's also no validation of the query, It seems that the only thing oci_parse() does is prepare for using bind variables to be used later with oci_execute().

For me the biggest challenge was that an improper or big and complex filter expression could run quite a bit and I had no reason to actually run the query, I just needed a way to validate it. From PHP Manual it seemed that the only option I had was oci_execute(). I didn't want to quit and admit defeat just yet. I reached out to my Oracle friend who pointed me to another php_manual page. It seems that oci_execute has some little known flags that you can pass. Specifically ( from http://www.php.net/manual/en/function.oci-execute.php ):

Quote

OCI_DESCRIBE_ONLY Make query meta data available to functions like oci_field_name() but do not create a result set. Any subsequent fetch call such as oci_fetch_array() will fail.

The description of this flag is not quite obvious, but this one will do something like explain plan for the query and provide back projected column information. As a side-effect it will also return an error if the query is not valid. Bazinga!!!

Just wanted to share this with the community in case others will find this useful for either validating SQL queries or to be able to pull up column information that a query will return without actually running the query itself.

Gena01

Going to PHP Tek 2010 next week.

Thu, 13 May 2010 20:12:23 GMT

I am heading to PHP Tek 2010 next week.

If you are going to be there come and say hi.

Gena01

My recent learnings with Oracle Instant Client and OCI8 API.

Thu, 13 May 2010 20:10:12 GMT

I was recently involved in upgrading our Application servers to the latest Oracle Instant Client 11.g (11.2.0.1) including installing the latest PHP OCI8 module (1.4.1).

Currently Oracle provides binaries for Linux in RPM and ZIP(?) format. (Side note: It still puzzles me as to why Oracle uses ZIP files for Linux binaries as ZIP is common to Windows and is not always available or generally used on Linux.)

I thought I was well prepared for the upgrade and we had enough time allocated to do some other maintenance tasks in the same window as well so I wasn't worried. I made all the prep work before hand and I was all set. This was going to be a piece of cake. I've done this sort of setup in Development and a bunch of other servers a number of times. I have even created my own setup and steps involved for the whole process. Including a patch to make things easier during the configure process.

A couple of interesting things happened though:
1. RPMs that Oracle provides are broken. They don't define all the required dependencies that they need. I saw this on a cpl of systems where after installing the RPM packages and running sqlplus I was told that a library was missing and that sqlplus couldn't start. This is how I remembered that I also need libaio to be installed.
2. When trying to do 'pear upgrade pecl/oci8' I ran into a rather weird issue. I got a ton of errors and the package wouldn't compile. This had me scratching my head for a little while. As it turns out I had set ORACLE_HOME in my environment (I guess I should document as to what I was trying to achieve by using ORACLE_HOME) and this screwed up configure script. So configure script managed to find the libraries for linking but didn't do sufficient checking for the include directory as it though that a full oracle client was installed. When trying to make it failed to locate oci.h which in turn triggered a ton of errors and warnings.
LESSON: Make sure you unset or never set ORACLE_HOME when trying to compile oci8 from PECL.
3. I had Oracle Instant Client 11.1 installed on another box. This one is an Ubuntu (Debian DEB packaging) VM. Since this was a production server I was trying to take a safe approach. So I used alien to convert RPMs to Debs and then uninstalled everything but 11.1 basic. Then i went and installed 11.2 DEbs along side which was fine until I tried to remove 11.1 basic package. All hell broke loose as it turns out that during conversion Alien found Oracle Instant Client 11.1 and made that a dependency of Oracle Instant Client 11.2. I had to remove 11.1 completely and run alien again to rebuild the DEBs. This was a big surprise to me. I sure hope that Oracle starts producing DEBs along side RPMS.

On the positive side I still managed to complete all of my tasks during the maintenance window. I was also lucky enough that we allocated enough time for the maintenance itself. After talking to one of our Sys Admins (SA's) it might have been a good thing that I was doing the upgrade as SAs are not always knowledgeable as to why things break on the Oracle Client side. Also specific knowledge that ORACLE_HOME can break things that bad is also a little known fact.

I hope somebody finds these notes useful and is able to save a ton of time not repeating these mistakes.

Gena01

CodeWorks 2009 NYC

Tue, 13 Oct 2009 15:29:00 GMT

I had a chance to attend the NYC stop of CodeWorks 2009 tour. NYC was the last stop of the tour. It was a two day event with a tutorial on the first day (Sunday) and the main conference event on Monday with a closing open bar event sponsored by Microsoft. I attended the Monday portion of the conference. It included a lot of familiar faces and some of the core PHP developers sharing their knowledge and projects that they are working on. It was a lot of new content. I have attended a couple of PHP conferences over the years (PHP|Tek 2007 and PHP|Tek 2008). It's too bad that the conference was only one day. It was also great to see old friends and familiar faces from #phpc chat room over on Freenode IRC network. Also considering that this was the last stop of the tour many speakers were a bit tired and anxious to go back home and to their families.

Overall the sessions were packed with hard choices to decide which of three tracks to go to. I do apologize to the speakers of the presentations I couldn't attend. Overall I think the presentation schedule had a nice balance of various types of topics and speakers. It also included some of the NYC local NYPHP members presenting and attending. These included Chris Shiflett and Mitch Pirtle.

We don't have enough PHP conferences here in NYC. The schedule was rather packed with a lot of discussions happening in the hallway. Some speakers wrapped up their sessions and had to go catch a flight back home. I think overall this was a nice gathering and respresentation of NYPHP community.

The only downside was the Marriot Brooklyn location which is a good location to get to, but way too expensive for Brooklyn (I guess because it's so close to Manhattan?). Another big item was missing Internet connection. I was told that the price that Marriott wanted to charge for WiFi was way too expensive. This also limited some of the things that speakers wanted to show, but couldn't due to lack of Internet access.

Overall this was a great conference, albeit a bit too short. ;-)

Gena01

Lack of updates

Fri, 02 Oct 2009 03:01:02 GMT

Has it really been over a month since my last blog entry? So what's happening with me? I have moved into the new place. Not quite settled in yet, got a ton of stuff and decisions to make. My stuff is still disassembled and in boxes/bags. Trying to cope with it for now.

Got some interesting ideas for some new blog entries.

Gena01

Presenting at the next NYPHP Meetup on Tuesday Aug 25, 2009

Fri, 21 Aug 2009 01:23:15 GMT

Come see me speak on Tuesday Aug 25, 2009 at the NYPHP Meetup about Xdebug.

Quote

A as in August and X as in Xdebug. Written by core developer Derick Rethans, Xdebug has grown to be a powerful toolset for both the savvy and new PHP developer. This month we're pleased to have power user Gennady Feldman speak on making the most with Xdebug.

Xdebug is a Swiss army knife of PHP extensions and a must have for any PHP developer and provides invaluable functionality out of the box. We'll cover some of Xdebug's greatest and often overlooked features, as well as it's best known value as a profiler and debugger. Gennady will also share some of his personal tips and tricks for getting the most out of Xdebug, including how to find where your performance bottlenecks are and tracking down where a particular function gets called and/or included from.

Gennady has been programming since the age of thirteen. He was always fascinated with computers and trying to make them do cool stuff. Over the years a personal hobby became a professional career and Gennady started coding php over six years ago. Current challenges include learning best practices, and finding answers to scalability and performance issues. In his spare time Gennady is also an Open Source contributor. He has contributed to BusyBox, Miranda IM and libyahoo2. Gennady also maintains a personal website where people can download free software and read his blog. Gennady is also a regular on the #phpc channel and tries his best to attend NYPHP and NYMySQL user group meetings. The rest of the time is spent with his wife, family and friends.

Thank you to IBM for providing a great presentation space in Midtown Manhattan. As a service to our community, New York PHP Community meetings are always free and open to the public.

Come prepared with a business card to enter book and software raffles.

Find more information and RSVP here: http://www.nyphp.org/

Update: Slides have been posted at NYPHP Site: http://www.nyphp.org/content/presentations/

Thank you,

Gena01

Quiet blog, Follow me on Twitter

Thu, 16 Jul 2009 02:38:46 GMT

I know I haven't been posting much on my blog. I am spending a lot more time on twitter. Twitter is great to follow the latest news and bits going on in various communities. It also makes it great to retweet some interesting items as well. Want to know what I am up to?

Follow me at: http://twitter.com/gena01

Gena01

Firebug 1.4.0 released

Thu, 16 Jul 2009 02:35:45 GMT

Firebug 1.4.0 was released today. I've been running alpha and beta versions for what seems like forever now. Things have been shaky, but they've gotten a lot better lately. There's a ton of changes. This release is recommended for all users and it looks like everybody will be upgraded soon to this new release. Great job dev team. I know 1.5a is going to be in full focus now with more great stuff. Firebug is also becoming a platform to build addons on. I keep seeing more and more addons built on top of firebug. I guess I should do a separate post with some great Firebug addons.

See the full announcement here: http://blog.getfirebug.com/?p=295

Gena01

Firefox running slow

Thu, 11 Jun 2009 15:42:00 GMT

I noticed recently that my Firefox 3.0 was running quite slow on my home PC. I know that there's a ton of news about browsers fighting to be the fastest but I felt that my home Firefox was running slower than my work copy. After doing a quick scan over addons that I have setup at home I noticed that my list is a bit longer compared to the setup I have at work. I went and disabled a number of addons that I am not using on a regular basis and restarted Firefox. Wow, what a difference. It seems like Firefox is like 10x snappier than it used to be. I don't know exactly which addon caused the slow down and don't want to create speculation. If your Firefox feels a bit sluggish then it's definitely worthwhile to go through your addons and disable or uninstall the ones you are not using.

Note: Firefox also has a Safe Mode option that can also help if your Firefox is misbehaving or freaking out. It might also be something to look into to see just how fast things load and run without any extensions running.

Gena01

Google releases Page Speed addon for Firebug

Fri, 05 Jun 2009 02:28:21 GMT

Today Google officially announced and released a new Firefox/Firebug addon called Page Speed. When I saw the announcement I was curious and yet perplexed. It definitely sounded like a YSlow like plugin. After installing and playing with it for a bit I do have to say that it's trying to do something similar with a different perspective. It has it's own set of rules and adds some unique features. I do recommend you install this one along with YSlow.

See the full announcement here: http://googlewebmastercentral.blogspot.com/2009/06/introducing-page-speed.html

P.S. One interesting point is that the above page gets quite a few "moderate" grade recommendations in order to improve performance.

Gena01

NYPHP: Taking the Zend Server Out For a Spin

Fri, 05 Jun 2009 02:08:46 GMT

Last tuesday NyPHP resumed monthly meetups. NyPHP doesn't disappoint with great presentations. This time they kicked things off the with a great presentation from Zend. Recently Zend released a new community product called Zend Server. Ed Kieltinski from Zend was here to give a tour of this new product as well as run some quick demos to show off some of the cool features. It seems like a great product.

Ed was showing off the commercial offering while explaining how much of the functionality is missing from the community version. You can find some of the demos in the posted videos on the zend site.

Find more information on Zend Server CE here: http://www.zend.com/community/zend-server-ce. There's a ton of resources including pdfs and videos.

Gena01

Oracle Instant Client 11g on Ubuntu

Mon, 04 May 2009 21:45:38 GMT

I know things have been quiet. I've been quite busy and I am also getting ready to share some things that I've been working on. One of the bigger things is looking at Ubuntu Server as a possible distro to use for our Linux servers. As you may already know that Oracle makes available Oracle Instant Client 11g as either RPM or zip package. Ubuntu being a debian based distro is out of luck. Also Oracle doesn't officially support debian or any debian based distro (which would explain the lack of binary packages). They do make 32-bit Oracle XE binaries available for Debian/Ubuntu ( http://www.oracle.com/technology/tech/linux/install/xe-on-kubuntu.html )?

So I tried to install things from .zip packages. The process is tedious and the files are all in one big directory which is totally different from RPM install/setup. Also shocking is the use of zip packages on Linux (and lack of symlink support in zip format). Another issue is that all those configure based scripts might not be able to handle the zip based installs. Also installing files from sources or zips kinda breaks the binary packagement rules. If you are still insistent on using ZIP packages here are updated notes from oracle: http://www.oracle.com/technology/pub/notes/technote_php_instant.html

So what do you do if Oracle won't release debian packages. The simple answer: build .deb(s) yourself. It's quite simple actually and works quite nicely. We will use a package called Alien. It's a great little tool that does binary package conversion.

Here's how:
1. Install libaio1 (this is now a requirement for Oracle 11g). you don't need it for Oracle 10g Instant Client:

Code:

apt-get install libaio1

2. Install alien:

Code:

apt-get install alien

3. Download the RPMs from here: http://www.oracle.com/technology/tech/oci/instantclient/index.html I usually grab grab the Basic, Devel and SqlPlus. (Basic Lite RPM doesn't work if you want to install Devel and/or SQLPlus).
4. Convert RPMs to .deb using alien:

Code:

alien oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
alien oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
alien oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm

5. Install the debian packages:

Code:

dpkg -i oracle-instantclient11.1-basic_11.1.0.7.0-2_i386.deb oracle-instantclient11.1-devel_11.1.0.7.0-2_i386.deb oracle-instantclient11.1-sqlplus_11.1.0.7.0-2_i386.deb

6. And you are pretty much done.

Code:

aptitude search oracle
p cl-sql-oracle - CLSQL database backend, Oracle
p libmono-oracle1.0-cil - Mono Oracle library
p libmono-oracle2.0-cil - Mono Oracle library
i oracle-instantclient11.1-basic - Instant Client for Oracle Database 11g
i oracle-instantclient11.1-devel - Development headers for Instant Client.
i oracle-instantclient11.1-sqlplus - SQL*Plus for Instant Client.
p spamoracle - A statistical analysis spam filter based on Bayes' formula
p spamoracle-byte - A statistical analysis spam filter based on Bayes' formula

P.S. The added benefit is that "pear install pecl/oci8" works out of the box. Just hit enter at the prompt and you got yourself a working php oci8 binary module.

Enjoy,

Gena01

Blog update

Thu, 09 Apr 2009 16:39:02 GMT

Yesterday night I reinstalled the SMF software and reinstalled some of the packages that I am using. I also replaced some of the packages with hopefully better alternatives. As a result the URLs that the forum was generating were changed. I noticed in Google Reader this morning 20 new entries for the Blog. The reason is that since the URLs are slightly different now RSS readers think that those are new items.

The old links should work as well and redirect you to the new location(s).

Thank you,

Gena01

Testers wanted for upcoming release of PHP Pecl 1.3.5

Mon, 16 Mar 2009 15:34:40 GMT

Last week Christopher Jones from Oracle pinged me about a patch I submitted a while back. The patch enables basic Oracle Instant Client detection during configure stage. The patch was meant to simplify install/upgrade of Pecl OCI8 module. After a number of revisions and improvements we have a working test version that will become OCI8 1.3.5.

Christopher also posted a number of blog entries on the changes:

This should also really simplify the installation/upgrade process if you are using "pear install pecl/oci8". It should basically install without requiring any user intervention if your Oracle environment is setup properly.

Update #1: Pecl OCI8 1.3.5 is released: http://pecl.php.net/package/oci8

Update #2: Christopher Jones posted more articles on the topic:

Gena01

Firebug Screencast

Wed, 04 Feb 2009 16:55:13 GMT

Vlad Didenko has posted a really nice Firebug Screencast. It's a ten minute quicktime movie that you need to download. The screencast covers a lot of great functionality of Firebug within a very short time.

He shows some really cool and time saving tricks like:
- Inspect Element.
- Going through the Firebug user interface and options.
- Doing JavaScript debugging and using conditional breakpoints.
- and many many more...

I highly recommend it to any web developer using Firebug.

P.S. Vlad also has other great content on his blog. Another article worth reading is: JavaScript meetup presentation. He is also an organizer of JavaScript User Group in Chicago.

Gena01

NYPHP: OWASP and Joomla

Sat, 31 Jan 2009 04:06:54 GMT

On Tuesday we had our first NYPHP user group meeting of 2009. We also had quite a turnout with many many fresh faces.

The user group meeting consisted of two presentations. The first one which ran for about 15 minutes was about OWASP. It was mostly general stuff about the organization and the projects that OWASP is involved in. I would have loved some more PHP specific material. For those who never heard of OWASP before it's a huge organization that covers various technologies. They also mentioned OWASP NYC AppSec 2008 Conference that ran in September of 2008. There is a ton of content posted from the conference including videos.

The second part of the user group meeting was the reason behind the meeting. The presenter is co-founder of Joomla. He started off with the "State of Joomla" and some history. After that he delved into how he got involved in Joomla and the project that he was involved in. He also mentioned the challenges that he and his team ran into and the strength of Joomla at solving some of these challenges. I was also impressed with the way the speaker covered the material with jokes, examples and stories. There was a lot of great content and questions.

I am waiting for the slides get posted so that those who missed the meeting will be able to see what they missed. Overall a great meeting that ran late.

P.S. I took a look and played a bit with Joomla. It seems like a great project to use and be involved in. I will definitely spend more time to see what it's all about and see what crazy things you can do with it. It also provides a rich and professional interface that's easy to customize with hundreds of extensions. Also looking through the documentation adding new views is a matter of writing a couple of files and is something that could be accomplished within an hour or so.

Gena01

RSS Part 2: do you check your own RSS feeds?

Wed, 14 Jan 2009 17:13:34 GMT

So i've been using Google Reader for some time now. It has been quite a learning experience. It also has been quite positive and a lot better than the days of having a ton of tabs open and trying to stay on top of all the great stuff happening in the blogosphere.

A couple of observations though:
1. Some sites do not provide RSS feeds at all (which is a pity).
2. Feedburner is great for many many reasons. Some of these include:
a. It takes over delivering most of the traffic (offloading your server) and caching the RSS feed.
b. It's able to convert and make sure that the delivered content is valid for many various RSS readers.
c. It provides a great way to analyze your RSS feed subscriptions and track articles.
d. It allows you to add links to each RSS post to make it easier to spread the word about the great stuff you are sharing through RSS feed.
e. You can combine FeedBurner with Adsense to get the benefits of both products.
3. Some sites that do provide RSS feeds do not provide full content to the article right in the feed and expect others to click on the link to read the full article. However it's not always obvious from the RSS item that the article is not complete and that there's more to it than what I am seeing in Google Reader. This one is quite annoying. I would love to see full content in the RSS feed. I would love to know that the article is a summary or a partial content that will continue once I follow the link. This is not obvious and I tend to play a guessing game of whether this is all the author wanted to say or there's actually a more detailed article.

If you run a site or a blog that provides RSS feeds do "eat your own dogfood" (check your own feeds) and see if there's anything that could be confusing or broken. Also don't forget to validate your RSS feeds. There's also a number of RSS validators. I would recommend to use more than one just to make sure that you have your feed covered and didn't miss anything.

P.S. I also wanted to mention BetaNews/Fileforum RSS feeds. BetaNews and Fileforum just went through a full redesign. They also provide RSS feeds. However the content in the actual feed is useless. There's a ton of stuff posted and the requirement to click and open each one defeats the purpose of using RSS altogether since I still need to open up each item in a separate tab/window just to see more information about the new item or program release. On a separate note it looks like the "Changelog" that was so useful when trying to stay on top of software releases is totally missing in the new design of the website. So if you are curious to see what changed you need to research that somewhere else.

See Also: RSS feeds and what you should know about them.

Gena01

Firebug 1.3.0 is out

Thu, 08 Jan 2009 16:58:50 GMT

Firebug 1.3.0 was released recently and it's highly recommended for anybody running Firefox 3.0.x. This is also quite a big release with a lot of changes and other effort that went into it. It also introduces some nice stuff and has some components redone completely. One of the most notable is the Net panel rewrite.

Read more about the changes here: Firebug 1.3.0 Final Released.

Gena01

My Pear DB fork.

Wed, 07 Jan 2009 17:09:56 GMT

We use PEAR DB extensively in our code (for my day job) and switching over to another abstraction library is too much hassle at the moment. We are also still in the migration phase from PHP4 to PHP5. (Yes I know PHP4 is dead and PHP5 rocks) One of the challenges that we faced was the fact that Pear DB generates a ton of warnings when running in PHP5 and it's also pretty much obsolete/abandoned. Which is a good thing for me, since I don't need to worry about having to merge different trees. I am currently focusing my efforts on db.php and the pear db oci8 driver implementation. I also added some new code to speed up Pear DB a little.

A couple of items that were fixed already:
+ Added a PHP version check to reduce the warnings and call the right function for the right version of PHP (4 vs 5).
+ Added oci8 implementation functions for GetCol(), GetAll() and GetAssoc() which use the oci8 ocifetchstatement() function to fetch rows in batches. 1 function call vs 2* N rows. The difference is huge if you are fetching 200-300 rows.

Things still looking at:
- There's a default pre-fetch that was introduced and somehow it's set at 400? That seems quite large. PHP oci8 module actually sets it to 100 by default. It's also set every time before fetching the rows. I am still pondering if we need another default in Pear DB driver.

Gena01