Oracle XE on Ubuntu

Finally I had a chance to fix up my install of Oracle XE (Oracle Express Edition) on my ubuntu linux box. This humble machine Pentium 3 660, 512M RAM is my play area for unix.
At some point last year I was completely over the stuffing around with Redhat (or Fedora) and tried Debian. That was a nightmare as well, the install hanging with no idea as to why.
So I asked my friendly unix team what they would recommend and use, and they mentioned Ubuntu which is debian distribution but nicer for dumb installers like me (yeh like I have never installed unix before)
So I grabbed a copy of the CD and installed no problems. Devices all found and very clean feel.
Rant: I am over any OS (including Windows XP) showing too much of the complexity to the user. Whilst I can jump thru the hoops, after a couple of rounds I am over the whole process and want something which just works properly. Ubuntu does just that.

Ok now to install Oracle XE. After playing around with MySQL, I decided to grab Oracle XE and install. This was NOT straight forward.
I found two good blog articles about installing Oracle XE on Ubuntu. However I had to combine both.

1. Check how much space is on / using df -k. I didn't have enough space so I needed to make a symbolic link to somewhere else. This actually makes sense anyway as installing Oracle software in /usr/lib/oracle is not something you want to do with oracle anyway. On our enterprise boxes the software is on a separate partition/filesystem, either in /home/oracle/product/... or /oracle/product/... So the command is: sudo ln -s /home/oracle /usr/lib/oracle
   
2. This article covers the install mostly.
3. Whilst apt-get might work I had to go and download and install libaio1 and libc6. I got them from Ubuntu Packages. Use dpkg -i to install both packages.
4. This article also covers how to add extra swap if the installer complains. I had to add additional swap space.
5. Run the converted rpm file as per article in step 2.

So now I have a version of Oracle XE running. I can now see how easy compared to MySQL LOAD INFILE it is to load my stock files into Oracle. Then I can start using a database will a more complete set of statistics functions :)

Have Fun

Paul

SQL Server procedure cache weirdness

We have had a ongoing problem where a sqlserver instance logging web activity and running reports takes 100% of CPU. This is a dual xeon CPU box, with each xeon hyperthreaded which is almost equivalent to 4 CPUs.

Yesterday I had the luxury of looking at everything about this problem as it was occurring without the need to return the system to a more stable state rapidly.
I looked at everything, and there was no perfmon counter and no bad waits/blockers in the instance. Eventually I removed all the sessions which were consuming CPU without any response and tried to rerun the stored procedure they were calling.
That proceeded to hang as well. The normal duration of this call is 4-6 secs. For a report that is not too bad given the size of the tables.
Given that the procedure should have worked, that suggested there was a some problem with the stored procedure. I extracted the SQL which is run and executed it. The SQL worked and proceeded to return data (without hanging).
This suggested that there was something wrong with the actual calling or executing of the stored procedure.
Background, SQLserver parses and stores the stored procedure and the determined execution plan in memory in an area called the procedure cache. Given this stored procedure had been parsed in the past, this potentially meant there was a problem with the cached stored procedure and most likely the execution plan.

So I executed the stored procedure with the recompile option.
eg. EXEC usp_report_this WITH RECOMPILE

The result was the stored procedure now worked and return a result within the expected duration. When I removed the recompile option, the call proceeded to hang again. This confirmed that the stored procedure in the procedure cache was corrupt in some form.
So to get rid of the problem, I used DBCC FREEPROCCACHE to flush the procedure cache of all cached stored procedures and execution plans.
The retest of the call with the recompile option now worked as well.

Given I am still unable to determine why the procedure cache was corrupted or how that occurs we recreated the stored procedure and added the WITH RECOMPILE option, so that all future calls of that stored procedure will be recompiled.
eg.
create procedure usp_fred
declare ...
WITH RECOMPILE
as

Given the reason for the corrupt stored procedure is unknown at this point and searches on Microsoft and google didn't reveal anything meaningful. If I find out the reason I will update this blog entry or produce a new one with a more thorough explanation.

Adding the WITH RECOMPILE option is something which should only be used as a last resort. As the parsing and determining execution plan part of the execution of SQL or stored procedures can add significant CPU overhead. We are lucky the frequency of these reports is low enough not to be a significant problem.

Have Fun

US real interest rate and emerging market financial crisis

This article talks about the correlation between US real rates and emerging market financial crisis.

Essentially a carry trade of borrowing in a low rate country and buying/investing/speculating in a high rate country is effected by rapid increases in the real rate in the low rate country.

There are plenty of investments which rely on and extrapolate current growth rates to justify higher Net Present Value (NPV), via higher future cash flows, without taking into consideration the effect of foreign source investment dollars having the ability to bid up the prices of services, goods and investments within the high rate country.

Have a play with the NPV calculator in Excel or whatever spreadsheet you use to see what the effect of increasing the growth rate of cash flows does the NPV.

Have Fun

Paul

Modeling connection pooling

There is a good direction in database land away from the idea that tuning is all seat-of-the pants, smoke-n-mirrors stuff. In Oracle land it was the recognition of Oracle's wait events and the ability to see what Oracle was waiting on and hence discover a probable bottleneck. This replaced the hit ratio and other ratio crowd as the logical and objective nature of the using Oracle waits proved more benefical.
There is still a way to go, as there is plenty of good work done on areas which directly relate to database stuff. Unfortunately for lots of people, math causes some serious FUD (Fear, Uncertainty and Doubt).
I use sage as my RSS feed controller in firefox, whilst the list of new blogs grows, the ability to read content in a single sitting doesn't diminish. I came across an article from one of the couple of math related blogs. This blog talks about using Markov Chains to model resource pools.

I will be having fun tomorrow gathering the necessary information to check the database connection pools which have databases I support. A couple have excess numbers of pools (which waste memory resources) and with better modeling we should be able to recommend some lower numbers.

Have Fun

Replacing Perl with MySQL and maybe Oracle XE

I have a series of Perl scripts which use Perl modules to perform some statistical calculations of stock market End-Of-Day (EOD) data. I had the prospect of writing a Perl script to return the values of statistics calculation.
So I decided it was time to convert from flat files to storing the data in a database. So I downloaded MySQL 5.1 and installed on a box running Ubuntu. It was a reasonably straight forward process, I had some fun and games with having the default data directory in /var/lib.
This was solved by shutting down MySQL (mysqladmin shutdown -u root -p), moving the files to a more appropriate location and creating a symbolic link (directory) pointing at the new location.
The next problem was the lack of some important statistical functions such as correlation. I had a look around on the net and found that Oracle has the ANSI SQL standard CORR(x,y) function.
So off the www.oracle.com to download Oracle XE.
Given the box in question has 512M of RAM, the 1Gig limit isn't a problem. Plus I will get the CORR function and plenty of other analytical functions to boot.

The next step is get the grips with the loading of data, most likely with external tables. The MySQL function LOAD INFILE 'filename' INTO table is very easy to understand and use.

Have Fun

Paul

Interesting articles and Dynamic SQL

I was websurfing and came across Steve Yegge website/blog from reddit.com.
I spent time last night and time day digesting the articles on the site. I would recommend you go and have a read of some of his articles. He has also put his top 7 most popular by traffic at the top.

I have played around a bit with various languages since starting with computers. Given my area is more technical and support than development or software engineering, I haven't had the need to learn any language more than being able to understand what the gist of the code and the overall logic.

The thing which makes me want to spend more time with Lisp and its macros as they remind me of dynamic SQL. Lisp macros are more powerful and expressive but the ability to generate code from code is a similar concept.

Good security suggests that any object permissions should be granted to roles and those roles granted to individual users/logins. Here is a good example of dynamic SQL in action.

-- Create the role read_only
-- Prompts for a schema name (owner)
-- Generates GRANT SELECT ON owner.table_name TO read_only

create role read_only;

set echo off
set pagesize 0
set verify off
spool /tmp/read_only_grants.sql
select 'GRANT SELECT ON '||owner||'.'||object_name||' TO read_only;'
from dba_objects
where owner = '&owner'
and object_name not like '%$%'
and object_type in ('TABLE','VIEW')
order by object_name;
spool off
@/tmp/read_only_grants.sql

You can extend this even further using DECODE or CASE to handle granting execute permissions on packages,procedures and functions.

select 'GRANT '||
CASE
when object_type in ('TABLE','VIEW') then 'SELECT'
when object_type in ('PACKAGE','PROCEDURE','FUNCTION') then 'EXECUTE'
else 'SELECT' end
||' ON '||owner||'.'||object_name||' TO read_only;'
from dba_objects
where owner = '&owner'
and object_name not like '%$%'
and object_type in ('TABLE','VIEW','PACKAGE','PROCEDURE','FUNCTION')
order by object_name;

I use dynamic SQL all the time, it saves time and allows the quick generation of repeating SQL.
It is like mail merge in Word or like Lisp macros.

Have Fun

Paul

Battlefield 2 fun #2

I have been playing Battlefield 2 (BF2) a bit over the last month or so, and it has been fun. There are some annoyances and suspect deaths (people shooting the other direction and getting a head shot) but the online game play is good, most players who have managed those JFK ballistic miracles don't last long as the admins are quick to ban suspect behaviour.

There are weapon upgrades for most kits as your player progresses up the ranks, from private to corporal all the way to a four star general. If you play on a ranked server you can get your scores updated to the main EA server.

The ranked servers which are most popular in Aus. are located at Telstra's GameArena. There are lots of stats available to see how well you are playing online, you can also compare yourself to other players included the highest ranking, rising stars (most change in points) etc.
At the moment I am averaging under 1 point per minute of game play, which is pretty ordinary. I blame the tools I use of course, a older P4, 1.5G RAM but only nVidia 5200 card means that sometimes my framerate and lag within the game are bad. I had one situation where I saw an opponent jump from another building, turn and fire in 3 lagged frames, naturally my ability to evade death is limited in that situation.
With all these handicaps :) I still have made Sergeant (2500 points) and now face the next rank of Staff Sergeant (5000 points).
At 1 per minute of game play I am looking at about 2200 minutes = 36 hours of game play to reach the next level, if I manage to double the rate, it is still 18 hours of free time to make the next rank. The highest rank is a whopping 100000 points, which is 97600 minutes of game play, If I was to play 8 hours every day it would take 203 days to reach that rank!!
I am not sure what the lifespan of the game is and whether the best players would have moved on by that stage, along with the majority of hosted servers to the next best thing by the end of 2006.

Naturally given these calculations gives pause to thoughts extended solo games and the social aspect of playing with mates in a squad kicks in, as does the weird and amazing kills and deaths which occur in the game.

Have Fun

Oracle: Performance feature 1

A lot of people have known that since Oracle 8 you have had the ability to create functional indexes. Basically functional indexes allow you to create an index of calculated values. The most common example provided is the infamous to_char (date-to-char) conversion function.

Consider this SQL, which returns the product and amount for today from the sales table.

select product,amount
from sales
where to_char(purchase_date,'YYYYMMDD') = '20060308'

If there is an normal index on purchase_date, it can't be used in this case as the explicit conversion of purchase_date to a character string.

Here Oracle's functional index can come to the rescue. Creating an index of to_char(...) calculated values:

Create index ix_func_sales_pdate
on sales(to_char(purchase_date,'YYYYMMDD'))

Henceforth the above SQL can use the functional index to retrieve calculated values of to_char, and sped access to rows which have those calculated values.

However, hat-tip to Tom Kyte of asktom.oracle.com fame, you can use the ability to create an index of calculated values to speed access for any user-defined function!
Oracle normal indexes (B-Tree) don't index NULL values. So you create a function which will return NULL for rows you don't want (which won't be indexed) and non-NULL values for rows which you require.
The best use of this ability is for columns of low cardinality, which are highly skewed to one value. For example, a table contains a column opt_out which tracks permission to see marketing information. The opt_out column has two values 0 or 1, where 0 = No and 1 = Yes. Naturally it is skewed, which most values in the column being 0.

You have a nightly process called send_newsletter which requires a list of people who have opted in, your choice of index for this column should be a functional index not a BITMAP index. BITMAP indexes work better when there are multiple low cardinality columns.
You need to create a function which turns opt_out = 0 into NULL. A simple solution is to use CASE.
eg.
CASE
when opt_out = 0 then NULL -- the most common first
when opt_out = 1 then 1
else NULL end
Creating a functional index on that function will mean Oracle will only index rows which have opted IN to receive the newsletter. Retrieving rows using SQL like below will sped access via the functional index.

select email_addr
from customers
where opt_in(opt_out) = 1

This is a powerful feature of Oracle which can be used in much more complex functional examples. The main caveat is that inserts,updates will be slower as the row values have to be calculated on insert or update. This penalty is less than the penalty of BITMAP indexes.

Have Fun

Oracle SQL equivalent to SQLserver TOP

In SQLserver, you can issue the following code to only return the TOP n rows.

Eg. To return the first 300 rows from a search table (to return to a webpage) order by price (lowest first) where suburb is rich and transport_rating is less than 3

select TOP 300 * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price

In Oracle, there is no TOP function to perform this, the simplest method is wrap the SQL returning the rowset as an inline view and use ROWNUM to limit, the same requirement as above:

select * from
(select * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price)
where ROWNUM <=300

Both SQLserver and Oracle have HINTs which you can hint to the database which type of plan to execute the SQL. In most cases, you want the rows back as fast as possible (for websites).
So for the SQLserver code add the following OPTION(FAST n) HINT. This hint will tell SQLserver you want the first n rows back as fast as possible. This normally means that SQLserver will choose to do NESTED LOOP joins over MERGE or HASH JOINs.

select TOP 300 * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price
OPTION(FAST 300)

In Oracle there is something similar called FIRST_ROWS, you can either set it at the database level or for individual sessions or in individual SQL. The Oracle SQL code is rewritten as follows,

select * from
(select /*+ FIRST_ROWS */ * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price)
where ROWNUM <=300

You can also pass the number of rows to Oracle as well as follows:

select * from
(select /*+ FIRST_ROWS(300) */ * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price)
where ROWNUM <=300

I find the SQLserver method cleaner, however getting used to Oracle INLINE views opens up a world of powerful options.

Have Fun

Paul

p.s. I thought I would start to post more often. I am not sure I can do daily however that is plan for the next 30 days.

Oracle 10G SQL Tuning Advisor

Over the 9 months or so I have been supporting a datawarehouse which has been running on 10G R1. The experience has been fun, given the new features and strange in other respects (see my post about PARALLEL_AUTOMATIC_TUNING behaviour.

Oracle 10G comes with a new PL/SQL package called DBMS_SQLTUNE, which is the API or command line way of running the SQL Tuning advisor, instead of through Oracle EM GUI.

Sometimes SQL or PL/SQL is complex, running to many lines and in some cases involving several levels of nested inline views. It can take time just to figure out what the SQL is trying achieve, before you can actually start to review if there are any potential tuning opportunities.

Enter SQL tuning advisor, in a nutshell it takes sql, either in text, referenced by SQL_ID or from a snapshot or captured source and does a more indepth search for a better execution plan.

If you want a quick analysis to kick off the tuning exercise this tool is probably to best spot to start.
I have added some simple scripts to run SQL Tuning advisor from the sqlplus command line at the end of this post. I used similar scripts to produce some analysis in which the advisor found a better execution plan (which I verified). It reduced the running time substantially.
DBMS_SQLTUNE has had extra work in Oracle 10G R2 and has lots of additional features.

Have Fun

Paul

### Cut here ###
-- Script to create tuning task and automatically run tuning task using default settings
-- Input: sql_id captured from v$sql or history tables
-- PM 2006/03/04

set serveroutput on size 80000

declare v_taskname varchar2(50);
begin
v_taskname := dbms_sqltune.create_tuning_task(sql_id=>'&sql_id');
dbms_output.put_line('TaskName: '||v_taskname);
dbms_sqltune.execute_tuning_task(v_taskname);
end;
/

### End of script ###

### Cut Here ###

-- Script to report results from tuning task
-- Input: TaskName
-- Modify linesize or chunksize to sort required output width.
-- PM 2006/03/04

set long 8000
set long_chunksize 200
set linesize 200
set pagesize 3000

spool sqltune_report;
select dbms_sqltune.report_tuning_task('&task_name') from dual;
spool off;
### End of script ###