## 3.14.2.1.1  Example: Reading the SQL Plan Cache
These examples aim to show you how to gain useful insights by analyzing the SQL plan cache.
## Execution in a Single-Host System
This example aims to show you how to interpret information about execution time. The following table is sorted by TOTAL_EXECUTION_TIME.
| USER_ NAME   | STATEMENT_ STRING                                                          | TOTAL_ EXECUTION_ TIME   | AVG_ EXECUTION_ TIME   | EXECUTION_ COUNT   |
|--------------|----------------------------------------------------------------------------|--------------------------|------------------------|--------------------|
| SYSTEM       | SELECT "REQUEST" , "DATAPAKID" , "PARTNO" , "RECORD" , "CALDAY" , ...      | 774,367,833              | 181,266                | 4,272              |
| SYSTEM       | SELECT * FROM "/BIC/ AZDSTGODO40" WHERE "SID" = ?                          | 726,672,877              | 60,556,073             | 12                 |
| SYSTEM       | SELECT "JOBNAME" , "JOBCOUNT" , "JOBGROUP" , "INTREPORT" , "STEPCOUNT" ... | 473,620,452              | 22,987                 | 20,604             |
| -            | <Further 6832 records>                                                     | -                        | -                      | -                  |
You could read these top 3 results as follows:
- Statement 1 takes the longest time overall but it is also executed frequently.
- Statement 2 is not executed very frequently but has the second highest total execution time. Why is this simple SQL taking so long? Does it have problems processing?
- The execution times for statement 3 are fine for one-off execution, but it runs too frequently, over 20,000 times. Why? Is there a problem in application code?
Sorting by AVG_EXECUTION_TIME or EXECUTION_COUNT provides a different angle on your analysis.
The following example aims to show you how to interpret information about locking situations. The information in columns TOTAL_LOCK_WAIT_COUNT and TOTAL_LOCK_WAIT_DURATION lets us know which statement is waiting for others and how much time it takes.
| USER   | STATEMENT_STRING                                                                 | TOTAL_LOCK_ WAIT_COUNT   | TOTAL_LOCK_ WAIT_DURATION   | TOTAL_ EXECU- TION_TIME   |
|--------|----------------------------------------------------------------------------------|--------------------------|-----------------------------|---------------------------|
| SYSTEM | SELECT "FROMNUMBER","TONUMBER", "NRLEVEL" FROM "NRIV" ... FOR UPDATE             | 11,549,961               | 210,142,307,207             | 676,473                   |
| SYSTEM | UPDATE "NRIV" SET "NRLEVEL" = ? WHERE "CLIENT" = '000' ... AND "TOYEAR" = '0000' | 0                        | 0                           | 3,706,184                 |
| SYSTEM | SELECT "DIMID" FROM "/BIC/DZDSTGCUBE4" WHERE "/B49/S_VERSION" = ?                | 0                        | 0                           | 460,991                   |
Here, it is clear that the first statement is waiting almost all the time. Known as pessimistic/optimistic locking, the SELECT...FOR UPDATE code locks the resulting columns and may be replaced by a non-locking variant, which can result in poor performance. If the application is critical, it may be necessary to revise the SELECT...FOR UPDATE code for better resource utilization and performance.
## Execution in a Distributed System
In distributed SAP HANA systems, tables and table partitions are located on multiple hosts. The execution of requests received from database clients may potentially have to be executed on multiple hosts, depending on where the requested data is located. The following example illustrates statement routing and how, if it is not enabled, requests from the database client are executed on the contacted index server (in this case the master index server) and the required data is fetched from the index server on the relevant host(s). However, if statement routing is enabled, after initial query compilation, request execution is routed directly to the host on which the required data is located.
Distributed Execution with Statement Routing Off and On
Statement Routing Off
Statement Routing On
Execution times should be better with statement routing enabled. You can use the SQL plan cache to compare the execution statistics of statements with statement routing enabled and disabled and thus confirm the effect.
Statement routing is controlled by the client_distribution_mode parameter in the indexserver.ini file. It is enabled by default (value=statement).
The following SQL plan cache examples show the execution times of sample statements based on the scenario illustrated above with hosts A, B and C.
##  Note
The column IS_DISTRIBUTED_EXECUTION indicates whether or not statement execution takes place on more than one host.
| host   |       |        |        | TABLE     |   COUNT | TOTAL EXECUTION _ TIWE   |   AvG EXECUTION TIME |
|--------|-------|--------|--------|-----------|---------|--------------------------|----------------------|
|        | 33103 |        | FFALSE | 233103,2) |      16 | 16792                    |                 1044 |
|        | 33103 | P"     | TRUE   |           |      91 | 125083                   |                 1374 |
|        | 33103 | SELECI | TRUE   | 233103 5} |  121444 |                          |                 1174 |
| host   |       |           |       | TABLE       |    |   TOTAL TIME | AvG EXECUTION TIME   |
|--------|-------|-----------|-------|-------------|----|--------------|----------------------|
|        | 33103 | P"        | FALSE | 233103,2)   | 20 |      3061773 |                      |
|        | 33103 | VPSERT DI | FALSE |             | 1B |       124625 | 1153                 |
|        | 33103 | R" NEIERE | FALSE | 16.33103,6} |    |     32911413 | 1174                 |
The TOTAL_EXECUTION_TIME for a statement is the sum of execution times on all hosts, therefore:
| Statement                                                | Request Path         | Total Execution Time                                 |
|----------------------------------------------------------|----------------------|------------------------------------------------------|
| UPSERT "RSBMONMESS_DTP" ( "MSGNO", "MSGTY", "MSGID", ... | Host A               | = execution time on Host A                           |
| SELECT * FROM"/BI0/SIOBJNM" WHERE"IOBJNM" = ?            | Host A > HostB       | = execution time on Host A + execution time on HostB |
| SELECT * FROM"/B49/SCUSTOMER" WHERE"/B49/S_CUSTOMER" = ? | Host A > HostB>HostC | = execution time on Host B+execution time on HostC   |
| Statement                                                | Request Path         | Total Execution Time                                 |
| UPSERT "RSBMONMESS_DTP" ( "MSGNO", "MSGTY", "MSGID", ... | Host A               | = execution time on Host A                           |
| SELECT * FROM"/BI0/SIOBJNM" WHERE"IOBJNM" = ?            | HostB                | = execution time on HostB                            |
| SELECT * FROM"/B49/SCUSTOMER" WHERE"/B49/S_CUSTOMER" = ? | HostC                | = execution time on HostC                            |