Self-tuning database

Self
-
tuning
Database

Leili Farzinvash

Nazanin Dehghani


Electrical and Computer Engineering Department

2

Introduction

Self
-
tuning Database

2

3

Outline



Introduction


Characteristics of Autonomic DBMS


Self
-
Tuning Architecture


Alternative Tuning Models


Monitoring Infrastructure


Case studies

Self
-
tuning Database

3

4

Characteristics of Autonomic DBMS



Self
-
optimizing


Self
-
configuring


Self
-
healing


Self
-
protecting


Self
-
organizing


Self
-
inspecting


Self
-
tuning Database

4

5

Phases of Self
-
tuning Loop

Self
-
tuning Database

5

Diagnose

Resole

Observe

6

Self
-
Tuning Architecture

Self
-
tuning Database

6

7

Alternative Tuning Models


Alerter (When to Tune)

Workload as a
Sequence

Dynamic (Online)
Tuning

Self
-
tuning Database

7

8

Monitoring Infrastructure


Query Progress Estimation

Ad
-
hoc Monitoring and Diagnostics

Self
-
tuning Database

8

9

Case Studies

Self
-
tuning Database

9

10

SQL Server



Creating “what
-
if ” physical structures


Search Architecture


Alerter


Self tuning histogram


SQLCM




Self
-
tuning Database

10

11

Database Engine Tuning Advisor (DTA)

Self
-
tuning Database

11

12

Self tuning histogram

Self
-
tuning Database

12

13

DB2



Key Ideas and Themes


Low
-
impact collection of accurate system data


Feedback


Re
-
using Optimizer as a “What if?” tool


Heuristics, new models


Automating batch operation


Self
-
tuning Database

13

14

Future Works



Comparison the quality of automated physical design
solutions


Migration of “heavyweight” to “lightweight”


Distributed tuning and monitoring


Machine learning techniques, control theory, and online
algorithms

Self
-
tuning Database

15

References

1.
Elnaffar, S.; Powley, W.; Benoit, D.; Martin, P. Today's DBMSs: how autonomic are they. Proceedings of
the
14
th International Workshop on Database and Expert Systems Applications (DEXA’
03
),
1
-
5
Sept.
2003
IEEE, Pages:
651
–
655

2.
Surajit Chaudhuri, Vivek Narasayya : Self
-
Tuning Database Systems: A Decade of Progress. VLDB
’
07
, September
23
-
28
,
2007
, Vienna, Austria.

3.
Benoit Dageville ,Karl Dias. Oracle’s Self
-
Tuning Architecture and Solutions : Bulletin of the IEEE
Computer Society Technical Committee on Data Engineering,
2006
.

4.
B. Dageville, M. Zait: SQL Memory Management in Oracle
9
i. VLDB
2002
,
962
-
973
.

5.
Agrawal, S., Chaudhuri, S. and Narasayya, V. Automated Selection of Materialized Views and Indexes
for SQL Databases.
In Proceedings of the VLDB,
Cairo, Egypt,
2000
.
.

6.
Zilio et al. Recommending Materialized Views and Indexes with IBM DB
2
Design Advisor. In
Proceedings of ICAC
2004
.

7.
S.Parekh, K.R.Rose, J.L. Heller stein, S.Lightstone, M.Huras, and V.Chang. Throttling utilities in the
IBM DBS universal database server. In Amer. Control Conf. (ACC),
2004

8.
Eugene Krayzman, Development of self
-
tuning and autonomic databases and latest achievements:
21
st Computer Science Seminar SE
2
-
T
4
-
1

9.
Sanjay Agrawal, Nicolas Bruno, Surajit Chaudhuri, Vivek Narasayya. AutoAdmin: Self
-
Tuning
Database Systems Technology: Bulletin of the IEEE Computer Society Technical Committee on
Data Engineering,
2006
.

10.
Berni Schiefer, Gary Valentin. DB
2
Universal Database Performance Tuning : Bulletin of the IEEE
Computer Society Technical Committee on Data Engineering,
1999
.