Re: Comparing apples to apples on Exadata

From: Will Beldman <wbeldma_at_uwo.ca>
Date: Mon, 18 Dec 2017 16:09:30 -0500
Message-ID: <5142455.O5MjeO74HA_at_wbeldma>

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.)

On Monday December 18 2017 03:27:13 PM Will Beldman wrote:
> Yes, I understand. There's a few different possibilities and I'll have to
> dig deep.
>
> Your blog post about direct path reads seems to be highly relevant. If I
> execute this query:
> SELECT v$buffer_pool.buffers,dba_tables.blocks FROM v$buffer_pool,
> dba_tables WHERE table_name ='##TABLE_NAME##';
>
> Here is what I see:
> Database 1:
> BUFFERS BLOCKS
> ---------- ----------
> 33445 386032
>
> Table blocks are greater than the buffer cache so a direct read is triggered
> (?)
>
> Database 2:
> BUFFERS BLOCKS
> ---------- ----------
> 584588 434368
>
> Table blocks are less than the buffer cache so a direct path read is not
> triggered (?)
>
> Am I understanding this correctly? Would it make sense to consider
> *reducing* the buffer cache on Database 2? Does this force the database to
> bypass it's own buffer cache and consult the storage cache directly
> instead?
>
> On Monday December 18 2017 09:00:36 PM Tanel Poder wrote:
> > I just listed a few semi-educated guesses of what *could* be causing this,
> > in addition to Jonathan's suggestions... but they are guesses.
> >
> > The easiest way to start narrowing this down would be to either run
> > Snapper
> > or just look into V$SESSTAT metrics after running both of these queries in
> > their databases and looking for metrics like Jonathan mentioned (table
> > fetch continued row, %undo records applied ones and cell blocks processed%
> > ones). If you paste the numbers here, we can help :)
> >
> > --
> > Tanel Poder
> > http://blog.tanelpoder.com

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

Received on Mon Dec 18 2017 - 22:09:30 CET