Transcript database administration

DATABASE ADMINISTRATION
Pertemuan ke-7
Application Performance
source :
Database Administration
the complete guide to practices and procedures
chapter 12
by. Craig S. Mullins
Designing Applications for Relational Access
• Design issues to examine when application
performance suffers include
– Type of SQL. Is the correct type of SQL (planned or
unplanned, dynamic or static, embedded or stand-alone)
being used for this particular application?
– Programming language. Is the programming language
capable of achieving the required performance, and is the
language environment optimized for database access?
– Transaction design and processing. Are the transactions
within the program properly designed to assure ACID
(atomicity, consistency, isolation, and durability ) properties,
and does the program use the transaction processor of
choice appropriately and efficiently?
Designing Applications for Relational Access
• Locking strategy. Does the application hold the wrong type of
locks, or does it hold the correct type of locks for too long?
• COMMIT strategy. Does each application program issue SQL
COMMIT statements to minimize the impact of locking?
• Batch processing. Are batch programs designed
appropriately to take advantage of the sequential processing
features of the DBMS?
• Online processing. Are online applications designed to return
useful information and to minimize the amount of information
returned to the user's screen for a single invocation of the
program?
Relational Optimization
• The optimizer is the heart of a relational database
management system.
• It is an inference engine responsible for determining the best
possible database navigation strategy for any given SQL
request.
• The application developer specifies what data is needed by
coding the SQL statements, the DBMS supplies information
about where the data is located, and the relational optimizer
decides how to efficiently navigate the database.
• The end user needs no knowledge of where and how the
actual data is stored. The optimizer knows this information.
Figure 12-1. Relational optimization
• Every RDBMS has an embedded relational
optimizer that renders SQL statements into
executable access paths.
• each vendor's relational optimizer works a
little differently, with different steps and using
different information.
• the core of the process is the same from
DBMS to DBMS.
• The optimizer parses the SQL statement and
performs various phases of optimization
SQL Coding and Tuning for Efficiency
steps occur for each SQL statement
1.
2.
Identify the business data requirements.
Ensure that the required data is available within existing
databases.
3. Translate the business requirements into SQL.
4. Test the SQL for accuracy and results.
5. Review the access paths for performance.
6. Tweak the SQL for better access paths.
7. Code optimization hints.
8. Repeat steps 4 through 7 until performance is acceptable.
9. Repeat step 8 whenever performance problems arise or a new
DBMS version is installed.
10. Repeat entire process whenever business needs change.
SQL Rules of Thumb
• Rule 1: "It Depends!"
– A successful DBA will know on what it depends.
• Rule 2: Be Careful What You Ask For
– Place the most restrictive predicate where the optimizer can
read it first.
• Rule 3: KISS
– Keep it simple, Stupid.
• Rule 4: Retrieve Only What Is Needed
• Rule 5: Avoid Cartesian Products
• Rule 6: Judicious Use of OR
• Rule 7: Judicious Use of LIKE
•
•
•
•
Rule 8: Know What Works Best
Rule 9: Issue Frequent COMMITs
Rule 10: Beware of Code Generators
Rule 11: Consider Stored Procedures
Additional SQL Tuning Tips
• Use indexes to avoid sorting.
• Create indexes to support troublesome queries.
• Whenever possible, do not perform arithmetic in SQL
predicates. Use the host programming language (Java,
COBOL, C, etc.) to perform arithmetic.
• Use SQL functions to reduce programming effort.
• Build proper constraints into the database to minimize coding
edit checks.
• Do not forget about the "hidden" impact of triggers. A delete
from one table may trigger many more operations. Although
you may think the problem is a poorly performing DELETE, the
trigger may be the true culprit.
Summary
• Application performance management and SQL tuning is a
complex area that requires the active participation of
programmers and DBAs.
• Each DBMS operates differently, and DBAs as well as
programmers will need to understand all of the minute details
of SQL and application performance management for their
DBMS.
• The relational optimizer combines access path strategies to
form an efficient access path for each SQL request.
• the optimizer is a very complex piece of software, and the
DBMS vendors do not share with their customers all the
intricacies of how the optimizer works.
• SQL performance tuning becomes an iterative artistic process,
instead of a science
Tugas individu
1.
2.
3.
Install Oracle
Buat tabel mahasiswa (dengan query)
Lakukan proses insert data : data anda, dan data 1 orang
teman anda
Kolom
Type
NULL
Default
Extra
Mhs_npm
integer (PK)
NOT NULL
Mhs_nama
Varchar(255)
NOT NULL
Mhs_thn_masuk
Integer(4)
NULL
-
Mhs_asal_kota
Varchar(100)
NULL
-
Mhs_catatan
Text
NULL
auto increment
(PK)
Mahasiswa
-
-
-
• Print screen instalasi dan query pembuatan
dan insert data
• Kirim soft copy format laporan ke :
• [email protected] dengan judul :
• tugas dba pertemuan 7 (NPM Nama kelas)
Contoh :
tugas dba pertemuan 7 (111100011 dwi kelas C )
Terima kasih