PCTFREE

If you’ve ever struggled with setting PCTFREE for a table, there’s a nice post on OTN from Anurag Tibrewal which should help you understand what you’re trying to achieve, and recognise the trade-off between wasting space and reducing row migration.

Free Space

I’ve just seen the following question on OTN:

I had a log table which was about 30G in Production, since it was growing in size we decided to drop it. After dropping it we can’t see the space being freed. Even at OS level we can’t see any reduction in filesize.Can you please explain and/or help in this?

This prompted me to come up with the following analogy.

Plan Notes

Nothing terribly important or exciting in this post, but you’ve probably seen the “Notes” section at the end of an execution plan from time to time. The notes are extracted from the “other_xml” column of (usually) the first line of the execution plan data using calls to the extractvalue() XML function.

It’s interesting to see from a trace of a call to dbms_xplan.display_cursor() how the number of things that can appear as notes is slowly growing over recent versions of Oracle. The statements below are extracted from the trace files for 10.2.0.3, 11.1.0.6, and 11.2.0.1 in that order:

Aliases

I was asked the following question recently: “Does the use of table aliases affect performance?” To which the best answer is probably “Yes, though in general you probably won’t notice the difference and there are reasons more important than performance for using table aliases.”

Consider the following two variants of a simple SQL statement:

Old plan_table

When using “explain plan” with “dbms_xplan.display()”, have you ever seen the following note at the end of the output:

Note
-----
   - 'PLAN_TABLE' is old version

It’s something I see surprisingly frequently, travelling as I do to many different sites, but it’s usually easy to deal with.

Old ideas

Here’s a question about function-based indexes that appeared a little while ago on OTN, demonstrating the problems of old information not going away. This is how the question started:

force_match

If you have looked at SQL Profiles (see for example Kerry Osborne’s blog) then you may have come across the force_match option for enabling or importing a SQL profile. I received an email recently asking a few questions about this feature.

first_rows_N again

If you run a query using first_rows_N optimisation you could run into a massive performance problem in cases where the optimizer thinks the complete result set is quite large when it is actually very small.

If both conditions are true the optimizer may choose a very resource-intensive execution path “expecting” to stop (or at least pause between fetches) after N rows – hoping to give the impression that it can respond very quickly – but find that the query has to run to completion because the N rows simply don’t exist.

Testing, Testing

Here’s a request that appeared on the OTN database forum recently:

I fetch reports in our Oracle 9i database each year between 9/1 to 8/31:

Each year I always have to manually change my sql and was wondering if there is a way to fetch the records between 9/1 to 8/31 without changing the year.

For example this will be my query for this year from 9/1/2009 to 8/31/2010:

SELECT  *
FROM    emp
WHERE   HIREDATE between to_date ('2009/09/01', 'yyyy/mm/dd')
                     AND to_date ('2010/08/31', 'yyyy/mm/dd')
;


 

Please advise how I create a query that I wont have to change each year and it will automatically fetch the current data from 9/1 to 8/31?

So here’s a question – if you want to write a piece of SQL that always gives the right answer whenever you run it, and doesn’t have to be modified each year, what’s the absolute minimum number of test scenarios that you should use to check that your code is correct ?

Update: My solution appears at comment #6 below.

Copy Stats

In a recent posting on OTN, someone was having trouble copying stats from one index to another using the import_index_stats and export_index_stats procedures from package dbms_stats modifying the contents of their “stat table” between the export and import.

Pseudo-parallel

There’s a posting on the OTN database forum asking for suggestions about restricting a query to data from one datafile of a tablespace.  It seems to be a slightly odd thing to do, but it reminded me of a “pseudo-parallel” trick I once used (and described) more than 10 years ago on a system running Oracle version 7. It was a long time ago, but I think I needed to do this because I wanted to execute a massive update with as many parallel threads as possible in the days when parallel DML wasn’t possible.

First Principles

I just had to start the new year with a little humour.