Web Site Performance Tips and Tools

I have found an interesting basic article about performance in this week SunFlash news letter. Same is available in link here. Enjoy reading.

Thanks,

Thiru

Oracle Column Selectivity – An Performance Impact

Lot of times I hear developer saying what is the big difference it is going to make when we execute “select * from tab1 where c1=1” instead of “select c2 from tab1 where c1=1”. (Even though they need only c2).  I bet, performance impact will  be big.

Benefits of selecting only required columns are listed below (Below list may not exclusive):

1. Network round trip between application server and database server.

2. Application server’s record set size will consume more memory which adds overhead to JVM’s GC engine.

3. Database server IO (Quite obvious IO will be having big impact on OLTP systems).

First 2 points are simple, straight and it is easy to understand. Regarding 3rd point, see my example below which shows IO impact it has because of bad query(selecting all columns).

SQL> create table tab1 as select * from all_objects;

Table created.

SQL> desc tab1

 Name                                      Null?    Type

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

 OWNER                                     NOT NULL VARCHAR2(30)

 OBJECT_NAME                               NOT NULL VARCHAR2(30)

 SUBOBJECT_NAME                                     VARCHAR2(30)

 OBJECT_ID                                 NOT NULL NUMBER

 DATA_OBJECT_ID                                     NUMBER

 OBJECT_TYPE                                        VARCHAR2(19)

 CREATED                                   NOT NULL DATE

 LAST_DDL_TIME                             NOT NULL DATE

 TIMESTAMP                                          VARCHAR2(19)

 STATUS                                             VARCHAR2(7)

 TEMPORARY                                          VARCHAR2(1)

 GENERATED                                          VARCHAR2(1)

 SECONDARY                                          VARCHAR2(1)

 NAMESPACE                                 NOT NULL NUMBER

 EDITION_NAME                                       VARCHAR2(30)

SQL> create index ind1 on tab1(object_id,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats( user, 'TAB1');

PL/SQL procedure successfully completed.

SQL> select * from tab1;

53762 rows selected.

Execution Plan

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

Plan hash value: 2211052296

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 53762 |  4882K|   228   (1)| 00:00:03 |

|   1 |  TABLE ACCESS FULL| TAB1 | 53762 |  4882K|   228   (1)| 00:00:03 |

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

Statistics

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

          1  recursive calls

          0  db block gets

       4352  consistent gets

          0  physical reads

          0  redo size

    6269625  bytes sent via SQL*Net to client

      39840  bytes received via SQL*Net from client

       3586  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      53762  rows processed

SQL> select object_id,object_name from tab1;

53762 rows selected.

Execution Plan

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

Plan hash value: 4126477464

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

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |      | 53762 |  1522K|    87   (0)| 00:00:02 |

|   1 |  INDEX FAST FULL SCAN| IND1 | 53762 |  1522K|    87   (0)| 00:00:02 |

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

Statistics

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

          1  recursive calls

          0  db block gets

       3887  consistent gets

          0  physical reads

          0  redo size

    1845080  bytes sent via SQL*Net to client

      39840  bytes received via SQL*Net from client

       3586  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      53762  rows processed

SQL> select * from tab1 where object_id=2001

  2  ;

Execution Plan

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

Plan hash value: 2086140937

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

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time   |

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

|   0 | SELECT STATEMENT            |      |     1 |    93 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1 |     1 |    93 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IND1 |     1 |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=2001)

Statistics

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

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1349  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> select object_name from tab1 where object_id=2001;

Execution Plan

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

Plan hash value: 3743434205

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

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |      |     1 |    29 |     2   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IND1 |     1 |    29 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("OBJECT_ID"=2001)

Statistics

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

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        428  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL> 

If we carefully look in our above example, query "select * from tab1" requires 4882K bytes whereas "select object_id,object_name from tab1" requires only 1522K bytes.

Likewise "select * from tab1 where object_id=2001" requires 93bytes whereas "select object_name from tab1 where object_id=2001" requires just 29 bytes.

All of this IO optimization is because of 

• "INDEX FAST FULL SCAN" ==>Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized
  
• "INDEX RANGE SCAN" ==> An index range scan is a common operation for accessing selective data. It can be bounded (bounded on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.
  

During any index access, if index has all column required to satisfy an SQL, table will not be accessed at all(avoids extra IO/buffer gets).

Note: Bytes in above examples means "Estimate by the cost-based approach of the number of bytes accessed by the operation".

As a single user, we might not see much performance impact; but at high load this will have huge impact. Whoever developing code, please mark this valuable performance tuning hints.

As usual, your comments are always welcome.

-Thiru

Improving performance with Result Cache in Oracle 11g

Initially in previous versions(before 11g), Oracle Database started off with caching data block in memory, which inturn is used to construct results for user’s request. Even though data blocks are cached in memory, it should construct results from cache which takes from time, resource, locks (latches & enqueues) etc., Oracle has gone to next level of caching in 11g database. Effective from Oracle 11g, Oracle can cache result sets. Oracle can cache the results of both SQL & PL/SQL. In this article, I am going to discuss basics of SQL result cache and I am planning to write about PL/SQL result cache in future articles. Quite obvious, at the end of article, most people will feel that this is the most interesting performance tuning hints while working in Oracle 11g database.

Few basics about result cache

Oracle introduced the memory structure called Result Cache to store the results of SQLs & PL/SQL. The database serves the results for the executed SQL queries and PL/SQL functions from the result cache instead of re-executing the actual query, quite obvious this feature will increase the performance. Memory allocated(can be controlled using RESULT_CACHE_MAX_SIZE parameter) for the result cache is taken from the shared pool. By now one might come to the conclusion that result cache will improve the performance of the repeatly used PL/SQL & SQL. RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. If the value of this parameter is 0, then the result cache feature is disabled.

Here are some of the parameters that control the behaviour of the result cache.

SQL> col name format a30

SQL> col value format a25

SQL> select name,value from v$parameter where name like 'result_cache%';

NAME                           VALUE

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

result_cache_mode              MANUAL

result_cache_max_size          5373952

result_cache_max_result        5

result_cache_remote_expiration 0

Brief description about the above parameters:

RESULT_CACHE_MODE: RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan. If set to FORCE, all the queries are cached if they are qualifed and fit in cache. The default is MANUAL, which indicates that only queries with the hint will be cached. 

RESULT_CACHE_MAX_SIZE: Specified maximum size of the result cache. Remember, result cache is part of shared pool and maximum can be of 75% of shared pool size. Also query result.

RESULT_CACHE_MAX_RESULT: Specifies percentage of RESULT_CACHE_MAX_SIZE that any single query result set can occupy. (Just to prevent the big result set from throwing out all other small resultset).

RESULT_CACHE_REMOTE_EXPIRATION: Specifies the number of minutes that a cached result that accesses a remote object will remain valid. Setting this parameter to 0 implies that results using remote objects should not be cached. Setting this parameter to a nonzero value may produce stale answers (for example, if the remote table used by a result is modified at the remote database). I personally think it should be set to positive value if remote database is read only database.

From the below query result, it is evident that result cache is part of shared pool.

SQL> select * from v$sgastat where pool='shared pool' and name like 'Result%';

POOL         NAME                            BYTES

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

shared pool  Result Cache: Memory Mgr          200

shared pool  Result Cache: Bloom Fltr         2048

shared pool  Result Cache: Cache Mgr           152

Like almost all memory structure in oracle instance, Result cache is also protected by latches. Latches, being serialization devices, are scalability inhibitors. Don’t just to come to conclusion that Latches are evil; afterall we expect database to maintain ACID property.

SQL> select * from v$latchname where name like 'Result Cache%';

    LATCH# NAME                                 HASH

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

       377 Result Cache: Latch            1545889529

       378 Result Cache: SO Latch          986859868

Oracle 11g provides couple of views to see the behavious/statistics of result cache. Click on the below view names to see the details in Oracle documentation.

V$RESULT_CACHE_STATISTICS 

V$RESULT_CACHE_MEMORY

V$RESULT_CACHE_OBJECTS

V$RESULT_CACHE_DEPENDENCY

APIs for the result cache management is available in PL/SQL package DBMS_RESULT_CACHE.

Couple of examples on how to use APIs on DBMS_RESULT_CACHE package.

SQL> set serveroutput on size 99999

SQL> exec  DBMS_RESULT_CACHE.MEMORY_REPORT("TRUE")

R e s u l t   C a c h e   M e m o r y   R e p o r t

[Parameters]

Block Size          = 1K bytes

Maximum Cache Size  = 5248K bytes (5248 blocks)

Maximum Result Size = 262K bytes (262 blocks)

[Memory]

Total Memory = 2400 bytes [0.001% of the Shared Pool]

... Fixed Memory = 2400 bytes [0.001% of the Shared Pool]

....... Cache Mgr  = 152 bytes

....... Memory Mgr = 200 bytes

....... Bloom Fltr = 2K bytes

....... State Objs =  bytes

... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESULT_CACHE.flush;

PL/SQL procedure successfully completed.

DBMS_RESULT_CACHE package has lot more functions/procedures and if you are looking for more, login into 11g using SQL* Plus and execute “desc DBMS_RESULT_CACHE” and the documentation for the same can be found here.

Background Process

SQL> select description from V$BGPROCESS where name like 'RC%';

DESCRIPTION

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

Result Cache: Background

Watch my blog to see result set in action.

As usual, comments are always welcome.

-Thiru

Monitoring Oracle database using Perfmon

Performance monitoring is the essential part of performance tuning exercise. Oracle database monitoring is no exception to this rule. As such Oracle database provides various ways to monitor such as statspack, AWR, tracing etc., Some people might be quite interested in seeing numerical values such as hit ratios, DBWR statistics etc., so that they can plot graphs against load and see how the trend is. Oracle running on Windows based Operating System provides features which help to monitor the system via perfmon (click here to know more about perfmon).

By default Oracle database related counters are not exposed to perfmon. But there are couple of ways to make it appear on perfmon. 

1. By using custom installation

2. By using operfcfg utility

3. By using administrative Assist on Windows

4. By adding couple of values in registry

First three ways indirectly adds the values to registry (hides complexity from end user). Oracle recommends user to user operfcfg utility. I am going to explain how to use operfcfg to add Oracle database counters in perfmon.

operfcfg is an Oracle Database tool that you run from the command prompt. Use this syntax:

operfcfg [-U username] [-D TNS_Alias_for_database] [-P password]

username is the username registry parameter value that Oracle Counters for Windows Performance Monitor uses to log in to the database. You must have a DBA privilege on this database.

TNS_Alias_for_database is the net service name that Oracle Counters for Windows Performance Monitor uses to connect to the database. It affects the Hostname registry parameter. The net service name corresponds to the SID of the database that you want to monitor. The -D command can be specified without providing a database name value.

password is the password registry parameter value for username.

If we omit –D & -P, then utility will take the default database on that system and it will interactive asks for password to connect to that user.

Debugging: 

If you are not able to see Oracle database counter after running through operfcfg utility, then

1. If you are using TNS alias then try to ping the database using tnsping utility and check whether you are able to reach the database. Click here to know more about tnsping utility

2. Try to connect to the Oracle database using sqlplus with the username, password provided for operfcfg utility.

3. Make sure the username provided has DBA privilege.

Once you got the counters, then enjoy the performance monitoring & tuning excerise. As usual, you comments are always welcome regarding my performance tuning hints.

Cheers,

Thiru

Safari - Performance

Apple came out with its new browser Safari 4 Beta and as a performance guys, I found something really interesting. Apple claims "Safari’s Nitro Engine executes JavaScript up to 30 times faster than Internet Explorer 7 and more than 3 times faster than Firefox 3. "

Wow! 

As I said earlier here, most of performance testing tools doesn't have ability to report end user response time and one of the reason being different Javascript engine used by different browser vendor.

For more information about Safari and its performance, check here and here

As usual, comments are always welcome.

-Thiru

Oracle 11g - Memory Management Made Simple

As and when Oracle release new version of database, DBA gets new bunch of features.

Before Oracle 9i, DBA has to maintain the whole bunch of memory parameters like DB_BLOCK_BUFFERS, SHARED_POOL_SIZE, set of PGA parameters *_AREA_SIZE,

LOG_BUFFER and so on. Real complexity before 9i is assigning correct values to *_AREA_SIZE

(PGA) parameters as these values is not for whole instance; it is for EACH oracle user process(Oracle user process count can grow upto maximum of PROCESSES settings).

In 9i, Oracle introducted a new parameters called PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY which helps users to assign a single value for the whole instance's PGA and Oracle instance itself will do self mangement for PGA memory. In earlier version, DBA has to assign value for _AREA_SIZE parameters which assign equal size for all sessions.

Oracle 10g has gone to next level from 9i wherein it automates the memory management of whole SGA. Instead of DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE,

LARGE_POOL_SIZE and

LOG_BUFFER parameter; user has to manage just SGA_TARGET and SGA_MAX_SIZE(see diagram).

Oracle 11g has gone to the next level from 10g of autmatic memory management where in the whole instance can be controlled by initialization parameter MEMORY_TARGET and a maximum memory size initialization parameter MEMORY_MAX_TARGET. Oracle Database then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA).

Oracle doc says

MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.

MEMORY_MAX_TARGET specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter.

Here is how internally the memory management parameters control the memory structure if MEMORY_TARGET is set to a non-zero value:

• If SGA_TARGET and PGA_AGGREGATE_TARGET are not set, it will distribute the total server memory in a fixed ratio as 60% and 40% and assign it to SGA_TARGET and PGA_AGGREGATE_TARGET respectively.
• If SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered the minimum values for the sizes of SGA and the PGA respectively (But sum of SGA_TARGET and PGA_AGGREGATE_TARGET should be less than or equal to MEMORY_TARGET).
• If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still auto-tune both parameters. PGA_AGGREGATE_TARGET will be initialized to a value of (MEMORY_TARGET-SGA_TARGET).
• If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still auto-tune both parameters. SGA_TARGET will be initialized to a value of min(MEMORY_TARGET-PGA_AGGREGATE_TARGET, SGA_MAX_SIZE (if set by the user)).

Note: MEMORY_TARGET can be dynamically increased until MEMORY_MAX_TARGET without bouncing the instance. Regarding reducing the size of MEMORY_TARGET, it is just a request to Oracle server which will be honoured in the course of time.

Monitoring Automatic Memory Management in Oracle 11g using views V$MEMORY_DYNAMIC_COMPONENTS (current size of all memory components) and V$MEMORY_RESIZE_OPS (circular history list of the last 800 SGA resize requests).

C:\Documents and Settings\tchettia>sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jul 13 11:43:05 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@ORCL> set pagesize 50
sys@ORCL> col COMPONENT format a30
sys@ORCL> select COMPONENT,CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;

COMPONENT                      CURRENT_SIZE
------------------------------ ------------
shared pool                       192937984
large pool                          4194304
java pool                          12582912
streams pool                              0
SGA Target                        536870912
DEFAULT buffer cache              318767104
KEEP buffer cache                         0
RECYCLE buffer cache                      0
DEFAULT 2K buffer cache                   0
DEFAULT 4K buffer cache                   0
DEFAULT 8K buffer cache                   0
DEFAULT 16K buffer cache                  0
DEFAULT 32K buffer cache                  0
Shared IO Pool                            0
PGA Target                        318767104
ASM Buffer Cache                          0

16 rows selected.

sys@ORCL> col PARAMETER format a30
sys@ORCL> select PARAMETER,OPER_TYPE,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS from V$MEM
ORY_RESIZE_OPS;

PARAMETER                      OPER_TYPE     INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS
------------------------------ ------------- ------------ ----------- ---------- ---------
shared_pool_size               STATIC                   0   192937984  192937984 COMPLETE
db_cache_size                  INITIALIZING     318767104   318767104  318767104 COMPLETE
java_pool_size                 STATIC                   0    12582912   12582912 COMPLETE
streams_pool_size              STATIC                   0           0          0 COMPLETE
sga_target                     STATIC                   0   536870912  536870912 COMPLETE
db_cache_size                  STATIC                   0   318767104  318767104 COMPLETE
db_keep_cache_size             STATIC                   0           0          0 COMPLETE
db_recycle_cache_size          STATIC                   0           0          0 COMPLETE
db_2k_cache_size               STATIC                   0           0          0 COMPLETE
db_4k_cache_size               STATIC                   0           0          0 COMPLETE
db_8k_cache_size               STATIC                   0           0          0 COMPLETE
db_16k_cache_size              STATIC                   0           0          0 COMPLETE
db_32k_cache_size              STATIC                   0           0          0 COMPLETE
pga_aggregate_target           STATIC                   0   318767104  318767104 COMPLETE
db_cache_size                  STATIC                   0           0          0 COMPLETE
large_pool_size                STATIC                   0     4194304    4194304 COMPLETE

16 rows selected.

For more information on Oracle database refer documentation and support check here and here.

Quite obvious, performance tuning will be quite easy.

As usual, comments are always welcome.

Cheers,

Thiru

Added by Thiru: Almost all parameter available in 9i and 10g is available in 11g

vmstat - Virtual Memory Statistics

If someone asks me to check how Linux/UNIX system is performing now, the first thing I do is vmstat. A lot of people just check for CPU and memory utilization statistics in vmstat. But in reality, it gives more information than just CPU and memory information. In this posting, I am going to explain the detail of vmstat.

vmstat stands for virtual memory statistics; it collects and displays summary information about memory, processes, interrupts, paging, and block I/O information. By specifying the interval, it can be used to observe system activity interactively.

Most commonly people will use 2 numeric arguments in vmstat; the first is delay or sleep between updates and the second is how many updates you want to see before vmstat quits. Please note this is not the full syntax of vmstat and also it can vary between OSs. Please refer to your OS man page for more information.

To run vmstat with 7 updates, 10 seconds apart type

#vmstat 10 7

Please note, in some systems, reported metrics might be slightly different. The heading that I am writing now is reported in Oracle Linux (Unbreakable Oracle Linux)

Process Block: Provides details of the process which are waiting for something (it can be CPU, IO etc; can be potentially for any resource)

r  -->  Processes waiting for CPU. More the count we observe, more processes waiting to run. If we just observe a spike in the count, we shouldn’t treat them as bottlenecks. If the value is constantly high (most people treat 2 * CPU count as high), it hints that CPU is the bottleneck.

b  -->  Uninterruptible sleeping processes, also known as “blocked” processes. These processes are most likely waiting for I/O but could be for something else too

w  -->  number of processes that can be run but have been swapped out to the swap area. This parameter gives hint about the memory bottleneck. Please remember, only some system reports this count

Memory Block: Provides detailed memory statistics

Swpd  -->  Amount of virtual memory or swapped memory used

Free  -->  Amount of free physical memory (RAM)

Buff  -->  Amount of memory used as buffers. This memory is used to store file metadata such as i-nodes and data from raw block devices

Cache  -->  amount of physical memory used as a cache (Mostly cached file).

Note: Most of the systems report memory block value in KB. Remember I said most; not all. So check the man page.

 

Swap Block: Provided memory swap information

si  -->  Rate at which the memory is swapped back from the disk to the physical RAM

so  -->  Rate at which the memory is swapped out to the disk from physical RAM

Note: Most of the systems report swap block value in KB. Check man page

I/O Block: I/O related information

bi  -->  Rate at which the system reads the data from block devices (in blocks/sec)

bo  -->  Rate at which the system sends data to the block devices (in blocks/sec)

System Block: System information

in  -->  Number of interrupts received by the system per second

cs  -->  Rate of context switching in the process space (in number/sec)

CPU block: Most used CPU related information

Us  -->  Shows the percentage of CPU spent in user processes. Most of the user/application/database processes come under the user processes category

Sy  -->  Percentage of CPU used by system processes, such as all root/kernel processes

Id  -->  Percentage of free CPU

Wa  -->  Percentage spent in “waiting for I/O”

A lot of people have problems here. Some people say us + sy +id + wa=100 and some other says us + sy +id =100. I stick to second (I/O doesn’t consume CPU). 

Interpretation with respect to performance:

The first line of the output is an average of all the metrics since the system was restarted. So, ignore that line since it does not show the current status. The other lines show the metrics in real-time.

Ideally, r/b/w values under procs block with close to 0 or 0 itself. If one or value counter values are constantly reporting high values, it means that system may not have sufficient CPU or Memory or I/O bandwidth.

If the value of swpd under swap is too high and it keeps changing, it means that system is running short of memory.

The data under “io” indicates the flow of data to and from the disks. This shows how much disk activity is going on, which does not necessarily indicate some problem(obviously data has to go to disk in order to be persistent). If we see some large number under “proc” and then “b” column (processes being blocked) and high I/O, the issue could be an I/O contention.

Rule of thumb in Performance

Adding more CPU, Memory, or I/O bandwidth to the system is not the solution to the problem always; this is just postponing of the problem to the future and it can blow anytime. The real solution is to tune the application(every component in the architecture) as far as possible. Adding hardware capacity or buying powerful hardware should be the last option.

As usual, comments are always welcome.

-Thiru

Thanks to Anonymous for pointing out the issue in bi/bo.

High Performance Oracle Database Export using Data Pump

Oracle offers high performance database not only in DDL and DML. Not only this that, but also in data backup and recovery. Oracle 10g offers new export/import utilities called data dump. The biggest advance of using data dump is it can push the system’s hardware capabilities to roof to achieve performance.

Oracle Data Pump is the replacement for the original Export and Import utilities. Available starting in Oracle Database 10g, Oracle Data Pump enables very high-speed movement of data and metadata from one database to another.

The Data Pump Export and Import utilities have a similar look and feel to the original utilities, but they are much more efficient and give you greater control and management of your import and export jobs.

Unlike original export/import utilities, data pump runs as jobs primarily on the server using server processes and stores the data in database server itself avoiding network latency(Except db link).

Data pump utilities uses direct file manipulation on the directory objects which helps us to achieve the speed. Since directory object can be managed from Oracle, security can be controlled by DBA.

Sample Practical example for export using data pump (expdp)

1. Creating directory 

Data pump works on direct file manipulation on database object directory. So before invoking data pump utilities, we need to create directory object and grant required permission. See the sample below.

create directory hr_backup_dir as '/u02/dbbackup';

grant read, write on directory hr_backup_dir to scott;

2. decide on parallelism

If you the whole system resource is dedicated to database backup, then PARALLEL can be set to number of processor available in that system. If you want to assign only limited processor for backup, then set PARALLEL count accordingly. Based on the PARALLEL count, data dump will span that many number of child Oracle processes.

Parallelism also depends on the number of DUMPFILE specified in expdp. Data pump thread needs exclusive access to DUMPFILE. So if you specify PARALLEL to 8 and give only 1 DUMPFILE, then only 1 data pump process will do export and all other processes will wait for first process to finish and release the dump file. So plan for both PARALLEL and DUMPFILE parameter.

3. run expdp

Here we go

expdp hr/hrworks SCHEMAS=HR DIRECTORY= hr_backup_dir DUMPFILE=hr.thiru.10-feb-2009%U.dmp LOGFILE=hr.thiru.10-feb-2009.log PARALLEL=8 JOB_NAME=HRDataPumpByThiru

If you specify %U in DUMPFILE, data pump will automatically create required number of files (most probably number of files created will be equal to PARALLEL).

4. check log files

Once export got completed, we can check the details in hr.thiru.10-feb-2009.log file.

Interactive Mode

While data pump export operation is going, user can press ctrl + c; it will stop displaying message in screen, but it will not stop the export operation. Instead it display Export prompt which is interactive. From here user can query to see the status of export operation and also alter data pump parameters such as parallel and so on.

Database Monitoring

DBA or any other user have permission can view the status of the export operation from the database views, too. The main view to monitor the jobs is DBA_DATAPUMP_JOBS, which tells how many worker processes (column DEGREE) are working on the job. The other view that is important is DBA_DATAPUMP_SESSIONS, which when joined with the previous view and V$SESSION gives the SID of the session of the main foreground process.

select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;

For more information refer Oracle documentation here

Post your valuable feedback.

Statspack

I have worked in databases including IBM's DB2 (infact I am at IBM certified DB2 DBA for version 8.1), Open source MYSQL, Oracle (From Version 8). As a performance tester, I used to wonder how Oracle along can provide such wide range of monitoring facility that no other vendor is able to match. From System wide monitoring using statspack/AWR to session level trace, Oracle's monitoring capability is amazing. This article I am going to cover couple of basic things about Oracle’s very own monitoring utility Statspack. Statspack is the build in tool and installation script comes along with the database itself. (No need to pay even an extra penny from your pocket). All you need is just to install the same and start using the same.

Quote from Oracle Database Documentation about statspack:

"The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters."

Installation:

To invoke the Statspack setup, all you need to do is call the spcreate.sql script which is available in ORACLE_HOME/rdbms/admin. PERFSTAT user own all PL/SQL code, database objects including tables, sequence, constrains etc., In windows, you can login into Oracle user (using SQL* Plus) which has enough privilege to install and run the following 

%ORACLE_HOME%\rdbms\admin\spcreate

In Linux/Unix OS, run the following

$ORACLE_HOME/rdbms/admin/spcreate

During installation it will ask for PERFSTAT schema’s password (usually people use perfstat as password), permanent tables and temporary tablespace. SPCREATE.SQL install script in turn automatically calls the following scripts.

SPCUSR.SQL: Creates the PERFSTAT user and grants privileges required to collect the performance data from V$ tables.

SPCTAB.SQL: Creates the tables which are going to stores performance data.

SPCPKG.SQL: Creates the package required for monitoring, data purging, reporting

Installation script dumps errors (if any) into SPCUSR.LIS, SPCTAB.LIS, and SPCPKG.LIS output files. 

How it works:

Snapshot of database’s performance was taken, stored in the PERFSTAT tables and will be assigned a unique SNAP_ID for the INSTANCE. Typically we can take snapshots for every pre-defined interval and between snapshots, we can generate performance report. If instance is restarted between snapshots, then reports will be meaning less. Snapshot can be taken at various levels depending up level of monitoring data required. Snapshot level can range from 1 to 10. Default level will be 5. More the level, more performance data (consumes bit high resource also).

Taking snapshot

Login as PERFSTAT user or user which can has execute privilege on statspack package and call statspack.snap function. Few examples.

exec statspack.snap

exec statspack.snap(I_SNAP_LEVEL=>7)

Statspack report

Statspack report will give instance-wide statistics between two snapshots. Between snapshots, we can generate reports (but if instance is restarted between snapshot, report will not be meaningful). Just call SPREPORT.SQL and provide begin SNAP_ID, end SNAP_ID and report file name. 

Oracle 10g and above has come up with something (feature which combines Statspack & ADDM) called Automatic Workload Repository(AWR). But statspack is still supported.

Introducing Measurement Lab

When an Internet application doesn't work as expected or your connection seems flaky, how can you tell whether there is a problem caused by your broadband ISP, the application, your PC, or something else?

So Google and some other folks found something called Measurement Lab to learn more about the broarband, ISP etc of broadband networks. Measurement Lab (M-Lab) is an open, distributed server platform for researchers to deploy Internet measurement tools. The goal of M-Lab is to advance network research and empower the public with useful information about their broadband connections. By enhancing Internet transparency, M-Lab helps sustain a healthy, innovative Internet.

Researchers are already developing tools that allow users to, among other things, measure the speed of their connection, run diagnostics, and attempt to discern if their ISP is blocking or throttling particular applications. These tools generate and send some data back-and-forth between the user's computer and a server elsewhere on the Internet. Unfortunately, researchers lack widely-distributed servers with ample connectivity. This poses a barrier to the accuracy and scalability of these tools. Researchers also have trouble sharing data with one another.

Over the course of early 2009, Google will provide researchers with 36 servers in 12 locations in the U.S. and Europe. All data collected via M-Lab will be made publicly available for other researchers to build on. M-Lab is intended to be a truly community-based effort, and we welcome the support of other companies, institutions, researchers, and users that want to provide servers, tools, or other resources that can help the platform flourish.

Wanna test, check some tools listed on measurement lab? Click here.

To know reason for why people/researchers look for these kind of tools, click here.

Sun Management Center - Sun's Ways of monitoring

"Sun Management Center", a product from Sun Microsystems for monitoring the Spark and x86 hardware running Solaris and Linux. It provides in-depth monitoring and diagnostics of servers and its services. Sun MC is based on server-agent model.

Architecture
Sun Management Center software includes three component layers: console, server, and agent. The product is based on the manager and agent architecture:

Console layer: Console layer is the interface to end users. It exposes web, JWS and console interfaces. Mutiple user can access the same Sun MC at the same time.

Server layer: server is the core, which talks to Console layer and agent layer. It acts as the central repository and stores data(both historical and current). It includes the components such as configuration manager, event manager, topology manager etc., Sun Management 4.0 uses PostgreSQL(open source) db to store data whereas the previous version 3.6 uses Oracle to store data.

Agent layer: Agent layer monitors, gather information about the server/system in which it is deployed and it communicates from server using SNMP(modules are used for gathering monitoring data. Different modules are used for different purposes). Agent apart from monitoring, also has the cabability to manage the nodes. The agent uses rule (it will get from server layer) to raise the alarm if the rule is not met.


Modules: Modules are the components in agent layer responsible for monitoring. They can dynamically loaded, invoked, started, stopped and unisntalled in Sun MC. Kernal reader, file scanning, directory scanning, config reader, fault manager, print spooler, process monitoring are some of the modules.

Like nmon, Sun MC is free to download and use (you can pay and get support). Like Glance for monitoring HP machines, Sun MC can be used to monitor the Sun based systems. Next time when you are planning to do performance testing, tuning on Spack or x86 hardware running Solaris, try Sun MC.

End User Response Time Vs Tool Response Time

In olden days browser is more of display tool and for heavy client side processing, thick clients were used. As time and technology evolved, things have changed and current browser itself is more like think client. As the client side logic becoming heavy, obviously client side processing time has to be added to server side response time to get end user response time.

The Truth about performance testing tool

In modern day internet/web application, response time can be computed as follows

Response time = server side response time + network delay + client side processing time + rendering time

Where as our load testing tools, computes response time as follows.

Response time = server side response time + network delay.

Since client side logic is heavy in modern day web/internet application, most of the tools available today are really missing out the significant portion in response time.

Adding fuel to fire (Java Script Engine)

Because Java Script is so important to the web today, no one can even imagine a web application without java script. A JavaScript engine (also known as JavaScript interpreter or JavaScript implementation) is an interpreter that interprets JavaScript source code and executes the script accordingly in client’s browser. In real, java script execution time by Java Script Engine is also added to end user response time. To name few Spider monkey, V8 are couple of open source java script engine.

Even if some tools has ability (like Loadrunner click & script), it is very limited to a particular browser and particular java script engine.

More Fuel

Even if a tool is able to makeup the java script's response time, calculating full response time by tool has exception. PLUGINS. It is practically impossible for a tool vendor to work with all plugins available. Java and flash are the commonly used plugins in lot of web sites.

Just want to quote an example

In one of my previous client environment, single user test for a particular page click takes around 50 sec, whereas the loadrunner @ 1000 user load reported the max response time as 10 sec. After seeing the result, client has called in for a meeting and they demonstrated his portion and proved that in real, that particular page takes much longer time to view. To stick to our result, we have done a sniffing of that request using network sniffer and showed that client side JS doing some nasty calculation is the culprit not the server. Also we explained them that, whatever loadrunner gives are the server side response time not the end user response time.

So sad

Even if performance testing tool vendors wants to include client side response time, it is very difficult for them as whole lot of java script engine out in market with various market shares.

Even tough for some, it is hard to believe that server side response time and end user response time are very different, we have to accept(as it is fact).