Oracle Performance Tuning Interview Questions and Answers
Q. Application user
is complaining the database is slow.How would you find the performance issue of
SQL queries?
Answer-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.
Q.What is the use
of iostat/vmstat/netstat command in Linux?
ANSWER- 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.
Q.If you are
getting high “Busy Buffer waits”, how can you find the reason behind it?
ANSWER -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;
Q.What to Look for
in AWR Report and STATSPACK Report?
ANSWER -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
Q.What is the
difference between DB file sequential read and DB File Scattered Read?
ANSWER -DB file sequential read is
associated with index read whereas 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.
Q.Which factors are
to be considered for creating index on Table? How to select column for index?
ANSWER -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.
Q.Is creating index
online possible?
ANSWER -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;
Q. How to recover
password in oracle 10g?
ANSWER -You
can query with the table user_history$. The password history is store in this
table.
Q. How can you
track the password change for a user in oracle?
ANSWER -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#;
Q. What is Secure
External password Store (SEPS)?
ANWESR -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.
Q. 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?
ANWESR - 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.
Q. What is the
difference between Redo, Rollback and Undo?
ANSWER -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.
Q. 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?
ANSWER -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
Q. Why drop table
is not going into Recycle bin?
ANSWER - 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;
Q. 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?
ANSWER - 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’
Q. What is the max
possible size of a SMALL and BIG datafiles?
ANSWER - Max datafile size for SMALL
FILE NORMAL TABLESPACE would be:
Database
Block Size Maximum Datafile File Size
2k 4194303 * 2k = 8 GB
4k 4194303 * 4k = 16 GB
8k 4194303 * 8k = 32 GB
16k 4194303 * 16k = 64 GB
32k 4194303 * 32k = 128 GB
Max
datafile size for BIG FILE TABLESPACE would be:
Database
Block Size Maximum Datafile Size
2k 4294967295 * 2k = 8 TB
4k 4294967295 * 4k = 16 TB
8k 4294967295 * 8k = 32 TB
16k 4294967295 * 16k = 64 TB
32k 4294967295 * 32k = 128 TB
Q. AWR Vs. ASH?
ANSWER ASH -
It can help you when there's a sudden performance degradation of the database
felt. Storage is not persistent and as time progresses, the old entries are
removed to accommodate new ones.
They can
be viewed using V$ACTIVE_SESSION_HISTORY.
AWR - It holds historic past
snapshot intervals and the session performance statistics for analysis later.
Q. What is explain
plan?
ANSWER -EXPLAIN PLAN parses a query and records the
"plan" that Oracle devises to execute it. By examining this plan, you
can find out if Oracle is picking the right indexes and joining your tables in
the most efficient manner.
Q. What is Database
Replay?
ANSWER - Oracle Database Replay is the
database upgrade companion tool. The Database Replay enables users to perform
real-time, real-world testing by capturing actual database workloads on the
production system
and
replaying them on the clone database system. The test on the clone database can
be performed with production characteristics including timing and transactions
concurrency.
The main
feature of Database Replay is it provides analysis and reporting to highlight
potential problems such as slow completion of a report on the new environment.
The
Oracle Database Replay has two interfaces . Oracle Enterprise Manager and
Oracle PL/SQL supplied packages.
Q. What is Fragmentaion?
ANSWER -As data is modified in a
database, the database and its indexes become fragmented. As indexes become
fragmented, ordered data retrieval becomes less efficient and reduces database
performance.
Q. What are
MATERIALIZED VIEWS?
ANSWER -A materialized view in Oracle
is a database object that contains the results of a query. It stores data
physically and get updated periodically. While querying Materialized View, it
gives data directly from Materialized
View and not from table.
Q. What are Trace
files?
ANSWER -Trace File are trace (or dump)
file that Oracle Database creates to help you diagnose and resolve operating
problems.
Each
server and background process writes to a trace file. When a process detects an
internal error, it writes information about the error to its trace file.
Q. What is ORA-600?
ANSWER -ORA-600 is an internal error
generated by the generic kernel code of the Oracle RDBMS software. It is
different from other Oracle errors in many ways. The following is a list of
these differences:
1. An
ORA-600 error may or may not be displayed on the screen. Therefore, screen
output should not be relied on for capturing information on this error.
Information on ORA-600 errors are found in the database alert and trace files.
We recommend that you check these files frequently for database errors. (See
the Alert and Trace Files section for more information.)
2. Each
ORA-600 error comes with a list of arguments They usually enclosed in square
brackets and follow the error on the same line for example:
3. Also
some time, if we have used many cte's in a SP then it throws same error because
of memory used by these ctc's exceed.
Each
argument has a specific meaning which can only be interpreted by an Oracle
support analyst. The arguments may also change meaning from version to version
therefore customers are not advised to memorize them.
4. Every
occurrence of an ORA-600 should be reported to Oracle Support. Unlike other
errors, you can not find help text for these errors. Only Oracle technical
support should diagnose and take actions to prevent or resolve damage to the
database.
5. Each
ORA-600 error generates a database trace file.
Q. What is OLTP AND
DATAWAREHOUSING?
ANSWER -The data warehouse and the OLTP
data base are both relational databases. However, the objectives of both these
databases are different.
The OLTP
database records transactions in real time and aims to automate clerical data
entry processes of a business entity. Addition, modification and deletion of
data in the OLTP database is essential and
the semantics
of the application used in the front end impact on the organization of the data
in the database.
The data
warehouse on the other hand does not cater to real time operational
requirements of the enterprise. It is more a storehouse of current and historical
data and may also contain data extracted from external data sources.
Q. What is Row
Chaning and Row Migration?
ANSWER
-Row
Migration:
A row
migrates when an update to that row would cause it to not fit on the block
anymore (with all of the other data that exists there currently). A migration means that the entire row will
move and we just leave behind the «forwarding address». So, the original block
just has the rowid of the new block and the entire row is moved.
Row Chaining:
A row is
too large to fit into a single database block. For example, if you use a 4KB
blocksize for your database, and you need to insert a row of 8KB into it, Oracle
will use 3 blocks and store the row in pieces. Some conditions that will cause
row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG
and LONG RAW columns are prone to having chained rows. Tables with more then
255 columns will have chained rows as Oracle break wide tables up into pieces.
So,
instead of just having a forwarding address on one block and the data on
another we have data on two or more blocks.
Q. How to find out
background processes ?
ANSWER -SQL> select SID,PROGRAM from
v$session where TYPE='BACKGROUND';
SQL>
select name,description from V$bgprocess;
Q. How to findout
background processes from OS:
ANSWER -$ ps -ef|grep ora_|grep SID
Q. To Find and
Delete bigger size and older files in Linux
ANSWER --To find out files size more
than 5MB
find .
-size +5000 -exec ls -ltr {} \;
-- To
**Remove** files size more than 5MB
find .
-size +5000k -exec rm -rf {} \;
--To
find out files older than 30days
find .
-mtime +30 -exec ls -ltr {} \;
--To
find **Remove** files older than 30days
find .
-mtime +30 -exec rm -rf {} \;
Q. When a Table space reaches 90%, what action you will take? How you decide whether to go for Resize or Add data file?
