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