Database Performance And Tuning

Database performance and tuning By:Subhash Hegde

June 27th 2009

1

Market proposition   

$25 Billion/120,000 Crore industry Oracle leads the market share Unix/Linux lead the market as the hosting operating system

June 27th 2009

Karthavya technologies

2

Market share 15%

Oracle

1%

IBM-DB2 44% 20%

Microsoft-Sql server Mysql Others

20%

June 27th 2009

3

Hosting operating system

8%

7%

45%

Unix/Linux Windows Mainframe Others

40%

June 27th 2009

4

Oracle architecture

Shared Pool

Redo Log Buffer

Bufer Cache

Control File Datafile Redo Log init.ora Archived log file Password File DBWR

LGWR

LCKO

Alert log file Trace files

SGA

Instance June 27th 2009

Database 5

Tuning methodology- Road map      

Tuning Tuning Tuning Tuning Tuning Tuning

June 27th 2009

business rules Database design database operations I/O and memory allocation resource contention the underlying platform 6

Database indexing- How it works  

B tree structure Oracle decides to use index or table scan depending on its calculations.

June 27th 2009

7

Example: Performance boost achieved by indexing   



Search without index Search with index Performance comparison against a index on a column with less cardinality Tools used to verify query performance – sqlplus autotrace utility, sql developer etc.

June 27th 2009

8

Database indexing (cntd..) Best practices   







Index columns having maximum cardinality Limit the number of indexes on each table Create an index if you frequently want to retrieve less than 15% of the rows To improve performance on joins of multiple tables, index columns used for joins. The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first. Drop indexes on a table before migration

June 27th 2009

9

Prepared statements 

 



Can help increase security by separating SQL logic from the data being supplied Pre-prepares execution plan Not recommended for rarely used inserts. Hibernate uses prepared statements

June 27th 2009

10

Buzzwords / applications      

RBO (Rule based optimizer) CBO (Cost based optimizer) Enterprise manager RMAN (Recovery manager) Schema Tablespace

June 27th 2009

11

Case study- BLIS Issues faced and action taken 







BLIS database contains approximately 27 Lakh records Initial approach to attain search speed failed Database went down because of issues like row lock and index failures Data backup method

June 27th 2009

12

Questions?

June 27th 2009

13