IOT性能测试

最新推荐文章于 2024-04-30 15:33:25 发布

cuiguizhuo6372

最新推荐文章于 2024-04-30 15:33:25 发布

阅读量649

点赞数

文章标签：嵌入式

使用普通堆表、有索引、非并行

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      3.95      57.80            90312          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.95      57.82            90312          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 26  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=90312 pr=90166 pw=0 time=57802936 us)
      0   INDEX FAST FULL SCAN SMSG_LOGS_ARCH_PK_3 (cr=90312 pr=90166 pw=0 time=57802911 us)(object id 279076)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                       1414        0.59         56.16
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

使用普IOT表、非并行

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.13          2          6          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      5.29     202.13          128870          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.30     202.26          128876          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 26  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=128870 pr=128655 pw=0 time=202135759 us)
      0   CONCATENATION  (cr=128870 pr=128655 pw=0 time=202135733 us)
      0    PARTITION RANGE ITERATOR PARTITION: 2 3 (cr=128865 pr=128651 pw=0 time=201995085 us)
      0     INDEX FAST FULL SCAN SMSG_LOGS_ARCH_PK_2 PARTITION: 2 3 (cr=128865 pr=128651 pw=0 time=201995068 us)(object id 279053)
      0    PARTITION RANGE ITERATOR PARTITION: 2 3 (cr=5 pr=4 pw=0 time=140641 us)
      0     INDEX RANGE SCAN SMSG_LOGS_ARCH_PK_2 PARTITION: 2 3 (cr=5 pr=4 pw=0 time=140629 us)(object id 279053)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                       2023        1.33        199.51
  db file sequential read                         5        0.11          0.15
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

并行度4 alter session force parallel query parallel 4;

堆表、有索引、并行度4

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.05          0          0          0           0
Execute      1      0.00       0.40          0          4          0           0
Fetch        2      0.00      79.06          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01      79.52          0          4          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 26  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=0 pw=0 time=79471555 us)
      4   PX COORDINATOR  (cr=4 pr=0 pw=0 time=83150351 us)
      0    PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0     SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0      PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0       INDEX FAST FULL SCAN SMSG_LOGS_ARCH_PK_3 (cr=0 pr=0 pw=0 time=0 us)(object id 279076)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  os thread startup                               4        0.23          0.30
  PX Deq: Join ACK                                4        0.00          0.00
  PX Deq: Parse Reply                             4        0.02          0.02
  SQL*Net message to client                       2        0.00          0.00
  PX Deq: Execute Reply                          74        1.95         78.95
  PX qref latch                                   3        0.00          0.00
  PX Deq: Signal ACK                              6        0.09          0.10
  enq: PS - contention                            2        0.06          0.06
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

IOT、并行度 4

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          1          6          0           0
Execute      1      0.00       0.00          0         12          0           0
Fetch        2      0.00      84.23          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01      84.26          1         18          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 26  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=12 pr=0 pw=0 time=74830237 us)
      4   PX COORDINATOR  (cr=12 pr=0 pw=0 time=79050852 us)
      0    PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0     SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0      CONCATENATION  (cr=0 pr=0 pw=0 time=0 us)
      0       PX BLOCK ITERATOR PARTITION: 2 3 (cr=0 pr=0 pw=0 time=0 us)
      0        INDEX FAST FULL SCAN SMSG_LOGS_ARCH_PK_2 PARTITION: 2 3 (cr=0 pr=0 pw=0 time=0 us)(object id 279053)
      0       PX PARTITION RANGE ITERATOR PARTITION: 2 3 (cr=0 pr=0 pw=0 time=0 us)
      0        INDEX RANGE SCAN SMSG_LOGS_ARCH_PK_2 PARTITION: 2 3 (cr=0 pr=0 pw=0 time=0 us)(object id 279053)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PX Deq: Join ACK                                3        0.00          0.00
  PX Deq Credit: send blkd                        4        0.00          0.00
  PX Deq: Parse Reply                             4        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  PX Deq: Execute Reply                          91        1.95         83.87
  PX qref latch                                   3        0.00          0.00
  PX Deq: Signal ACK                              6        0.09          0.09
  enq: PS - contention                            1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

删除掉堆表上的索引，再观察

堆表、无索引、并行度 4

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.06          3          7          0           0
Fetch        2      0.00     123.09          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01     123.16          3          7          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 26  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=7 pr=3 pw=0 time=123161651 us)
      4   PX COORDINATOR  (cr=7 pr=3 pw=0 time=128144738 us)
      0    PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
      0     SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0      PX BLOCK ITERATOR PARTITION: 2 3 (cr=0 pr=0 pw=0 time=0 us)
      0       TABLE ACCESS FULL SMSG_LOGS_ARCH_ZXM_HEAP PARTITION: 2 3 (cr=0 pr=0 pw=0 time=0 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         3        0.02          0.05
  PX Deq: Join ACK                                4        0.00          0.00
  PX Deq Credit: send blkd                        6        0.00          0.00
  PX Deq: Parse Reply                             2        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  PX Deq: Execute Reply                          99        1.95        122.98
  PX Deq: Signal ACK                              5        0.09          0.09
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

堆表、没有索引、没有并行

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      7.28      82.40          206700          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.28      82.40          206700          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 26  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=206700 pr=206537 pw=0 time=82400559 us)
      0   PARTITION RANGE ITERATOR PARTITION: 2 3 (cr=206700 pr=206537 pw=0 time=82400537 us)
      0    TABLE ACCESS FULL SMSG_LOGS_ARCH_ZXM_HEAP PARTITION: 2 3 (cr=206700 pr=206537 pw=0 time=82400523 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                       3235        0.50         80.00
  latch free                                      2        0.00          0.00
  latch: shared pool                              1        0.00          0.00
  db file sequential read                         5        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00

使用IOT，压缩度 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.12          2          8          1           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      3.04      15.28            53986          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.05      15.41      53880      53994          1           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 26

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=53986 pr=53878 pw=0 time=15289977 us)
      0   CONCATENATION  (cr=53986 pr=53878 pw=0 time=15289958 us)
      0    PARTITION RANGE ITERATOR PARTITION: 2 3 (cr=53982 pr=53875 pw=0 time=15283223 us)
      0     INDEX FAST FULL SCAN SMSG_LOGS_ARCH_ZXM_IOT_CK1_PK PARTITION: 2 3 (cr=53982 pr=53875 pw=0 time=15283208 us)(object id 27
9216)
      0    PARTITION RANGE ITERATOR PARTITION: 2 3 (cr=4 pr=3 pw=0 time=6727 us)
      0     INDEX RANGE SCAN SMSG_LOGS_ARCH_ZXM_IOT_CK1_PK PARTITION: 2 3 (cr=4 pr=3 pw=0 time=6707 us)(object id 279216)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                        906        0.24         13.29
  db file sequential read                         6        0.01          0.01
  free buffer waits                               3        0.01          0.02
  SQL*Net message from client                     2        0.02          0.02

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/75321/viewspace-619981/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/75321/viewspace-619981/