Identifying Expensive SQL Statements

Expensive SQL statements are long-running statements and one of the main causes of performance problems.

Expensive SQL statements can have the following effects on the entire system:

->They cause high CPU utilization percentages and high I/O loads

->They block SAP work processes for a long time.

->They read many data blocks into the data buffer of the database server, which displaces data required by other SQL statements.

Analyzing Currently Running SQL Statements

DBA Cockpit - PERFORMANCE • WAIT EVENT ANALYSIS • SESSION MONITOR

The following are important events:

CPU, which indicates that the database CPU currently executes an operation (for instance, sorting)

DB FILE SEQUENTIAL READ for a file access

SQL*NET MORE DATA TO CLIENT (or also ... MORE DATA FROM CLIENT), I which means data from the shadow process is transferred to the SAP work process (or vice versa)

ENQ: TX - ROW LOCK CONTENTION, which shows that a shadow process is waiting for a lock

Because both monitors display the application server and the PID of the related SAP work process for the database processes, you can see which database process corresponds to which SAP work process. From the two monitors, you can determine the following:

->Program name and transaction code of the executed program (from the work process overview)

->Table name (from the work process overview and the database process monitor)

->The user who started the program (from the work process overview)

->WHERE conditions of the SQL statement (from the database processmonitor)

->In the database performance monitor, you can use the ExPLAIN function to create an execution plan for the SQL statement;

Detailed Analysis of Times Used in Shadow Processes (Oracle Wait Events)

Analysis of Statistics of SQL Statements (SQL Statistics)

Fields of the SOL Statistics as an Example of the Oracle Shared Cursor Cache

Field	Explanation
ExECUTIONS	Number of times the statement has been executed since the start of the database.
ELAPSED TIME	Time required for all the executions of the statement.
ElAPSED TIME/EXECUTION	Average time required for an execution of the statement.
DISK READS	Number of physical read accesses required for all executions of the statement; a high value indicates that there were a lot of read accesses to the hard disks.
READS/EXECUTION	Number of physical read accesses required, on average, for one execution of the statement.
BUFFER GETS	Number of logical read accesses required for all the executions of the statement; the ratio of buffer gets and disk reads indicates whether many accesses are possible from t he buffer.
BUFFER GETS/ ExECUTION	Number of logical read accesses required, on average, for one execution of the statement.
PROCESSED ROWS	Number of rows read for all of the executions of the statement.
PROCESSED ROWS/EXECUTION	Average number of rows read for an execution of the statement.
BUFFER GETS/ROW	Average number of logical read accesses per row read; a high value indicates an inefficient search strategy (except for aggregating accesses-for instance, summation).

Analysis of the SQL statistics

1. Sort the SQL statistics according to the ELAPSED TIME column.
2. Are there only a few expensive SQL statements that constitute more than 5% of the total? If this is the case, you can usually improve database performance significantly by tuning these statements.