Re: Table Partitioning Question

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 20 Aug 2006 20:36:36 -0700
Message-ID: <1156131396.634570@bubbleator.drizzle.com>

Dereck L. Dietz wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1156115363.498021_at_bubbleator.drizzle.com...

>> The indexes should almost undoubtedly be local not global and it seems
>> likely your understanding of the partitioning, translated into your
>> WHERE clauses is not correct leading to a lack of pruning.
>>

>
> I have had little experience with partitioned tables up till now. I would
> think that if partitioning were to be useful that the indexes should be
> local and that the primary key should include the field partitioned on. Or
> am I mistaken about the primary key?

The primary key is irrelevant to partitioning.

>> It would be nice to see the Explain Plan? Be sure you create it using
>> DBMS_XPLAN not some legacy script.

>
> I got the explain plan from Enterprise Manager. I don't have a copy but I
> do plan on doing some work on another program which will be reading the
> partitioned table(s) so I should have plenty of time to get a copy of it. I
> expect the query to run at least 2 hours as it is right now.

EXPLAIN PLAN using DBMS_XPLAN will take less than 100 milliseconds. I strongly recommend using it. If you are unfamiliar with it go to Morgan' Library at www.psoug.org and look up the syntax and view a demo.

>> Can you run DBMS_METADATA on the remote server and get the actual DDL?

>
> I haven't tried that but I can certainly look into it.
>

>> Does this Oracle installation have a version number?

>
> Oracle 10g release 1.

?
10.1.0.3?
10.1.0.4?
If not 10.1.0.4 I would strongly urge applying the patch.

>> Are statistics current and created using DBMS_STATS? Using what
>> METHOD_OPT value?

>
> The statistics are recreated every week after every load. Unfortunately
> this is a process I'm not responsible for - at least not yet - and they are
> still gathering statistics using the old ANALYZE command.

The ANALYZE command was deprecated a long time ago and is of no value with 10.1.0.4. This is one change that should be made immediately.

>> But in the end it is likely inevitable that you will either have global
>> indexes or good performance. You seem to indicate that your efforts are
>> doomed to failure in advance. Why?

>
> I'd rather go into this in private. Suffice it to say that the DBA at the
> remote site once blamed a bad sector on a disk on someone's nested query
> (read only too). We're also a datawarehouse which is using SHARED SERVERS
> for all connections; all the control files and redo log files are
> multiplexed but are located in the SAME directory on the same disk; when
> monitoring the ADDM alerts I keep seeing PGA, SGA, virtual memory and other
> I/O warnings and tuning suggestions. I don't think my efforts are
> necesarily doomed to failure but it will be an uphill climb.

Contact me off-line. Your management needs to see this as a situation where they either give this DBA some serious training or replace them. What you describe is irresponsible: Likely common but irresponsible.

Oh am I glad for tenure. ;-)

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Aug 20 2006 - 22:36:36 CDT