I am facing the issue with database performance.
Earlier, my database was running on XSeries Platform, Solaris OS.
Then I migrated whole data and object to SPARC Platform.
Both Servers have a bit different specs.
For XSeries, data stores on SAS hdd and SPARC, data stores on SSD.
Then I compare database performance, I see the huge different number of CARDINALITY and COST, after that I am trying to optimize it by using sql tuning but the result seem not better.
XSeries Flatform EXPLAIN PLANS
1- Original
-----------
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 Platform EXPLAIN PLANS
1- Original
-----------
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 |
---------------------------------------------------------------------------------------------------------------------
So Please guide me how to make change this to be better.
Thanks.
The estimated row counts are very different between the two databases. Which suggests the stats are different.
An easy way to check this is with the info+ command in SQL Developer or SQLcl. This will give you column level stats for the tables:
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
If these are substantially different, check how the stats gathering on the two databases is set up. If necessary, you can copy the stats from one database to another:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/transporting-optimizer-statistics.html#GUID-7F8EE1CC-A173-4B87-AA2E-CD22198EF4F8 Other things to check:
- Are there any SQL profiles or baselines on the original database?
- Are all the indexes from the original DB loaded into the new one?
And its always worth checking: are these really the plans you're getting? Explain plan is just a prediction. The execution plan you get when you run the query may be different. For details on this, read:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan