Much of the OLAP reporting feature embedded in Oracle SQL is ignored. People turn to expensive OLAP reporting tools in the market - even for simple reporting needs. This article outlines some of the common OLAP reporting needs and shows how to meet them by using the enhanced aggregation features of Oracle SQL.
The article is divided in two sections. The first introduces the GROUP BY extensions of SQL, and the second uses them to generate some typical reports. A section at the end introduces the common OLAP terminologies.
The AWR tables contain super-useful information about the time-series execution plans for SQL statements and this repository can be used to display details about the frequency of usage for table and indexes. This article will expore these AWR tables and expose their secrets.
There is a great debate about the rapidly-falling costs of RAM and the performance benefits of full caching of Oracle databases. Let's take a closer look at the issues over large RAM data buffers, tuning by adjusting system parameters and using fast hardware to correct sub-optimal Oracle code:
This article provides a clear, thorough concept of analytic functions and its various options by a series of simple yet concept building examples. The article is intended for SQL coders, who for might be not be using analytic functions due to unfamiliarity with its cryptic syntax or uncertainty about its logic of operation. Often I see that people tend to reinvent the feature provided by analytic functions by native join and sub-query SQL. This article assumes familiarity with basic Oracle SQL, sub-query, join and group function from the reader. Based on that familiarity, it builds the concept of analytic functions through a series of examples.
Prior to Oracle9i, the only two cost-based optimizer modes were all_rows and first_rows optimization. One of the shortcomings of traditional first_rows SQL optimization was that the first_rows goal did not know the scope of the query and generally favored index access over full-table scans.
Sometimes it is a rouge query, sometimes a simple data clean up effort by the users, whatever may the cause be, inadvertent data-loss is a very common phenomenon. Backup and recovery capabilities are provided by the database management systems which ensure the safety and protection of valuable enterprise data in case of data loss however, not all data-loss situations call for a complete and tedious recovery exercise from the backup. Oracle introduced flashback features in Oracle 9i and 10g to address simple data recovery needs.
Prior to Oraclre10g, capturing wait event information was a cumbersome process involving the setting of special events (e.g. 10046) and the reading of complex trace dumps. Fortunately, Oracle10g has simplified the way that wait event information is captured and there are a wealth of new v$ and wrh$ views relating to Oracle wait events.
The performance-improvement process is an iterative, long-term approach to monitor and tune various aspects of a database. Depending on the result of monitoring, the DBA should adjust the configuration of the database server and make changes to the applications that use the database server. But before embarking on the journey to tune the database server, the Application itself should be tuned to remove inefficient sql code. So is there a method to the madness?
XML is everywhere. Whether you are an Oracle system administrator, a .NET developer, or a J2EE analyst, XML undoubtedly is something that you bump into. In this article, I attempted to give you the basics of what you need to know to jumpstart your understanding of XML.
Oracle10g Enterprise Manager (EM) has a fantastic interface for easily creating exception alerts and mailing them directly to the Oracle professional. However, the EM has limitations. Until EM evolves into a true Decision Support System (DSS) for the Oracle DBA, the DBA will still need to extract and use the workload information stored in the AWR (Advanced Workload Repository).