A PROACTIVE APPROACH FOR DATABASE PERFORMANCE TUNING

The Oracle database is a trendsetter and highly tunable software product. It’s very flexible to allow you to make small adjustments that affect the database performance. By tuning you can tailor its performance to best meet your needs. Performance tuning cannot be performed best after a system is put into production. To achieve performance targets of response time, and throughput, one must proactively analyze database design, implementation and tune ahead in the life cycle.


ISSN: 2320-5407
Int. J. Adv. Res. 10(01), 426-438 427 components, cannot easily shrink or must remain at a minimum size, the database instance prevents from setting up the target memory size too low.
Find below the three steps to calculate the minimum value for MEMORY_TARGET as follows: (i) Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET by entering the following SQL*Plus command: SHOW PARAMETER TARGET (ii) Run the following query to determine the maximum instance PGA allocated since the database was started: select value from v$pgastat where name='maximum PGA allocated'; value in MB 120 (iii) Compute the maximum value between the query result from step 2b and PGA_AGGREGATE_TARGET. Add SGA_TARGET to this value. memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated) The below tables (table 1 & 2) has the necessary parameters which needs to be taken care during the installation of the oracle database.  If you are using the shared server, then the deduced value is likely to be insufficient. UNDO_MANAGEMENT Specifies the undo space management mode used by the database.
The default is AUTO. If unspecified, the database uses AUTO.

UNDO_TABLESPACE
Specifies the undo tablespace to be used when an instance starts.

Few Oracle Hidden Parameters
In addition, to the above parameters, there are few Oracle hidden Parameters that help to boost the performance. The below parameters, take advantage of hardwarespecific performance features: _in_memory_undo=false _cursor_cache_frame_bind_memory = true _db_cache_pre_warm = false _in_memory_undo=false _check_block_after_checksum = false _lm_file_affinity

II. Space Management
When we talk about the space management in Oracle performance tuning, the following three parameters needs to be taken care Oracle provides automatic undo management, which completely automates the management of undo data. A database running in an automatic undo management mode transparently creates and manages undo segments. Oracle Corporation strongly recommends using automatic undo management, because it significantly simplifies database management and removes the need for any manual tuning of undo (rollback) segments.
Adding the UNDO TABLESPACE clause in the CREATEDATABASE statement sets up the undo tablespace. Set the UNDO_MANAGEMENT initialization parameter to AUTO to operate your database in automatic undo management mode.
The V$UNDOSTAT view contains statistics for monitoring and tuning undo space. Using this view, Oracle can better estimate the amount of undo space required for the current workload. Oracle also uses this information to help tune undo usage in the system. The V$ROLLSTAT view contains information about the behavior of the undo segments in the undo tablespace.

Sizing Redo Log Files:
The size of the redo log files influence the performance, because the behavior of the database writer, and archived processes depend on the redo log sizes. Generally, larger redo log files provide better performance. Undersized log files increase checkpoint activity and reduce performance.
Although the size of the redo log files does not affect LGWR performance, it can affect DBWR and checkpoint behavior. Checkpoint frequency is affected by several factors, including log file size and the value of the FAST_START_MTTR_TARGET initialization parameter. If the FAST_START_MTTR_TARGET parameter is set to limit the instance recovery time, Oracle automatically tries to checkpoint as frequently as necessary. Under this condition, the size of the log files should be large enough to avoid additional checkpoints due to the under sized log files. The optimal size can be obtained by querying the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY view. The below picture shows the REDO LOG structure with two disks  It may not always be possible to provide a specific size recommendation for redo log files, but redo log files in the range of a hundred megabytes to a few gigabytes are considered reasonable. Size your online redo log files according to the amount of redo your system generates. A rough guide is to switch logs, once every twenty minutes.

Creating Temporary Tablespace:
Appropriately configuring the temporary tablespace helps optimize disk sort performance. Temporary tablespaces can be dictionary-managed or locally managed. Oracle Corporation recommends the use of locally managed temporary tablespaces with a UNIFORM extent size of 1 MB.
You should monitor temporary tablespace activity to check how many extents are being allocated for the temporary segment. If an application extensively uses temporary tables, as in a situation when many users are concurrently using temporary tables, the extent size could be set smaller, such as 256K, because every usage requires at least one extent. The EXTENTMANAGEMENTLOCAL clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The default for SIZE is 1M.

III. SQL Tuning
SQL tuning is the iterative process of improving SQL statement performance to meet specific, measurable and achievable goals. SQL tuning implies fixing problems in deployed applications. In contrast, application design sets the security and performance goals before deploying an application.
After identifying application problem, the below standard performance tuning implies 1. Reduce user response time 2. Improve throughput SQL tuning involves the following basic steps: 1. Identifying high load or top SQL statements 2. Verifying that the execution plans produced by the query optimizer for these statements perform reasonably 3. Implementing corrective actions to generate better execution plans for poorly performing SQL statements There are many areas to discuss when we talk about SQL tuning. As we focused on proactive tuning approach there are few things that need to be taken care during development phase itself. 1. Index tuning 2. Table partitioning 3. Scalar sub-query 4. Hints usage 5. Session tuning

1.
Index tuning is the major in proactive performance tuning to improve the speed of SQL queries. Oracle includes many new indexing algorithms that dramatically increase the speed of the queries. There are below indexing parts which helps to increase the speed of queries.
a. Bitmap index b. Function based index a. Bitmap Index: Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table. At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.
The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality. The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries.
For example, assume there is an insurance company database with numerous low-cardinality columns such as customer_type, insurance_made_year etc. and each column contains less than 100 distinct values by themselves and a b-tree index would be fairly useless in a database of 20 million insurers. However, combining these indexes together in a query can provide blistering response times a lot faster than the traditional method of reading each one of the 20 million rows in the base table. Prior to Oracle8, the use of a built-in function would not be able to match the performance of an index. Consequently, Oracle would perform the dreaded full-table scan. Example now you can do the following: Create index emp_ix on emp (upper(name));... where upper(name) like 'SMITH%' Now we have a way to get at the function modified data using an index. This is a benefit that makes this technique really easy, that you can index expressions on the data. The logic being used in the where clause pretty much matches the expression in the index. This means you can enable indexed access to data that previously you could not. Consider questions like these: (i) how do I find rows where values are null select * from emp where release_date is null create index emp_ix on emp (decode(release_date,null,1,null)); select * from emp where decode(release_date,null,1,null) = 1; (ii) how do I find rows based on calculations select * from line_item where nvl(total,0) -nvl(discount,0) -nvl(mgr_reduction,0) = 0 create index line_item_ix on line_item (nvl(total,0) -nvl(discount,0) -nvl(mgr_reduction,0)); (iii) how do I convert a range scan to an exact match query select * from line_item where nvl(total,0) -nvl(discount,0) -nvl(mgr_reduction,0) > 0 create index line_item_ix on line_item (decode(sign(nvl(total,0) -nvl(discount,0) nvl(mgr_reduction,0)),1,1,null))); select * from line_item where decode(sign(nvl(total,0) -nvl(discount,0) -nvl(mgr_reduction,0)),1,1,null)) = 1; It would offer that function based indexes tend to be special purpose in nature. Its support specific pieces of code, that needs them and not to try use them as generic performance enhancers. In numerous situations, function based indexes yield stellar enhancements in performance. 432 2. Table Partitioning: Table partitioning is a divide-and-conquer approach to improving Oracle maintenance and SQL performance. Anyone with un-partitioned databases over 500 gigabytes is courting disaster. Table partitioning will increase the performance by following: (a) Disk load balancing: Table and index partitioning allows to segregate portions of very large tables and indexes onto separate disk devices, thereby improving disk I/O throughput and ensuring maximum performance.
(b) Improved query speed: The Oracle optimizer can detect the values within each partition and access only those partitions that are necessary to service the query. Since each partition can be defined with its own storage parameters, the Oracle SQL optimizer may choose a different optimization plan for each partition.
(c) Faster Parallel query: The partitioning of objects also greatly improves the performance of parallel query. When Oracle detects that a query is going to span several partitions, such as a full-table scan, it can fire off parallel processes. Each of processes will independently retrieve data from each partition. This feature is especially important for indexes, since parallel queries do not need to share a single index when servicing a parallel query  With table partitioning, a table can be physically divided into multiple smaller tables, called partitions, while logically it stays one table. This means that the code stays the same, but full partition scans will be executed instead of a full table scan.
These partitions are created based on a key. Depending on which value a certain column has, the record will be stored in a certain partition. It's important to choose a column that is often used in queries as our key CREATE  The big advantage of partitioning is the possibility for -partition pruning‖. When we look for a value of -1‖ in column -N10‖, we know we will only find this in partition -PART1‖, so we don't need to access the other partitions, and our query will execute ten times as fast. When we need all values, it will scan all partition, and there will only be a small overhead. The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.  Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note, that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning.

Scalar Sub-query:
A scalar subquery expression is a subquery that returns exactly one column value from one row. The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL. If the subquery returns more than one row, then Oracle returns an error.
So now most of them will raise a question whether I can use a Scalar sub-query or a Join?
Answer to this always use Join based on the performance background Scalar sub-query takes more burdensome to database.
User JOIN (outer join if need be) to T2 and T3, it would be: select t2.a, col_2, col_3, t2.b, t3.x from ...., T2, T3 where .... and t2.c(+) = t1.c and t3.d(+) = t1.d --outer join IF and ONLY IF necessary In this scalar sub-query caching which can change that slightly, but in general, you'll be running a query inside of another query. You want to use SET processing and currently the optimizer does not roll/merge the scalar subquery into the outer query.
Below are the statistics of a real scenario which is having scalar query and converted to join query. The scalar subquery retrieves 198 records, took 12mins 30secs but after changing to join query it took just only 4secs. The scalar query took more time because fetch more data unnecessarily. So if you are pulling data from more number of data should not use scalar query.

Hints usage in query:
Hints give specific information that we know about our data and application. A way to override the default query optimization in the DBMS. Influence the execution plan of query Why using hints? Oracle comes with an optimizer that promises to optimize a query's execution plan. But it does not always choose the best execution plan. Using hints may improve the performance by changing the execution plan oracle takes. It really helps to improve the response time for few queries and Oracle recommends using the hints in some places even though if the query is optimized.

Session Tuning:
Before start troubleshooting for slow performance database, one has to first understand that the database itself is never slow or fast-it has a consistent speed. The sessions connected to the database, however, slow down when they hit a bump in the road. To resolve a session performance issue, you need to identify the bump and remove it. Fortunately, it's very easy to do in most of the cases.
The first step to resolving a session performance issue is to ascertain what the database session is doing at any instant of time. An Oracle database session is always in one of three states: Idle Not doing anything-just waiting to be given some work. Processing Doing something useful-running on the CPU. Waiting Waiting for something, such as a block to come from disk or a lock to be released.
You can query the database's current state using a view called V$SESSION.
How difficult is it to get information about what's causing the session to stop? It's actually very easy: Oracle database is instrumented to talk about what the database sessions are doing. All you need to do is to listen attentively or, more precisely, look for that information in the right place, and that place is a view called V$SESSION. Everything you need for your analysis is in this view.
How to identify and solve the session issue? Update in one table  Example: update t1 set col2 = 'x' where col1 = 1; The output will show -1 row updated,‖ indicating that the row was updated. Do not issue a COMMIT after the statement. By not committing, you will force the session to get and hold a lock on the first row of the T1 table. Now go to the second session and issue the following SQL statement: update t1 set col2 = 'y' where col1 = 1; This statement will hang. Why? The answer is simple: the first session holds a lock on the row, which causes the second session to hang and the user to complain that the session is slow. To know what the second session is doing, the first thing one has to understand the need to check is the STATE column in V$SESSION: The session is waiting because it wants to lock one or more rows, but another session has already placed locks on the row or rows. Unless that other session commits or rolls back the transaction, session 3346 will not get the lock it needs and will have no choice but to wait.
Getting the amount of time a session has been waiting makes sense for sessions that are waiting right now, but what about the sessions that are working now? Recall that the EVENT column shows not only the event a session is experiencing now but also the last wait event the session has experienced. Another column-WAIT_TIME-in the same V$SESSION view shows how long that wait lasted.
Below query to display the session, session state and wait details col "Description" format a50 selectsid, decode(state,'WAITING','Waiting','Working')state, decode(state,'WAITING','So far '||seconds_in_wait, 'Last waited '||wait_time/100)||' secs for '||event) "Description" from v$session where username = 'DEERAJ'; The output of listing 2 provides enough information to enable you to make a diagnosis about the performance of these three sessions. Session 4208 is idle, so any complaints that session 4208 is slow just aren't related to the database. Any performance issues related to this session could be related to a bug in the code that's going through an infinite loop or high CPU consumption on the application server. The performance troubleshooting focus has to be redirected toward the application client.
The story of session 3346 is different. This session is truly a bottleneck to the application. Now that it is understandable, to know why this session appears slow-it is waiting for a row lock-the next logical question is which session holds that lock.
b. Session Parameter Tips: If processes and session value are set as 1000 and 1248 in the database respectively. If, using 11.2.0.1, when DB is restarted, it shows sessions as 1524. It confuses to see higher value than the value set for sessions: SQL> alter system set processes=1000 scope=spfile; SQL> alter system set sessions=1248 scope=spfile; It is clear that, that the session parameter is derived as a function of the processes parameter It looks like: 11R1 -sessions = (1.5 * PROCESSES) + 22 11R2 -sessions = (1.1 * PROCESSES) + 5 If sessions parameter was set as a low value, than the derived value, then Oracle automatically bumps it to above derived value.
If session parameter was set higher value, than the derived value, Oracle will consider our set value.

Conclusion:-
Realizing the value of proactive tuning approach on database performance tuning, will bring out the true potential of identifying and addressing the performance issue during application development cycle, well before any impact to end users. There are many varieties of tools available in the market which can be used for performance tuning. These tools are used to diagnose and proactively tune an Oracle database.
There are some key tools which used for proactive performance. Such as -SQL optimizercan analyze for query tuning, index tuning. Enterprise managercan be used to monitor an Oracle database for performance related. Spotlight -This is can be used to monitor for memory and also overall performance of Oracle database.
At HCLT, Performance Engineering Service Line group is providing exclusively various performance engineering services not only to tune but also to identify and fix the various potential performance issues and application stability related bottlenecks.