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