Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, and Oracle Developers (Medium). Moreover, he has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Paul is based from a global delivery center in South East Asia but has clients from Australia, Europe, Asia, and North America. Paul Guerin is an international consultant that specializes in Oracle Database. I say that SQL Developer should be renamed the SQL Dash boarder! About the Author There you are! For dashboards, you don’t need to look much farther than SQL Developer. Those percentages are comparing the utilization of the allocated file, and don’t consider files that can auto-extend.Ī file that can auto-extend will often have a utilization close to 100%. In the above screenshot, there are utilization percentages against the data files, temporary files, and undo files. The utilization metrics are really comparing the allocated file size with the usage size within the file. There are also indicators for the storage capacity of 3 types of files: The second dashboard is for storage capacity, and there are workload graphs of: The memory capacity dashboard indicates the level of workload being experienced by the database. The memory capacity dashboard gives a snapshot of: There are two capacity summaries: one for memory, and another for storage. One day, if a critical SQL statement uses, say, 10k I/O over 10 executions for an average of 1k I/O per execution, then the next day the average is 100k then that would be cause for further investigation because the efficiency is less.Ĭursor performance is most helpful for applications that are transactional in nature. If there are several SQL statements that are critical and are the most used, then it’s easy to calculate a performance metric. In a transactional application, cursors should be reused by different sessions when possible. There are also accumulated metrics for the top cursors (or SQL statements) still cached. The Cursor Dashboardįor overall cursor performance, the cursor dashboard shows the following metrics: Includes CPU time, IO time, and wait timeĪs for the accumulated wait summary, the DB CPU time ratio could also be used as a health check for an application that is transactional.Time spent in the database by foreground sessions.Then there is the DB CPU time ratio indicator which according to the official documentation are the database operations as a percentage of CPU activity. The accumulated wait summary can be used as a health check for an application that is transactional, where the lower the accumulated waits the better. This indicator is most relevant for applications that are transactional in nature.Īnother session indicator is the accumulated wait summary of all sessions. Users that are relying on a transaction to finish will experience extended delays while its session is blocked. In the screenshot above, the session view shows there are 11 sessions in total, and 3 of those are blocked. If a transaction locks an object (or part of an object), then another transaction in another session can be locked out.Ĭonsequently, the transaction that is locked out will indicate that the session is blocked. The session view in SQL Developer counts the total number of sessions, and the session total for: So, it’s useful to view the session status to determine if a transaction is impacting another transaction. The Session/Transaction IndicatorsĪ transaction occurs inside a session, but a transaction in one session can impact a transaction in another session. The basic health indicator is appropriate for applications that are either transactional, or data warehouse in nature. The most basic indicator gives just some simple details such as: Let’s examine the various indicators and dashboards available from the Instance Viewer. To get started, choose the Instance Viewer from Database Status from the DBA menu. To view the dashboards, the user account needs to be granted the SELECT_CATALOG_ROLE role. These dashboards are relevant for both transactional, and data warehouse environments. Not so well known, is that SQL Developer produces some amazing dashboards of the database and application operation. Oracle Database 19c is the world’s leading relational database, and it gets even better when combined with Oracle SQL Developer.Įveryone knows that SQL Developer is a great tool for creating world-class applications.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |