Thursday Oct 11, 2012

System Wide Performance Sanity Check Procedures

Do you need to boost your overall implementation performance?

Do you need a direction to pinpoint possible performance opportunities?

Are you looking for a general performance guide?

Try MOS note 69565.1

This paper describes a holistic methodology that defines a systematic approach to resolve complex Application performance problems.  It has been successfully used on many critical accounts.  The 'end-to-end' tuning approach encompasses the client, network and database and has proven far more effective than isolated tuning exercises.  It has been used to define and measure targets to ensure success. 

Even though it was created in 2001 and checked for relevance on 13-Oct-2008, the procedures are still very valuable.

Regards!

 

Thursday Sep 06, 2012

AWR Performance Report and Read by Other Session Waits

For the questions regarding "read by other session" and its relation to "db file sequential/scattered read",
the logic is like this:


When a "db file sequential/scattered read" is done, the blocks are either already in the cache or on the disk.  Since any operation on blocks is done in the cache and since and the issue is "read by other session" I will relate to the case the blocks are on the disk.

Process A is reading the needed block from the disk to the cache.  During that time, if process B (and C and others) need the same block, it will wait on "read by other session".  A and B can be threads of the same process running in parallel or unrelated processes.  For example two processes doing full table scan on mdp_matrix etc.

Solutions for that can be lowering the number of processes competing on the same blocks, increasing PCTFREE.  If it is a full table scan, maybe an index is missing that can result in less blocks being read from the cache and so on.

Wednesday Jun 06, 2012

Need a Quick Sure Method to Produce a Formatted Explain Plan? This will help!

Please use the following on the production machine to get formatted explain
plan and sql trace using the SLOW sql (e.g. 'T_COMB_LIST.COMB_ID = 216') or
any other value that takes longer:

-- Open new session is SQL*Plus */
-- Make sure you are using updated PLAN_TABLE
-- This can be done by dropping it and recreate it by running:
-- SQL> @?/rdbms/admin/utlxplan.sql)

set lines 1000
set pages 1000
spool xplan_1.txt
EXPLAIN PLAN FOR
<<<<Replace this line with exactly the same query you used above. Force hard
parse by modifying the case of a character>>>>
@?/rdbms/admin/utlxplp
spool off
EXIT

--Open a second session is SQL*Plus
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = '10046';
ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET events '10046 trace name context forever, level 12';
<<<<Replace this line with exactly the same query you used above. Force hard
parse by modifying the case of a character>>>>
select 'verify cursor closed' from dual;
ALTER SYSTEM SET EVENTS '10046 trace name context off';
EXIT

Make sure spooled file is formatted properly and that the 10046 trace has relevant explain plan in it.  Please Upload both files (10046 trace is generated in udump).

Need instructions to find udump?  

sqlplus "/ as sysdba"
show parameters dump_dest

This will show you bdump, cdump and udump locations.

About

This blog delivers the latest information regarding performance and install/upgrade. Comments welcome

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today