Thank you all for the help, we found the solution I had already configured this :
db2set DB2_PARALLEL_IO=*
But didn't restart the instance (it's always a small parameter that do the difference)
Original Message:
Sent: Tue March 07, 2023 12:31 PM
From: Largou walid
Subject: Access plan optimisation
Indeed the runstats on the older database are really old that explains the number of rows, but doesn't explain the performance of the query (worst with the up to date runstats the new instance should have better perf). For the detailed explain you requested they are pretty much the same
Note : I created a support case with IBM
DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 11.05.8
FORMATTED ON DB: XXXX
SOURCE_NAME: SQLC2P31
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2023-03-07-16.14.27.128620
EXPLAIN_REQUESTER: XXXX
Database Context:
----------------
Parallelism: None
CPU Speed: 2.834065e-07
Comm Speed: 100
Buffer Pool size: 361429
Sort Heap size: 50000
Database Heap size: 5176
Lock List size: 617955
Maximum Lock List: 77
Average Applications: 1
Locks Available: 15226411
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 9
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select
count(*)
from
XSCHEMAX.XTABX
where
XCOLX=1
Optimized Statement:
-------------------
SELECT
Q3.$C0
FROM
(SELECT
COUNT(*)
FROM
(SELECT
$RID$
FROM
XSCHEMAX.XTABX AS Q1
WHERE
(Q1.XCOLX = +1.)
) AS Q2
) AS Q3
Access Plan:
-----------
Total Cost: 1.36208e+07
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
1.36208e+07
7.45711e+06
|
1.55296e+07
TBSCAN
( 3)
1.36197e+07
7.45711e+06
|
7.39507e+08
TABLE: XSCHEMAX
XTABX
Q1
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 1.36208e+07
Cumulative CPU Cost: 1.65256e+12
Cumulative I/O Cost: 7.45711e+06
Cumulative Re-Total Cost: 1.36208e+07
Cumulative Re-CPU Cost: 1.65256e+12
Cumulative Re-I/O Cost: 7.45711e+06
Cumulative First Row Cost: 1.36208e+07
Estimated Bufferpool Buffers: 7.45711e+06
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v11.5.8.0 : s2209201700
CPUCACHE: (Per-thread CPU cache size)
1048576
HEAPUSE : (Maximum Statement Heap Usage)
96 Pages
MAX CARD: (Maximum Cardinality)
1
PLANID : (Access plan identifier)
7771fa15fcccf014
PREPTIME: (Statement prepare time)
13 milliseconds
SEMEVID : (Semantic environment identifier)
0000000000000001
STMTHEAP: (Statement heap size)
8192
STMTID : (Normalized statement identifier)
6803016f64819e9a
TENANTID: (Compiled In Tenant ID)
0
TENANTNM: (Compiled In Tenant Name)
SYSTEM
Input Streams:
-------------
3) From Operator #2
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$C0
2) GRPBY : (Group By)
Cumulative Total Cost: 1.36208e+07
Cumulative CPU Cost: 1.65256e+12
Cumulative I/O Cost: 7.45711e+06
Cumulative Re-Total Cost: 1.36208e+07
Cumulative Re-CPU Cost: 1.65256e+12
Cumulative Re-I/O Cost: 7.45711e+06
Cumulative First Row Cost: 1.36208e+07
Estimated Bufferpool Buffers: 7.45711e+06
Arguments:
---------
AGGMODE : (Aggregation Mode)
COMPLETE
GROUPBYC: (Group By columns)
FALSE
GROUPBYN: (Number of Group By columns)
0
MAX CARD: (Maximum Cardinality)
1
ONEFETCH: (One Fetch flag)
FALSE
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 1.55296e+07
Number of columns: 0
Subquery predicate ID: Not Applicable
Output Streams:
--------------
3) To Operator #1
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$C0
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 1.36197e+07
Cumulative CPU Cost: 1.64856e+12
Cumulative I/O Cost: 7.45711e+06
Cumulative Re-Total Cost: 1.36197e+07
Cumulative Re-CPU Cost: 1.64856e+12
Cumulative Re-I/O Cost: 7.45711e+06
Cumulative First Row Cost: 13.7235
Estimated Bufferpool Buffers: 7.45711e+06
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Predicates:
----------
2) Sargable Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.021
Predicate Text:
--------------
(Q1.XCOLX = +1.)
Input Streams:
-------------
1) From Object XSCHEMAX.XTABX
Estimated number of rows: 7.39507e+08
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$+Q1.XCOLX
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 1.55296e+07
Number of columns: 0
Subquery predicate ID: Not Applicable
Objects Used in Access Plan:
---------------------------
Schema: XSCHX
Name: XTABX
Type: Table
Time of creation: 2018-11-30-16.14.26.236355
Last statistics update: 2023-03-06-14.14.40.843875
Number of columns: 36
Number of rows: 739506583
Width of rows: 32
Number of buffer pool pages: 7457109
Number of data partitions: 1
Distinct row values: No
Tablespace name: TBS_XXX_DATA_L1
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 192
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
------------------------------
Largou walid
Original Message:
Sent: Tue March 07, 2023 11:03 AM
From: Jan Nelken
Subject: Access plan optimisation
Use this method - to obtain additional information from explain facility:
C:\>db2 set current explain mode explain
DB20000I The SQL command completed successfully.
C:\>db2 select count(*) from org
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
C:\>db2 set current explain mode no
DB20000I The SQL command completed successfully.
C:\>db2exfmt -d sample -u <user> <pw> -g TIC -1 -o explain.out
DB2 Universal Database Version ********, 5622-044 (c) Copyright IBM Corp. 1991, 2022
Licensed Material - Program Property of IBM
IBM Db2 Explain Table Format Tool
Connecting to the Database as user db2admin.
Connect to Database Successful.
Using only explain schema found: DB2ADMIN.
Output is in explain.out.
Executing Connect Reset -- Connect Reset was Successful.
Please show the result of:
SELECT COUNT(*) FROM XXSCHEMAXX.XXTABXX
and
SELECT COUNT(*) FROM XXSCHEMAXX.XXTABXX WHERE XXCOLXX=1
PS.
Correct approach for your new (V11.5.8 database) is:
1. REORG TABLE table_name
2. REORG INDEXES ALL for table_name
(you may want to consider RECLAIM EXTENTS for 1. above and REBUILD CLEANUP ALL RECLAIM EXTENTS for 2. above)
3. RUNSTATS ON TABLE table_name WITH DISTRIBUTION AND DETAILED INDEXES ALL
4. Rebind all packages (may want to use db2rbind tool)
Retry your both SELECTS and post results.
------------------------------
Jan Nelken
Original Message:
Sent: Tue March 07, 2023 08:11 AM
From: Largou walid
Subject: Access plan optimisation
I use currently : db2expln -database XXXX -t -g -f perf_query.sql.bkp
------------------------------
Largou walid
Original Message:
Sent: Tue March 07, 2023 08:09 AM
From: Jan Nelken
Subject: Access plan optimisation
PS.
Use EXPLAIN facility properly to get additional information like in the following example:
DB2 Universal Database Version *****, 5622-044 (c) Copyright IBM Corp. 1991, 2022
Licensed Material - Program Property of IBM
IBM Db2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: *******
FORMATTED ON DB: SAMPLE
SOURCE_NAME: SQLC2Q31
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2023-03-07-14.00.54.834000
EXPLAIN_REQUESTER: DB2ADMIN
Database Context:
----------------
Parallelism: None
CPU Speed: 7.872404e-008
Comm Speed: 100
Buffer Pool size: 250
Sort Heap size: 256
Database Heap size: 600
Lock List size: 4096
Maximum Lock List: 22
Average Applications: 1
Locks Available: 28835
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select
*
from
org
Optimized Statement:
-------------------
SELECT
Q1.DEPTNUMB AS "DEPTNUMB",
Q1.DEPTNAME AS "DEPTNAME",
Q1.MANAGER AS "MANAGER",
Q1.DIVISION AS "DIVISION",
Q1.LOCATION AS "LOCATION"
FROM
DB2ADMIN.ORG AS Q1
Access Plan:
-----------
Total Cost: 6.82796
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
139
TBSCAN
( 2)
6.82796
1
|
139
TABLE: DB2ADMIN
ORG
Q1
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0020W Statistics have not been collected for
table "DB2ADMIN"."ORG". This may result in a
sub-optimal access plan and poor performance.
Statistics should be collected for this table.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 6.82796
Cumulative CPU Cost: 291673
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.0189788
Cumulative Re-CPU Cost: 241080
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.80912
Estimated Bufferpool Buffers: 1
Arguments:
---------
BLDLEVEL: (Build level)
DB2 ****************************
CPUCACHE: (Per-thread CPU cache size)
1048576
HEAPUSE : (Maximum Statement Heap Usage)
80 Pages
PLANID : (Access plan identifier)
bfe49975b71010f4
PREPTIME: (Statement prepare time)
48 milliseconds
SEMEVID : (Semantic environment identifier)
8592dd1fccfabb08
STMTHEAP: (Statement heap size)
8192
STMTID : (Normalized statement identifier)
f5d3ae7cc846116f
TENANTID: (Compiled In Tenant ID)
0
TENANTNM: (Compiled In Tenant Name)
SYSTEM
Input Streams:
-------------
2) From Operator #2
Estimated number of rows: 139
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LOCATION+Q2.DIVISION+Q2.MANAGER
+Q2.DEPTNAME+Q2.DEPTNUMB
2) TBSCAN: (Table Scan)
Cumulative Total Cost: 6.82796
Cumulative CPU Cost: 291673
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.0189788
Cumulative Re-CPU Cost: 241080
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 6.80912
Estimated Bufferpool Buffers: 1
Arguments:
---------
CUR_COMM: (Currently Committed)
TRUE
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SKIP_INS: (Skip Inserted Rows)
TRUE
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
1) From Object DB2ADMIN.ORG
Estimated number of rows: 139
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$+Q1.LOCATION+Q1.DIVISION+Q1.MANAGER
+Q1.DEPTNAME+Q1.DEPTNUMB
Output Streams:
--------------
2) To Operator #1
Estimated number of rows: 139
Number of columns: 5
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LOCATION+Q2.DIVISION+Q2.MANAGER
+Q2.DEPTNAME+Q2.DEPTNUMB
Objects Used in Access Plan:
---------------------------
Schema: DB2ADMIN
Name: ORG
Type: Table
Time of creation: 2023-02-27-17.01.02.596002
Last statistics update:
Number of columns: 5
Number of rows: 139
Width of rows: 46
Number of buffer pool pages: 1
Number of data partitions: 1
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 6.725000
Tablespace transfer rate: 0.080000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: -1
Percentage Rows Compressed: -1
Average Compressed Row Size: -1
------------------------------
Jan Nelken
Original Message:
Sent: Tue March 07, 2023 07:22 AM
From: Largou walid
Subject: Access plan optimisation
Old Production :
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"XXSCHEMAXX"
Statement:
select count(*)
from XXSCHEMAXX.XXTABXX
where XXCOLXX=1
Section Code Page = 819
Estimated Cost = 2871201.250000
Estimated Cardinality = 1.000000
Access Table Name = XXSCHEMAXX.XXTABXX ID = 8,39
| #Columns = 0
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 1
| | Predicate Aggregation
| | | Column Function(s)
Aggregation Completion
| Column Function(s)
Return Data to Application
| #Columns = 1
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
1
RETURN
( 1)
2.8712e+06
|
1
GRPBY
( 2)
2.8712e+06
|
1.0995e+07
TBSCAN
( 3)
2.8704e+06
|
1.55958e+08
Table:
XXSCHEMAXX
XXTABXX
New production :
DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
DB2 Universal Database Version 11.5, 5622-044 (c) Copyright IBM Corp. 1991, 2019
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
"XXSCHEMAXX"
Statement:
select count(*)
from XXSCHEMAXX.XXTABXX
where XXCOLXX=1
Section Code Page = 819
Estimated Cost = 13620829.000000
Estimated Cardinality = 1.000000
Access Table Name = XXSCHEMAXX.XXTABXX ID = 8,39
| #Columns = 0
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 1
| | Predicate Aggregation
| | | Column Function(s)
Aggregation Completion
| Column Function(s)
Return Data to Application
| #Columns = 1
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
1
RETURN
( 1)
1.36208e+07
|
1
GRPBY
( 2)
1.36208e+07
|
1.55296e+07
TBSCAN
( 3)
1.36197e+07
|
7.39507e+08
Table:
XXSCHEMAXX
XXTABXX
------------------------------
Largou walid
Original Message:
Sent: Tue March 07, 2023 06:03 AM
From: Jan Nelken
Subject: Access plan optimisation
Hi!
You stated: A select count with a where clause take 13min as opposed to 3min.
Can you post complete explain output of this SELECT COUNT .. from V10.5.x and 11.5.8?
------------------------------
Jan Nelken
Original Message:
Sent: Mon March 06, 2023 04:53 PM
From: Largou walid
Subject: Access plan optimisation
Hi,
We are running a production DB2 on v10.5 on power system for our warehouse, we initiated a migration to the latest version 11.5.8 on more performing machine.
We did the migration using a backup -> restore, doing some benchmarks we noticed that simple request are slower on the newer instances. A select count with a where clause take 13min as opposed to 3min.
We suspected the stats to be the cause, so we did a runstats with key columns first then with sampled index, but still the same issue.
Tried the explain and we saw that the cost on the new server is 13 more than the old production. We have the same db2 configuration for instances, we also tried to follow the number of read rows per seconds (using db2top) and the new platform is 10% more performant.
What do you suggest can be the issue ? Is it related to the backup/restore ? Is it related to the version ? Any known issues/limitations ? How can we improve the access plan ?
Note that the table contains nearly 800million rows.
------------------------------
Largou walid
------------------------------