一个小表但访问很频繁的表的优化问题,请高手指教

显示全部楼层 · 2011-1-4 10:35:48

sql 语句为:select a.sess_from_role, a.sess_to_role, a.auth_code from sess_auth a
执行计划为:
SQL> select a.sess_from_role, a.sess_to_role, a.auth_code from sess_auth a;
已选择516行。

执行计划
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=516 Bytes=4

644)
10 INDEX (FAST FULL SCAN) OF 'IDX_SESS_AUTH' (INDEX (UNIQUE))
(Cost=2 Card=516 Bytes=4644)
表结构为(三个列共同建了一个唯一索引)
SQL> desc sess_auth
名称
是否为空? 类型
----------------------------------------- -------- ------------
SESS_FROM_ROLE
NOT NULL NUMBER(10)
SESS_TO_ROLE
NOT NULL NUMBER(10)
AUTH_CODE
NOT NULL NUMBER(10)
SQL> select sql_text,executions,buffer_gets from v$sqlareawhere buffer_gets>50
0000000;
SQL_TEXT,EXECUTIONS, BUFFER_GETS
---------- -----------
select a.sess_from_role, a.sess_to_role, a.auth_code from sess_auth a,1935004,508713173

以上请高手帮我优化下请么办,把这个表CACHE会好点吗?(这个表才500多行)

千问 · 2011-1-4 10:35:48

主要是从v$sql里看buffer_gets是占第一位的,有没有办法降低一点?

千问 · 2011-1-4 10:35:48

才500多行,3个字段,1次执行的逻辑读就
300 ??
你这个全索引对查询没任何意义,反而会减慢速度,这个索引比表大

千问 · 2011-1-4 10:35:48

从表的名字猜测是个权限表,为什么权限表会经常全表扫描呢,一般是会查某个用户的权限吧
ps:
这种表应该是类似只读的表,可以考虑把pctfree设置成很小,甚至可以是0,减小表的大小,减少逻辑读,同时可以把这个表放
在keep池里

千问 · 2011-1-4 10:35:48

同意棉花的：）keep cache是一个办法。
如果上11g并且该表基本上只读，放进result cache更爽。
btw，没有where条件的SQL不是好SQL

千问 · 2011-1-4 10:35:48

各位大侠我的是10.2.0.1.0的版本，linux as 5.0平台的，keep cache会有效吗？

千问 · 2011-1-4 10:35:48

你这个全索引对查询没任何意义,反而会减慢速度,这个索引比表大
re:不是全索引扫描不要扫描数据文件的块了吗？怎么会更慢呢？

千问 · 2011-1-4 10:35:48

以下为全索引扫描的执行计划:
SQL> select a.sess_from_role, a.sess_to_role, a.auth_code from sess_auth a;
已选择516行。

执行计划
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=516 Bytes=4

644)
10 INDEX (FAST FULL SCAN) OF 'IDX_SESS_AUTH' (INDEX (UNIQUE))
(Cost=2 Card=516 Bytes=4644)

统计信息
----------------------------------------------------------

0recursive calls

0db block gets
40consistent gets

0physical reads

0redo size
8729bytes sent via SQL*Net to client
809bytes received via SQL*Net from client
36SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
516rows processed
以下为不走索引计划：
SQL> select /*+no_index(a idx_sess_auth)*/a.sess_from_role, a.sess_to_role, a
th_code from sess_auth a;
已选择516行。

执行计划
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=516 Bytes=4

644)
10 TABLE ACCESS (FULL) OF 'SESS_AUTH' (TABLE) (Cost=3 Card=51

6 Bytes=4644)

统计信息
----------------------------------------------------------

0recursive calls

0db block gets
39consistent gets

0physical reads

0redo size
8729bytes sent via SQL*Net to client
809bytes received via SQL*Net from client
36SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
516rows processed
从以上的执行计划看全表扫描也没什么优势，请问下各位大侠我要不要改为全表扫描呀

千问 · 2011-1-4 10:35:48

db_keep_cache_size
big integer 0
db_recycle_cache_size
big integer 0
cache是不是要设置以上参数呀，我刚CACHE了好像没什么作用，BUFFER——GETS还是那么高呀

千问 · 2011-1-4 10:35:48

一次40是比较正常的,但是你贴的那个平均每次接近300,是不是程序里设置的fetch size太小了,把表keep住要设置db_keep_cache_size,keep只是会减少物理读,不会减少逻辑读
ps:这种表适合放在前端的缓存里