苏州做网站建设公司,广州骏域网站建设专家 V,自定义内容网站,海建网站附录说明
附录是对测试过程中涉及到的一些操作进行记录和解析。
oracle清除缓存
alter system flush shared_pool;
将使library cache和data dictionary cache以前保存的sql执行计划全部清空#xff0c;但不会清空共享sql区或者共享pl/sql区里面缓存的最近被执行的条目。刷…附录说明
附录是对测试过程中涉及到的一些操作进行记录和解析。
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/LanguageManualIndexing 客户端抽样
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
