附录说明
附录是对测试过程中涉及到的一些操作进行记录和解析。
oracle清除缓存
alter system flush shared_pool;
将使library cache和data dictionary cache以前保存的sql执行计划全部清空，但不会清空共享sql区或者共享pl/sql区里面缓存的最近被执行的条目。刷新共享池可以帮助合并碎片(small chunks)，释放少数共享池资源，暂时解决shared_pool中的碎片问题。但是，这种做法通常是不被推荐的。原因如下：
·Flush Shared Pool会导致当前未使用的cursor被清除出共享池，如果这些SQL随后需要执行，那么数据库将经历大量的硬解析，系统将会经历严重的CPU争用，数据库将会产生激烈的Latch竞争。
·如果应用没有使用绑定变量，大量类似SQL不停执行，那么Flush Shared Pool可能只能带来短暂的改善，数据库很快就会回到原来的状态。
·如果Shared Pool很大，并且系统非常繁忙，刷新Shared Pool可能会导致系统挂起，对于类似系统尽量在系统空闲时进行。
alter system flush buffer_cache;
为了最小化cache对测试实验的影响，需要手动刷新buffer cache，以促使oracle重新执行物理访问(统计信息里面的：physical reads)。
查询表大小
oracle查看表占用空间
SELECT
segment_name AS table_name,
segment_type,
bytes,
bytes/1024/1024 AS size_in_mb
FROM
dba_segments
WHERE
segment_type = 'TABLE'
AND segment_name = 'YOUR_TABLE_NAME'; -- 替换为你的表名
hive查看表占用空间
describe formatted table_name; -- 替换为你的表名
hive索引说明
参照
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Indexing
客户端抽样
oracle的客户端sqldeveloper，对于超过50行的，非聚合查询、非分析查询要想得到真正的执行时间，外加select count(*) from (query)。
hive的客户端dbvear，同样也会存在类似的问题。
解析缓存
无缓存，耗时较久
set autot on statistics
SELECT sum(ncostmny),sum(ninnum) FROM t_od_ic_flow_100 WHERE vbillcode >= 'CR2023080100000001' AND vbillcode <= 'CR2023083000000001'
已启用自动跟踪
仅显示统计信息。
SUM(NCOSTMNY) SUM(NINNUM)
------------- -----------
31575009.6
Statistics
-----------------------------------------------------------
1661 CPU used by this session
1661 CPU used when call started
9576 DB time
16 Requests to/from client
15 SQL*Net roundtrips to/from client
93570 buffer is not pinned count
240031 buffer is pinned count
480 bytes received via SQL*Net from client
27134 bytes sent via SQL*Net to client
2 calls to get snapshot scn: kcmgss
556163072 cell physical IO interconnect bytes
94270 consistent gets
3 consistent gets - examination
94270 consistent gets from cache
91572 consistent gets from cache (fastpath)
9 dirty buffers inspected
2 enqueue releases
2 enqueue requests
2 execute count
2809845 file io wait time
69937 free buffer inspected
67891 free buffer requested
12200 hot buffers moved to head of LRU
1 index scans kdiixs1
772259840 logical read bytes from cache
94267 no work - consistent read gets
6708 non-idle wait count
9402 non-idle wait time
2 opened cursors cumulative
1 opened cursors current
1 parse count (hard)
2 parse count (total)
1 parse time cpu
67891 physical read IO requests
556163072 physical read bytes
67891 physical read total IO requests
556163072 physical read total bytes
67891 physical reads
67891 physical reads cache
61199 physical reads cache prefetch
4 pinned buffers inspected
1 recursive calls
1 recursive cpu usage
1 session cursor cache count
94270 session logical reads
3 shared hash latch upgrades - no wait
1 sorts (memory)
679 sorts (rows)
166800 table fetch by rowid
9402 user I/O wait time
16 user calls
有缓存，耗时很短
set autot on statistics
SELECT sum(ncostmny),sum(ninnum) FROM t_od_ic_flow_100 WHERE vbillcode >= 'CR2023080100000001' AND vbillcode <= 'CR2023083000000001'
已启用自动跟踪
仅显示统计信息。
SUM(NCOSTMNY) SUM(NINNUM)
------------- -----------
31575009.6
Statistics
-----------------------------------------------------------
228 CPU used by this session
234 CPU used when call started
197 DB time
4 Requests to/from client
1 enqueue releases
1 enqueue requests
6688 non-idle wait count
91 non-idle wait time
23 opened cursors cumulative
1 opened cursors current
67090 physical read total IO requests
1 pinned cursors current
194 recursive calls
1 recursive cpu usage
94343 session logical reads
90 user I/O wait time
4 user calls