RE: Comparing apples to apples on Exadata

+42 on testing the CTAS.  

IF your usage profile (availability online requirements, mostly) allows enough time for the copy and a rename it *may* be useful to do the CTAS as a solution. Especially if you have an access pattern by order where including small range reads or bounded ranges amenable to storage indexes or zonemaps. So IF you do the test CTAS in the most desirable order on the select and you test check that no indexes you have that are actually used for queries (and which are desirable to use in those cases) get a horribly worse cluster factor, this may be useful to you.  

(Horribly worse can be quantified as “it gets selected as an index in the plan currently with a happy result and is displaced in the ordered CTAS target by a different access method that produces a plan with a less useful solution profile, remembering that the usefulness of a solution should be weighted by your evaluation of elapsed time to solution AND machine costs for your situation regarding a particular queries’ business importance.” Whew, I hope you can parse that paragraph sentence.  

Typing this requires a caution: This is NOT, most certainly NOT, a recommendation to get on a treadmill rebuilding everything routinely. A bit of thinking and research into your actual queries will tell you whether the importance of a queries’ elapsed time is worth any effort and whether the time and inconvenience of any particular physical rebuild passes the laugh test to make it even worth testing.  

For this particular case, since a step along the way may be a CTAS anyway, you probably want to consider a physically ordered CTAS and measure whether it is worthwhile piggybacked on the apparent need to both “unmigrate” rows, clean out undo, and/or get multipiece due to column count rows into the same block.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder Sent: Tuesday, December 19, 2017 1:49 PM To: Will Beldman
Cc: Oracle-L Freelists
Subject: Re: Comparing apples to apples on Exadata  

Chained rows can definitely cause trouble for smart scans - especially if the chaining has happened over time (e.g. the next row piece resides physically far away from the previous piece(s)).  

If you have enough disk space - you can run a test first, before going through the index drop & shrink operations. Clone the table to a separate copy with CTAS and test if the query runs faster on the copy without chained rows.  

Tanel.  

On Tue, Dec 19, 2017 at 12:36 AM, Will Beldman <wbeldma_at_uwo.ca> wrote:

For the wayward googler, I can not shrink because the table has functional indexes.

I'll have to drop them, do the shrink, and recreate the indexes. Not sure if it will resolve my issue but it will definitely help.

On Monday December 18 2017 04:09:30 PM Will Beldman wrote:
> I checked the segment advisor and I think this might be coming in to play as
> well.
>
> Database 1 shows no problems.
>
> Database 2 shows the table is 49.00% chained rows and recommends a shrink:
> Reclaimable Space (MB):
> 1,362.83
>
> Allocated Space (MB):
> 3,458.00
>
> Used Space (MB):
> 2,095.17
>
> I tried to execute a shrink on the table on Database 2 but I get:
> ======================
> begin
> EXECUTE IMMEDIATE 'alter table "SYSADM"."##TABLE_NAME##" enable row
> movement'; EXECUTE IMMEDIATE 'alter table "SYSADM"."##TABLE_NAME##" shrink
> space'; end;
> Error report -
> ORA-10631: SHRINK clause should not be specified for this object
> ORA-06512: at line 3
> 10631. 00000 - "SHRINK clause should not be specified for this object"
> *Cause: It is incorrect to issue shrink on the object
> *Action: Verify the object name and type and reissue the command
> ======================
> ?!
> (and yes, I have the right object name. ##TABLE_NAME## is just a placeholder
> I'm putting in this thread.)

--
http://www.freelists.org/webpage/oracle-l



 



--
http://www.freelists.org/webpage/oracle-l