sql – Oracle中临时数据的性能注意事项
我正在评估各种选项,以针对Oracle中的单个临时数据集运行一堆高性能查询.在T-SQL中,我可能会使用内存中的临时表,但Oracle没有与此功能完全等效的内容.
我目前看到这些选项: 1.全球临时表 CREATE GLOBAL TEMPORARY TABLE test_temp_t ( n NUMBER(10),s VARCHAR2(10) ) ON COMMIT DELETE ROWS; -- Other configurations are possible,too DECLARE t test_t; n NUMBER(10); BEGIN -- Replace this with the actual temporary data set generation INSERT INTO test_temp_t SELECT MOD(level,10),'' || MOD(level,12) FROM dual CONNECT BY level < 1000000; -- Replace this example query with more interesting statistics SELECT COUNT(DISTINCT t.n) INTO n FROM test_temp_t t; DBMS_OUTPUT.PUT_LINE(n); END; 计划: ---------------------------------------------------- | Id | Operation | A-Rows | A-Time | ---------------------------------------------------- | 0 | SELECT STATEMENT | 1 |00:00:00.27 | | 1 | SORT AGGREGATE | 1 |00:00:00.27 | | 2 | VIEW | 10 |00:00:00.27 | | 3 | HASH GROUP BY | 10 |00:00:00.27 | | 4 | TABLE ACCESS FULL| 999K|00:00:00.11 | ---------------------------------------------------- 2.解决PL / SQL表类型变量的问题 CREATE TYPE test_o AS OBJECT (n NUMBER(10),s VARCHAR2(10)); CREATE TYPE test_t AS TABLE OF test_o; DECLARE t test_t; n NUMBER(10); BEGIN -- Replace this with the actual temporary data set generation SELECT test_o(MOD(level,12)) BULK COLLECT INTO t FROM dual CONNECT BY level < 1000000; -- Replace this example query with more interesting statistics SELECT COUNT(DISTINCT n) INTO n FROM TABLE(t) t; DBMS_OUTPUT.PUT_LINE(n); END; 计划: ------------------------------------------------------------------ | Id | Operation | A-Rows | A-Time | ------------------------------------------------------------------ | 0 | SELECT STATEMENT | 1 |00:00:00.68 | | 1 | SORT GROUP BY | 1 |00:00:00.68 | | 2 | COLLECTION ITERATOR PICKLER FETCH| 999K|00:00:00.22 | ------------------------------------------------------------------ 3.物化观点 我正在为这个用例排除它们,因为所讨论的临时数据集相当复杂,对更新物化视图的影响太大了. 真实的数据考虑 以上是我正在尝试做的例子.真实的数据集涉及: >临时数据从大约15个连接表中非规范化. 问题 根据我的直觉,临时表查询“应该”较慢,因为它(可能)涉及I / O和磁盘访问,而PL / SQL集合查询仅仅是内存中的解决方案.但是在我的琐碎基准测试中,情况并非如此,因为临时表查询比PLx SQL集合查询要多3倍.为什么会这样?是否有一些PL / SQL< - > SQL上下文切换发生了什么? 我是否有其他选项可以在明确定义的临时数据集上进行快速(但广泛的)“内存中”数据分析?是否有任何重要的公开基准比较各种选项? 解决方法由于缓存和异步I / O,临时表实际上与内存表相同,并且临时表解决方案不需要在SQL和PL / SQL之间进行转换的任何开销.确认结果 将这两个版本与RunStats进行比较,临时表版本看起来更糟糕. Run1中的临时表版本的所有垃圾,以及Run2中PL / SQL版本的额外内存.起初似乎PL / SQL应该是明显的赢家. Type Name Run1 (temp) Run2 (PLSQL) Diff ----- -------------------------------- ------------ ------------ ------------ ... STAT physical read bytes 81,920 0 -81,920 STAT physical read total bytes 81,920 LATCH cache buffers chains 104,663 462 -104,201 STAT session uga memory 445,488 681,016 235,528 STAT KTFB alloc space (block) 2,097,152 0 -2,152 STAT undo change vector size 2,350,188 0 -2,188 STAT redo size 2,804,516 0 -2,516 STAT temp space allocated (bytes) 12,582,912 0 -12,912 STAT table scan rows gotten 15,499,845 0 -15,845 STAT session pga memory 196,608 19,857,408 19,660,800 STAT logical read bytes from cache 299,958,272 0 -299,272 但在一天结束时,只有挂钟时间很重要.使用临时表时,加载和查询步骤的运行速度都要快得多. PL / SQL版本可以通过用cast替换BULK COLLECT(collect(test_o(MOD(a,’|| MOD(a,12)))作为test_t)INTO t来改进.但它仍然比临时表版本慢得多. 优化读取 从小临时表中读取仅使用内存中的缓冲区缓存.多次运行查询部分,并观察物理读取缓存(磁盘)保持不变时缓存(内存)增加的一致性如何增加. select name,value from v$sysstat where name in ('db block gets from cache','consistent gets from cache','physical reads cache'); 优化的写作 理想情况下,没有物理I / O,尤其是因为临时表是ON COMMIT DELETE ROWS.而且听起来Oracle的下一个版本可能会引入这样的机制.但在这种情况下并不重要,磁盘I / O似乎不会减慢速度. 多次运行加载步骤,然后通过sample_time desc;运行select * from v $active_session_history order.大多数I / O都是BACKGROUND,这意味着什么都没有等待它.我假设临时表内部逻辑只是常规DML机制的副本.通常,如果已提交新表数据,则可能需要将其写入磁盘. Oracle可能会开始研究它,例如将数据从日志缓冲区移动到磁盘,但在实际的COMMIT之前不会匆忙. PL / SQL时间在哪里? 我没有任何线索.是否有多个上下文切换,或SQL和PL / SQL引擎之间的单个转换?据我所知,没有可用的指标 我们可能永远不知道为什么PL / SQL代码会变慢.我不担心太多.一般的答案是,绝大多数数据库工作都必须在SQL中完成.如果Oracle花费更多时间来优化其数据库SQL的核心,那么与附加语言PL / SQL相比,这将是很有意义的. 补充笔记 对于性能测试,将逻辑连接移除到单独的步骤中会很有帮助. SQL是加载数据的一个很好的技巧,但它可能非常慢并且资源密集.使用该技巧加载样本表一次,然后从该表插入更为现实. 我不会赌它,但我可以看到一种方式,随着数据变大,结果会完全改变.日志缓冲区和缓冲区缓存只能变得如此之大.最终,后台I / O可能会累积并压倒一些进程,将BACKGROUND等待转为FOREGROUND等待.另一方面,PL / SQL解决方案只有那么多的PGA内存,然后就会崩溃. 最后,这部分证实了我对“内存数据库”的怀疑.缓存并不是什么新鲜事,数据库已经做了几十年. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |