Oracle 数据库迁移后的数据库性能不同计划

我面临着数据库性能的问题。

早些时候，我的数据库运行在XSeries平台Solaris OS上。
然后我将整个数据和对象迁移到SPARC平台。

两台服务器的规格略有不同。
对于XSeries，数据存储在SAS hdd和SPARC上，数据存储在SSD上。

然后我比较数据库性能，我看到巨大的基数和成本的不同数量，之后我试图通过使用sql调优来优化它，但结果似乎不是更好。


XSeries Flatform解释计划
1-原件
-----------

Plan hash value: 3251059040

 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                     |     2 |   190 |     8  (38)| 00:00:01 |
|   1 |  SORT ORDER BY                         |                     |     2 |   190 |     8  (38)| 00:00:01 |
|   2 |   VIEW                                 |                     |     2 |   190 |     7  (29)| 00:00:01 |
|   3 |    UNION-ALL                           |                     |       |       |            |          |
|*  4 |     FILTER                             |                     |       |       |            |          |
|   5 |      HASH GROUP BY                     |                     |     1 |   100 |     3  (34)| 00:00:01 |
|   6 |       NESTED LOOPS                     |                     |       |       |            |          |
|   7 |        NESTED LOOPS                    |                     |     1 |   100 |     2   (0)| 00:00:01 |
|   8 |         VIEW                           | ACVW_ALL_AC_ENTRIES |     2 |   100 |     2   (0)| 00:00:01 |
|   9 |          UNION-ALL                     |                     |       |       |            |          |
|* 10 |           TABLE ACCESS BY INDEX ROWID  | ACTB_DAILY_LOG      |     1 |    63 |     1   (0)| 00:00:01 |
|* 11 |            INDEX SKIP SCAN             | IX08_ACTB_DAILY_LOG |     1 |       |     1   (0)| 00:00:01 |
|* 12 |           TABLE ACCESS BY INDEX ROWID  | ACTB_HISTORY        |     1 |    60 |     1   (0)| 00:00:01 |
|* 13 |            INDEX SKIP SCAN             | IX01_ACTB_HISTORY   |     1 |       |     1   (0)| 00:00:01 |
|* 14 |         INDEX UNIQUE SCAN              | PK01_GLTM_GLMASTER  |     1 |       |     0   (0)| 00:00:01 |
|* 15 |        TABLE ACCESS BY INDEX ROWID     | GLTM_GLMASTER       |     1 |    50 |     0   (0)| 00:00:01 |
|  16 |     NESTED LOOPS                       |                     |       |       |            |          |
|  17 |      NESTED LOOPS                      |                     |     1 |    89 |     4  (25)| 00:00:01 |
|  18 |       VIEW                             |                     |     1 |    41 |     3  (34)| 00:00:01 |
|* 19 |        FILTER                          |                     |       |       |            |          |
|  20 |         HASH GROUP BY                  |                     |     1 |    66 |     3  (34)| 00:00:01 |
|  21 |          NESTED LOOPS                  |                     |     1 |    66 |     2   (0)| 00:00:01 |
|  22 |           TABLE ACCESS FULL            | STTM_CUST_ACCOUNT   |     1 |    24 |     2   (0)| 00:00:01 |
|  23 |           VIEW                         | ACVW_ALL_AC_ENTRIES |     2 |    84 |     0   (0)| 00:00:01 |
|  24 |            UNION ALL PUSHED PREDICATE  |                     |       |       |            |          |
|* 25 |             TABLE ACCESS BY INDEX ROWID| ACTB_DAILY_LOG      |     1 |    66 |     0   (0)| 00:00:01 |
|* 26 |              INDEX RANGE SCAN          | IX07_ACTB_DAILY_LOG |     1 |       |     0   (0)| 00:00:01 |
|* 27 |             TABLE ACCESS BY INDEX ROWID| ACTB_HISTORY        |     1 |    63 |     0   (0)| 00:00:01 |
|* 28 |              INDEX RANGE SCAN          | IX05_ACTB_HISTORY   |     1 |       |     0   (0)| 00:00:01 |
|* 29 |       INDEX UNIQUE SCAN                | PK01_GLTM_GLMASTER  |     1 |       |     0   (0)| 00:00:01 |
|  30 |      TABLE ACCESS BY INDEX ROWID       | GLTM_GLMASTER       |     1 |    48 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
复制

SPARC平台解释计划
1-原件
-----------

Plan hash value: 3983466711
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     | 41007 |  3804K|       |   562K (68)| 00:09:30 |
|   1 |  SORT ORDER BY                        |                     | 41007 |  3804K|  4568K|   562K (68)| 00:09:30 |
|   2 |   VIEW                                |                     | 41007 |  3804K|       |   561K (68)| 00:09:29 |
|   3 |    UNION-ALL                          |                     |       |       |       |            |          |
|*  4 |     FILTER                            |                     |       |       |       |            |          |
|   5 |      HASH GROUP BY                    |                     | 40296 |  3935K|  1604M|   338K (66)| 00:05:43 |
|*  6 |       HASH JOIN                       |                     |    13M|  1331M|       |   258K (77)| 00:04:22 |
|*  7 |        TABLE ACCESS FULL              | GLTM_GLMASTER       |   373 | 18650 |       |     6  (50)| 00:00:01 |
|   8 |        VIEW                           | ACVW_ALL_AC_ENTRIES |    41M|  1977M|       |   252K (76)| 00:04:16 |
|   9 |         UNION-ALL                     |                     |       |       |       |            |          |
|* 10 |          TABLE ACCESS BY INDEX ROWID  | ACTB_DAILY_LOG      |     1 |    43 |       |     4   (0)| 00:00:01 |
|* 11 |           INDEX SKIP SCAN             | IX05_ACTB_DAILY_LOG |     1 |       |       |     3   (0)| 00:00:01 |
|* 12 |          TABLE ACCESS FULL            | ACTB_HISTORY        |    41M|  1542M|       |   252K (76)| 00:04:16 |
|* 13 |     HASH JOIN                         |                     |   711 | 63279 |       |   223K (73)| 00:03:47 |
|  14 |      VIEW                             |                     |   711 | 29151 |       |   223K (73)| 00:03:47 |
|* 15 |       FILTER                          |                     |       |       |       |            |          |
|  16 |        HASH GROUP BY                  |                     |   711 | 62568 |       |   223K (73)| 00:03:47 |
|* 17 |         HASH JOIN                     |                     |  3443K|   288M|       |   218K (72)| 00:03:42 |
|  18 |          TABLE ACCESS FULL            | STTM_CUST_ACCOUNT   |   644 | 23184 |       |     4  (25)| 00:00:01 |
|  19 |          VIEW                         | ACVW_ALL_AC_ENTRIES |  5924K|   293M|       |   217K (72)| 00:03:41 |
|  20 |           UNION-ALL                   |                     |       |       |       |            |          |
|* 21 |            TABLE ACCESS BY INDEX ROWID| ACTB_DAILY_LOG      |     1 |    45 |       |     4   (0)| 00:00:01 |
|* 22 |             INDEX SKIP SCAN           | IX05_ACTB_DAILY_LOG |     1 |       |       |     3   (0)| 00:00:01 |
|* 23 |            TABLE ACCESS FULL          | ACTB_HISTORY        |  5924K|   231M|       |   217K (72)| 00:03:41 |
|  24 |      TABLE ACCESS FULL                | GLTM_GLMASTER       |  2612 |   122K|       |     4  (25)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
复制

所以请指导我如何让改变这一点变得更好。
谢谢。

估计的行计数在两个数据库之间非常不同。这表明统计数据是不同的。

一种简单的检查方法是使用SQL Developer或SQLcl中的info命令。这将为您提供表的列级别统计信息:

create table t ( x primary key, y not null, z )
as 
  select level x, chr(mod(level, 26)+65), sysdate+1/24 
  from   dual
  connect by level <= 100;
  
create index i on t (y);

exec dbms_stats.gather_table_stats(user, 't');

info+ t

Columns 
NAME         DATA TYPE          NULL  DEFAULT    LOW_VALUE             HIGH_VALUE            NUM_DISTINCT   HISTOGRAM  
*X           NUMBER             No                   1                     100                   100            NONE       
 Y           VARCHAR2(1 BYTE)   No                   A                     Z                     26             NONE       
 Z           DATE               Yes                  2018.01.30.04.02.44   2018.01.30.04.02.44   1              NONE       

Indexes
INDEX_NAME           UNIQUENESS   STATUS   FUNCIDX_STATUS   COLUMNS   
CHRIS.I              NONUNIQUE    VALID                     Y         
CHRIS.SYS_C0014260   UNIQUE       VALID                     X 
复制

如果这些差异很大，请检查如何设置两个数据库上的统计信息。如有必要，您可以将统计信息从一个数据库复制到另一个数据库:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/transporting-optimizer-statistics.html#GUID-7F8EE1CC-A173-4B87-AA2E-CD22198EF4F8

其他要检查的事项:

-原始数据库上是否有任何SQL配置文件或基线？
-是否将原始数据库中的所有索引加载到新数据库中？

它总是值得检查: 这些真的是你得到的计划吗？解释计划只是一个预测。运行查询时获得的执行计划可能不同。有关此的详细信息，请阅读:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan