Re: cached table questions

Francois Junod <fjunod_at_swissquote.ch> wrote in message news:87m8kl$dmm$1_at_pollux.ip-plus.net...
> Hello,
>
> I cached a table, to get better performance, using the following commands
:
>
> alter table emp cache;
> select * from emp;
>
> Questions :
>
> 1. How can I see the cached tables and their size in memory ?

V$CACHE dinamic view which created in database dictionary by running $ORACLE_HOME/rdbms/admin/catparr.sql script under the SYS account.

> 2. Where are they cached, in the shared pool or included with the db
blocks
> buffers ?

In database buffer cache (a part of Oracle SGA).

> Which parameter should I increase to pin tables in memory ?

DB_BLOCK_BUFFERS.
> 3. Is a cached table only used when I run a select statement or is it used
> for update statement as well ?

Basically ALTER TABLE ... CACHE is used to improve a full-scan query performance.

> 4. What occurs with an insert statement on a cached table ? Is the new row
> stay in memory ?
>

Use V$CACHE to analyze this for your environment.
>
> Any advises or experiences about cached table are welcome.
>
> Francois
>
>
Received on Mon Feb 07 2000 - 08:58:11 CST