Семера (О7) не кеширует данные

Relic Hunter
Дата: 05.11.2008 19:00:15
Приветствую!

Наблюдаю такую картину. При повторных обращениях к тойже таблице на Oracle 7 не уходят "physical reads" почему-то. На десятке все происходит как и положено. При первом обращении к таблице наблюдаю "physical reads", при повторном они уходят. Отсюда возникают жуткие тормоза на семере. В чем прикол или как заставить семеру кешировать?

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle7 Workgroup Server Release 7.3.4.0.0 - Production
PL/SQL Release 2.3.4.0.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for 32-bit Windows: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.0 - Production

SQL> set autotrace on
SQL> select /*+ full(c) */ count(*) from sc_claim c;

COUNT(*)
---------
1293263


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3841 Card=1091848)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SC_CLAIM' (Cost=3841 Card=109184
8)





Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
73129 consistent gets
73129 physical reads
0 redo size
188 bytes sent via SQL*Net to client
284 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ full(c) */ count(*) from sc_claim c;

COUNT(*)
---------
1293263


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3841 Card=1091848)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SC_CLAIM' (Cost=3841 Card=109184
8)





Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
73129 consistent gets
73129 physical reads
0 redo size
188 bytes sent via SQL*Net to client
284 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> exit

SQL> alter system flush buffer_cache;

System altered.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> set autotrace on
SQL> select /*+ full(c) */ count(*) from sc_claim c;

COUNT(*)
----------
929398


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3772 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SC_CLAIM' (TABLE) (Cost=3772 Car
d=929398)





Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13680 consistent gets
13672 physical reads
0 redo size
205 bytes sent via SQL*Net to client
276 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select /*+ full(c) */ count(*) from sc_claim c;

COUNT(*)
----------
929398


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3772 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SC_CLAIM' (TABLE) (Cost=3772 Car
d=929398)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13680 consistent gets
0 physical reads
0 redo size
223 bytes sent via SQL*Net to client
276 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> exit
wurdu
Дата: 05.11.2008 22:46:58
Возможно cможет помочь вот эта ссылка Cached tables in Oracle 7
Relic Hunter
Дата: 05.11.2008 23:27:56
Возможно помогла :)

Спасиба
wurdu
Возможно cможет помочь вот эта ссылка Cached tables in Oracle 7