Oracle took on the challenge of ever-increasing management complexity for the database server beg... more Oracle took on the challenge of ever-increasing management complexity for the database server beginning some eleven years ago with Oracle 10g and continuing through the 11g release. The Oracle RDBMS offers numerous services and features that share infrastructure and resources to enable a rich portfolio of industry-leading data management capabilities. Oracle Database Server Manageability refers to a cross-organization effort across various RDBMS core teams to deliver a coherent feature suite that enables customers to manage an Oracle database with consistency, confidence and relative ease through the Enterprise Manager UI. Our efforts to improve database manageability can be classified into three levels of increasing sophistication: • Statistics and reporting level-where key workload performance statistics and run-time data are continuously and efficiently collected by default, persisted into a self-managing repository, and exposed through reports and interactive UI • Advisory level -where instrumentation level performance data are periodically analyzed using a time-based methodology that makes findings and recommendations about sub-optimal performance, its root cause, and opportunities for improvement • Automated implementation level -where advisory level recommendations that can be implemented with low and well-understood risk are automatically put in place (given prior consent of administrators) Each layer builds upon the ones below it, and each plays a part in satisfying the key customer use cases -it is only through exposing features at all three levels that the solution becomes complete. In this paper we will discuss our key offerings at each level, review motivating use cases and lessons learned, and conclude by looking forward to some problems that excite us in the near future.
We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing datab... more We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing database changes, such as upgrades, parameter changes, schema changes, and gathering optimizer statistics. The SQL Performance Analyzer offers a comprehensive solution to enable users to forecast and analyze how a system change will impact SQL query plans and run time performance, so they can tune their system before they make the change in production. The SQL Performance Analyzer identifies potential problems that may occur and makes suggestions for avoiding any SQL performance degradation. It provides quantitative estimates of the system's performance in the new environment with high confidence and performs a comparative analysis of the response time of the SQL workload thus allowing for an easy assessment of the change. In this paper we describe the architecture of the SQL Performance Analyzer, its usage model, and its integration points with other Oracle database components to form an end-to-end change management solution.
Abstract Commercial database customers across the board list SQL performance tuning as one of the... more Abstract Commercial database customers across the board list SQL performance tuning as one of the most time-consuming tasks for database administrators (DBAs). The 10g Oracle Database provides a feature called the SQL tuning advisor to simplify the task. The 11g ...
Un site Web a usage intensif de donnees (SWID) est un site Web qui gere un nombre important de pa... more Un site Web a usage intensif de donnees (SWID) est un site Web qui gere un nombre important de pages dont le contenu est construit dynamiquement, a partir de grandes bases de donnees. C'est a dire que l'ensemble des pages Web constituant le site n'est pas construit et stocke une fois pour toutes, sur le serveur Web, pour etre utilise ; mais au contraire, les pages Web sont regenerees dynamiquement lorsque le site est parcouru par les utilisateurs. . Cette creation demande d'extraire des donnees d'une base de donnees et de construire les pages HTML correspondantes. Dans ce contexte, la demande d'une page par un client peut n"cessiter une interaction couteuse avec le systeme de gestion de base de donnees, pour la connexion a la base et l'execution des requetes necessaires a la recuperation des donnees, risquant ainsi d'augmenter considerablement le temps d'attente du client. Dans cette these nous adressons ce probleme de performance en nous appuyant sur la specification declarative de sites Web. Nous proposons une architecture configurable de caches a plusieurs niveaux et sa mise en oeuvre dans le cadre de Weave, un systeme de gestion de sites Web developpe a l'INRIA. Dans notre approche, il est possible de cacher des donnees extraites d'une base de donnees (sous forme de vues materialisees), des fragments XML et/ou des pages HTML. Pour specifier et personnaliser differentes strategies de caching, nous offrons un langage de haut niveau, appele WeaveRPL. Nous developpons aussi une infrastructure de base permettant de compiler une specification declarative d'un site Web et de generer automatiquement une strategie de caching optimale. Nous illustrons notre approche a l'aide d'un site Web construit a partir de la base de donnees TCP/D. Puis, nous evaluons experimentalement, les performances de differentes strategies de caching en utilisant notre plate-forme de test WeaveBench. Les resultats obtenus montrent clairement qu'une strategie mixte est generalement la meilleure.
this paper we show that exploiting the structure of a Web site can lead to signicant savings over... more this paper we show that exploiting the structure of a Web site can lead to signicant savings over and above the application of the known techniques mentioned above. Our nal goal is to automatically compile a declarative specication into a run-time policy, which decides which actions to perform and which queries to evaluate depend1 ing on the browsing history. Run-time policies should be able to express several traditional optimizations, such as view materialization and data caching, and novel optimizations that depend on the structure of the web site, such as optimization under preconditions and lookahead computation that we present in this paper.
Beginning in the June Bulletin, and continuing with the September issue, I have been keeping you ... more Beginning in the June Bulletin, and continuing with the September issue, I have been keeping you abreast of a proposed change in how a part of the IEEE Computer Society governs itself. In particular, a governance subcommittee of the Technical Activities Committee (TAC) was formed to propose changes in how its chair is selected. All chairs of Technical Committees (TCs) are members of this committee, and I, as chair of the TC on Data Engineering thus attend its meetings. Under the current rules, the chair is appointed, but under the proposal from the governance committee, the chair would be elected by TAC members. I am happy to report that the TAC members have now voted to approve this proposal. So now the proposal goes forward to the Computer Society Board of Governors. We are hopeful that they will approve, and if so, the proposal becomes the new way of choosing the TAC chair. I belonged to the subcommittee and am a firm believer in the proposed change, as it gives TCs a way to have their views more effectively expressed, both in our TAC meeting and because the TAC chair represents the TCs at higher levels of the Computer Society. So we are not done yet, but progress has been made. Stay tuned. The Current Issue Database systems have been notoriously cranky to deal with, requiring skilled professionals to manage and keep tuned. The research into self-tuning database systems, while not eliminating all such manual involvement, reduced the level of effort considerably. This has permitted the classic database market to service much smaller enterprises than it had earlier. And, of course, it made life simpler for many more enterprises, large and small. This "self tuning" research has been going on now for over ten years. The result has been that the "friendliness" of databases has improved greatly. But it is still the case that databases can be a challenge to deal with. So interest in the field has remained high, leading to the establishment, a few years back, of the Data Engineering Workgroup on Self-Managing Database Systems within the Technical Committee on Data Engineering. The current issue has been assembled by a pair of editors from the workgroup, Shivnath Babu and Kai-Uwe Sattler. They know well the self-managing databases research community, the researchers, the practitioniers, and their work, and they have successfully brought together a blend of papers that reflect this diversity, with a heavier dose of industrial participation than usual. This is a reflection that this topic has commercial importance to the database industry, and hence is the kind of issue that might only come from the Data Engineering Bulletin. I want to thank Shivnath and Kai-Uwe for their efforts in bringing this fine issue to fruition.
A data-intensive Web site is a Web server that accesses large numbers of pages whose content is d... more A data-intensive Web site is a Web server that accesses large numbers of pages whose content is dynamically extracted from a database. In this context, returning a Web page may require costly interaction with the database system (for connection and querying) thereby increasing much the response time. In this paper, we address this performance problem. Our approach relies on the declarative specification of the Web site. We propose a customizable cache system architecture and its implementation, in the context of the Weave Web site management system developed at Inria. The system can cache database data, XML fragments or HTML files. We illustrate it using a Web site derived from the TPC/D benchmark database. Based on experiments using our test platform WeaveBench, we assess the performance of various caching strategies. The results clearly show that a mixed strategy is generally close to optimal (static files). Finally, we derive guidelines for specifying cache management based on th...
Un site Web a usage intensif de donnees (SWID) est un site Web qui gere un nombre important de pa... more Un site Web a usage intensif de donnees (SWID) est un site Web qui gere un nombre important de pages dont le contenu est construit dynamiquement, a partir de grandes bases de donnees. C'est a dire que l'ensemble des pages Web constituant le site n'est pas construit et stocke une fois pour toutes, sur le serveur Web, pour etre utilise ; mais au contraire, les pages Web sont regenerees dynamiquement lorsque le site est parcouru par les utilisateurs. . Cette creation demande d'extraire des donnees d'une base de donnees et de construire les pages HTML correspondantes. Dans ce contexte, la demande d'une page par un client peut n"cessiter une interaction couteuse avec le systeme de gestion de base de donnees, pour la connexion a la base et l'execution des requetes necessaires a la recuperation des donnees, risquant ainsi d'augmenter considerablement le temps d'attente du client. Dans cette these nous adressons ce probleme de performance en nous app...
An increasing number of web sites have their data extracted from relational databases. Several co... more An increasing number of web sites have their data extracted from relational databases. Several commercial products and research prototypes have been moving in the direction of declarative specification of the structure and content of sites. Specifically, the entire site is specified using a collection of queries describing the site's nodes (corresponding to web pages and the data contained in them) and edges (corresponding to the hyperlinks). Given this paradigm, an important issue is when to compute the site's pages. In one extreme approach, the site is precomputed in advance, while in the other extreme, the queries necessary to construct a given page are computed on demand. Both approaches have their obvious drawbacks: large space and maintenance overhead in the first approach, and poor run-time performance and unnecessary repeated computations in the second. In this paper we consider the problem of automatically optimizing the run-time management of declaratively specifie...
We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing datab... more We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing database changes, such as upgrades, parameter changes, schema changes, and gathering optimizer statistics. The SQL Performance Analyzer offers a comprehensive solution to enable users to forecast and analyze how a system change will impact SQL query plans and run time performance, so they can tune their system before they make the change in production. The SQL Performance Analyzer identifies potential problems that may occur and makes suggestions for avoiding any SQL performance degradation. It provides quantitative estimates of the system's performance in the new environment with high confidence and performs a comparative analysis of the response time of the SQL workload thus allowing for an easy assessment of the change. In this paper we describe the architecture of the SQL Performance Analyzer, its usage model, and its integration points with other Oracle database components to form an end-to-end change management solution.
Oracle took on the challenge of ever-increasing management complexity for the database server beg... more Oracle took on the challenge of ever-increasing management complexity for the database server beginning some eleven years ago with Oracle 10g and continuing through the 11g release. The Oracle RDBMS offers numerous services and features that share infrastructure and resources to enable a rich portfolio of industry-leading data management capabilities. Oracle Database Server Manageability refers to a cross-organization effort across various RDBMS core teams to deliver a coherent feature suite that enables customers to manage an Oracle database with consistency, confidence and relative ease through the Enterprise Manager UI. Our efforts to improve database manageability can be classified into three levels of increasing sophistication: • Statistics and reporting level-where key workload performance statistics and run-time data are continuously and efficiently collected by default, persisted into a self-managing repository, and exposed through reports and interactive UI • Advisory level -where instrumentation level performance data are periodically analyzed using a time-based methodology that makes findings and recommendations about sub-optimal performance, its root cause, and opportunities for improvement • Automated implementation level -where advisory level recommendations that can be implemented with low and well-understood risk are automatically put in place (given prior consent of administrators) Each layer builds upon the ones below it, and each plays a part in satisfying the key customer use cases -it is only through exposing features at all three levels that the solution becomes complete. In this paper we will discuss our key offerings at each level, review motivating use cases and lessons learned, and conclude by looking forward to some problems that excite us in the near future.
We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing datab... more We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing database changes, such as upgrades, parameter changes, schema changes, and gathering optimizer statistics. The SQL Performance Analyzer offers a comprehensive solution to enable users to forecast and analyze how a system change will impact SQL query plans and run time performance, so they can tune their system before they make the change in production. The SQL Performance Analyzer identifies potential problems that may occur and makes suggestions for avoiding any SQL performance degradation. It provides quantitative estimates of the system's performance in the new environment with high confidence and performs a comparative analysis of the response time of the SQL workload thus allowing for an easy assessment of the change. In this paper we describe the architecture of the SQL Performance Analyzer, its usage model, and its integration points with other Oracle database components to form an end-to-end change management solution.
Abstract Commercial database customers across the board list SQL performance tuning as one of the... more Abstract Commercial database customers across the board list SQL performance tuning as one of the most time-consuming tasks for database administrators (DBAs). The 10g Oracle Database provides a feature called the SQL tuning advisor to simplify the task. The 11g ...
Un site Web a usage intensif de donnees (SWID) est un site Web qui gere un nombre important de pa... more Un site Web a usage intensif de donnees (SWID) est un site Web qui gere un nombre important de pages dont le contenu est construit dynamiquement, a partir de grandes bases de donnees. C'est a dire que l'ensemble des pages Web constituant le site n'est pas construit et stocke une fois pour toutes, sur le serveur Web, pour etre utilise ; mais au contraire, les pages Web sont regenerees dynamiquement lorsque le site est parcouru par les utilisateurs. . Cette creation demande d'extraire des donnees d'une base de donnees et de construire les pages HTML correspondantes. Dans ce contexte, la demande d'une page par un client peut n"cessiter une interaction couteuse avec le systeme de gestion de base de donnees, pour la connexion a la base et l'execution des requetes necessaires a la recuperation des donnees, risquant ainsi d'augmenter considerablement le temps d'attente du client. Dans cette these nous adressons ce probleme de performance en nous appuyant sur la specification declarative de sites Web. Nous proposons une architecture configurable de caches a plusieurs niveaux et sa mise en oeuvre dans le cadre de Weave, un systeme de gestion de sites Web developpe a l'INRIA. Dans notre approche, il est possible de cacher des donnees extraites d'une base de donnees (sous forme de vues materialisees), des fragments XML et/ou des pages HTML. Pour specifier et personnaliser differentes strategies de caching, nous offrons un langage de haut niveau, appele WeaveRPL. Nous developpons aussi une infrastructure de base permettant de compiler une specification declarative d'un site Web et de generer automatiquement une strategie de caching optimale. Nous illustrons notre approche a l'aide d'un site Web construit a partir de la base de donnees TCP/D. Puis, nous evaluons experimentalement, les performances de differentes strategies de caching en utilisant notre plate-forme de test WeaveBench. Les resultats obtenus montrent clairement qu'une strategie mixte est generalement la meilleure.
this paper we show that exploiting the structure of a Web site can lead to signicant savings over... more this paper we show that exploiting the structure of a Web site can lead to signicant savings over and above the application of the known techniques mentioned above. Our nal goal is to automatically compile a declarative specication into a run-time policy, which decides which actions to perform and which queries to evaluate depend1 ing on the browsing history. Run-time policies should be able to express several traditional optimizations, such as view materialization and data caching, and novel optimizations that depend on the structure of the web site, such as optimization under preconditions and lookahead computation that we present in this paper.
Beginning in the June Bulletin, and continuing with the September issue, I have been keeping you ... more Beginning in the June Bulletin, and continuing with the September issue, I have been keeping you abreast of a proposed change in how a part of the IEEE Computer Society governs itself. In particular, a governance subcommittee of the Technical Activities Committee (TAC) was formed to propose changes in how its chair is selected. All chairs of Technical Committees (TCs) are members of this committee, and I, as chair of the TC on Data Engineering thus attend its meetings. Under the current rules, the chair is appointed, but under the proposal from the governance committee, the chair would be elected by TAC members. I am happy to report that the TAC members have now voted to approve this proposal. So now the proposal goes forward to the Computer Society Board of Governors. We are hopeful that they will approve, and if so, the proposal becomes the new way of choosing the TAC chair. I belonged to the subcommittee and am a firm believer in the proposed change, as it gives TCs a way to have their views more effectively expressed, both in our TAC meeting and because the TAC chair represents the TCs at higher levels of the Computer Society. So we are not done yet, but progress has been made. Stay tuned. The Current Issue Database systems have been notoriously cranky to deal with, requiring skilled professionals to manage and keep tuned. The research into self-tuning database systems, while not eliminating all such manual involvement, reduced the level of effort considerably. This has permitted the classic database market to service much smaller enterprises than it had earlier. And, of course, it made life simpler for many more enterprises, large and small. This "self tuning" research has been going on now for over ten years. The result has been that the "friendliness" of databases has improved greatly. But it is still the case that databases can be a challenge to deal with. So interest in the field has remained high, leading to the establishment, a few years back, of the Data Engineering Workgroup on Self-Managing Database Systems within the Technical Committee on Data Engineering. The current issue has been assembled by a pair of editors from the workgroup, Shivnath Babu and Kai-Uwe Sattler. They know well the self-managing databases research community, the researchers, the practitioniers, and their work, and they have successfully brought together a blend of papers that reflect this diversity, with a heavier dose of industrial participation than usual. This is a reflection that this topic has commercial importance to the database industry, and hence is the kind of issue that might only come from the Data Engineering Bulletin. I want to thank Shivnath and Kai-Uwe for their efforts in bringing this fine issue to fruition.
A data-intensive Web site is a Web server that accesses large numbers of pages whose content is d... more A data-intensive Web site is a Web server that accesses large numbers of pages whose content is dynamically extracted from a database. In this context, returning a Web page may require costly interaction with the database system (for connection and querying) thereby increasing much the response time. In this paper, we address this performance problem. Our approach relies on the declarative specification of the Web site. We propose a customizable cache system architecture and its implementation, in the context of the Weave Web site management system developed at Inria. The system can cache database data, XML fragments or HTML files. We illustrate it using a Web site derived from the TPC/D benchmark database. Based on experiments using our test platform WeaveBench, we assess the performance of various caching strategies. The results clearly show that a mixed strategy is generally close to optimal (static files). Finally, we derive guidelines for specifying cache management based on th...
Un site Web a usage intensif de donnees (SWID) est un site Web qui gere un nombre important de pa... more Un site Web a usage intensif de donnees (SWID) est un site Web qui gere un nombre important de pages dont le contenu est construit dynamiquement, a partir de grandes bases de donnees. C'est a dire que l'ensemble des pages Web constituant le site n'est pas construit et stocke une fois pour toutes, sur le serveur Web, pour etre utilise ; mais au contraire, les pages Web sont regenerees dynamiquement lorsque le site est parcouru par les utilisateurs. . Cette creation demande d'extraire des donnees d'une base de donnees et de construire les pages HTML correspondantes. Dans ce contexte, la demande d'une page par un client peut n"cessiter une interaction couteuse avec le systeme de gestion de base de donnees, pour la connexion a la base et l'execution des requetes necessaires a la recuperation des donnees, risquant ainsi d'augmenter considerablement le temps d'attente du client. Dans cette these nous adressons ce probleme de performance en nous app...
An increasing number of web sites have their data extracted from relational databases. Several co... more An increasing number of web sites have their data extracted from relational databases. Several commercial products and research prototypes have been moving in the direction of declarative specification of the structure and content of sites. Specifically, the entire site is specified using a collection of queries describing the site's nodes (corresponding to web pages and the data contained in them) and edges (corresponding to the hyperlinks). Given this paradigm, an important issue is when to compute the site's pages. In one extreme approach, the site is precomputed in advance, while in the other extreme, the queries necessary to construct a given page are computed on demand. Both approaches have their obvious drawbacks: large space and maintenance overhead in the first approach, and poor run-time performance and unnecessary repeated computations in the second. In this paper we consider the problem of automatically optimizing the run-time management of declaratively specifie...
We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing datab... more We present the SQL Performance Analyzer, a novel approach in Oracle Database 11g to testing database changes, such as upgrades, parameter changes, schema changes, and gathering optimizer statistics. The SQL Performance Analyzer offers a comprehensive solution to enable users to forecast and analyze how a system change will impact SQL query plans and run time performance, so they can tune their system before they make the change in production. The SQL Performance Analyzer identifies potential problems that may occur and makes suggestions for avoiding any SQL performance degradation. It provides quantitative estimates of the system's performance in the new environment with high confidence and performs a comparative analysis of the response time of the SQL workload thus allowing for an easy assessment of the change. In this paper we describe the architecture of the SQL Performance Analyzer, its usage model, and its integration points with other Oracle database components to form an end-to-end change management solution.
Uploads
Papers by Khaled Yagoub