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