[20230106]测试宽表查询.txt

来源：这里教程网时间：2026-03-03 18:20:43 作者：

[20230106]测试宽表查询.txt --//https://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/,重复测试: 1.环境: SCOTT@test01p> @ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 --//create_wide_table.sql,来自tpt脚本的demos目录. --//建立包含100条记录的1000个字段的表. SCOTT@test01p> @ tpt/demos/create_wide_table.sql PL/SQL procedure successfully completed. Commit complete. PL/SQL procedure successfully completed. --//在我的笔记本上很慢.主要集中在分析表建立直方图的操作上. $ cat create_wide_table.sql -- Copyright 2020 Tanel Poder. All rights reserved. More info at https://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. DECLARE cmd CLOB := 'CREATE TABLE widetable ( id NUMBER PRIMARY KEY '; ins CLOB := 'INSERT INTO widetable SELECT rownum'; BEGIN FOR x IN 1..999 LOOP cmd := cmd || ', col'||TRIM(TO_CHAR(x))||' VARCHAR2(10)'; ins := ins || ', TRIM(TO_CHAR(rownum))'; END LOOP; cmd := cmd || ')'; ins := ins || ' FROM dual CONNECT BY level <= 100'; EXECUTE IMMEDIATE cmd; EXECUTE IMMEDIATE ins; END; / COMMIT; -- stats with histograms EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'WIDETABLE',method_opt=>'FOR TABLE, FOR ALL COLUMNS SIZE 254'); -- no histograms -- EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'WIDETABLE',method_opt=>'FOR TABLE, FOR ALL COLUMNS SIZE 1'); -- EXEC sys.dbms_shared_pool.purge('SYSTEM', 'WIDETABLE', 1, 1); 2.测试1: SCOTT@test01p> alter system flush shared_pool; System altered. SCOTT@test01p> SET AUTOTRACE TRACE STAT SCOTT@test01p> SELECT * FROM widetable /* test100 */; 100 rows selected. Statistics ---------------------------------------------------------- 8124 recursive calls 0 db block gets 21487 consistent gets 0 physical reads 332 redo size 367114 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5009 sorts (memory) 0 sorts (disk) 100 rows processed SCOTT@test01p> alter system flush shared_pool; System altered. SCOTT@test01p> SELECT id,col1 FROM widetable /* test101 */; 100 rows selected. Statistics ---------------------------------------------------------- 1069 recursive calls 0 db block gets 254 consistent gets 0 physical reads 0 redo size 1732 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 20 sorts (memory) 0 sorts (disk) 100 rows processed --//我的测试比作者的recursive calls次数多,我每次执行前刷新了共享池. --//如果硬解析sql语句: SCOTT@test01p> SELECT * FROM Widetable /* test300 */; 100 rows selected. Statistics ---------------------------------------------------------- 7108 recursive calls 0 db block gets 21388 consistent gets 0 physical reads 0 redo size 367114 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4994 sorts (memory) 0 sorts (disk) 100 rows processed SCOTT@test01p> SELECT id,col1 FROM widetable /* test301 */; 100 rows selected. Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 63 consistent gets 0 physical reads 0 redo size 1732 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100 rows processed --//依旧会出现许多recursive calls. 2.测试2: --//session 1: SCOTT@test01p> SET AUTOTRACE off SCOTT@test01p> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- -------------------------------------------------- 252 61813 8104:7540 DEDICATED 8616 27 7 alter system kill session '252,61813' immediate; SYS@test01p> EXEC sys.dbms_shared_pool.purge('SCOTT', 'WIDETABLE', 1, 1); PL/SQL procedure successfully completed. SCOTT@test01p> SET AUTOTRACE TRACE STAT SCOTT@test01p> SELECT * FROM widetable /* test1 */; 100 rows selected. Statistics ---------------------------------------------------------- 8104 recursive calls 0 db block gets 21462 consistent gets 0 physical reads 0 redo size 367114 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5000 sorts (memory) 0 sorts (disk) 100 rows processed --//session 2: SYS@test01p> @ tpt/snapper stats,gather=t 5 1 252 Sampling SID 252 with interval 5 seconds, taking 1 snapshots... -- Session Snapper v4.33 - by Tanel Poder ( https://tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 252, SCOTT , TIME, hard parse elapsed time , 618784, 123.63ms, 12.4%, [## ], , , 252, SCOTT , TIME, repeated bind elapsed time , 9640, 1.93ms, .2%, [ ], , , 252, SCOTT , TIME, parse time elapsed , 619904, 123.86ms, 12.4%, [## ], , , 252, SCOTT , TIME, DB CPU , 608404, 121.56ms, 12.2%, [@@ ], , , 252, SCOTT , TIME, sql execute elapsed time , 354869, 70.9ms, 7.1%, [# ], , , 252, SCOTT , TIME, DB time , 634836, 126.84ms, 12.7%, [## ], , , 87.32 % unaccounted-for time* -- End of Stats snap 1, end=2023-01-23 10:29:40, seconds=5 PL/SQL procedure successfully completed. --//session 1: SCOTT@test01p> EXEC sys.dbms_shared_pool.purge('SCOTT', 'WIDETABLE', 1, 1); PL/SQL procedure successfully completed. SCOTT@test01p> SELECT id,col1 FROM widetable /* test2 */; 100 rows selected. Statistics ---------------------------------------------------------- 1109 recursive calls 0 db block gets 249 consistent gets 0 physical reads 0 redo size 1732 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 20 sorts (memory) 0 sorts (disk) 100 rows processed --//session 2: SYS@test01p> @ tpt/snapper stats,gather=t 5 1 252 Sampling SID 252 with interval 5 seconds, taking 1 snapshots... -- Session Snapper v4.33 - by Tanel Poder ( https://tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 252, SCOTT , TIME, hard parse elapsed time , 41333, 8.26ms, .8%, [# ], , , 252, SCOTT , TIME, repeated bind elapsed time , 196, 39.15us, .0%, [ ], , , 252, SCOTT , TIME, parse time elapsed , 42664, 8.52ms, .9%, [# ], , , 252, SCOTT , TIME, DB CPU , 31200, 6.23ms, .6%, [@ ], , , 252, SCOTT , TIME, sql execute elapsed time , 27512, 5.5ms, .5%, [# ], , , 252, SCOTT , TIME, DB time , 43366, 8.66ms, .9%, [# ], , , 99.13 % unaccounted-for time* -- End of Stats snap 1, end=2023-01-23 10:32:27, seconds=5 PL/SQL procedure successfully completed. --//可以发现查询字段少,分析时间也大大减少. 3.取消直方图信息呢? --//session 1: SCOTT@test01p> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'WIDETABLE',method_opt=>'FOR TABLE, FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed. SCOTT@test01p> EXEC sys.dbms_shared_pool.purge('SCOTT', 'WIDETABLE', 1, 1); PL/SQL procedure successfully completed. SCOTT@test01p> SELECT * FROM widetable /* test3 */; 100 rows selected. Statistics ---------------------------------------------------------- 2971 recursive calls 0 db block gets 6139 consistent gets 0 physical reads 0 redo size 367114 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 100 rows processed --//session 2: SYS@test01p> @ tpt/snapper stats,gather=t 5 1 252 Sampling SID 252 with interval 5 seconds, taking 1 snapshots... -- Session Snapper v4.33 - by Tanel Poder ( https://tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :) ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 252, SCOTT , TIME, hard parse elapsed time , 177785, 34.12ms, 3.4%, [# ], , , 252, SCOTT , TIME, repeated bind elapsed time , 2200, 422.18us, .0%, [ ], , , 252, SCOTT , TIME, parse time elapsed , 178474, 34.25ms, 3.4%, [# ], , , 252, SCOTT , TIME, DB CPU , 187201, 35.92ms, 3.6%, [@ ], , , 252, SCOTT , TIME, sql execute elapsed time , 78676, 15.1ms, 1.5%, [# ], , , 252, SCOTT , TIME, DB time , 192798, 37ms, 3.7%, [# ], , , 96.3 % unaccounted-for time* -- End of Stats snap 1, end=2023-01-23 10:37:43, seconds=5.2 PL/SQL procedure successfully completed. --//如果取消直方图信息,hard parse elapsed time=177785,而前面的hard parse elapsed time=618784. --//618784/177785 = 3.5 , 快了3.5倍. --//也就是在需要的字段建立直方图才能更好发挥性能,减少不必要的内存占用.继续看看共享内存的消耗. 4.共享内存的占用情况分析: --//session 1: SELECT id,col1 FROM widetable /* test2 */; SELECT * FROM widetable /* test3 */; SELECT * FROM widetable /* test2 */; SYS@test01p> SELECT sharable_mem, sql_id, child_number, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % FROM widetable%'; SHARABLE_MEM SQL_ID CHILD_NUMBER SQL_TEXT ------------ ------------- ------------ ------------------------------------------------------------ 878452 80n8tmqxn1vq3 0 SELECT * FROM widetable /* test3 */ 878484 2cv3jrfv2y25c 0 SELECT * FROM widetable /* test2 */ 19354 78m12kgbsbjrq 0 SELECT id,col1 FROM widetable /* test2 */ 6 rows selected. --//2列光标占用19 kB 和100列 takes 886 kB 在共享内存段!! SYS@test> @tpt/sqlmem 80n8tmqxn1vq3 Show shared pool memory usage of SQL statement with SQL_ID 80n8tmqxn1vq3 CHILD_NUMBER SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM ------------ ------------ -------------- ----------- 0 878452 316376 219144 TOTAL_SIZE AVG_SIZE CHUNKS ALLOC_CL CHUNK_TYPE STRUCTURE FUNCTION CHUNK_COM HEAP_ADDR ---------- ---------- ---------- -------- ---------- -------------------- -------------------- -------------------- ---------------- 264000 264 1000 freeabl 0 kccdef qkxrMem kccdef: qkxrMem 000007FF11FA0508 136368 136 1000 freeabl 0 opn qkexrInitO opn: qkexrInitO 000007FF11FA0508 112528 56 2002 freeabl 0 qosdInitExprCtx qosdInitExprCtx 000007FF11FA0508 96504 96 1000 freeabl 0 qosdUpdateExprM qosdUpdateExprM 000007FF11FA0508 56352 56 1000 freeabl 0 idndef*[] qkex idndef*[]: qkex 000007FF11FA0508 48272 48 1000 freeabl 0 qeSel qkxrXfor qeSel: qkxrXfor 000007FF11FA0508 40608 40 1005 freeabl 0 idndef qcuAll idndef : qcuAll 000007FF11FA0508 40024 40024 1 freeabl 0 kafco qkacol kafco : qkacol 000007FF11FA0508 37800 581 65 freeabl 0 181.kggfa 181.kggfa 000007FF11FA0508 8040 8040 1 freeabl 0 kggac kggacCre kggac: kggacCre 000007FF11FA0508 8040 8040 1 freeabl 0 qeeRwo qeeCrea qeeRwo: qeeCrea 000007FF11FA0508 8024 8024 1 freeabl 0 kksoff opitca kksoff : opitca 000007FF11FA0508 4008 4008 1 free 0 free memory free memory 000007FF11FA0508 3408 64 53 freeabl 0 kksol kksnsg kksol : kksnsg 000007FF11FA0508 1152 576 2 freeabl 0 16322.kgght 16322.kgght 000007FF11FA0508 1024 1024 1 freeabl 0 ctxdef kksLoadC ctxdef:kksLoadC 000007FF11FA0508 576 576 1 recr 4095 181.kggfa 181.kggfa 000007FF11FA0508 432 432 1 freeabl 0 qertbs qertbIAl qertbs:qertbIAl 000007FF11FA0508 432 432 1 freeabl 0 kctdef qcdlgo kctdef : qcdlgo 000007FF11FA0508 432 432 1 freeabl 0 opixpop kctdef opixpop:kctdef 000007FF11FA0508 296 296 1 freeabl 0 unmdef in opipr unmdef in opipr 000007FF11FA0508 296 296 1 freeabl 0 pqctx kkfdParal pqctx:kkfdParal 000007FF11FA0508 248 49 5 freeabl 0 kggsmInitCompac kggsmInitCompac 000007FF11FA0508 168 168 1 freeabl 0 audRegFro audta audRegFro:audta 000007FF11FA0508 152 152 1 freeabl 0 qcctx kkmqccr qcctx : kkmqccr 000007FF11FA0508 144 144 1 freeabl 0 kggsmCommonInit kggsmCommonInit 000007FF11FA0508 128 64 2 freeabl 0 kksol kkscuf kksol : kkscuf 000007FF11FA0508 112 112 1 perm 0 permanent memor permanent memor 000007FF11FA0508 104 104 1 freeabl 0 opiprwd opitc opiprwd : opitc 000007FF11FA0508 96 96 1 freeabl 0 ctxqrol kkqsr ctxqrol : kkqsr 000007FF11FA0508 96 96 1 freeabl 0 qksmm qksmmCs qksmm: qksmmCs 000007FF11FA0508 96 96 1 freeabl 0 qertbAllocatePa qertbAllocatePa 000007FF11FA0508 96 96 1 freeabl 0 qkaapd qkaqkn qkaapd : qkaqkn 000007FF11FA0508 96 96 1 freeabl 0 qesmaInitTblCtx qesmaInitTblCtx 000007FF11FA0508 88 88 1 freeabl 0 KGHSC_ALLOC_BUF KGHSC_ALLOC_BUF 000007FF11FA0508 88 88 1 freeabl 0 ctxPlanSig qksc ctxPlanSig:qksc 000007FF11FA0508 88 88 1 freeabl 0 qcsctx kkmqccr qcsctx: kkmqccr 000007FF11FA0508 80 80 1 freeabl 0 qcpctx kkmqccr qcpctx: kkmqccr 000007FF11FA0508 80 40 2 freeabl 0 chedef qcuatc chedef : qcuatc 000007FF11FA0508 80 80 1 freeabl 0 kggsmInit sm kggsmInit:sm 000007FF11FA0508 64 64 1 freeabl 0 cxach opiSem cxach : opiSem 000007FF11FA0508 64 64 1 freeabl 0 kggslHd Init kggslHd:Init 000007FF11FA0508 56 56 1 freeabl 0 xplGenXpl planL xplGenXpl:planL 000007FF11FA0508 56 56 1 freeabl 0 qcmemctx kkmq qcmemctx : kkmq 000007FF11FA0508 56 56 1 freeabl 0 qctctx kkmqccr qctctx: kkmqccr 000007FF11FA0508 48 48 1 freeabl 0 ktamd ktagmd ktamd : ktagmd 000007FF11FA0508 48 48 1 freeabl 0 qksrcMarkQB qks qksrcMarkQB:qks 000007FF11FA0508 40 40 1 freeabl 0 opixfalo froaty opixfalo:froaty 000007FF11FA0508 40 40 1 freeabl 0 qkaEnableWide c qkaEnableWide:c 000007FF11FA0508 40 40 1 freeabl 0 opixfalo ctxkct opixfalo:ctxkct 000007FF11FA0508 40 40 1 freeabl 0 kobjn kkdcchs kobjn : kkdcchs 000007FF11FA0508 40 40 1 freeabl 0 qcptgc kkmqccr qcptgc: kkmqccr 000007FF11FA0508 52 rows selected. https://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/ 1000列选择*光标有大量的内部分配(分配在光标堆中)，其中内部块的计数是1000或接近1000的倍数，因此编译的光标中的每列都有一个( 或两个)。这些结构是执行计划所必需的(比如，当需要将字段#3传递到执行计划树时，需要调用什么Oracle内核的C函数)。例如如果列 #77恰好是一个日期，后来相比一个时间戳列#88计划的一个单独的步骤，需要有一个额外的操作码，指示Oracle执行一个额外的数据类型转换函数的一个列计划步骤。执行计划是由其中的动态分配的结构和操作码组成的树。显然，即使是从单个表中简单地选择，没有任何进一步的复杂性，也需要大量这样的内部分配。 SYS@test> @tpt/sqlmem 78m12kgbsbjrq Show shared pool memory usage of SQL statement with SQL_ID 78m12kgbsbjrq CHILD_NUMBER SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM ------------ ------------ -------------- ----------- 0 19354 6640 5216 TOTAL_SIZE AVG_SIZE CHUNKS ALLOC_CL CHUNK_TYPE STRUCTURE FUNCTION CHUNK_COM HEAP_ADDR ---------- ---------- ---------- -------- ---------- -------------------- -------------------- -------------------- ---------------- 1984 1984 1 free 0 free memory free memory 000007FF1586FDD0 1152 576 2 freeabl 0 16322.kgght 16322.kgght 000007FF1586FDD0 1024 1024 1 freeabl 0 ctxdef kksLoadC ctxdef:kksLoadC 000007FF1586FDD0 840 280 3 freeabl 0 181.kggfa 181.kggfa 000007FF1586FDD0 576 576 1 recr 4095 181.kggfa 181.kggfa 000007FF1586FDD0 568 284 2 freeabl 0 kccdef qkxrMem kccdef: qkxrMem 000007FF1586FDD0 432 432 1 freeabl 0 opixpop kctdef opixpop:kctdef 000007FF1586FDD0 432 432 1 freeabl 0 kctdef qcdlgo kctdef : qcdlgo 000007FF1586FDD0 432 432 1 freeabl 0 qertbs qertbIAl qertbs:qertbIAl 000007FF1586FDD0 328 54 6 freeabl 0 qosdInitExprCtx qosdInitExprCtx 000007FF1586FDD0 296 296 1 freeabl 0 pqctx kkfdParal pqctx:kkfdParal 000007FF1586FDD0 296 296 1 freeabl 0 unmdef in opipr unmdef in opipr 000007FF1586FDD0 272 136 2 freeabl 0 opn qkexrInitO opn: qkexrInitO 000007FF1586FDD0 256 42 6 freeabl 0 idndef qcuAll idndef : qcuAll 000007FF1586FDD0 208 41 5 freeabl 0 kggsmInitCompac kggsmInitCompac 000007FF1586FDD0 192 96 2 freeabl 0 qosdUpdateExprM qosdUpdateExprM 000007FF1586FDD0 168 168 1 freeabl 0 audRegFro audta audRegFro:audta 000007FF1586FDD0 152 152 1 freeabl 0 qcctx kkmqccr qcctx : kkmqccr 000007FF1586FDD0 144 144 1 freeabl 0 kggsmCommonInit kggsmCommonInit 000007FF1586FDD0 128 64 2 freeabl 0 kksol kksnsg kksol : kksnsg 000007FF1586FDD0 128 64 2 freeabl 0 kksol kkscuf kksol : kkscuf 000007FF1586FDD0 112 112 1 perm 0 permanent memor permanent memor 000007FF1586FDD0 112 56 2 freeabl 0 idndef*[] qkex idndef*[]: qkex 000007FF1586FDD0 104 104 1 freeabl 0 opiprwd opitc opiprwd : opitc 000007FF1586FDD0 104 104 1 freeabl 0 kafco qkacol kafco : qkacol 000007FF1586FDD0 96 96 1 freeabl 0 ctxqrol kkqsr ctxqrol : kkqsr 000007FF1586FDD0 96 96 1 freeabl 0 qkaapd qkaqkn qkaapd : qkaqkn 000007FF1586FDD0 96 48 2 freeabl 0 qeSel qkxrXfor qeSel: qkxrXfor 000007FF1586FDD0 88 88 1 freeabl 0 KGHSC_ALLOC_BUF KGHSC_ALLOC_BUF 000007FF1586FDD0 88 88 1 freeabl 0 qertbAllocatePa qertbAllocatePa 000007FF1586FDD0 88 88 1 freeabl 0 ctxPlanSig qksc ctxPlanSig:qksc 000007FF1586FDD0 88 88 1 freeabl 0 qcsctx kkmqccr qcsctx: kkmqccr 000007FF1586FDD0 80 80 1 freeabl 0 qcpctx kkmqccr qcpctx: kkmqccr 000007FF1586FDD0 80 40 2 freeabl 0 chedef qcuatc chedef : qcuatc 000007FF1586FDD0 80 80 1 freeabl 0 kggsmInit sm kggsmInit:sm 000007FF1586FDD0 72 72 1 freeabl 0 qksmm qksmmCs qksmm: qksmmCs 000007FF1586FDD0 64 64 1 freeabl 0 kggslHd Init kggslHd:Init 000007FF1586FDD0 64 64 1 freeabl 0 qesmaInitTblCtx qesmaInitTblCtx 000007FF1586FDD0 64 64 1 freeabl 0 cxach opiSem cxach : opiSem 000007FF1586FDD0 56 56 1 freeabl 0 kggac kggacCre kggac: kggacCre 000007FF1586FDD0 56 56 1 freeabl 0 qcmemctx kkmq qcmemctx : kkmq 000007FF1586FDD0 56 56 1 freeabl 0 qeeRwo qeeCrea qeeRwo: qeeCrea 000007FF1586FDD0 56 56 1 freeabl 0 qctctx kkmqccr qctctx: kkmqccr 000007FF1586FDD0 48 48 1 freeabl 0 ktamd ktagmd ktamd : ktagmd 000007FF1586FDD0 48 48 1 freeabl 0 qksrcMarkQB qks qksrcMarkQB:qks 000007FF1586FDD0 40 40 1 freeabl 0 kobjn kkdcchs kobjn : kkdcchs 000007FF1586FDD0 40 40 1 freeabl 0 xplGenXpl planL xplGenXpl:planL 000007FF1586FDD0 40 40 1 freeabl 0 kksoff opitca kksoff : opitca 000007FF1586FDD0 40 40 1 freeabl 0 opixfalo froaty opixfalo:froaty 000007FF1586FDD0 40 40 1 freeabl 0 opixfalo ctxkct opixfalo:ctxkct 000007FF1586FDD0 40 40 1 freeabl 0 qcptgc kkmqccr qcptgc: kkmqccr 000007FF1586FDD0 51 rows selected. --//事实上，我们已经不再看到数千个内部分配块了(例如，与之前的1000个相比，只有2个kccdefs)。 5.补充直方图的情况: --//sesson 1: SCOTT@test01p> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'WIDETABLE',method_opt=>'FOR TABLE, FOR ALL COLUMNS SIZE 254'); PL/SQL procedure successfully completed. --//建立直方图很慢. SELECT id,col1 FROM widetable /* test4 */; SELECT * FROM widetable /* test4 */; --//session 2: SYS@test> SELECT sharable_mem, sql_id, child_number, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % FROM widetable%'; SHARABLE_MEM SQL_ID CHILD_NUMBER SQL_TEXT ------------ ------------- ------------ ------------------------------------------------------------ 878492 3mbjd8trhv711 0 SELECT * FROM widetable /* test4 */ 19394 60v2shu5cbx6d 0 SELECT id,col1 FROM widetable /* test4 */ --//说明直方图信息作为数据字段加载到共享池里面. 6.总结: --//查询最好避开*,选择需要查询的字段. --//在需要的字段建立直方图,oracle从10g开始改变收集字段统计信息的模式采用auto,9i是repeat,这样会导致在许多不必要的字段建立 --//直方图.

编辑推荐：

手动处理DG间隔

[20230106]测试宽表查询.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推