Blog Archives
How to compile cx_Oracle (python 3.2 for Windows x64)
I needed cx_Oracle 5.0.4 binaries for recently released python 3.2 for Windows x64. Since there were no binaries released for python 3.2, I built binaries myself.
Ingredients that you’ll need for cooking:
- 64-bit Windows (I used Windows 2003 x64)
- 64-bit python 3.2
- 64-bit Oracle client of your choice (I build with both, 10g R2 and 11g R2)
- Visual Studio 2008 Express (don’t be tempted to install VS 2010!!)
- Windows SDK for Windows 2008 and .NET 3.5
- Optionally: I used Virtual Clone Drive for conveniently mounting ISO images.
Note: Similarly, you can build 32-bit cx_Oracle, in that case you’ll use 32-bit Windows, 32-bit python 3.2 and 32-bit Oracle client. I’ll point to the difference when necessary.
0) Prepare Windows x64 (I used Windows 2003 x64 test machine)
1) Download Visual Studio Express 2008 with SP1 from Microsoft site
I downloaded VS2008ExpressWithSP1ENUX1504728.iso. I’ll let you to find the iso on Microsoft site for yourself!
Because Python 3.2 was built with VC 2008, you must use VS 2008 and not for example more recent version, VS 2010.
2) Install Visual C++ with default options
Note: When installing on x64, some components are installed in “Program Files (x86)” and some in “Program Files”.
3) Download Windows SDK for Windows 2008 and NET 3.5
I downloaded 6.0.6001.18000.367-KRMSDK_EN.iso.
4) Install SDK for Windows 2008 and NET 3.5 in default location
Note: I deselected Documentation and Samples to speedup the installation process.
5) Install 64-bit python 3.2 for Windows x64
Note: Alternatively, if you want to build 32-bit cx_Oracle binary, you must install 32-bit python 3.2.
6) Download cx_Oracle 5.0.4 tar file with the source
I downloaded cx_Oracle-5.0.4.tar.gz. Unzip in some temporary directory (D:\cx_Oracle)
7) Prepare command prompt for build
Start -> Run ->
If you're building on Windows x64:
%comspec% /k ""C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\bin\vcvars64.bat""
If you're building on x86:
%comspec% /k ""C:\Program Files\Microsoft Visual Studio 9.0\VC\bin\vcvars32.bat""
set ORACLE_HOME=D:\ORACLE\ORA11R2\NETWORK\ADMIN
set PATH=D:\ORACLE\ORA11R2\BIN;C:\python32;%PATH%
set PYTHONHOME=D:\Python32
8) cd to temporary cx_Oracle directory
cd D:\cx_Oracle\cx_Oracle
cmd> python setup.py build
..this step wil create build directory with the build, you'll see something like this:
D:\cx_Oracle\cx_Oracle-5.0.4>dir build
Volume in drive D is SW
Volume Serial Number is B83A-326E
Directory of D:\cx_Oracle\cx_Oracle-5.0.4\build
04.03.2011 15:06 .
04.03.2011 15:06 ..
04.03.2011 15:06 lib.win-amd64-3.2-11g
04.03.2011 15:06 temp.win-amd64-3.2-11g
0 File(s) 0 bytes
4 Dir(s) 10.261.749.760 bytes free
Now you can install library with:
cmd> python setup.py install
You will see output simiral to this one:
D:\cx_Oracle\cx_Oracle-5.0.4>python setup.py install
running install
running build
running build_ext
running install_lib
copying build\lib.win-amd64-3.2-11g\cx_Oracle.pyd -> C:\python32\Lib\site-packages
running install_data
creating C:\python32\cx_Oracle-doc
copying BUILD.txt -> C:\python32\cx_Oracle-doc
copying LICENSE.TXT -> C:\python32\cx_Oracle-doc
copying README.TXT -> C:\python32\cx_Oracle-doc
copying HISTORY.txt -> C:\python32\cx_Oracle-doc
creating C:\python32\cx_Oracle-doc\html
copying html\connection.html -> C:\python32\cx_Oracle-doc\html
copying html\cursor.html -> C:\python32\cx_Oracle-doc\html
copying html\genindex.html -> C:\python32\cx_Oracle-doc\html
copying html\index.html -> C:\python32\cx_Oracle-doc\html
copying html\license.html -> C:\python32\cx_Oracle-doc\html
copying html\lob.html -> C:\python32\cx_Oracle-doc\html
copying html\modindex.html -> C:\python32\cx_Oracle-doc\html
copying html\module.html -> C:\python32\cx_Oracle-doc\html
copying html\objects.inv -> C:\python32\cx_Oracle-doc\html
copying html\search.html -> C:\python32\cx_Oracle-doc\html
copying html\searchindex.js -> C:\python32\cx_Oracle-doc\html
copying html\session_pool.html -> C:\python32\cx_Oracle-doc\html
copying html\subscription.html -> C:\python32\cx_Oracle-doc\html
copying html\variable.html -> C:\python32\cx_Oracle-doc\html
creating C:\python32\cx_Oracle-doc\html\_static
copying html\_static\basic.css -> C:\python32\cx_Oracle-doc\html\_static
copying html\_static\default.css -> C:\python32\cx_Oracle-doc\html\_static
copying html\_static\doctools.js -> C:\python32\cx_Oracle-doc\html\_static
copying html\_static\file.png -> C:\python32\cx_Oracle-doc\html\_static
copying html\_static\jquery.js -> C:\python32\cx_Oracle-doc\html\_static
copying html\_static\minus.png -> C:\python32\cx_Oracle-doc\html\_static
copying html\_static\plus.png -> C:\python32\cx_Oracle-doc\html\_static
copying html\_static\pygments.css -> C:\python32\cx_Oracle-doc\html\_static
copying html\_static\searchtools.js -> C:\python32\cx_Oracle-doc\html\_static
creating C:\python32\cx_Oracle-doc\samples
copying samples\DatabaseChangeNotification.py -> C:\python32\cx_Oracle-doc\samples
copying samples\DatabaseShutdown.py -> C:\python32\cx_Oracle-doc\samples
copying samples\DatabaseStartup.py -> C:\python32\cx_Oracle-doc\samples
copying samples\ReturnLongs.py -> C:\python32\cx_Oracle-doc\samples
copying samples\ReturnUnicode.py -> C:\python32\cx_Oracle-doc\samples
copying samples\RowsAsInstance.py -> C:\python32\cx_Oracle-doc\samples
creating C:\python32\cx_Oracle-doc\test
copying test\3kNumberVar.py -> C:\python32\cx_Oracle-doc\test
copying test\3kStringVar.py -> C:\python32\cx_Oracle-doc\test
copying test\Connection.py -> C:\python32\cx_Oracle-doc\test
copying test\Cursor.py -> C:\python32\cx_Oracle-doc\test
copying test\CursorVar.py -> C:\python32\cx_Oracle-doc\test
copying test\DateTimeVar.py -> C:\python32\cx_Oracle-doc\test
copying test\IntervalVar.py -> C:\python32\cx_Oracle-doc\test
copying test\LobVar.py -> C:\python32\cx_Oracle-doc\test
copying test\LongVar.py -> C:\python32\cx_Oracle-doc\test
copying test\NumberVar.py -> C:\python32\cx_Oracle-doc\test
copying test\ObjectVar.py -> C:\python32\cx_Oracle-doc\test
copying test\SessionPool.py -> C:\python32\cx_Oracle-doc\test
copying test\SetupTest.sql -> C:\python32\cx_Oracle-doc\test
copying test\StringVar.py -> C:\python32\cx_Oracle-doc\test
copying test\test.py -> C:\python32\cx_Oracle-doc\test
copying test\test3k.py -> C:\python32\cx_Oracle-doc\test
copying test\TestEnv.py -> C:\python32\cx_Oracle-doc\test
copying test\test_dbapi20.py -> C:\python32\cx_Oracle-doc\test
copying test\TimestampVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uConnection.py -> C:\python32\cx_Oracle-doc\test
copying test\uCursor.py -> C:\python32\cx_Oracle-doc\test
copying test\uCursorVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uDateTimeVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uIntervalVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uLobVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uLongVar.py -> C:\python32\cx_Oracle-doc\test
copying test\UnicodeVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uNumberVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uObjectVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uSessionPool.py -> C:\python32\cx_Oracle-doc\test
copying test\uStringVar.py -> C:\python32\cx_Oracle-doc\test
copying test\uTimestampVar.py -> C:\python32\cx_Oracle-doc\test
running install_egg_info
Writing C:\python32\Lib\site-packages\cx_Oracle-5.0.4-py3.2.egg-info
9) Test the build by importing cx_Oracle library
cmd> python
D:\cx_Oracle\cx_Oracle-5.0.4>python
Python 3.2 (r32:88445, Feb 20 2011, 21:30:00) [MSC v.1500 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>>
Optionally, if you want to build cx_Oracle for 10g client (assuming you have
10g client installed on the same machine, simply change the home and run the build:
set ORACLE_HOME=D:\ORACLE\ORA10
set PATH=D:\ORACLE\ORA10\BIN;%PATH%
python setup.py build
and you’ll find two additional directories under build:
D:\cx_Oracle\cx_Oracle-5.0.4>dir build
Volume in drive D is SW
Volume Serial Number is B83A-326E
Directory of D:\cx_Oracle\cx_Oracle-5.0.4\build
04.03.2011 15:16 .
04.03.2011 15:16 ..
04.03.2011 15:13 bdist.win-amd64
04.03.2011 15:16 lib.win-amd64-3.2-10g
04.03.2011 15:06 lib.win-amd64-3.2-11g
04.03.2011 15:15 temp.win-amd64-3.2-10g
04.03.2011 15:06 temp.win-amd64-3.2-11g
0 File(s) 0 bytes
7 Dir(s) 10.260.930.560 bytes free
Happy cx_Oracle coding!
ORA-600 [KCBLASM_1], [103] on Patchset 10.2.0.5
During routine alert.log check control I spotted several ORA-600 errors:
ORA-00600: internal error code, arguments: [kcblasm_1], [103]
It is a known issue introduced with 10.2.0.5. This platform generic bug (7612454) is a regression bug introduced in Patchset 10.2.0.5 according to MOS note:
Bug 7612454 – More “direct path read” operations / OERI:kcblasm_1 [ID 7612454.8]
Obviously, bug 7612454 causes performance problem with direct path reads, compared to performance from before (10.2.0.5). [Right now, I can’t confirm how severe performance impact is, because no one so far reported a performance problem or any crashes.]
Since no workaround was mentioned in above MOS note I searched further and found related note that highlights the problem a bit more:
ORA-600 [KCBLASM_1] RUNNING A QUERY WITH HASH GROUP BY [ID 848094.1]
At the time of this writing I’ll probably disable group by hash aggregation at instance level (_gby_hash_aggregation_enabled = false), if the problem becomes severe. So far, this bug is more of an annoyance than a show stopper – but I feel better being prepared with a workaround. I hope we’ll not hit the case when neither setting _gby_hash_aggregation_enabled = false nor _hash_join_enabled=false helped as a workaround, as described in MOS note Bug 9918715: ORA-00600 [KCBLASM_1] ERROR.
The second suggested workaround, upgrade to 11.2, is (of course) out of the question at this time. Besides, who can guarantee us that we will not hit some XY regression bug in 11.2 the day after we go in production with 11.2! ;-)
What to say for the end, 10gR2 deserves to stay written in my black book with golden letters as being the buggiest version ever released by Oracle. Someone can argue that bugs goes hand in hand with features, software business as usual; I had to say, with every new grey hair I have less and less tolerance to excuses like this.
OracleXE 11g will raise DB limit to 10GB
I expected OracleXE 11g announcement at OOW 2010. It didn’t happen. Naturally, I start questioning if OracleXE is perhaps a dead end. Hopefully not, according to this good news. I think XE 11g will be released in the first half of 2011. Another good news, according to the same source, is that Oracle will raise DB size limit from 4GB to 10GB. Perhaps 10GB is not much in your part of the World, but I live and work in a small country where almost every official register (Business Register, Register of Territorial Units, Tax register, Population Census etc.) can individually be stored in database with 10GB limit. That doesn’t mean XE should be used casually as a production database for such tiny databases (registers). On the other hand, public sector should strive to save the money when it can — in most cases XE is perfectly suited to support dissemination of such data to public and/or for exchange in data hubs between register owners.
Oracle ANSI joins poor performance after patchset 10.2.0.5 installation
A week passed by since I installed 10.2.0.5 + Patch 2 on a bunch of production servers. Time for a short confession.
Was it trouble free? Of course not. :)
One particular problem hit us pretty hard. At the time of this writing the case is not yet fully resolved nor documented with a repeatable test cases so I can’t give you any proof in the shape of code, I’m afraid you’ll have to believe on my word. :-)
While troubleshooting performance problem of some reports that run well on 10.2.0.3 (elapsed time measured in tens of seconds), but get crazy execution plans after patchset installation (elapsed time measured in hours, if not days!) .
The pattern that I spotted was:
- in all cases complex views are involved in the queries (outer joins, nested selects, unions…you named it),
- roughly half of the views were written using ANSI join syntax, the other half used old-style join syntax,
- performance problem was reported only on queries that selected data from the views having ANSI join syntax, old-style views performed well
- if the problematic view with ANSI join was rewritten to old-style, it performed well
Workarounds that we found:
- rewrite views containing ANSI joins to use old style join syntax,
- hint the views with /*+ NO_QUERY_TRANSFORMATION */ hint,
- disable CBO query transformations at the session level with logon trigger such as:
CREATE OR REPLACE TRIGGER disable_cbo_qt
AFTER LOGON ON DATABASE
WHEN (USER IN('APP1','APP2'))
BEGIN
-- turn CBO query transformation from LINEAR to OFF
execute immediate 'alter session set "_optimizer_cost_based_transformation"=off';
END;
/
We’ll very likely replace ANSI joins in the views with old-style joins on the long run, at the moment hint was placed in the views. I don’t like the idea to completely turn off query transformation at the session level.
In theory there should be no difference from the performance point of the view between old-style joins vs. ANSI joins. In theory. If you’re using ANSI joins (and there is no reason to be discouraged of using them after reading this post, really!), then at least remember that sometimes is worthwhile trying to refactor poorly performing query having ANSI join with equivalent one written in old-style syntax. I hope Oracle will eliminate such “surprises” in the future.
How to unwrap PL/SQL code
Today, I got link to the site that allow us to unwrap PL/SQL code with online service (thanks Jože!). If you’re not familiar with PL/SQL unwrapping I would recommend Pete Finnigan famous BlackHat convention 2006 presentation, where he showed the principles to unwrap PL/SQL code. At first, I hesitated to publish link to the site, but when I saw that Google is returning link as a second hit I changed my mind:).
dbaportal.eu 