top

Performance 

Performance related to different areas of an system

1. application design
2. application code
3. memory
4. io
5. resource contention
6. o.s.
7. cpu

Common  performance problems (appl design and code)

- incorrect session management
- poorly designed cursor management
          binding variables,  cursor shareing, non-set operations
- inadequate relation design
- improper use of storage structures

 Diagnostic Tools

•  Data Dictionary and Dynamic Performance Views
• ‰‰ Analyze command
• ‰‰ Analyze schema and database with DBMS_UTILITY package
•  DBMS_STATS package
•  Statspack report
•  Alert log and trace files
•  Automatic Workload Repository (AWR)
•  Automatic Database Diagnostic Monitor (ADDM)

Soft Parses

Parsing is a CPU-bound operation (it requires many CPU cycles) that involves latches, hence serialization, and hence waiting. We want to avoid unnecessary parsing.

The parsing process always performs a syntax and semantic check of the statement; after this phase, the database engine first searches for the statement in the shared SQL area. To do so, it calculates a hash of the literal statement, and compares it with the hash of theshared SQL statements. In this way, similar statements, which differ only in whitespaces, in case or in the name of bind variables are treated as different.

To avoid hard parsing, write SQL statements in reusable code sections. Use bind variables and not constants, trying to minimize the number of different queries executed against the database. Recommended is the use of bind variables and not constants. When the same statement is found in a shared SQL area, a soft parse occurs, otherwise a hard parse operation is needed. This requires two extra steps, optimizing and generating the execution plan for the query. In developing applications, try to minimize the number of hard parses and maximize the soft-to-hard parse ratio.

Diagnosing soft and hard parsing

How can we diagnose a parsing problem, caused by any third-party application, whose
sources we cannot inspect? To find the answer, take a look at the dynamic performance views and monitor the following values—library cache hit ratio and parse count.

Query the V$LIBRARYCACHE, using the following statement to view hit ratios related to different areas of the database:

SELECT NAMESPACE, GETS, PINS, GETHITRATIO FROM V$LIBRARYCACHE

We are interested in the SQL AREA values,  the first in the response, which indicates a GETHITRATIO of 26 percent (poor value) for our example. For an OLTP database, we probably want a value of .99999, while in a data warehouse environment a value above .9 is good.

 To investigate parse data in a session, use the following statement:
SELECT S.NAME, V.VALUE FROM V$STATNAME S, V$MYSTAT V WHERE S.STATISTIC# = V.STATISTIC# AND S.NAME LIKE 'parse%';
The output will be as follows:

Flush library cache

For test purposes you can flush library chach, only in Test envrion : alter system flush shared_pool

Autotrace SQLPLUS

The autotrace provides instantaneous feedback including the returned rows, execution plan, and statistics. The user doesn’t need to be concerned about trace file locations and formatting since the output is displayed instantly on the screen. This is very important data that can be used to tune the SQL statement.

Oracle autotrace supports the following options:

• autotrace on – Enables all options.

• autotrace on explain – Displays returned rows and the explain plan.

• autotrace on statistics – Displays returned rows and statistics.

• autotrace trace explain – Displays the execution plan for a select statement without actually executing it. "set autotrace trace explain"

• autotrace traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.

voorbeeld
SET AUTOTRACE ON
SET TIMING ON
SELECT PROD_ID, SUM(AMOUNT_SOLD) FROM SH.SALES GROUP BY PROD_ID;
SET TIMING OFF
SET AUTOTRACE OFF

Storage Structures Optimizing

row chaining

We encounter row chaining when the size of the row data is larger than the size of the database
block used to store it. In this situation, the row is split across more than one database block, so,
to read it we need to access more than one database block, resulting in greater I/O.

refresh statistics
- ANALYZE TABLE HR.BIG_ROWS COMPUTE STATISTICS;

look for chained rows
- SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER = ‹HR› AND TABLE_NAME = ‹BIG_ROWS›
- select chain_cnt, table_name,owner from all_tables where chain_cnt >0