+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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxx> 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.)
