晶晶实验二十二之 直接路径插入篇

Yong Huang

Great posting! I'm learning from your posting.

Some minor comments. v$bh.objd is better matched against data_object_id. Some operations can change it to a value higher than object_id.

将buffer cache清空, why not alter system flush buffer cache (or ALTER SESSION SET EVENTS 'immediate trace name flush_cache' in 9i). I remember somebody posted a test that seems to show bouncing the database could actually pre-load some block buffers and manually flushing it removes this artifact. But I never chased down the root cause.

If you really want to avoid going through buffer cache even for the select part (which does a full table scan), you can alter session set "_serial_direct_read"=true before the insert select.

I've never tested but can you test setting _idl_conventional_index_maintenance to false and see if the index can also behave the same way as the table in direct path insert? (I don't have access to a database right now.)

Yong Huang

Yong Huang

原帖由 晶晶小妹 于 2008-4-3 07:59 发表
YONG HUANG:我英文不好．．看的头晕．．
If you really want to avoid going through buffer cache even for the select part (which does a full table scan),

这句话中的ＥＶＥＮ，是不是只是做加强语气用的一个副词？可以翻译为甚至．或者不必翻译？

翻译为甚至. Hope everything else is clear.

Yong Huang

Yong Huang

原帖由 eagle_fan 于 2008-4-3 23:23 发表

Considering the index maintenance mechanism, how can you expert index using direct path insert

I thought about that. But was just curious about the description of this parameter _idl_conventional_index_maintenance, "enable conventional index maintenance for insert direct load". What could that mean? Maybe it's only about the indexes on those IDL% tables?

Yong Huang

Yong Huang

原帖由 Yong Huang 于 2008-4-5 23:59 发表

I thought about that. But was just curious about the description of this parameter _idl_conventional_index_maintenance, "enable conventional index maintenance for insert direct load". What could that mean? Maybe it's only about the indexes on those IDL% tables?

Yong Huang

I did a test. That mysterious parameter has no effect. I would be surprised if there was some magic here. Data in an index has to be stored in order.

Yong Huang

Yong Huang

> ctas直接路径插入的数据不在SGA中(目标数据),推测应该是在PGA中

That data, during CTAS, is in the target tablespace datafile as a temporary segment. It's NOT in PGA.

Yong Huang