It is a well known fact that great object oriented programmers are not great database developers and the opposite holds good with a few exceptions in both the cases. Each of these are experts in their own space and often leave the job of fine tuning applications as a reactive approach after the size of the data grows into hundreds of TB and the same becomes stale over a period of time and users start to complain about serious performance issues. This is when cracks get visible and an extensive performance analysis is done or a Performance Analyst takes over who comes up with a heavy knowledge of third party tool usage and the Tool may identify the root cause at an abstract level. Ex: This piece of SQL, DB Object or Stored Procedure is the bottleneck.
This is followed by a DBA or a Sys Admin thinking on the lines of altering the values for database instance parameters whether dynamic or static. Most sought after panacea is to increase the size of the SGA. Worse, a look at the hardware configuration starts creeping in such as adding more RAM, Disk Space with an exception to Cloud environments.
Especially when companies are choosing to focus on the core businesses and partner with application development outsourcing or offshore application development companies for development needs AND application testing services for independent validation and verification, a significant disconnect can occur between the various streams in the Engineering function. For a typical IT consulting firm operating in a global delivery model, diverse group of teams are involved in Logical/Physical Schema Design, Coding and Testing. It becomes an unmanageable task to trace back to root cause of the problem related to performance which is routed to software maintenance OR a sustain engineering firm over a period of time that never had the required insights into design or coding aspects of the Application.
It is worth mentioning that a proactive approach to write fine tuned SQL code offers many benefits not limited to a good user experience; avoid expensive performance analysis or a formal exhaustive performance testing phase thereby improving the time to market metrics. Isolating the DB layer would help the Performance tuning to be confined to a focus area and front end servers or the middleware servers could be subjected to tracking Available Memory, CPU usage, Disk I/O etc... Using a performance testing automation tool such as HP-Mercury’s LoadRunner or Microsoft’s VSTS 2010 OR any other licensed tool while not ignoring open source tools. An important aspect of this blog is that well written SQL or PL/SQL can achieve almost 70% of application performance while the remaining leaves room for Instance level or Hardware tweaks. Listed are a few approaches one could follow to facilitate proactive tuning:
1) Bind Variables: Hard Coding variables in PLSQL should be avoided as these tend to generate execution plans again and again. Using bind variables shall facilitate a pre-computed plan thereby improving performance with some help from SGA and OR shared pool
2) Explain Plan: Look at the Plan Table or set SQLPLUS to display the plan (especially useful if there are schematic changes due to business needs and you add additional tables and want to compare the plans before and after).
This could be done for the frequently used queries that one could identify ahead of time. As an alternative, one could use TKPROF, but I prefer Explain Plan to keep it simple
3) SQL HINTS: These have been my all time favorite tweaks which however could be reactive in nature. As the size of the data increases, the problems start to creep in. With an exception to RULE hint, all other hints force the usage of Cost Based Optimizer. The last I heard in early 2000 was that Oracle planned to deprecate Rule Based Optimizer. Some of my favorite ones are:
a) /+*Ordered*/ : Determines the Join Order and the first table in the list becomes the driving table. Useful if you know something about the tables which the optimizer might not know
b) /+*Index*/ Specify the Table and Index Name
c) /+*Append*/ : When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used there by the inserts are much faster
d) /+*Parallel (Table Name, number)*/ : Provide the degree of parallelism , i.e the number of parallel query servers that needs to be used.
e) /+*Cache Table Name*/: Useful for small tables as the blocks for this table are placed in buffer cache
f) /+*Index_FFS*/: This hint causes a fast full index scan to be performed rather than a full table scan.
Note: Since hinting a view results in a suboptimal query plan, an alternative would be to embed the HINT in the view definition itself
4) Triggers: Avoid usage of Triggers unless required for business reasons as they compile each and every time they are called as against Stored Procedures and Functions. That is why these are probably not called as Stored Triggers
[Tune SQL, PLSQL and ‘Business’ using Oracle 8i and above]
5) External Tables: Usage of External Tables to avoid SQL*Loader Operations [8i and above]. These create an object that points to the delimited file. The object can be queried as you query a table. There are more operational benefits in that one could avoid operations on Flat Files on a regular basis. Automating the creation of the definitions can be of great benefit.
Script the creation of the external table and use the ‘CREATE TABLE as SELECT … option with NO LOGGING option’ if you have a business reason to run complex queries on the table for use in an OLAP environment.
6) Materialized Views : To pre-compute aggregates and store them as a view with data readily available. Storage is the only disadvantage. They can get stale, but can be set to refresh periodically
7) Analytical Functions: Check Oracle documentation for benefits these can offer. Analytical Functions were introduced in Oracle 8i and as against native SQL, these offer many benefits in terms of speed and simplicity. While the syntax is cryptic, it’s a matter of getting used to the same and exploit the benefits of partition clause amongst other things
[For OLAP]
8) Bit Map Index: For columns with low cardinality (Ex: Gender, Marital Status) usage of bit map indexes in OLTP as well as OLAP is a good idea. For instance, a bank wanted to update the account holder’s credit limit based on a value from a Scoring column which was constrained to accept low/high/excellent values only. For OLAP environments, Bit Map Index can be useful for adhoc queries amongst other benefits such as indexing null values. Additional benefits regardless of the cardinality of the columns could be explored but I have never used them
In a nutshell, having this simple and easy to use arsenal, one could pro-actively write PL/SQL, SQL or design databases which augment the business process. It always pays to ‘build it right the first time’ but not ‘build it rigid the first time’. Sometimes you may just leave it to the Optimizer if the volume of data is not so high!
No comments:
Post a Comment