IMPROVING DATABASE APPLICATIONS AND DATABASE INDEX PERFORMANCE

by Robert Vanaman M.B.A., M.S.

You can find this article in SQL SERVER COMPENDIUM – FORENSICS ANALYSIS

DATABASE APPLICATIONSPERFORMANCE

IMPROVING DATABASE APPLICATIONS AND DATABASE INDEX PERFORMANCE
IMPROVING DATABASE APPLICATIONS AND DATABASE INDEX PERFORMANCE

Astonishingly, Bob Bryla and Kevin Loney in their salient text “Oracle Database 11g DBA Handbook” have concluded that “conservatively” 50% of the time performance problems are designed into an application! In some instances, it is a simple case of the right hand not knowing what the left hand is going to do. The application development team maybe simply unaware of the various roles within the database structure that the users will apply “the application data over time” (Bryla, & Loney, 2008, p. 124). Therefore, initially some performance issues will be readily apparent, and can be dealt with as such. Conversely, due to a lack of clairvoyance on the application development teams part, other issues will not be realized until the application meets the database under load, or the “business usage of the application” (Bryla, & Loney, 2008, p. 124) morphs. Bryla and Loney continue this discussion with the observation that some solutions to poorly performing applications can be relatively clear-cut to fix. Other situations unfortunately may require overhauling the applications architecture.

 

One solution to this unfortunate situation is requiring DBAs to be part of the application development team. There are several reasons why a DBA wants (needs) to be in integral part of an application team. First and foremost, managing the development of an application in many instances is an involved, time-consuming, and challenging process. When the DBA takes a principal role within the development team, it places the DBA in an ideal capacity to design and implement the database parameters that bests support the application (Bryla, & Loney, 2008, p. 124). Therefore, best project management practices dictates that the DBA becomes embedded with the various teams involved in the process. This creates a sense of common purpose between the DBA and members of the development team, rather than an adversarial relationship. This sense of camaraderie, an esprit de corps if you will, will facilitate the DBA if (when) they find it necessary to modify or augment the applications direction or goals. Additionally, this embeddedness elevates the DBA to the role of “stakeholder” within the project application development team (Project Management Institute, 2008). This puts the DBA in direct contact with other stakeholders; whom direction and suggestions can be gleaned, and presented.

Users of database applications tend to be insensitive to their internal structure. To paraphrase Hamlet’s opening soliloquy “To normalize or not to normalize, that is the question”, may very well be the interrogatory occupying the DBA's mind, but is far from a primary concern for the end user. Their concern is built around two primary issues: user-friendliness and performance. User-friendliness will leave for another time, therefore concentrating on performance issues, Bryla and Kevin have identified five areas that should be concentrated upon when performance tuning a database application (Bryla, & Loney, 2008, p. 124).

  • Avoid logical reads: Logical reads consume CPU resources. Intensive logical reads will consume as much of the CPU resources as the system can support. When code which intensively use logical reads are discovered, substituting commands, and procedures within that code which does not require a processor read is the solution (Bryla, & Loney, 2008, p. 125).


  • Minimize trips to the database: Remember, we are refining applications not individual queries; therefore, repetitive exchanges between the database server, the application server, and the user’s computer is what is proposed for refinement. End-user screen refreshing, due to data fields being populated by the execution of multiple queries, is a condition where this occurs. The solution is the aggregation of the multiple queries into a single unified block of code. This “bundled-query approach is particularly relevant for thin-client applications that rely on multiple application tiers” (Bryla, & Loney, 2008, p. 126).


  • Record the data the way the users will query it: This entails storing the data in a form that will be utilized in the query, and is expected to be presented to the user. In reporting systems, especially ones that use parameterized reports - this is a report that uses input values to complete the reporting process - storing a default value for the parameter fields, and prompting the user to type or select a value, is a sound methodology (Bryla, & Loney, 2008, p. 127).


  • Circumvent repeated database connections: Opening database connections are both time and resource intensive. Concentrating tables within one or a few databases, rather than in multiple databases is sensible, as well as a resource conserving practice (Bryla, & Loney, 2008, p. 127). This methodology, to some degree, flies in the face of contemporary normalization theory; however, it is a prominent feature in data warehouses.


  • Use the right indexes: Creating too many indexes can be as performance hampering as using too few, or not the right ones. Using too many indexes significantly slows performance times on INSERT an ALTER commands. The degree of cardinality within databases tables and attributes, to a large degree dictates the type of index strategies which should be employed (Bryla, & Loney, 2008, p. 127). Tables and attributes of high cardinality (that is tables with a large amount of unique data) are ideally suited for B-tree indexes (Techopedia, 2012). Oracle's default indexing method, its normal indexes, are B-tree indexes. These are ideally suited for highly normalized Online Transaction Processing (OLTP) Relational Database Management Systems (RDBMS) (Oracle® Database SQL Reference 10g Release 1 (10.1), 2003). These types of databases consist of a number of high and medium cardinality attributes such as Account Number (high cardinality) columns and Last Name (medium cardinality) columns (Techopedia, 2012). Bitmap indexes in contrast are ideally suited for a data warehouse environment. Data warehousing operations traditionally include ad hoc queries on extensive amounts of data with a rather low occurrence of Data Manipulation Language (DML) transactions such as SELECT, INSERT, UPDATE, DELETE, etc. Essentially, users are issuing SELECT statements as opposed to ALTER, and INSERT type commands. In this environment, bitmap indexing provides advantages such as: “Reduced response time for large classes of ad hoc queries. Reduced storage requirements compared to other indexing techniques. Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory. Efficient maintenance during parallel DML and loads” (Oracle® Database Data Warehousing Guide11g Release 1 (11.1), 2007).


DATABASE INDEX PERFORMANCE

Of primary importance in tuning database queries is to minimize the search path that the database utilizes to locate the data. Eliminating full table scans goes a long way to this end. Utilizing a WHERE clause in the SQL statement facilitates the Optimizer in “using an index to help speed the retrieval of the desired rows”(Bryla, & Loney, 2008, p. 247). Using indexes mitigates the need for a full table scan; which for large tables can be very resource intensive. Additionally, indexes should be specifically tailored to the specific access path. When employing a composite index - one made up of several attributes - the first attribute within the WHERE clause will be examined by the Optimizer first for index search. The Optimizer is capable of selecting a skip scan indexing method (one that searches for index based on any of the attributes in the where clause) but this is not guaranteed.

The type of index chosen based on cardinality of the attribute and the environment in which it is to be employed significantly impacts query performance. The degree of cardinality within a databases tables and attributes, to a large degree dictates the type of index strategies which should be employed. Tables and attributes of high cardinality (that is tables with a large amount of unique data) are ideally suited for B-tree indexes (Techopedia, 2012). This is Oracle's default indexing method. These are ideally suited for highly normalized OLTP RDBMS that utilize heavy loads of both read and write statements (Oracle® Database SQL Reference 10g Release 1 (10.1), 2003). These types of databases consist of a number of high and medium cardinality attributes such as Account Number (high cardinality) columns and Last Name (medium cardinality) columns (Techopedia, 2012). Having said that, there is one distinct caveat when creating indexes on primary keys in Oracle, and that is Oracle only supports B-tree indexes on these attributes. Bitmap and clustered indexes are verboten (Sharma, 2005).

In contrast, bitmap indexes are ideally suited for a database environment such as OLAP which utilize read intensive SQL statements. For example, data warehousing operations traditionally include ad hoc queries on extensive amounts of data with a rather low occurrence of DML transactions. Essentially, users are issuing Select statements as opposed to Alter, and Insert commands . In this environment bitmap indexing provides advantages such as:

  • Reduced response time for large classes of ad hoc queries.


  • Reduced storage requirements compared to other indexing techniques.


  • Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory.


  • Efficient maintenance during parallel DML and loads”


(Oracle® Database Data Warehousing Guide11g Release 1 (11.1), 2007).

The classic example of where bitmap indexing is superbly suited is in a gender column. Here, there is a very low cardinality consisting of two possible entries; the Omega of the primary keys Alpha.

Of considerable assistance in determining the search path that the database utilizes to locate the data is in generating an explain plan in Oracle Databases. This command assesses the execution path for queries. By setting “autotrace” on, an explain plan output and trace information will be automatically generated for every query that is run. To evaluate the explain plans output, and understand the hierarchy within the output, is necessary to read “from the inside out until you come to a set of operations at the same level of indentation then read from top to bottom” (Bryla, & Loney, 2008, p. 252). This not only provides a bit of a graphical display of how the queries path was executed, but each step is assigned a cost. You can use these costs to identify the steps in the query that contribute the greatest system overhead, and “then target them for specific tuning efforts” (Bryla, & Loney, 2008, p. 252).

References

Bryla, B. & Loney, K. (2008). Oracle database 11g DBA handbook. New York, NY: McGraw-Hill.

MSDN. (2012). Running a parameterized report. Retrieved from http://msdn.microsoft.com/en-us/library/ms159825(v=sql.105).aspx

Project Management Institute (2008). A guide to the project management body of knowledge (PMBOK guide) (4th ed). Newtown Square, PA: Project Management Institute, Inc.

Oracle® Database Data Warehousing Guide11g Release 1 (11.1). (2007). 6 Indexes. Retrieved fromhttp://docs.oracle.com/cd/B28359_01/server.111/b28313/indexes.htm

Oracle® Database SQL Reference 10g Release 1 (10.1). (2003). Create index. Retrieved from http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_5010.htm

Sharma, V. (2005). Bitmap index vs. B-tree index: Which and when? Retrieved from http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

Techopedia. (2012). Cardinality. Retrieved from http://www.techopedia.com/definition/18/cardinality

 

September 5, 2014
© HAKIN9 MEDIA SP. Z O.O. SP. K. 2013