Oracle Database Performance Tuning

Application user is complaining the database is slow.How would you find the performance issue of SQL queries?

High performance is common expectation for end user, in fact the database is never slow or fast in most of the case session connected to the database slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit. To know exactly what the session is doing join your query v$session with v$session_wait. 

SELECT NVL(s.username,'(oracle)') as username,s.sid,s.serial#,sw.event,sw.wait_time, sw.seconds_in_wait, sw.state FROM v$session_wait sw,v$session s

WHERE s.sid=sw.sid and s.username= '&username'ORDER BY sw.seconds_in_wait DESC;

1.Check the events that are waiting for something.

2.Try to find out the objects locks for that particular session.

3.Locking is not only the cause to effects the performance. Disk I/O contention is another case. When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as "db file sequential read" (for index scan) or "db file scattered read" (for full table scan).When you see the event, you know that the session is waiting for I/O from the disk to complete. To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.

4.Run SQL Tuning Advisor (@$ORACLE_HOME/rdbms/admin/sqltrpt.sql) by providing 

SQL_ID as the input for generating the findings and recommendations.

SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics.

SQL Tuning Advisor suggests indexes that might be very useful.

SQL Tuning Advisor suggests query rewrites.

SQL Tuning Advisor  suggests SQL profile.

More:

1.Run TOP command in Linux to check CPU usage.

2.Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.

3.Enable the trace file before running your queries,then check the trace file using tkprof create output file.

According to explain plan check the elapsed time for each query,then tune them respectively.

What is the use of iostat/vmstat/netstat command in Linux?

Iostat – reports on terminal, disk and tape I/O activity.

Vmstat – reports on virtual memory statistics for processes, disk, tape and CPU activity.

Netstat – reports on the contents of network data structures.

If you are getting high “Busy Buffer waits”, how can you find the reason behind it?

Buffer busy wait means that the queries are waiting for the blocks to be read into the db cache. There could be the reason when the block may be busy in the cache and session is waiting for it. It could be undo/data block or segment header wait.

Run the below two query to find out the P1, P2 and P3 of a session causing buffer busy wait

then after another query by putting the above P1, P2 and P3 values.

SQL> Select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait Where event = 'buffer busy waits';

SQL> Select owner, segment_name, segment_type from dba_extents

Where file_id = &P1 and &P2 between block_id and block_id + blocks -1;

What to Look for in AWR Report and STATSPACK Report?

Many DBAs already know how to use STATSPACK but are not always sure what to check regularly.

Remember to separate OLTP and Batch activity when you run STATSPACK, since they usually

generate different types of waits. The SQL script “spauto.sql” can be used to run STATSPACK

every hour on the hour. See the script in $ORACLE_HOME/rdbms/admin/spauto.sql for more

information (note that JOB_QUEUE_PROCESSES must be set > 0). Since every system is different,

this is only a general list of things you should regularly check in your 

STATSPACK output:

¦ Top 5 wait events (timed events)

¦ Load profile

¦ Instance efficiency hit ratios

¦ Wait events

¦ Latch waits

¦ Top SQL

¦ Instance activity

¦ File I/O and segment statistics

¦ Memory allocation

¦ Buffer waits

 
What is the difference between DB file sequential read and DB File Scattered Read?

DB file sequential read is associated with index read where as DB File Scattered Read has to do with full table scan.

The DB file sequential read, reads block into contiguous memory and DB File scattered read gets from multiple block and scattered them into buffer cache.

Which factors are to be considered for creating index on Table? How to select column for index?

Creation of index on table depends on size of table, volume of data. If size of table is large and we need only few data for selecting or in report then we need to create index. There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create unique index.

It is important to note that creation of so many indexes would affect the performance of DML on table because in single transaction should need to perform on various index segments and table simultaneously.

Is creating index online possible?

YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index building is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

How to recover password in oracle 10g?

You can query with the table user_history$. The password history is store in this table.

How can you track the password change for a user in oracle?

Oracle only tracks the date that the password will expire based on when it was latest changed. Thus listing the view DBA_USERS.EXPIRY_DATE and subtracting PASSWORD_LIFE_TIME you can determine when password was last changed. You can also check the last password change time directly from the PTIME column in USER$ table (on which DBA_USERS view is based). But If you have PASSWORD_REUSE_TIME and/or PASSWORD_REUSE_MAX set in a profile assigned to a user account then you can reference dictionary table USER_HISTORY$ for when the password was changed for this account.

SELECT user$.NAME, user$.PASSWORD, user$.ptime, user_history$.password_date

FROM SYS.user_history$, SYS.user$

WHERE user_history$.user# = user$.user#;

What is Secure External password Store (SEPS)?

Through the use of SEPS you can store password credentials for connecting to database by using a client side oracle wallet, this wallet stores signing credentials. This feature introduced since oracle 10g. Thus the application code, scheduled job, scripts no longer needed embedded username and passwords. This reduces risk because the passwords are no longer exposed and password management policies are more easily enforced without changing application code whenever username and password change.

Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it?

If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be performed.

What is the difference between Redo,Rollback and Undo?

I find there is always some confusion when talking about Redo, Rollback and Undo. They all sound like pretty much the same thing or at least pretty close.

Redo: Every Oracle database has a set of (two or more) redo log files. The redo log records all changes made to data, including both uncommitted and committed changes. In addition to the online redo logs Oracle also stores archive redo logs. All redo logs are used in recovery situations.

Rollback: More specifically rollback segments. Rollback segments store the data as it was before changes were made. This is in contrast to the redo log which is a record of the insert/update/deletes.

Undo: Rollback segments. They both are really one in the same. Undo data is stored in the undo tablespace. Undo is helpful in building a read consistent view of data.

You have more than 3 instances running on the Linux server? How can you determine which shared memory and semaphores are associated with which instance?

Oradebug is undocumented oracle supplied utility by oracle. The oradebug help command list the command available with oracle.

SQL>oradebug setmypid

SQL>oradebug ipc

SQL>oradebug tracfile_name

Why drop table is not going into Recycle bin?

If you are using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we have already SET recycle bin parameter TRUE.

Select * from v$parameter where name = 'recyclebin';

Show parameter recyclebin;

Temp Tablespace is 100% FULL and there is no space available to add datafiles to increase temp tablespace. What can you do in that case to free up TEMP tablespace?

Try to close some of the idle sessions connected to the database will help you to free some TEMP space. Otherwise you can also use ‘Alter Tablespace PCTINCREASE 1’ followed by ‘Alter Tablespace PCTINCREASE 0’