Oracle vs SQLserver: Consistent Reads

I am in a good position given I work extensively with both Oracle and SQLserver databases to see the subtle but major differences in how certain parts of the database are implemented.

What is a consistent read?
It is the C in the ACID (Atomic,Consistent,Isolated and Durable) transaction model. It means when you read the data, your query will see a version of the data which has not changed from when you started running your query.

To provide consistent reads (or READ_COMMITTED transaction level) SQLserver uses shared locks on the data to stop any changes occurring to the data whilst a query is run. This effectively blocks any writes to the data.

***IMPORTANT*** In SQLserver 2000 and earlier, this is the main reason for explosive lock contention and table lock escalations, especially on tables having many inserts, deletes or updates happening concurrently. This type of lock and table escalation will blow away your CPU and any scalability you were hoping for.

Note: In SQLserver 2005, Microsoft have introduced READ_COMMITTED_SNAPSHOT as a database option, which will allow the database to show a version of the data (at the time of execution) without requiring shared locks, similar to the read consistent view Oracle provides with rollback/undo segments. See this part of the MSDN documentation for more information.

Oracle provide consistent reads (or read consistent view) of the data via the use of rollback or undo segments, these segments store the prior versions of the blocks (similar to pages in SQLserver) which contain the rows which are being written to. When you query the data and hit a block which has changed since you query started, Oracle will rebuild the data from the rollback or undo segment to keep the data consistent. See the Oracle Concepts manual for a detailed description of this method.

The dirty little secret (of SQLserver) is that you can do dirty or read uncommitted reads which will return data, even if it has changed since the query started. In some cases this is the only way to enable SQLserver to scale without falling over with lock contention. You use the TABLE hint (NOLOCK) or SET TRANSACTION LEVEL READ UNCOMMITTED at the beginning of your SQL code. This should be implemented only after understanding the nature of the work your application is performing against the database.

Finally SQLserver 2005 is getting closer in functionality to Oracle. It will be interesting to see the snapshots that SQLserver use to provide the versions of data are prone to problems which Oracle has had in the past i.e. the infamous "snapshot too old" error.

Have Fun

Paul

SQL Server: Remote queries on views

I spent most of Friday testing out different methods for retrieving data using remote queries with SQLserver.

Remote queries are of the form

select ... from databaseServer2.databaseName.TableOwner.TableName JOIN ...

One of the limitations of remote queries is you can't use TABLE HINTS like (NOLOCK) on remote queries. Given that this query call was for a report which doesn't require uncommitted data that is a bit of a bummer. The report aggregates some log data on a table which is getting constant inserts, so the inability to read the data whilst not effecting the constant writes was causing LOCK contention.

The first thought was the wrap the table in a view on the remote server something like this

create view uv_logtable as select from logtable (NOLOCK)

Unfortunately it would seem that remote queries and views are not a good option if you want the SQLserver execution plan to be any good. The execution plan when there is a view involved was very ordinary. Queries which had a 4 sec response time blew out to more than 4 mins, in some causes much longer. I am going to test some more and determine what the rules are.

The final thought was to take the stored procedure which runs the report and port to the remote server i.e. adding remote queries for the other data. This worked well, especially considering that most of the work was being down on the remote server in that report.
The added benefit was that I could add NOLOCK hints to the now local tables and amount of lock requests (tracked from perfmon) was stable compared to the current setup.

I will update after we go through our Monday peak period as to whether this was a success.

Have Fun

Paul

Update: Monday was fine, Tuesday was back to 100% CPU!!! Unfortunately I had missed adding a NOHINT to the local table for the report stored procedure. At one point we had 500,000+ lock requests per sec. Once I fixed the stored procedure, it took 2-3 minutes for the CPU to go back to 4-5%!
That massive CPU load caused by lock contention is a demostration of the problem SQLserver has with providing the ACID transactional model, by using shared locks to enable consistent reads. More in this post.

Personal DJ

Here is an interesting website Pandora, (ht: Steve Pavlina) which allows you to search for an artist or song title and builds a music feed based on that search. You can also guide your computer DJ by giving it feedback on whether you like to song or not. Very cool.

This service is a similar progression towards more consumer (individual) driven pulling of media content like time shifting TV, radio, movies and podcasts, with RSS and Atom providing an easy tool to see new or updated content.

Have Fun

Paul

Demographics explaining housing boom

I was looking at this article on Stephen Kirchner's economics blog, it is an interesting article from the Federal Reserve in the US about a model which uses demographics to predict housing demand and hence property booms.

Here are link to the PDF article from the Federal Reserve.

I noticed that the author of the article didn't run the model over Australia or New Zealand. It would be interesting to see what the model predicts for Australia. The other interesting country to model would be China, given the one child policy will affect China's demographics in the future.

I recommend you go read some of the Julian L. Simon's articles, this article is a good example of the idea that people are largely an untapped resource of potential, and there is also the online book "Ultimate Resource"

Have Fun

trackbacks: Fundamentals of House Price Inflation

Oracle: Silent installs using response files

I have been setting up Oracle 10g R2 on a Redhat box which has to be reached by logging into 3 separate boxes (3 hops). Unfortunately the ability to run a x-windows client and use the Oracle universal installer GUI is not possible.
So I spent 30 minutes studying how to make a response file to pass to the installer.

The Oracle installer (on unix) is called normally from the command line using the command:
bash% runInstaller

Running the installer in silent mode and passing a response file is achieved by running the command:
bash% runInstaller -silent -Responsefile response.rsp

Luckily Oracle don't leave you to create your own, they provide a few sample response files. For 10g those were located (after unzipping the Oracle database zipfile) in the database/response directory.
After that it was dead-easy. Each sample response file is extremely well commented and explains each parameter well enough to understand its importance. Bravo Oracle!

So after about 15-20 minutes editing a copy of a sample response file, I kicked off the installer and bingo!! I now have a new Oracle10g install, this was complete in about 5 minutes.

The best part of the response file was the ability to take that file and reuse on the failover box. The return on investment of time after the first edit is that each new machine which is using a similar Oracle install becomes simple. Almost to the point of adding the command to the standard unix (redhat) build guide.

I will write about using the DB creation assistant in the next couple of days.

Have Fun

More site changes

I have been playing around with the various bits and pieces which you can plug into the blog. You will notice I have shed some anonymity.
I have noticed a couple of people who have come to the site have looked at my profile and I guess it is time to step into the 21st Century internet.

Will I add adsense? I am not sure, I have reading elsewhere that more specific blogs which focus on a specific topic are more likely to have more traffic. However I don't see this as my new 9-5 job anytime in the near future. I am enjoying SQLserver and Oracle tuning too much to leave it.

If I do spin off parts of the site to other specific blogs I will probably continue posting the articles here anyway.

Have Fun

Oracle: Automatic Statistics Collection

I have been reading about the new features in Oracle10g. One of the nice add-ons is the out-of-the-box automatic statistics collection.
This means one less script to run externally from the OS, whether it is cron or AT or whatever.

DBMS_SCHEDULER is a PL/SQL package which provides a complete scheduling system. You can have groups of windows of schedules. This provides plenty of flexibility to choose when and what you want run on the database. Plus given the some other packages (FILE_TRANSFER) this could include any log shipping, ETL or other tasks as well. I haven't tested this functionality yet, but it is on the to-do list.

So I checked out what scheduler_jobs where scheduled to run using this SQL

-- List scheduler jobs

column "Name" format a30
column "Start Date" format a21
column "Next Run" format a21

select owner||'.'||job_name "Name",
to_char(start_date, 'Mon DD HH24:MI:SS') "Start Date",
to_char(next_run_date, 'Mon DD HH24:MI:SS') "Next Run",
enabled,state,run_count,last_run_duration
from dba_scheduler_jobs
order by owner,job_name;

I found the in-built PURGE_LOGS and GATHER_STATS_JOB scheduled.
GATHER_STATS_JOB runs the package DBMS_STATS.GATHER_DATABASE_STATS.

However the GATHER_STATS_JOB didn't have a next_run_date, which was weird. So off to the metalink.oracle.com to check what was the story with the missing next_run_date. Not much, one forum question with a response to raise an iTAR.

Instead of rushing to log an iTAR I thought it might be more useful to check out how the GATHER_STATS_JOB was meant to be schedule.
The job is created during install through the running of the script in $ORACLE_HOME/rdbms/admin/catmwin.sql.
This script creates the job, two schedule windows, a schedule group and a schedule resource group.
I checked the various DBA_SCHEDULER_ views to check that everything was there and it was, I also dropped and recreated using the catmwin.sql script. Still the job had no next_run_date.

So I decided to create a new schedule called NIGHTLY which will run at 10pm each night. I assigned that schedule to the GATHER_STATS_JOB. Here is the PL/SQL

BEGIN
dbms_scheduler.create_schedule(
schedule_name=>'NIGHTLY',
start_date=>sysdate+7/24,
repeat_interval=>'freq=daily;byhour=22;byminute=0;bysecond=0',
end_date=>NULL,
comments=>'Nightly window for maintenance task');
dbms_scheduler.set_attribute('GATHER_STATS_JOB','SCHEDULE_NAME','NIGHTLY');
EXCEPTION
when others then
if sqlcode = -27477 then NULL;
else raise;
end if;
END;
/

Note: You will have to set the start_date to some future date. I set it to 9.30pm (as it was 2.30pm when I run this).

I checked the scheduler_jobs again and bingo. The GATHER_STATS_JOB now has a next_run_date which is set to start at 10pm.
It would seem that the start_date information was not being resolved from the various windows within the window group.

Hopefully this will help you if you want to get the statistics collection happening faster.

This is just scratching the surface of the power of this package. It is used by several different packages within Oracle and suggests plenty of flexibility and power to perform database tasks.

Have Fun

Oracle
DBMS_SCHEDULER

SQL Server Logshipping and Daylight saving

Last weekend, Most of eastern Australia switched to summer time or daylight saving time.

Fun and games. The logshipping is normally done every 2 minutes, unfortunately those jobs which run on the hour (2am became 3am) broke.
SQLserver job agent reruns all jobs between 2am and 3am. So BACKUP LOG was run up to 30 times at 3am. Unfortunately the filename was not sufficiently unique, even a timestamp wasn't enough eg. dbname_200510300300.log
So bang the RESTORE LOG on the failover box breaks as it is now missing those overwriten logs.

Once the alarms are handled. This just required a restore from backup, leaving in STANDBY mode and rolling the failover database forward until it is up-to-date.

Moral of the story, we will investigate more unique filenames and/or having a 61 minute blackout period in the schedule of the log shipping. This doesn't mean the logshipping won't run for one hour, it will just mean it won't be run every 60/x time per hour to make up for the missing hour.

Have Fun


SQL
Daylight saving
Logshipping

Rent vs Mortgage: Comparisons of Dead Money

The standard advice is that rent is dead money. But is it?
In this article we'll be delve into this standard financial advice. We will compare the differences between paying rent and paying a mortgage, and investing the difference.
If one is better than the other, is there an optimal point for either.

Any bank website will provide information about the loan repayments and also the schedule of the loan (year by year). This information provides a good start to determine the optimal strategy for renting vs mortgage.
If you are not disciplined enough to save the difference between rent and mortgage repayment then having mortgage is a good form of forced investment.
Also as the loan amount may not change for the life of the loan (if no equity is withdrawn), the amount owed remains static. This means that whilst you might have a $300,000 loan, the property should appreciate over the period of the loan to have a value much higher than the original purchase price.
This capital gain/appreciation was used as a sample return on property and used to determine the value and hence the rent over the life of a loan.

The calculations were made with the following numbers:
1) Mortgage interest rate: 7.32%
2) Rental Yield: 4%
3) Property Return: 5% (Total 9%)
4) Fixed Interest: 5%
5) Stocks Return: 12%
6) loan amount: $270,000.00 ($300,000 with 10% deposit)

Assumptions:
1) Landlord is able to increase rent each 12 months to maintain rental yields in line with property value. ** This has not been the case in Sydney over the last 5 years
2) Straight line increases in investments (ala no cycles)
3) No changes in mortgage interest rates for the life of the loan.

Basically rent and interest are dead money in terms that neither provide a return. The amount of money available to invest (the difference between mortgage and rent) enables to renter try and out-perform the property investor (mortgage repayments).

Here are the results:
1) If the money difference between rent and mortgage is invested in stocks, the long term gain means the total investment after 30 years is higher for the renter, with a $750,000 difference.
2) The optimal point were rent begins to exceed interest repayment is around 10 years. This result would indicate that if you must buy property, moving into the house after 10 years is a effective way to minimize the cost of housing.
3) Around year 13-14 the rent begins to exceed the total repayment per week. (Remember assumption 1 though). This point is found by using the percentage the principal makes up of the mortgage repayment vs the difference between rent and mortgage.

What these results indicate is that if you are a good tenant paying a reasonable rent,
1) Sign a longer lease as this demostrates you are a good tenant to the landlord.
2) Setup an automatic saving account which deducts at paytime.
3) Invest the proceeds in a stock market or buy the property and rent elsewhere and put the difference towards repaying the loan faster.

I didn't check whether using leverage ala investment property or stock margin loan increased the overall return to the renter. Without checking, it probably will and will also be tax effective.
Result one demostrates that renting can be an lifestyle choice which with discipline to invest can result in having more wealth at the end of the loan term.

Have Fun

Disclaimer: You will need to check these calculations for yourself and seek financial advice.

Oracle Text LOBs - space allocation

Careful allocation of space for Oracle Text or other LOBs is extremely important, given that the storage unit is in chunks of Oracle blocks.
The CHUNK size is a multiple of the blocksize!

So you have chosen a reasonable blocksize of 8K for your default blocksize in the database,
what happens if your LOBs are much less than 8K? ... All that extra space is wasted per lob.

I noticed Oracle have released a good whitepaper on this issue which spells this out much better than the documentation and various scattered support notes. Pity this was too late (May 2004) for the people creating the database I now support :)

This database which has been severely burnt by the problem ala 50% space wastage of a large database.
We have both text and pictures, with the pictures stored out of the row. The LOB information is XML data. The average text size is 2K, the default blocksize is 8K. The average picture LOB XML is even less than 2K.
I ran a couple of scripts to determine the average lob space used and the amount of space wastage is averages 50-60%!!!

We have a couple of steps to complete before we can migrate to tablespaces which have smaller blocksizes (4k) and move the partitioned table lob data to those new tablespaces. In our case only the lob segments need to reside on the new (smaller blocksize) tablespaces.

I have included the script. The script gets back the distribution of lob sizes for a given table and lob name.
Note: The lob name is the column name which is of a LOB datatype.

It will also take a while to run this script on very large datasets or smaller spec machines.

Have Fun

--- script begins ---

-- Show sizes for LOBS
select avg(dbms_lob.getlength(&&lobname)) "average LOB size", stddev(dbms_lob.getlength(&&lobname)) "stddev LOB size", min(dbms_lob.getlength(&&lobname)) "Min LOB size",
max(dbms_lob.getlength(&&lobname)) "Max LOB size"
from table_name
/

The difference between debt and equity

This is an interesting ruling to say the least. The SMH reports the ruling in favour of a shareholder to be classed as a creditor.
This is plain wrong.

Investing in a company makes you a shareholder (you own a share of the company). A part OWNER in the company.
When a company appoints an administrator, it is saying the current owners are no longer capable of running the company. Normally the creditors appoint administrators.

Being a creditor of a company is different. You have either lent money to the company or you have sold services or goods to the company. Both actions are essentially the same thing... In lending money to the company, you have transfered money to the company so they can go purchase goods and services.
As a creditor you have performed one part of a transaction. As a creditor the company recognises that the company owes you payment in the future, in the form of money.

This is the fundamental difference between equity and debt.
Equity = share in company profits either through dividends or share price increase
Debt/Creditor = owed money (future goods and services)

Have Fun

Automating SQL Profiler

Stored procedures to automate SQL profiler. Here is the two scripts which I use to automate running SQL profiler on a weekly basis.

This script sets the necessary settings for profiler.
Three things you must do before running this stored procedure.

1. You need to add a path to the file. I normally use d:\perfmon\database_... This script will add the date and time to the end of the filepath. This means the filename looks something like _20050914_150.trc
2. Check or change the maxfilesize. It is currently set to 50M.
   
3. Check or change the duration filter. It is currently set to only capture SQL/Stored Procedures which run for more than 500ms.
4. Change or remove the filter for database name. I normally trace the specific database related to the application.

Have Fun

--- script begins here ---

use msdb
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dba_trc_database]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dba_trc_database]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dba_trc_database

@Sec int = 7200 -- Duration in seconds for the trace (2 hours)
, @TraceID int OUTPUT -- Return the TraceID to the caller.
WITH RECOMPILE
/*
* Creates a trace on the selected database.
***********************************************************************/
AS
declare @rc int
declare @dbaTraceID int
declare @maxfilesize bigint
declare @DateTime datetime
DECLARE @FileName nvarchar(256)
, @StartDT datetime -- When the trace started

SELECT @StartDT = getdate()
SELECT @FileName = N'c:\trace_'+convert(varchar,getdate(),112)+'_'+cast(datepart(hh,getdate()) as varchar)+cast(datepart(mi,getdate()) as varchar) ,
@DateTime = DATEADD (s, @Sec, @StartDT)

set @maxfilesize = 50

exec @rc = sp_trace_create @TraceID output, 2, @FileName , @maxfilesize, @Datetime

if (@rc != 0) goto error

-- Set the events
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 2, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Duration greater than 500ms
set @bigintfilter = 500
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter

-- If you want a specific database traced change to database name
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'insert database name here'
-- Enable trace

exec sp_trace_setstatus 1, 1

-- display trace id for future references
print @TraceID
goto finish

error:
select ErrorCode=@rc

finish:

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--- script ends here ---

The second script is to setup a weekly trace at 3pm. It will run for 2 hours (7200 secs).
Steps before running this script

1. Check or modify the duration of the trace. The current duration is 7200 secs (2 hours)
2. Check or modify the time the job will run. This is easier to perform within Enterprise Manager after the job has been created.
   

--- script begins here ---

-- Script generated on 4/22/2005 11:06 AM
-- By: sa
-- Server:

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') <>
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Weekly shoulder peak profiler trace')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Weekly shoulder peak profiler trace'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Weekly shoulder peak profiler trace'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Weekly shoulder peak profiler trace', @owner_login_name = N'sa', @description = N'Runs a profiler trace to 50M or half hour on a shoulder peak (3pm)', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Trace database', @command = N'declare @traceID int
declare @rc int

exec dba_Trc_database ''7200'',@traceID

PRINT ''RC from starting Trace = '' + convert(varchar(9), @rc)
PRINT ''DBA TraceID = '' + convert(varchar(9), @traceID)', @database_name = N'msdb', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Weekly', @enabled = 1, @freq_type = 8, @active_start_date = 20050323, @active_start_time = 150000, @freq_interval = 2, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

--- script ends here ---



Required Disclaimer: Use at your own risk. I accept NO responsibility for any losses which occur in the use of this script.

profiler
SQL

Performance Tuning using SQL Profiler

The standard method of capturing SQL within SQLserver is to use Profiler. The output produced from the profiler can be saved to a file or to a table.
Once this information has been captured you can start checking out what SQL has the longest duration, one way to start SQLserver performance tuning.

Reading around the various SQLserver websites it is better to save the output from the profiler to a file.

On all the instances I look after I have setup an automated job which runs the profiler on a weekly basis. I have to test whether I can run the profiler more often. However as the databases are reasonable stable production environments there is little need to run every day.

I have written a couple of scripts to create stored procedures to run the profiler and create the job. I will add the scripts as a separate post.

After all that, what do you look for?

SQL and Stored procedures which have a long duration per call and large overall duration.

This is only part of the whole tuning picture which should always include looking at waits, however I prefer to look for the low hanging fruit during the tuning process. This low hanging fruit is the SQL or procedure missing an index, or whose logic is flawed from a performance perspective.

I will provide more scripts in the near future to perform the analysis based on the trace files produced from SQL Profiler. In SQLserver 2000 SP3, Microsoft provided a function which can be called directly from the database called ::fn_trace_gettable.
The best method is to use that function to grab the trace data from the trace file and insert it into a table.

The scripts to automate the tracing will follow this post.

Have Fun

profiler
SQL

Trade and Foreign Debt - 5

The trade deficit was close...so close to become a trade surplus in July, it will be interesting to see what happens in August.
As per normal the ABS has the document as a PDF which you can download and read at your leisure.
The real numbers had the deficit at AUS$389 Million, wiping $1,768 Million of the June Deficit. As always the moving average (Seasonally adjusted) and Trend Estimate (moving average of Seasonally adjusted) still show the deficit being more than a billion in the red.

How was the gold plated BBQ economy going in July 2005?
Finally the big increase in prices (mentioned by BHP, RIO, numerous coal companies) in coal, coking coal, iron showed up. So much for the forecast for the surplus in June or July. Maybe August is the swap over month.
The only chance that it won't is the potential for the higher oil price to show up. Although given Australia also exports fuel, any increase will be offset by an increase in export oil revenue.

Goods exports are more almost $2 Billion over July 2004 from $10.129 Billion to $12.275 Billion. That is a whooping 20% increase. This is with cereal grains and wool missing in action due to drought.
Wool now only makes up 2.1% of the total good exports, the same amount as beverages!

On the import side, the capital investment still stands out. Industrial machinery, steel and processed industrial supplies all show increases over July last year. This capital investment will see real wages rise over time as each laborer becomes more productive. The equipment imported (capital goods) is mining and related equipment. Computers and Scientific equipment also feature.
Investment in more and better tools leads to productivity gains. This means more can be produced, which leads to more profits. This is showing up in the annual reports of many public companies and corporate tax revenues of the Government.

When you look at the state by state breakdown, it becomes clear that Queensland and Western Australia have increased their exports and imports, pursuing their comparative advantages in mining.

The most interesting nugget of information is in the state by state breakdown of exports and imports. Victoria is going backwards in both exports and imports! Something to delve into in the future.

The more Australia produces the more we can buy. Produce and consume.

Have Fun

Previous articles:
Trade Deficit and Foreign Debt -1
Trade Deficit and Foreign Debt -2
Trade Deficit and Foreign Debt -3
Trade Deficit and Foreign Debt -4
Related Articles:
Australian Trade Partners

Is rational economics heartless?

To be rational to use reason. To be heartless is to lack compassion or morals/ethics, something like a sociopath.
EconLog have a good article on this very subject.

This is more of a philosophical question. Is being rational, using the facility of reason or your mind mean you lack compassion?

That is the real kicker, Adam Smith nailed this centuries ago. You can be completely self directed in your production and consumption and people still benefit more, than if you allowed someone else to determine or direct your actions. The invisible hand!!

This is the difference between a free, bottom-up market (capitalism) and a directed top-down market (statist = socialism, communist, nazism, rabid environmentism).

I suggest reading some Julian L Simon (Ultimate Resource) or Ayn Rand, or even "7 effective habits". The human mind is the powerhouse, the fount of capitalism.

As my older brother used to say when we were whining and complaining, "Get a grip (with reality)" i.e. no amount of whining and complaining is going to change your situation. You have to think and act.

Have Fun

Economic illiteracy - 1

Is this bordering on a political opinion piece... maybe

Here is a quote the maiden speech from a certain new Aussie senator.

"The purpose of the economy is not to produce the lowest price product for the end consumer. That may be a consequence of a good economy but it is not the purpose. The purpose of the economy is to create the greater nexus between the wealth of the nation and its people, and it generally does this through small business."

Drop the NOT and that is THE purpose of an free market/capitalist economy.

"The purpose of the (free market/capitalist) economy is to produce the lowest price product for the end consumer."

The senator's belief is that the economy exists to increase the connections (greater nexus) between the wealth of the nation and its people. It portrays the wealth as being static, and does not mention how this wealth is created. If you believe wealth is static, redistribution of that said wealth is just around the corner.

Why build a mine, run a farm or business if it is not to offer (produce) the most product at the cheapest price to every other producer/consumer.
This more product at a cheaper price is the driver, the purpose of the economy which creates the wealth the senator speaks connecting to everyone.
Every producer will want to connect to as many consumers as possible, and every consumer wants to connect to as many producers (of the same product) as possible. Anything which gets in the way means the market is less free than it could be.
If the senator wants more connectivity between producers and consumers, get rid of excessive government regulation and subsidies.

The senator needs to read some real economics. Bastiat is a good start.

The other underlining issue for the senator is the lack of investment and migration to regional areas. He is trying to answer and provide solutions to the question: why do people move and live in the city?

The answer is labour specialisation. In a city you can become more specialised at what you do, making yourself more productive and earn higher wages to boot.
Regional areas due to lower populations don't allow specialisation to occur to the same degree. This is why there are few if any brain surgeons and DBAs in regional areas.

Have Fun

Articles on CAPM model

This blog has a entry to some essays written by Arnold Kling about the Capital Asset Pricing Model (CAPM) developed by Fischer Black.

It is worth reading all the essays. They are easy to find by using the articles by the same author link on the left side of the techcentral station website.

It gave me a couple of ideas on how to get the most diversified portfolio. Basically you want to hold stocks which have a very low correlation with each other (or covariance). I had a quick google for ASX share betas and didn't find much. Most stock betas relate the stock's correlation to the market. In a way that is bad, considering that some indexes are weighted and therefore the correlation is actually a correlation to the those (more heavily weighted) stocks.

On the ASX there are plenty of indexes available and even some exchange tradable funds which are index funds.
So is holding a index fund the most diversified portfolio available to an Australian based investor?
If there exists a most diversified portfolio, how was it's performance against the index over the last 2,5 and 10 years?
To be competitive with an index fund, the portfolio will need to minimize the number of rebalancing actions required to reduce the overall transaction cost.

The other interesting idea from the CAPM is that individual investors control their risk exposure. One example was taking a low risk stock and using margin (debt) to leverage into a higher risk.

Have Fun

Australian trading partners

I still can not believe the lack of research in some of the opinion pieces in the newspaper. China is not our biggest trading partner.

Here are the facts (from the ABS May 2005 Trade figures) and this release from ABS from 2004

Trading partners (AUS$) overall
#1 US -$10.9 Billion
#2 Japan +$6.7 Billion
#3 Germany -$6.7 Billion
#4 China -$6.6 Billion
#5 South Korea +$4.3 Billion

Go and read these publications, they are free and they will set the record straight. The most interesting facts are what each of these countries trades with Australia. Most of the trade from the US and Germany (and France) is capital goods.

How many years of cultural cringe have we had to put up with when Australia is called a commodity economy, somehow implying with a certain smugness the backward nature of our gold plated BBQ economy (Meat,Bread (wheat), gold, iron and coal/coke).
It is our comparative advantage!

The other interesting fact is the growth of India and China and the massive trade we do with our New Zealand friends. Given the potential size of both India and China compared to NZ the trade potential is enormous.

Have Fun

Freehold property what is that?

I noticed an article yesterday about residents in Byron Bay having a bad case of the NIMBYs (Not In My BackYard), no it is not that nuclear reactor or power station, or that three storey development over the back fence. It is about summer holidaymakers i.e. Tourists.
Byron is sick of tourists who won't use the motels, hotels and caravan parks!
Today the local council discussed issuing rules to homeowners on how long they could rent their houses for each year!!! Here is the article from the SMH.

The best line for mind was from the Mayor who clearly has a socialist (perhaps watermelon) bent over private property and the use thereof.

Quote:"Asked if the impact would be to stop all summer lettings in the town's residential areas, Cr Barham said: "Essentially, yes. I'm challenging this because it has become so commercial it is having a negative impact on our community, and it puts at risk our legitimate tourism industry."

Someone better tell her that issuing compliance letters with the threat of fines will destroy summer lettings. That "so commercial" use of vacant property adds money to the local economy. I have been meaning to read up on Australian property law to get a better feel for freehold title, but this would smack of stepping over the mark.
More tourist business for the local councils which allow property owners to let as much as they want.
I wonder how much the 600 houses (homeowners) add to the local tourist economy? (see update)

This is one of those thought balloons which is floated to see what reaction occurs. Testing the waters, if this is let slide what is the next thing that local councils will issue compliance letters for???
There is already some NSW legislation which requires removal, introduced no doubt with approval from the watermelons. That is the native vegetation act, not only does this cover trees is also covers native grasses. So farmers wishing to improve their land can be fined for planting better pastoral grasses. So instead of running 20-30 DSE per acre they have to survive with grasses which can support 1-3 DSE per acre!! This means running 3 sheep rather than 20 sheep, so if the average profit per sheep is $30, that is $90 vs $600 per acre, that means you would need to own 6 times more land to produce the same profit.

The other side of this whole process is that local residents see tourists as spoiling their town and atmosphere. Instead of complaining, why don't they rent their houses during the summer period as well and holiday somewhere else! It makes good economic sense, as their holiday is partially subsidised by the renters and they don't have to be around during summer to get distressed about tourists.

Have Fun

Update: The cost of stopping the practice of holiday letting.
I did a search on Domainholiday for holiday houses and apartments in Byron Bay. Check it out. Places range from $700-$5000+ that would be per week.
So you live in Byron Bay and rent your house for 8 weeks over summer for an average of $2000 per week. That is $16,000 gross ($9000-$12000 depending on tax bracket) that you can spend on having a holiday somewhere else!
What if you want to be there for summer and work in Sydney or Melbourne for the rest of the year, the offpeak rate would be lower, say $500 per week,
so 52-8 weeks = 44 weeks @ $500 per week = $22,000.
That rent would mostly cover the loan on a investment/holiday apartment or house being slightly negatively geared.

After the holiday tourist has paid the $2000 for the week, they are probably going to spend $50-$200 per person if not more per day. That money is spent in town and surrounding areas.
So 8 weeks (56 days) for a couple or family spending $200 per day, that is $11,200 per house. Or $6.7 million for the 600 houses. If the current average letting period is 13 weeks (3 months), the town will forgo 5 weeks (35 days) or $7000 per house, or $4.2 million is tourist spending, plus the owners will miss out on $10,000 (5 weeks @ $2000 per week), a cost of $6 million.

Whereabouts

I have decided I should spend more time developing a proper essay rather than short newspaper like opinion pieces, not that I won't post short articles. I might even write a book review occasionally. I read alot, but I tend to reread the books I have as I get more ideas on the 2nd and 3rd reading.

In amongst that my wife and I became proud parents for the second time on Wednesday 3.48pm Aus EST to a lovely (little) daughter.
This means things have been slightly hectic over the last couple of days.

My sympathies to all the families who lost friends and family to the bomb attacks recently in London, and to the recent attacks in Iraq. I could say more, but you'll get better commentary at belmontclub for starters.

Have Fun

Trade deficit and Foreign Debt - 4

That time of the month again. Most papers ran the same article from AAP on the day of the release of the May 2005 Trade figures. The next day it was doom and gloom again.
SMH:
Australia falls off Sheeps back. How old is this, it has been happening for a century or more. The 1950s were a blip caused by cold climate Korean War.

Here is the full publication of the May 2005 trade figures from the ABS. Scroll past the trend estimates (Moving average of seasonally adjusted figures) and seasonally adjusted figures (moving average of original (real) figures to the breakdown by type.

As I have mentioned in the past, non-rural, specially coal and iron ore are having explosive growth coal up 54% over last year and ore up 31.7%. Whilst wheat farmers, up until last week's rain were the focus of woe for the rural sector, meat goods have been going well too up 22% on last year. If you managed to catch landline (ABC TV) you would have noticed that live sheep exports have restarted, causing a very big jump in mutton prices!
So the drivers for exports are iron ore, coke and coal, and meat. The stuff to make and have a BBQ with... iron and coke for steel, coal for electricity and meat to eat!
Don't forget to gold plate that BBQ as non-monetary gold has been doing well.

The BBQ exports will continue to growth as the 90 day receivables start to be paid by Mr Japan and Mr China et al.

The forecast (sounding like an economist now) is for a trade surplus in the June or July 2005 figures.

On the other side, imports are looking good. If the economic reporters actually looked at the figures in detail they see that consumptions goods are only part of the import picture.
The biggest growth in imports has been fuel (48%), and iron and steel (485), both immediate goods. The iron and steel is getting used to make stuff to make Australia more productive.

In capital good land, the land of greater productivity, machinery and industrial equipment and industrial transport have risen 22% or more over last year. This is stuff which enables us to mine more, sow and harvest more and transport to market more.

Things are looking good, companies are investing in more equipment to drive increased exports in our key comparative advantage areas.

Go and read for yourself, down deep in the report and see for yourself what Australia buys and sells. It is not ten billions of Chinese toys, clothes and furniture in a lopsided one way trade as the media and others portray. The Chinese are fourth behind Germany, Japan and the US.

Have Fun

Previous articles:
Trade Deficit and Foreign Debt -1
Trade Deficit and Foreign Debt -2
Trade Deficit and Foreign Debt -3
Next Articles:
Trade Deficit and Foreign Debt -5
Related Articles:
Australian Trade Partners

Oracle tuning stories - 1

Like any DBA I have a warchest of old tuning stories. I tend to use them to illustrate a point to a developer who can't see the reason for changing the way the program runs.

The easy low hanging fruit in tuning is finding the dodgy SQL which is not using a index. The performance increase with proper indexing can be truly amazing.

Given the large variety of index types and options available in Oracle, there will be an index for any tuning occasion. You have got to keep in mind though what the application is trying to achieve overall. Grabbing every row individually via an index, when you are going to read the whole table anyway should be dealt with differently then a complex or simple SQL join which randoms picks the rows it wants (adhoc user and search type queries).

I was talking to a developer about the log aggregation process which had failed and was taking a long time to catch up, about 8-10 hours for each days worth of weblogs. The application was in twlight zone, one of the applications and databases where the reason for it continued existence is questionable i.e. as long as it is stable and up, not much time is allocated to stressing over it.

A quick check of the logs produced nailed down the offending SQL.

select ... from t1, t2
where to_char(t1.username) = to_char(t2.username)

This was taking the 8-10 hours to run.

I checked the statistics, and added a function based index to t1.username
create functional index fx_t1_username
on t1(to_char(username))
tablespace ...

We rerun and it now takes.... 1 minute.

Now instead of the database being the slow portion of the whole process it is the perl scripts which aggregrate the weblogs. 1 hour per day. The catchup, instead of taking the rest of the month was finished in 12 hours.

Have Fun

AUS Credit Growth Year over Year

AUS_credit_growth_yoy
Originally uploaded by roobaron.

Here is the graph I was talking about earlier. It shows the credit growth by broad type (RBA defined).
It is current up to March 2005.

The most interesting info is the fact that housing credit has never been below 10% the start of 1984!!

When I upload the total credit, you will see what 20 years of compounding more than 10% does.

Housing affordability eh :)

Have Fun

Media is becoming pull not push

Here is a article really a rant about podcasting which completely misses the point how media is changing. Podcasting is cast as a step back from broadcast radio. What about all those people from around the world who can now listen the writer's radio content via podcasting.

Media has been a push medium, where the content is broadcast (pushed) at set times. Normally only once. You miss it and its gone.
Podcasting, blogs enabled by RSS and Tivo in TV land are pull. A pull medium allows the consumer of the content to decide what to listen/read/watch and more importantly when to listen/read/watch.

RSS is the protocol which allows software to check a website from new content and download it.

The consumer is in control of the content they consume. They can also consume content which they could never have been able to in the past i.e. overseas content.

The real beauty of podcasting for me is the amazing content from US technology conferences available from the ITconversations website. You can listen and in most cases see the same slides as someone who went there. The other important part is you can rewind or relisten to the presentation. So even if you were at the same conference and had to choose which to presentation, you can still see and listen to the rest.

My living standard has improved because of podcasting!!! As now I can listen and see material I which would have cost a small fortune to attend!! or in the case of radio shows never heard.

Have Fun

Oracle: Gotcha 1

I did an patch upgrade to 10G R1 (10.1.0.2) -> 10.1.0.4 on linux last night.
The patch went thru fine, cygwin x-window connected to linux with xauth.
A couple of clicks and then simple @?/rdbms/admin/catpatch.sql from sqlplus and its done.

However we have been having issues with Oracle being able to see more than 3 Gig of memory on linux. There is a solution, however I haven't seen the solution. I will shortly...

I restart the db with a older parameter file, save that as spfile and do the catpatch.
However the alert.log complained about the parameter PARALLEL_AUTOMATIC_TUNING being depreciated. I remove the parameter and restart.

Later on the production support blokes can't connect, they are getting TNS-12516, TNS-12560 errors. I log on from home can tnsping, can connect locally.
Eventually even sqlplus "/ as sysdba" as oracle from unix doesn't connect (without any errors as to why). Finally Oracle throws an max processes exceeded error when I try to connect as a different user.

It turns out setting the depreciated parameter PARALLEL_AUTOMATIC_TUNING = false (or not specifying it) means Oracle sets the processes to 30 automatically!!!!

I found that info in a forum entry on metalink (Oracle support website). Apparently that behaviour is undocumented and was raised to be documented. It is not there yet as I use the Oracle documentation libraries from tahiti.oracle.com (requires technet login)

I fixed set the PROCESSES parameter to a reasonable number and the connection isssues (and TNS errors) disappear.

Have Fun

Interesting article about Trade Deficit

By now you might have guessed my low opinion of the MSM economic reporting and opinion. Here is an interesting article about trade deficit and housing on another Australian economics blog

I need to create a flckr account so I can upload some nice graphs here. The main one I want to show is credit growth.

Have Fun

Reason for it all

Why roobaron? Started as a joke and it stuck.
In Aus. we have beer barons, media barons and cattle kings. So why not one animal which is unique to Australia, the kangaroo or known locally as roo or due to their massive numbers roos. So was born the nick "roobaron".
I remember a while back there was a media storm about the culling of roos which I think even made it to the UK (I was there at the time). The population has seriously exploded due to the number of extra watering points such as bores and dams which have been built to sustain the sheep and cattle.
Kangaroo tastes pretty good too. Must be cooked medium to medium rare due the low fat marbling.

Now I am just rambling...
At some point I will get over it and use my real name rather than a nick. I have been using the internet for a long time using nicks is second nature as anonymity was/is a big part of the internet.
Back in them olden days sonnie almost everyone used a nick.

Have Fun

Trade deficit and Foreign Debt - 3

Another month and another bunch of cherry picking economic reporting and analysis from the leading newspapers.

The ABS report releases a couple of different numbers; The trend estimate, seasonally adjusted and original. Most commentators seem to cherry pick the numbers to build a case for their report.

I will continue to use the original numbers where available rather than averages (Trend) of estimates, modified using a price deflator (Seasonally adjusted).

The Balance on the Current Account improved 19%! improving $3.215 Billion!
The Balance on Capital and Financial Account naturally improved as well by the same amount.

My bet is an adjustment to the figures will be required in the next couple of months.

I am still amazed at the amount of print and air time the woes of the rural sector have in comparison to their actual proportion of Australia's overseas trade.
Rural Goods exports = $6.390 Billion
Non-rural Goods exports = $22.636 Billion, almost 4 times as big or 80% of the total Goods exported. I looked up some sample reports that the ABS has and COAL is Australia's main export.
There are some nice historical spreadsheets on the Reserve Banks website. Basically the rural portion of Australian exports has been decreasing since the 1950s.
Even Treasurer Peter Costello got in on the act, saying the rural downturn was dragging on the national accounts. Big woop!
If we diverted some of the fresh monsoon rain which flows straight back out to sea towards inland Queensland, providing extra moisture, we could be the world's steak sandwich, or Teraraki Beef with rice, depending on which country the food is going to.

Unfortunately the fallacy of comparing foreign debt to income (GDP) continues. Even the ABS gets in on the game. Australia's net liability position is now 66% of GDP. It was 52.7% a decade ago.
Lending criteria is based on existing assets and incoming producing capacity.
In December 1994, the GDP stood at $139.530 Billion, in December 2004 it stood at $200.287 Billion. That is a 43.5% growth in GDP over ten years.
In December 1994, total foreign debt stood at $244.492 Billion, December 2004 it stood at $548.464 Billion. That was a 124% increase in ten years. Hence the increase as a percentage of GDP.
Before we throw the towel in, and start yelling "Banana Republic", we need to see how much the asset base grew. That is a task for stats from the ATO (a future post). We already know that the tax figures at saving we have an asset base around $4000 Billion or $4 Trillion. And that was just for reasonable sized businesses.

Clearly not all that extra debt when into investing in business. If you live in Sydney, Melbourne or Brisbane you already know the probable answer, Property. Sure enough, the RBA has a nice spreadsheet showing credit growth.
Property credit has rocketed, taking on the old exponential trajectory. Interesting enough it has never been below 10% year over year since 1982. It peaked at 20% twice in the last 5 years.

The other interesting tidbit is the increasing portion of the property credit which is securitised. Securitisation is the buzz financial tool in the US. Basically you, the lender bundle up a group of loans, low risk with a sprinkling of higher risk loans and offload/sell those loans as an asset! Hence the use of Mortgage Backed Assets (MBS) jargon in the US. This offloading clears the decks and allows you, the lender to turn about and lend again! A more detailed explanation can be found at the Prudent Bear. Doug Noland really nails this practice.

Business credit growth recovered rapidly from the nadir of the 1990-1992 recession "we had to have" but peaked in 1996. However recently it has started to pick again. My guess is commodity prices have allowed companies to start thinking about borrowing to explore, build and invest in new mine equipment. The longer the China build,US spend boom continues the more likely the trade deficit will go closer to zero, as increasing volume of commodities from Australia fuel the chinese manufacturers appetites.

I would love to show the graph of credit growth, however blogger doesn't allow inline images, they must come from somewhere else. I will sort out that somewhere else in time.

Have Fun.

Update:

The ABS released April trade figures. The increased price of goods and services kicked in, as volumes remained stable or declined slightly. More info to digest. The figures for May should be interesting, more so if the volumes pick up.

Previous articles:
Trade Deficit and Foreign Debt -1
Trade Deficit and Foreign Debt -2
Next articles:
Trade Deficit and Foreign Debt -4
Trade Deficit and Foreign Debt -5
Related Articles:
Australian Trade Partners

Tax cuts are NOT inflationary and super wins!

This is the idea that the tax cuts announced in the current Australian Federal government budget will somehow be inflationary.
This demonstrates a mis-understanding of what inflation is.

Inflation is a monetary thing NOT price thing.

Even the dictionary has it right.

Inflation: A persistent increase in the level of consumer prices or a persistent decline in the purchasing power of money, caused by an increase in available currency and credit beyond the proportion of available goods and services.

The sooner more people in Australia (and elsewhere) realise what inflation is the sooner we realise what is happening and why prices seem to always increase.

How can a tax cut be inflationary if the amount of money doesn't change (increase). The government isn't creating new money to give back, it is just giving back a small portion of the total tax. The amount of money hasn't changed.

The only way the tax cut can be inflationary is if there is new credit (loans) involved.

For example:
Mr 1.2 times-average wage (considered rich by some people, including the leader of the opposition) takes is $100 per month and uses his credit card to buy that $7000 plasma TV. Eventually he will pay the full $7000 back, but at the purchase the amount of money in circulation has increased through his use of credit. As he pays back the $7000 and watches new TV with better features sell for less than half his purchase price, that money disappears from circulation.

Mrs 2.5 times-average wage (got to be a stinkin' rich capitalist sow) puts her extra money $86.58 per week into a high interest term deposit (starting with $1000) earning 6% for her child's future university fund.
In 15 years, junior has a little over $110,000.00.
If she invested that money in a share fund returning 12%, junior walks away with a shade under $195,000.00 in 15 years.

Now the real deal, super.
On $125,000.00 she is getting super payment of $384.62 per fortnight, 8% of the salary is paid as well as super. Normally this would mean she is on a $135,000.00 package.
If she puts the tax cut money into her super fund returning 12% (without the stupid surcharge) with pre-tax dollars ($100?) and works for another 30 years, she is way better off.
Starting with $10,000 in the fund, she walks away with a cool $4.8 million!!!

Does she need a pension when she is 60-65?? She could (will) live for another 30 years and have $161,000 per year to live, assuming she took the whole amount out and put it under the bed.

It gets better...

At 65, she takes out $1 million and buys the dream everything, then she rolls the amount over and continues to get 12%, 12% of $4.8 million is $580,000 per year. Even if she lives it up and spends $200,000 per year, the nest-egg continues to grow and grow (the power of compounding) at aged 95 (who knows how far medical tech will advance in 30 years)
She will have $66 million dollars!!!

I added in inflation, it makes little difference in real term over the longer term. The numbers are smaller but given that apart from capital city housing, everything material tends to get cheaper over time (even food) someone earning $200,000 in 30 years will be better off than someone earning $100,000 now.

Have Fun

Trade deficit and Foreign Debt - 2

The SMH and the Australian still run the same old scare campaigns about trade deficits every month.
The SMH article is clearly a cherry picking exercise. Picking and choosing from the different figures from the ABS report i.e. trend estimates, seasonally adjusted and original (read real).
The article takes the trend estimate number for the headline deficit and uses original figures for fuels and lubricants.

From the SMH

"Australia has recorded its second largest trade deficit ever, casting further doubt on the health of the economy ahead of next week's budget."

This is a fallacy. Countries have run trade deficits in the past and have had booming economies. This fallacy assumes that the health of the economy is purely determined by its international trade position. Plus given the economy grows over time, in the future we will probably have a largest deficit on record. yawn

If you read the ABS report, the original (real) numbers tell a different story.

Like I mentioned last month. The imports are the good stuff. Capital and intermediate goods to make companies (and employees) more productive. The consumerables dropped off after the Christmas splurge.
An example of why investing in capital goods and intermediate goods makes sense for Australian companies.
March 2005: Buy $100 million of mining equipment which enables expansion of production of mine. Mine production expands from $20 million to $25 million per month.
Assume an equipment life of 3 years (36 months) and the company is now $80 million better off at the end of 3 years.
Calculation: 36 x $5 million (extra) - (cost of new equipment $100 million) = $80 million.

Before people do the culture cringe and say Australia is just a mine and a farm. There is good reasons, we have a big comparative advantage in both those areas.

For all the worry about rural exports, resources are the biggest export area for Australia.

	Goods				Services
	Rural	Resources	Manufactures	Other
Dec-04	6340	14704	7298	2362	8479

Rural exports only made up 16% of the total exports in Dec2004, less than services. The source is ABS but the Reserve Bank has a nice downloadable spreadsheet on this page.

The last laugh was that because rural exports are down that means drought. Sorry, no rain means drought.

"These falls suggest that the emerging debate about whether Australia is heading into drought is largely academic, with rural exports already behaving as if one is under way."

There is always the Australian dollar mentioned as well, it is too high, it is too low. A good exchange rate works both ways, yes exports seem dearer to our customers overseas, but imports are cheaper. Customers will want to pay lesser price... but we can pay less as well.

My guess the foreign debt issue will be another sore point. These companies and consumers just don't know what they are doing, maybe the Government should step in and regulate. Damn sovereign individuals.

Have Fun

Previous articles:
Trade Deficit and Foreign Debt -1
Next articles:
Trade Deficit and Foreign Debt -3
Trade Deficit and Foreign Debt -4
Trade Deficit and Foreign Debt -5
Related Articles:
Australian Trade Partners

Database design - 1

There are plenty of good books and websites on database design. A keyword search for "database design" is sure to return plenty of hits.

The unfortunate fact is that design is overlooked for many applications which decide they need a database to store their data. So most databases become little more than data repositories or glorified text files (with a SQL wrapper).
Mention the word constraint and most developers throw their hands in the air and think immediately of the "constraint violated" errors. Even dedicated database developers try to get out of using constraints. The excuses are varied, here are some examples:

1) Constraints are slow.
2) Constraint errors are database specific.
3) What is a constraint? (common response from free database land)
4) The application implements the business logic not the database.

Good database design is determining what tables are required and relationships between those tables are required to implement business rules.
eg. If you have a sales app, a simple database would have a sales table and customer table. It makes little business sense to have a sale without an customer. An enabled constraint would stop a user or application adding sales without customers.

The main reason developers try to code the business logic in the application is so the application can be database generic.
If a designer designs the business logic into the database, the database becomes application generic.
Making the database application generic does not mean that there is no need for a specific app. It means that the database can be accessed by more than one specific application.

There seems to be a flow from everything runs on the server, to everything runs on client, with the potential of grid and clustering, once again running most of the stuff on a server becomes the best idea from a performance and recoverable viewpoint.

From a DBA perspective, performance is determined by design. No amount of indexing or parameter changes, memory, disk etc will fix flawed application logic and poor database design.

More on the markers of good and poor design later

Have Fun

Woeful Manly

For all those NRL (National Rugby League) fans... Tonites Friday night big game was a fizzer. I tried to watch Manly vs Brisbane and it was a shocker. Manly's mistakes (and an interesting referee decision early) made it painful to watch. The Manly fans from work will be spewing.

I enjoy watching a good game of league or any footy for that matter. It is no fun watching one side self-destruct.

Unfortunately the range of footy on free-to-air TV is now League or AFL. Premier League Soccer summary is gone and the summary show and shortened game for Super 12 union is gone. And noone bothers with NFL, NHL either. I want to now why my hard-earned tax dollars go to buying junk science programs on the ABC rather than showing some days old sports summary program from the NFL or NHL. At least the sport is reality, super-volcano and mock-documentaries are pure fiction.

Have Fun

SQL server 2005 or is that 2006

Database journal has a series of articles about SQLserver 2005 or codename Yukon.

It seems that a beta release is the standard practice, which allows the software company to gauge what features to spend the most time getting stable. Ahhh I am just an cynical DBA.

Considering security is a big issue for databases, the list of features apparently in SQLserver 2005 which look like low hanging fruit for those black hats out there is interesting. Peer-to-peer replication via http or https, with replication from Oracle databases!! hmmmm.
The mention that replication could be done over the internet using anything but https or more likely a hardcore VPN channel i.e. http makes the idea of "secure as default" an interesting statement.

There was no mention of fixing the locking mechanism where readers can block writers (by default) using a shared lock. Considering that the majority of database applications tend to be 90-95% reads, the idea of slowing down writes to tables, in an attempt to achieve read consistency is interesting.
You want transactions to write and get finished asap. Especially when trying to scale to handle larger number of transactions.
It depends on your application, but unless you must be sure that large read sees the data without changes, you should use the NOLOCK hint as required. This is definitely the case where the database is used more as a data repository than a well designed source of data and more importantly business rules.

For all the auto-tuning that Oracle 10G and SQLserver 2005 (apparently) can do. The big gainers, massive improvements in performance are in the design and the communication logic between the app and the database. Missing indexes and I/O balancing are simple, and shouldn't occur. If they do, it is easy to look the performance guru/hero. Nailing a database which won't scale, convincing the developer(s) that the comm logic is flawed and a better solution exists and the grand-daddy, showing where an extra table will remove redundancy and dodgy self-joins (Design) are much more fun.

More later.

Have Fun

Oracle articles

Seems like Tuesday might be the day of the post.
I promised a database article and here it is.

I was hunting for something else the other day and came across the website for the Rocky Mountain Oracle Users group. The training day material is awesome.
I suggest you delve into the many presentations which are available. It will make you understand Oracle heaps better. I thoroughly recommend the articles by Tim Gorman. The one about using statspack with the lag function as a performance stat. datawarehouse is very good.

I have finally had the chance to play with Oracle 10G in a work development environment, supporting a couple of projects which are going into production. The wheels unfortunately turn very slowly. So 12 months after the new release we are finally getting CIO or equivalent willing to use Oracle 10G in a production environment. Risk aversion city!
The change in the amount of information available at the DBAs fingertips is refreshing. The added bonus is the amount of extra history tables which allow you to check what was happening in the past.
Whilst you can be as proactive as possible, sometimes problems still occur. The ability to see what the database was doing previously makes reconstructing what was happening at the time very easy.

It is refreshing to have this wealth of information because I have been supporting mainly SQLserver for the last 18 months. The lack of a historical table such as v$sqlarea (which stores executed SQL) means that this type of detective work is not possible when you are trying to solve a performance problem after the fact.
I think I am close to developing a solution but the amount of work to gather historical SQL info is a real pain.
Hopefully I will time to make this completely stored procedure based. It looks possible.

Have Fun

Consumer waste guilt-trip

Pack your bags we're going on a socialist, environmental guilt trip

The SMH has an article about "Wasteful Consumers". Shame about the statistical sample being 1644 people out of the population of 20 Million! That is 0.00822% of the population.

Deep down the authors of the report and this article want the reader to feel guilty for having individual tastes and wants...
"The value of wasted food and drink represented more than 13 times what Australian households gave to overseas aid agencies in 2003."

The cart the report is pushing is that individuals can not be trusted to spend their money wisely. That is their spending habits are wasteful.
Who should spend their money instead? blankout

I suggest the author of the report and the article should read some Hayek.

Have Fun

p.s. I have promised to write some database articles (and I will). However there has been too many easy pickings in the economic orchard at the moment.

Education

The SMH is running an article about the refusal of the board of studies to release a students raw marks. The attitude of the education bureaucrats is typical. If the truth about their scaling scheme was revealed I wonder what the response of individual students would be?
But releasing the raw scores was quote: "The disclosure of this information is contrary to the public interest,". This type of statement means the exact opposite. It is in the public interest to disclose the information because it will show the current marking scheme to be a farce.

Behind the current scheme is the idea that every student is equal in ability and to fail is bad. Failing is a feedback mechanism like touching a hot plate i.e. you are doing something wrong so try something else.

It is any wonder that having been coocooned within the school system students hit the reality of working or university hard.

A former student runs a site discussing many areas of the current education system.

Have Fun

Trade deficit and Foreign Debt - 1

Have a read of "Your Say" on the smh on the day the trade figures were released.

The whole trade deficit scare campaign based on Chinese made consumer goods is plain stupid. The Chinese are helping our living standards and we are close to running a trade surplus with them. At the end of the day selling to 20 million people in Australia is like selling to 20 million people in the same per capita group in China. I guess there will be more people in that category in China then Australia.

Immediate and Capital goods imports were larger than consumer goods and even larger than combined consumer goods and services. I have attached a table which breaks the component imports into percentages.

$Millions % of Total
Consumer goods: 4260 27.06%
Capital goods 2760 17.53%
Immediate 5589 35.50%
Services 3135 19.91%
Total imports: 15744

Capital goods are things like machines and computers to make the Australian workforce more productive. Immediate goods are refined petroleum products, parts to make capital goods etc.

There has been questions about GDP growth and of course debt. However the GDP is an income/expense item and debt is a balance sheet item. Finding what the asset base of Australia is harder... so I went to the ATO website.

The ATO (Australian Taxation Office) is a mine of information. Here is the latest 2001-2002 stats.

The ATO reports that these companies assets were valued at AUS $4.1 trillion. I looked but couldn't see an kind of assets for either personal, partnerships or funds.

The ABS (Australia Bureau of Stats) reported that Aus. net foreign debt was AUS$ 400 billion.

Some perspective is required. $400 Billion is only 10% of the assets as reported to the ATO by Australian companies. So foreign debt is nothing compared to debt denominated from within Australia.

Have Fun

Next articles:
Trade Deficit and Foreign Debt -2
Trade Deficit and Foreign Debt -3
Trade Deficit and Foreign Debt -4
Trade Deficit and Foreign Debt -5
Related Articles:
Australian Trade Partners

Welcome

Gudday,

I thought it about time I took my opinions and experience and uploaded them onto net.

The diet here will be mostly articles about databases and the vast field of economics.

As the frequency of posts it will depend on time constraints.

Note: There are no comments. I want to wait and see how the battle on comment and trackback spam proceeds.

Have Fun