1 / 17

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.

omer
Télécharger la présentation

DATABASE ADMINISTRATION

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DATABASE ADMINISTRATION Pertemuan ke-7

  2. Application Performancesource : Database Administrationthe complete guide to practices and procedureschapter 12by. Craig S. Mullins

  3. 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?

  4. 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?

  5. 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.

  6. Figure 12-1. Relational optimization

  7. 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

  8. SQL Coding and Tuning for Efficiency steps occur for each SQL statement • Identify the business data requirements. • Ensure that the required data is available within existing databases. • Translate the business requirements into SQL. • Test the SQL for accuracy and results. • Review the access paths for performance. • Tweak the SQL for better access paths. • Code optimization hints. • Repeat steps 4 through 7 until performance is acceptable. • Repeat step 8 whenever performance problems arise or a new DBMS version is installed. • Repeat entire process whenever business needs change.

  9. 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

  10. Rule 5: Avoid Cartesian Products • Rule 6: Judicious Use of OR

  11. Rule 7: Judicious Use of LIKE

  12. Rule 8: Know What Works Best • Rule 9: Issue Frequent COMMITs • Rule 10: Beware of Code Generators • Rule 11: Consider Stored Procedures

  13. 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.

  14. 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

  15. Tugas individu • Install Oracle • Buattabelmahasiswa (dengan query) • Lakukanproses insert data : data anda, dan data 1 orangtemananda

  16. Print screen instalasi dan query pembuatan dan insert data • Kirim soft copy format laporan ke : • tanto.tc04@gmail.com dengan judul : • tugas dba pertemuan 7 (NPM Nama kelas) Contoh : tugas dba pertemuan 7 (111100011 dwi kelas C )

  17. Terimakasih

More Related