finding bottlenecks

8/13/2019 Finding Bottlenecks

1/36

Finding the Performance

Bottlenecks in Your Application

Ian Jones and Roger Schrag

Database Specialists, Inc.www.dbspecialists.com

IOUG-A Live! 1999Paper #158
http://www.dbspecialists.com/http://www.dbspecialists.com/


2/36

Finding the Bottleneck:

Half the Battle in Tuning

One bad SQL statement can spoil performance

Too much code to take the lets tune every

statement approach

DBA cant be familiar with every line of code


3/36

Zero in on the Bottleneck

Use the v$ views

Use SQL Trace and timed statistics

Use GUI tools


4/36

Todays Presentation

Half a dozen real-life examples

scenario

command-line efforts

resolution

Demo of GUI tools

Enterprise Manager

freeware


5/36

The Terrifyingly Slow EDI Load

A transportation company used EDI to exchange datawith customers. They loaded EDI files into a temp table

with SQL*Loader and then ran a 1500 line PL/SQL

stored procedure to validate the data and update

application tables.

As more customers began sending EDI files, the PL/SQL

stored procedure could no longer keep up. Datavalidation took as long as 24 hours for some EDI files.

Where do we start tuning?


6/36

Start Load Process and

Identify the Database Session

SQL> SELECT sid, serial#, status, username, osuser,

2 module, action

3 FROM v$session;

SID SERIAL# STATUS USERNAME OSUSER MODULE ACTION

---- ------- -------- -------- -------- -------- --------

1 1 ACTIVE oracle

2 1 ACTIVE oracle

3 1 ACTIVE oracle

4 1 ACTIVE oracle

5 1 ACTIVE oracle

6 1 ACTIVE oracle7 54959 ACTIVE BJENKINS bjenkins de

8 4921 INACTIVE RTHOMAS rthomas de

9 2492 INACTIVE EJOHNSON ejohnson de

45 3415 ACTIVE EDI_LOAD edi SQL*Plus validate


7/36

View the Statement Being Executed

SQL> SELECT B.sql_text

2 FROM v$session A, v$sqlarea B

3 WHERE A.sid = 45

4 AND B.address = A.sql_address;

SQL_TEXT

-------------------------------------------------------

SELECT ITEM_ID FROM ITEM_TRANSLATIONS WHERE

SOURCE_ID = :b1 AND SUBSTR(SOURCE_SKU_CODE,1,6)= :b2

AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL

(END_DATE_ACTIVE, SYSDATE)


8/36

We Found a Bottleneck!

PROCEDURE edi_validate_and_load

(p_cust_id IN NUMBER)

IS

CURSOR c_get_item_id (cp_cust_id IN NUMBER,cp_sku IN VARCHAR2) IS

SELECT item_id

FROM item_translations

WHERE source_id = cp_cust_id

AND source_sku_code LIKE cp_sku || '%'AND SYSDATE BETWEEN start_date_active

AND NVL (end_date_active, SYSDATE);


9/36

Disk Array Far Too Busy

One third of a financial institutions loan processing

department started using a new PowerBuilder

application. Response time was acceptable, but disk

utilization on the server was at 100%.

What could be done to reduce I/O so that the

response time will still be acceptable after the rest ofthe department starts using the new application?


10/36

Identify the SQL Statements

Causing the Most Disk ReadsSELECT sql_text, disk_reads, executions,

disk_reads / DECODE (executions, 0, 1, executions)

reads_per_exec

FROM v$sqlarea

ORDER BY reads_per_exec;

SELECT sql_text, disk_reads, executions,

disk_reads / DECODE (executions, 0, 1, executions)

reads_per_exec

FROM v$sqlarea

ORDER BY disk_reads;

SELECT sql_text, buffer_gets, executions,

buffer_gets / DECODE (executions, 0, 1, executions)

gets_per_exec

FROM v$sqlarea

ORDER BY buffer_gets;


11/36

Part of the Query Results

SQL_TEXT

------------------------------------------------------------

DISK_READS EXECUTIONS READS_PER_EXEC

---------- ---------- --------------

SELECT P.PRODUCT_DESC, CP.PRODUCT_ID, UPPER (:b1) CLIENT_ID

FROM CLIENT_PRODUCT CP, PRODUCT P WHERE CP.PRODUCT_ID =

P.PRODUCT_ID AND (UPPER (CP.CLIENT_ID),

CP.VALID_CLIENT_LEVEL_ID) IN (SELECT UPPER (:b1),

CA.VALID_CLIENT_LEVEL_ID FROM CLIENTS C, CLIENT_ADDRESS CA

WHERE UPPER (C.CLIENT_ID) = UPPER(:b1) AND C.CLIENT_ID= CA.CLIENT_ID)

208734602 18657 11188.0046


12/36

The Same Query

Formatted for Readability

SELECT P.product_desc, CP.product_id,

UPPER (:b1) client_id

FROM client_product CP, product P

WHERE CP.product_id = P.product_id

AND (UPPER (CP.client_id), CP.valid_client_level_id) IN(SELECT UPPER (:b1), CA.valid_client_level_id

FROM clients C, client_address CA

WHERE UPPER (C.client_id) = UPPER(:b1)

AND C.client_id = CA.client_id)

A case-insensitive query turns out to be very

inefficient. Now we know what to tune.


13/36

The So-Called Locking Problem

A developer coded a PL/SQL function called

compute_irr for computing internal rate of

return. Response times varied widely. The

developer claimed there was a locking problemon the database.

What caused the slow performance in

compute_irr?


14/36


15/36

Check Session Statistics

Before Calling compute_irrSQL> SELECT A.name, B.value

2 FROM v$statname A, v$sesstat B

3 WHERE B.statistic# IN (12, 37, 38, 39, 119, 123,

4 139, 140, 141)

5 AND B.sid = 63

6 AND A.statistic# = B.statistic#;

NAME VALUE

------------------------------ ----------

CPU used by this session 1292

db block gets 10186

consistent gets 86810

physical reads 346

table scans (long tables) 0

table scan rows gotten 1054

sorts (memory) 826

sorts (disk) 0

sorts (rows) 4693


16/36

Check Session Statistics Again

While compute_irr Is RunningSQL> SELECT A.name, B.value

2 FROM v$statname A, v$sesstat B

3 WHERE B.statistic# IN (12, 37, 38, 39, 119, 123,

4 139, 140, 141)

5 AND B.sid = 63


NAME VALUE

------------------------------ ----------


db block gets 10294

consistent gets 86813physical reads 346



sorts (memory) 826

sorts (disk) 0

sorts (rows) 4693


17/36

Session Statistic Before After 60

Seconds

After 120

Seconds

CPU Used by this session 1292 7274 13255

db block gets 10186 10294 10294

consistent gets 86810 86813 86813

physical reads 346 346 346

table scans (long tables) 0 0 0

table scan rows gotten 1054 1054 1054

sorts (memory) 826 826 826

sorts (disk) 0 0 0

sorts (rows) 4693 4693 4693

Compute the Deltas

Look for an infinite loop that involves no SQL

statements.


18/36

Where Is the I/O Coming From?

An application that performed well in a demo

database quickly became I/O-bound when a

significant amount of data was loaded into the

database. It seemed as if there was a hugeamount of I/O even with no users logged on to

the application.

What could be causing so much I/O activity?


19/36

Look at Physical Reads

Instance-Wide and Per Session

SQL> SELECT name || ' (instance-wide)', value

2 FROM v$sysstat

3 WHERE statistic# = 394 UNION ALL

5 SELECT 'sid = ' || TO_CHAR (sid), value

6 FROM v$sesstat

7 WHERE statistic# = 39;


20/36

And the Results Are...

NAME VALUE

-------------------------------- ----------

physical reads (instance-wide) 6048399

sid = 1 0

sid = 2 0

sid = 3 0

sid = 4 0

sid = 5 0

sid = 6 23120

sid = 7 186sid = 8 0

sid = 9 18984

sid = 12 1375

sid = 13 5830219

sid = 17 27821


21/36

Investigate Suspicious Session 13


2 module, action

3 FROM v$session

4 WHERE sid = 13;

SID SERIAL# STATUS USERNAME OSUSER MODULE ACTION---- ------- -------- --------- -------- -------- --------

13 829 ACTIVE APPSCHEMA daemon EXTRACT GET

SQL> SELECT A.name, B.value

2 FROM v$statname A, v$sesstat B3 WHERE B.statistic# IN (12, 37, 38, 39, 119, 123,

4 139, 140, 141)

5 AND B.sid = 13



22/36

Suspicious Session 13 (continued)

NAME VALUE

------------------------------ ----------


db block gets 5928722

consistent gets 293physical reads 5873918



sorts (memory) 3sorts (disk) 0

sorts (rows) 4210


23/36

What SQL Is Session 13 Executing?

SQL> SELECT B.sql_text

2 FROM v$session A, v$sqlarea B

3 WHERE B.address = A.sql_address

4 AND A.sid = 13;

SQL_TEXT

------------------------------------------------------

SELECT * FROM TRANSACTIONS WHERE EXTRACTED = 'N

Let me guess!The transactions table is about 10,000blocks in size and the extracted column is notindexed.


24/36

Fast Reports That Were Too Slow

A software vendor built an application using

Oracle, Developer/2000, and a third-party

reporting tool. A key report took about six

seconds to complete, but consider that users willrun the report in batches of several thousand.

The report has thousands of lines of spaghetti

code. How do you figure out what's slowing it

down?


25/36

Build a Version of the Report

That Enables SQL TraceDECLARE

c INTEGER;

i INTEGER;

BEGIN

c := dbms_sql.open_cursor;dbms_sql.parse (c,

'ALTER SESSION SET TIMED_STATISTICS=TRUE',

dbms_sql.native);

i := dbms_sql.execute (c);

dbms_sql.close_cursor (c);

dbms_session.set_sql_trace (TRUE);

END;

Trace only what you need to trace!


26/36


27/36

The Chameleon Application

An application ran well on a test database loaded with a

full set of production data. But when the application

was deployed in production, queries took over a minute

to complete. In the test environment these same queriescompleted in under ten seconds.

Policies forbid modifying code in production.

What could be causing the application to run slower in

production?


28/36

Find a Power User to

Reproduce the Slow Behavior

Identify the database session:


2 module, action

3 FROM v$session

4 WHERE username = 'MARYD';

SID SERIAL# STATUS USERNAME OSUSER MODULE ACTION

---- ------- -------- --------- -------- -------- --------

17 9172INACTIVE MARYD mbd frontend query


29/36

Enable Timed Statistics Temporarily

SQL> ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

System altered.

SQL>


30/36

Enable SQl Trace Just

Before the Query Is Launched

SQL> BEGIN

2 dbms_system.set_sql_trace_in_session (17, 9172, TRUE);

3 END;

4 /

PL/SQL procedure successfully completed.

SQL>


31/36

Disable SQL Trace

When the Query Is Finished

SQL> BEGIN

2 dbms_system.set_sql_trace_in_session (17, 9172, FALSE);

3 END;

4 /

PL/SQL procedure successfully completed.

SQL>

SQL> ALTER SYSTEM SET TIMED_STATISTICS = FALSE;

System altered.

SQL>


32/36

Fetch the Trace File and Run TKPROFcall count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 1.44 1.45 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 17 68.39 68.54 0 1878 2 254

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 19 69.83 69.99 0 1878 2 254

Misses in library cache during parse: 1

Optimizer goal: RULE

Parsing user id: 142 (BUILD4P2)

Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: RULE

0 MERGE JOIN (OUTER)

254 SORT (JOIN)

115 NESTED LOOPS (OUTER)

253 NESTED LOOPS (OUTER)

254 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'MNME'

114539 INDEX GOAL: ANALYZED (FULL SCAN) OF 'MNME_I1'(UNIQUE)

253 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'LCONTYPE'

254 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'LCONTYPE_PK'

(UNIQUE)

115 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'MTAX'

254 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'MTAX_I1'

(UNIQUE)

129 SORT (JOIN)

129 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'LPOST

The

optimizer

can behave

differently

from one

Oracleversion to

the next.


33/36

GUI Tools Demo


34/36

To Find the Bottlenecks

in Your Applications:

Monitor v$sqlarea

Monitor v$sysstat and v$sesstat Use SQL Trace judiciously

Consider using GUI tools


35/36

Resources

Oracle Server Tuning

- Overview of the tuning process

- How to use SQL Trace and TKPROF

Oracle Server Reference

- Descriptions of all v$ views

High Performance SQL Tuningby Guy Harrison

- Lots of tuning tips

- Discussion of GUI tools available on the Internet

www.dbspecialists.com/present.html- Download this presentation

- Download a companion white paper
http://www.dbspecialists.com/http://www.dbspecialists.com/


36/36

Contact Information

Ian Jones: [email protected]

Roger Schrag: [email protected]

Database Specialists, Inc.

388 Market Street, Suite 400San Francisco, CA 94111

415/344-0500

finding bottlenecks

Documents