alter table ........cache?

显示全部楼层 · 2011-1-25 15:42:33

alter table aaa cache;
表的访问速度提升很快，是什么原理呀
好象和一般的buffer cache 中的数据访问不同

千问 · 2011-1-25 15:42:33

就是把常用表的data blocks放到cache里面，好象和一般的buffer cache 中的数据访问应该相同。作全表select的时候不会被最早撵出data buffers

千问 · 2011-1-25 15:42:33

Here's what Jonathan Lewis says as a supplement to yours:
The CACHE option was to tell Oracle that
a table that exceeded the small table threshold
should nevertheless be treated as a small
table for LRU/MRU purposes, provided it
also fell below cache_size (default 10% of
db_block_buffers).

If the system is Oracle 8, then one option
is to declare a KEEP buffer pool larger
than the size of the table, and allocate
the table to the KEEP pool. Once the table
is pulled into the pool by non-scanning
activity it will stay there.
Jonathan Lewis
Yet another Oracle-related web site:http://www.jlcomp.demon.co.uk
So powerneed got performance improvement probably because the table he full-scanned on is larger than 10% of db_block_buffers. Note that Oracle is pushing for the new KEEP buffer pool in place of ALTER TABLE CACHE.
Yong Huang
最初由玉面飞龙发布
[B]就是把常用表的data blocks放到cache里面，好象和一般的buffer cache 中的数据访问应该相同。作全表select的时候不会被最早撵出data buffers [/B]

千问 · 2011-1-25 15:42:33

那么，alter table table_name cache;后表是在data buffer cache 还是在library cache里呢？
------------------------
. Note that Oracle is pushing for the new KEEP buffer pool in place of ALTER TABLE CACHE.
Yong Huang
是什么意思？
谢谢

千问 · 2011-1-25 15:42:33

Table data always goes to buffer cache. Only parsed SQLs, some control structures such as locks and latches go to library cache.
Oracle has been touting about their splitting of buffer cache into 3 pools. It sounds like assigning a table to the Keep buffer pool instead of using the old alter table cache trick is what Oracle wants us to do. I didn't do much research to compare them. Tom Kyte, Steve Adams or J. Lewis may already have made some wonderful comments on these two things. If you find any, post it here.
Yong Huang
最初由 supersea 发布
[B]那么，alter table table_name cache;后表是在data buffer cache 还是在library cache里呢？
------------------------
. Note that Oracle is pushing for the new KEEP buffer pool in place of ALTER TABLE CACHE.
Yong Huang
是什么意思？
谢谢 [/B]

千问 · 2011-1-25 15:42:33

yong huang老大,
帮我看看我的那个vxfs的问题如何?不知道你有没有经验?

(

千问 · 2011-1-25 15:42:33

I saw your message. We're using vxfs but not with Quick I/O (that option costs a lot of money, doesn't it?). So I can't answer your question. Why does using quick I/O change your backup strategy or the setup of a standby server?
Yong Huang
最初由 chao_ping 发布
[B]yong huang老大,
帮我看看我的那个vxfs的问题如何?不知道你有没有经验?

( [/B]

千问 · 2011-1-25 15:42:33

最初由 supersea 发布
[B]那么，alter table table_name cache;后表是在data buffer cache 还是在library cache里呢？
------------------------
. Note that Oracle is pushing for the new KEEP buffer pool in place of ALTER TABLE CACHE.
Yong Huang
是什么意思？
谢谢 [/B]
cache后的数据库放在数据缓冲区的高端（常驻内存）
我们现在编程的时候
经常是只是第一次从数据库中读取
然后按照需要排列在内存里面了
这样比数据库buffer cache快了

千问 · 2011-1-25 15:42:33

最初由 Yong Huang 发布
[B]I saw your message. We're using vxfs but not with Quick I/O (that option costs a lot of money, doesn't it?). So I can't answer your question. Why does using quick I/O change your backup strategy or the setup of a standby server?
Yong Huang
[/B]
用了quick io之后,系统变成这个样子了, 每个数据文件都变成了:
bash-2.03$ ls -l
total 22544
-rw-rw---- 1 oracle oinstall 115425284月 14 09:51 control03.ctl
lrwxrwxrwx 1 oracle oinstall253月92001 index001.dbf -> .index001.dbf::cdev:vxfs:
lrwxrwxrwx 1 oracle oinstall253月92001 index002.dbf -> .index002.dbf::cdev:vxfs:
lrwxrwxrwx 1 oracle oinstall253月92001 index003.dbf -> .index003.dbf::cdev:vxfs:
lrwxrwxrwx 1 oracle oinstall253月92001 index004.dbf -> .index004.dbf::cdev:vxfs:
lrwxrwxrwx 1 oracle oinstall253月92001 index005.dbf -> .index005.dbf::cdev:vxfs:
lrwxrwxrwx 1 oracle oinstall255月 172001 index006.dbf -> .index006.dbf::cdev:vxfs:
lrwxrwxrwx 1 oracle oinstall257月 252001 index007.dbf -> .index007.dbf::cdev:vxfs:
lrwxrwxrwx 1 oracle oinstall25 12月 24 11:38 index008.dbf -> .index008.dbf::cdev:vxfs:
lrwxrwxrwx 1 oracle oinstall253月 11 09:43 index009.dbf -> .index009.dbf::cdev:vxfs:
不能直接rcp, 用cp倒是可以拷贝到本机的另外一个目录,
而且用qiomkfile的时候,生成的文件会比Oracle认识的大32KB,拷贝过去之后还是这样, 不知道是否正确的.
结果等了3个小时,还是可以用的

,还算幸运

千问 · 2011-1-25 15:42:33

另外:
vxfs如果不用quick io似乎也没有太大的优势啊?
我听这边原来的dba说,用了quick IO之后,性能提升却是还是比较明显的.
不知道价格如何.