As of this writing ( LUW V10.1 or older), there is a class of query that does not play nice with DB2 optimizer, which takes the form of a disjunctive predicate such as below:
SELECT 1 FROM
T1 JOIN T2
ON ( C1 = D1 OR C2 = D2)
Currently, the optimizer can only come up with a plan that uses a NLJOIN, *regardless of the data volume*. Imagine a scenario where you have a couple of million rows in T1 and T2 and there is no suitable covering index, you will have plenty of free time in your hand to do other things such as taking a shower and going for a swim, because the query will take a very long time to complete.
Is there a workaround? Fortunately yes. Just take a look at how Oracle handle this sort of query.
I shall demo an example on this in future post.
Saturday, May 25, 2013
The Evil of Prefetch
For many implementations, much emphasis has been placed on some kind of readahead to boost performance. But if it is used blindly, it can have adverse effect.
Cache maintenance is not free, and is definitely not unlimited. If the DB is allowed to perform prefetch too aggressively, you might end up flooding the cache with useless data pages which will be flushed out anyway.
Remember that the key to good performance is to let the system do as little work as possible.
Cache maintenance is not free, and is definitely not unlimited. If the DB is allowed to perform prefetch too aggressively, you might end up flooding the cache with useless data pages which will be flushed out anyway.
Remember that the key to good performance is to let the system do as little work as possible.
Extent and RAID Stripes Alignment
As usual, the interplay of RAID strip/RAID stripes/filesystem block size/extent size/tablespace block or page size can be a complex matter. There is a tiny titbit on the RAID/extent relationship in the official documentation that largely goes unnoticed. I will highlight the interesting bit here:
Quote:"Do not set the DB2_USE_PAGE_CONTAINER_TAG registry variable. As described earlier, you should create a table space with an extent size that is equal to, or a multiple of, the RAID stripe size. However, when you set DB2_USE_PAGE_CONTAINER_TAG to ON, a one-page container tag is used, and the extents do not line up with the RAID stripes. "
Link to the documentation:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0052067.html
Quote:"Do not set the DB2_USE_PAGE_CONTAINER_TAG registry variable. As described earlier, you should create a table space with an extent size that is equal to, or a multiple of, the RAID stripe size. However, when you set DB2_USE_PAGE_CONTAINER_TAG to ON, a one-page container tag is used, and the extents do not line up with the RAID stripes. "
Link to the documentation:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0052067.html
File System Caching Info
Other than using the OS mount option, file system caching can be disabled/enabled during tablespace creation time. As important as this option is, it is not being shown in the standard 'list tablespaces' output.
To get this info, you will have to resort to this:
GET SNAPSHOT FOR TABLESPACES ON <db name>
A sample output is shown below. Note the line with 'File system caching' ( highlighted in italic bold font). For this example, it is 'No' for USERSPACE1:
Tablespace name = USERSPACE1
Tablespace ID = 2
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Large table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 32
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = No
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 32
For tablespaces created in v9.7 and later, NO FILE SYSTEM CACHING is the default. Before deciding whether caching is good for the system or not, make sure you understand the characteristics of the DB ( the deciding factor would involve the presence of inlined/non-inlined LOB).
To get this info, you will have to resort to this:
GET SNAPSHOT FOR TABLESPACES ON <db name>
A sample output is shown below. Note the line with 'File system caching' ( highlighted in italic bold font). For this example, it is 'No' for USERSPACE1:
Tablespace name = USERSPACE1
Tablespace ID = 2
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Large table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 32
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = No
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Tablespace Prefetch size (pages) = 32
For tablespaces created in v9.7 and later, NO FILE SYSTEM CACHING is the default. Before deciding whether caching is good for the system or not, make sure you understand the characteristics of the DB ( the deciding factor would involve the presence of inlined/non-inlined LOB).
Wednesday, May 22, 2013
A 'Better' Execution Plan Graph
One crucial number that is missing from the standard plan generated by 'EXPLAIN PLAN' is the actual rows flowing in each node of the graph. Without it, it is close to impossible to judge the efficiency of the plan. How do you decide if the 'optimizer has underestimated/overestimated the row source' at any point of the execution?
A pretty neat feature which first appeared in v9.7 and unfortunately still largely unknown in v10.1, is the 'section actuals', which can be set at the database level as SECTION_ACTUALS, or at the individual application using WLM_SET_CONN_ENV.
You will also need to capture the stats using activity event monitor, eg: ALTER WORKLOAD WL1 COLLECT ACTIVITY DATA WITH DETAILS,SECTION, etc etc ....
But assuming that all you want to do is to do simple test on certain query, there is an easier way. Hidden somewhere in the sea of binaries is a utility called 'db2caem'. It's really easy to use.
Let's illustrate this using a simple example. Assuming that you want to test your query 'select count(*) from t1', all you need to do is issue this:
db2caem -d db2test -st "select count(*) from t1"
____________________________________________________________________
_____ D B 2 C A E M _____
DB2 Capture Activity Event Monitor data tool
I B M
The DB2CAEM Tool is a utility for capturing the activity event
monitor data with details, section and values, as well as actuals.
which could be used when analyze the SQL statement performance.
____________________________________________________________________
________________________________________________________________________________
DBT7041I "db2caem": The db2caem utility is connecting to the following database: "db2test".
DBT7038I "db2caem": The db2caem utility successfully connected to the following database: "db2test".
DBT7042I "db2caem": The SQL statement "select count(*) from t1" is being issued.
DBT7043I "db2caem": The db2caem utility is disconnecting from the following database: "db2test".
DBT7039I "db2caem": The db2caem utility successfully disconnected from the following database: "db2test".
DBT7000I db2caem completed. Output path: "C:\\DB2CAEM_2013-05-22-16.58.09.762000".
That's pretty much to it. The execution plan you want is in the directory ( as shown on the last line) "C:\\DB2CAEM_2013-05-22-16.58.09.762000"
Look for the file named 'db2caem.exfmt' inside, and you can see something like below:
-------------------------------
< some stuff removed for clarity>
Original Statement:
------------------
select
count(*)
from
t1
Optimized Statement:
-------------------
SELECT
Q3.$C0
FROM
(SELECT
COUNT(*)
FROM
(SELECT
$RID$
FROM
XXXXXX.T1 AS Q1
) AS Q2
) AS Q3
Explain level: Explain from section
Access Plan:
-----------
Total Cost: 78.1959
Query Degree: 1
Rows
Rows Actual
RETURN
( 1)
Cost
I/O
|
1
1
GRPBY
( 2)
78.1957
NA
|
10001
10001
IXSCAN
( 3)
77.8301
NA
|
-1
NA
INDEX: XXXXXXX
T1X2
Q1
<more stuff removed> ...
Note that now there are *two* numbers, one is the standard estimated rows, and the one immediately below it is the *actual* rows.
Easy eh? :)
While this is all good, unfortunately the interpretation of the numbers can be very confusing for certain execution plan. I shall leave that for another post ...
A pretty neat feature which first appeared in v9.7 and unfortunately still largely unknown in v10.1, is the 'section actuals', which can be set at the database level as SECTION_ACTUALS, or at the individual application using WLM_SET_CONN_ENV.
You will also need to capture the stats using activity event monitor, eg: ALTER WORKLOAD WL1 COLLECT ACTIVITY DATA WITH DETAILS,SECTION, etc etc ....
But assuming that all you want to do is to do simple test on certain query, there is an easier way. Hidden somewhere in the sea of binaries is a utility called 'db2caem'. It's really easy to use.
Let's illustrate this using a simple example. Assuming that you want to test your query 'select count(*) from t1', all you need to do is issue this:
db2caem -d db2test -st "select count(*) from t1"
____________________________________________________________________
_____ D B 2 C A E M _____
DB2 Capture Activity Event Monitor data tool
I B M
The DB2CAEM Tool is a utility for capturing the activity event
monitor data with details, section and values, as well as actuals.
which could be used when analyze the SQL statement performance.
____________________________________________________________________
________________________________________________________________________________
DBT7041I "db2caem": The db2caem utility is connecting to the following database: "db2test".
DBT7038I "db2caem": The db2caem utility successfully connected to the following database: "db2test".
DBT7042I "db2caem": The SQL statement "select count(*) from t1" is being issued.
DBT7043I "db2caem": The db2caem utility is disconnecting from the following database: "db2test".
DBT7039I "db2caem": The db2caem utility successfully disconnected from the following database: "db2test".
DBT7000I db2caem completed. Output path: "C:\\DB2CAEM_2013-05-22-16.58.09.762000".
That's pretty much to it. The execution plan you want is in the directory ( as shown on the last line) "C:\\DB2CAEM_2013-05-22-16.58.09.762000"
Look for the file named 'db2caem.exfmt' inside, and you can see something like below:
-------------------------------
< some stuff removed for clarity>
Original Statement:
------------------
select
count(*)
from
t1
Optimized Statement:
-------------------
SELECT
Q3.$C0
FROM
(SELECT
COUNT(*)
FROM
(SELECT
$RID$
FROM
XXXXXX.T1 AS Q1
) AS Q2
) AS Q3
Explain level: Explain from section
Access Plan:
-----------
Total Cost: 78.1959
Query Degree: 1
Rows
Rows Actual
RETURN
( 1)
Cost
I/O
|
1
1
GRPBY
( 2)
78.1957
NA
|
10001
10001
IXSCAN
( 3)
77.8301
NA
|
-1
NA
INDEX: XXXXXXX
T1X2
Q1
<more stuff removed> ...
Note that now there are *two* numbers, one is the standard estimated rows, and the one immediately below it is the *actual* rows.
Easy eh? :)
While this is all good, unfortunately the interpretation of the numbers can be very confusing for certain execution plan. I shall leave that for another post ...
Subscribe to:
Posts (Atom)