数仓技术hive与oracle对比（五）

附录说明

附录是对测试过程中涉及到的一些操作进行记录和解析。

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