[20230106]测试宽表查询.txt
[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,这样会导致在许多不必要的字段建立
--//直方图.
--//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,这样会导致在许多不必要的字段建立
--//直方图.
