4.3 Check SQL Trace and access plan
Run transaction RSRT, enter Query Name “ZSD_C01/BENCHMARK_QUERY_2”, click on “Execute + Debug”,
check flags ““Display SQL/BIA Query” (under “Data Manager” section), hit Continue button.
Firstly you will see the SQL statement display window, review the SQL statement.
From the above SQL statement, you can tell it is selecting data from F fact table joined with dimension tables for dimension T, U, 13 (sales org) and P where CALMONTH is between 200001 and 200012 and SALESORG is equal to 83, returning summarized col-umn INCORDVAL (total incoming order value), INVCD_QTY (total invoiced quantity) and INVCD_VAL (total invoiced value) that are grouped by sales division and calendar year/month.
On this window, click on SQL Query Visualizer button , you will get the Query
Visualization screen. Further click on the “Schema View Block Query Block 0” bar, you will see the graphical view of the query structure. Click on one of the tables, e.g.
/BIC/DZSD_C01T table, you will see the details about this table as well as the table join with the fact table.
Questions:
1. what are the “local conditions” applied to this table? What selectivity can you get when applying these conditions on this table?
2. how many number of rows in this table?
3. what’s the estimated number of output rows?
4. what condition is used to join this table with the fact table?
Open another SAP GUI window and activate ST05 SQL trace;
then click on “back” button on the tool bar of SQL screen to complete the query execu-tion;
deactivate ST05 trace.
Display ST05 SQL Trace, locate the SQL-Statements that correspond to the query in the RSRT SQL window (obj. name = /BIC/FZSD_C01).
Explain this SQL statements from within the ST05 trace (move the cursor on the state-ment text first, then click on Explain button)
On the Display Execution Plan for SQL Statement window, you get the access plan.
Click on the Tree Info button, you will see the number of rows returned, total cost and I/O cost of each operation.
Questions:
1. What is the name of the fact table?
2. Which dimension table is joined firstly to the fact table, what type of join?
3. What method is used to access the fact table? If it is an index access, what is the index being used?
4. What is the total cost of this access plan?
5. How many rows will be returned by this query according to the access plan?
4.4 check SQL Cache
Another way to check the query access plan, without using ST05 trace, is to check DB2 SQL cache (Package cache) directly.
In another SAP GUI window, go to DBACockpit -> Performance -> SQL Cache.
On the Selection Criteria window, remove 100 from the Executions field, and enter the
“SELECT*FZSD_C01*” in the SQL Text field.
The following statement execution result is returned:
Write down the Avg. Execution time, Buffer Quality, Rows Read, SQL Sorts and the total Estimated Costs to the Work Sheet on the final page of this hand book.
*Note* For more accurate comparison, especially to demonstrate the disk I/O cost sav-ing by ussav-ing MDC feature in the next exercise, you need to run the query in a “cold”
buffer pool, which means the table data should not have been cached in the buffer pool.
This will require comparing the result on the first query execution on a newly started system. Therefore you might want to stop and restart your SAP system before running the query.
Double click on the statement, or click on Explain button, you will get the access plan of this statement.
In the case of query performance problem, and you want to open an SAP OSS message, you need to collect related diagnostics data for this query.
On the Display Execution Plan screen, click on Collect button, which will allow you to download query related data, such as database configuration parameters, table and index definitions and statistics, detailed access plan, etc. into respective files.
4.6 use
For deeper analysis, you can use db2support tool to collect much more detailed data, which will allow IBM DB2 support to mimic your runtime environment and reproduce the problem on their test system.
Steps:
1. Save SQL statement in a text file. From the Execution Plan screen, you can click on Edit button, which will open a Edit SQL Statement window, you could use one of the following ways to create a statement text file on the database :
a. select the entire statement text, hit CTRL+C to copy the text to the clipboard, then on the DB2 command window (in user db2n4s) on the database server, use vi editor to create a file query2.sql, paste the statement text to vi, add an “;” at the end of the statement, and save this file;
b. you can save the file locally by using the Save as local file button.
Then move the file from local to the database server in a directory that can be accessed by user db2n4s (e.g. /db2/N4S/db2dump). You also need to edit the file by adding “;” at the end.
2. On the database server, in the db2n4s command window, enter the following command:
n4shost:db2n4s 176> db2support /db2/N4S/db2dump –d n4s sf query2.sql -curschema sapn4s
Once the db2support.zip file is generated, download it to the local server, and check what data has been collected.
Or use the following command to unzip the file on the server:
n4shost:db2n4s 193> unzip db2support.zip
Unit 5 – MDC for SAP BW
5.1
Change/defin e MDC set-tings for an existing In-foCube
Run transaction RSA1: Modelling Æ InfoProvider Æ Select “benchmark” Æ Right-click InfoCube “Benchmarkcube” Æ “Additional Functions” Æ Reclustering
Click button “Initialize”
5.2 Confirm that a recent backup was done before activating the changes
The system is asking if a database backup was done. You must answer the pop-up with
“Yes.” Otherwise, you cannot activate the changes. Reclustering entails copying data from the original table into a shadow MDC table. Therefore a valid backup before the changes is required. In this exercise, we will skip the database backup.
5.3 Select clustering type
Select “Multi-Dimensional Clustering” Æ Confirm selection
5.4 Select the correct num-ber of MDC columns
The default setting for the number of MDC columns is 3. If you select more than three columns (excluding “Package Dimension”), the system displays an error pop-up.
Choosing too many columns will probably result in many half empty MDC cells allo-cated.
By default, the time dimension SID_0CALMONTH is automatically selected as the first MDC dimension. Check “Package Dimension on F Fact Table”. A dimension on Package number will help to improve BW compression function.
Also enter 2 on line “ZSD_C013 Sales Area” to define it as the second MDC dimen-sion.
Check “Execute Space Check”.
Click “Continue” button, and receive the following space check result:
The Space Check result is displayed in red colour, which means over 50% (Number of free pages 159180 / Number of pages that can be allocated 318360) of table space will be wasted. Further looking at the result, you see that the “Number of different values in MDC dimension KEY_ZSD_C013” (Sales Area) is 64203, which is too great, com-pared to the other two MDC dimension columns on Package Dimension (12) and CALMONTH (132). Therefore “Sales Area” is not a good candidate for MDC dimen-sion, and we need to remove the second MDC dimension on “Sales Area”.
Click on “Cancel” button, go back to previous MDC definition screen, remove “2”
from the line “Sales Area”, and leave Package Dimension and Calendar Month as MDC dimension columns. Click “Continue” button again, and display the new space check result:
This time the space check result is displayed in green colour, because the potential wasted space is less than 30% (Number of free pages 55 / Number of pages that can be allocated 1848 = 2.9%)
Click “Continue” button.
5.6 Confirm request
New request created confirmation. Click on “Continue” button.
5.7 Schedule re-cluster job in batch
Start re-cluster job in background. Select “Immediate” options for the background job, then click “Save” icon.
Going back to Reclustering of InfoProviders screen, click on “Monitor” button. On the Monitor Requests screen, click on Refresh button until you see the status becomes green.
You can also use SM37 to check the background job status.
5.8 Check storage pa-rameter
Run transaction SE14. Enter table name: /BIC/FZSD_C01 Æ Edit To check the table structure use button “Storage Parameter.”
The clause “Organize by Dimensions” is used to define a MDC dimension. In this case, two MDC dimensions are defined: “KEY_ZSD_C01P” and “SID_0CALMONTH”.
5.9 Fact Ta-ble Analysis
Run transaction DBACockpit, go to Space Æ Single Table Analysis, enter table name
“/BIC/FZSD_C01” and hit Enter.