Whilst writing some code that referenced the DBA_SEGMENTS dictionary view today, I realised that the contents of the PARTITION_NAME column actually contains the name of the subpartition when the table is a subpartitioned table…a script and results to illustrate: drop table jeff_unpartitioned purge/drop table jeff_partitioned purge/drop table jeff_subpartitioned purge/create table jeff_unpartitioned(col1 number,col2 number)/create table jeff_partitioned(col1 [...]
Archive of posts tagged tuning
Using partitioning to colocate data for optimal multiple index access paths
I came across a situation the other day where a client was accessing rows from a table via one of several different indexes and getting poor performance on all of them. The table in question had about five million rows and about four indexes. For the process in question, the table was hitting each of [...]
Changing the subpartition template and pre-existing partitions
My colleague Tank could not insert some records into a subpartitioned table the other day as he kept getting an ORA-14400 error and couldn’t work out why – nor could I straight away until he mentioned the possibility that the subpartition template had changed on the table…which it had. Lets try and work through the [...]
Using AWR to summarise SQL operations activity
I was asked recently how much “use” a database had – a non too specific question but basically the person asking it wanted to know how many queries, DMLs, DDLs etc.. were run against the database on a daily basis…kind of like the queries per hour metrics that are sometimes quoted on TPC benchmarks I [...]
Why you should skim read deep stuff first!
Seems that my recent posting about constraint generated predicates is already thoroughly covered in Jonathans latest book (Ch 6, pp 145-6)…including the bit about specifying a “NOT NULL” predicate to get around the issue with when the column in question is declared as “NULL” and not “NOT NULL”. Doug said to me recently that it [...]
Help the Cost Based Optimizer – add constraints – but beware the NULLS
If you use a function on a column then the optimizer can’t use an index right ? Not quite. You can of course use a Function Based index…but that’s not the subject of this post…so what else can we use in some circumstances ? Well, I attended the Scottish Oracle User Group conference in Glasgow [...]
10gR2: OBJECT_NAME missing from V$SQL_PLAN for INSERT statement
I’d written a query the other day to try and identify SQL statements that had been run which wanted a certain degree of parallelism (DOP) and had either run with less than the requested amount of PX slaves or had run serially – in order that we could identify whether the (poor) performance of a [...]
Scalar Subqueries and their effect on execution plans
Scalar subqueries…I remember Tom extolling their virtues at the UKOUG last year in one of his presentations. They seem like a neat idea except that they have an unpleasant side effect which I came across the other day on a production system. We had a situation where a piece of DML was running slowly and [...]
Parallel downgrades to serial after an OWB 10.1.0.4 upgrade
For the last few days we’ve encountered a problem with our OWB mappings running with wildly varying performance from one day/mapping to the next and we were a little confused as to why…figured it out now and thought I’d blog what we found… The problem we found was that we were getting lots of “Parallel [...]
Compression and ITL
I’ve been a bit quiet on the blogging front lately – I’ve been trying to work out what happens inside the database blocks during compression as well as trying to run some benchmarking stuff based on Doug Burns latest parallel execution presentation. To help me with the compression internals, Jonathan Lewis advised me a while [...]