1.How to identify the sid using Server Process id?
select sid
from v$session
2.How to identify the sid using Server Process id?where paddr in (select addr
from v$process
where background is null
and spid=&Server_process_id);
select sid
from v$session
where paddr in (select addr
from v$process
where background is null
and spid=&Server_process_id);
3.How to identify the sessions which are INACTIVE for more than 1 hour?
select sid
from v$session
where paddr in (select addr
from v$process
where background is null)
and status='INACTIVE'
and last_call_et/60/60>1;
4. How to identify the sql used by a session?
select sql.sql_text
from v$session ses, v$sqltext sql
where sql.address=ses.sql_address
and sql.hash_value=ses.sql_hash_value
and sid=&session_id
order by piece;
5.How to identify the rollback/Undo segments used by a session?
select *
from v$rollname
where usn = (select xidusn
from v$transaction
where addr in (select taddr
from v$session
where sid=&session_id));
6.How to identify the free space in undo tablespaces?
select sum(bytes/1024/1024/1024) GB, status, tablespace_name
from dba_undo_extents
group by status, tablespace_name;
7.Definition of Transaction Status:
a. ACTIVE means that this undo segment contains active transactions
b. EXPIRED means that this segment is not required at all (as per undo_retention).
c. UNEXPIRED means that this segment does not contain any active transactions but it contains transactions which are still required for Flashback option (as per Undo_retention).
8.How to identify the wait events for a Session?
select p1, p2, p3, event
from v$session_wait
where sid=&session_id;
Also check the following:
select count(*), event
from v$session_wait
group by event;
9.Why the session is taking more time than normal?
Step 1: Check the Alert log for errors
Step 2: Check with the end user on how long the session used to take to complete.
Step 3: Check with user whether any increase in the volume of data being
Processed.
Step 4: Check the wait events for this sessions by using the query given in Point
No.10.
Step 5: Identify the tables begin accessed by that session using the Point No.18.
Step 6: Check whether Statistics have been generated for these tables identified
in Step 4 using the following query:
select last_analyzed, num_rows
from dba_tables
where owner='&table_owner'
and table_name='&object_name';
Note: If the table is partitioned, check in dba_tab_partitions also and
for subpartitions check in dba_tab_subpartitions.
Step 7: Check whether Statistics have been generated for the indexes of the tables
Identified in Step 4 Using the following query:
select last_analyzed, num_rows
from dba_indexes
where table_owner='&table_owner'
and table_name='&table_name';
Note: If the table is partitioned, check in dba_ind_partitions and for
subpartitions check in dba_ind_subpartitions.
Step 8: If Statistics is not up-to-date, Generate stats using dbms_stats package.
Step 9: Follow the Query Tuning approach
10.Is the session hanging or running fine?
select last_call_et/60/60, status
from v$session
where sid=&session_id;
If the Status in the above query is “ACTIVE', then the current sql being executed by the session is running for so many hours(last_call_et/60/60).
If the time taken by the current sql is too high, then trouble shoot using the steps mentioned in Point No. 7
If the Status in the above query is “INACTIVE', then the session is “INACTIVE” for so many hours(last_call_et/60/60). Then get the long running query, or find out the query using mentioned in Point No. 3, You may not get any sql. Then you can discuss with the end user and you can kill this session and re-start the job.
11.How to handle “db file sequential Read” wait event?
db file sequential Read wait event signify time waited for I/O read requests to
complete. Time is reported in 1000's of a second.
A db file sequential read operation reads data into contiguous memory
(usually a single-block read with p3=1, but can be multiple blocks).
Single block I/Os are usually the result of using indexes.
This event is also used for rebuilding the control file and
reading datafile headers (P2=1). In general, this event is indicative of
disk contention on index reads.
Find out the P1, P2, and P3 for this Wait event using the query mentioned in Point
No 6.
In this case:
P1 = file#
P2 = block#
P3 = blocks
To find out the Segment on which it is doing the db file sequential read, Use the
following query:
select segment_name
from dba_extents
where file_id=&p1
and p2 between block_id and block_id+blocks-1;
Follow the Steps Mentioned in Point No. 7 to troubleshoot it further.
12.How to handle “db file scattered Read” wait event?
A db file scattered read is the same type of event as "db file sequential read",
Except that Oracle will read multiple data blocks. Multi-block reads are
typically used on full table scans. The name "scattered read" may seem
misleading but it refers to the fact that multiple blocks are read into DB block
buffers that are 'scattered' throughout memory.
Find out the P1, P2 and P3 for this Wait event using the query mentioned in Point
No 6.
In this case:
P1 = file#
P2 = block#
P3 = blocks
To find out the Segment on which it is doing the db file sequential read, Use the
following query:
select segment_name
from dba_extents
where file_id=&p1
and p2 between block_id and block_id+blocks-1;
Follow the Steps Mentioned in Point No. 7 to troubleshoot it further.
13.What should we do if the CPU load on the server is high?
Use the top command to identify the top 5 sessions. Identify the process id of these top sessions.
Identify the Oracle Session id using the query given in Point No. 2.
Identify the SQL used by this session using the query given in Point No. 4
Follow the Steps mentioned in the Point No 7 to Troubleshoot it further.
14.How to identify the sid of the session in the remote database?
Step 1: Identify the spid of the Session in the local database:
select spid
from v$process
where background is null
and addr in (select paddr
from v$session
where sid=&session_id);
Step 2: Identify the Session id in the Remote Database:
select sid
from v$session
where process='&SPID_IDENTIFIED_IN_STEP 1';
15.How to identify the objects accessed by a session?
select owner, object, type
from v$access
where sid=&session_id
and owner not in ('SYS','SYSTEM');
16.How to identify the parallel sessions for any oracle session id?
Select qcsid, sid
from v$px_session
where qcsid=&session_id;
17.What is the best approach for Tuning an Oracle Sql Query?
Avoid Using the Following:
a. Boolean Operators, Is null & Is not Null.
b. not in, != Operators
c. like '%patterns', not exists
Do's:
a. Enable aliases to prefix all columns.
b. Use sql joins instead of sub-queries
c. Make the tables with the least number of rows as the driving table by keeping them first in the FROM clause.
d. Use concatenated indexes wherever appropriate.
e. Pick up the Best Join method.
f. Nested loops joins are best for indexed joins of subsets.
g. Hash joins are usually the best choice for "big" joins
h. Pick the best "driving" table
i. Use bind variables. Bind variables are key to application scalability.
j. Use Oracle hints wherever appropriate
k. Compare performance between alternative syntax for your SQL statement
Use Explain Plan to Identify the Access path being used by the query.
Syntax is explain plan for actual_sql_statement;
You can see the output of the explain plan by running the following sql:
$ORACLE_HOME/rdbms/admin/utlxplp.sql
Alternatively, You can also trace the session by using the
following Command:
alter session set events '10046 trace name context forever,level 12';
Run the sql Query. This will generate the trace file in udump directory.
Use tkprof utility to get the readable output of this trace file. Use the
following Syntax:
tkprof trace_file_name trace_file_name.out sys=no explain=userid/pwd
This tkprof output file trace_file_name.out will have the access path
Used by the queries and as well the various timed statistics like
cpu time, elapsed time etc.
• sort – Sorts the SQL statements in the trace file by the criteria deemed most important by the DBA. This option allows the DBA to view the SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers. The following are the data elements available for sorting:
• prscnt – The number of times the SQL was parsed.
• prscpu – The CPU time spent parsing.
• prsela – The elapsed time spent parsing the SQL.
• prsdsk – The number of physical reads required for the parse.
• prsmis – The number of consistent block reads required for the parse.
• prscu - The number of current block reads required for the parse.
• execnt – The number of times the SQL statement was executed.
• execpu – The CPU time spent executing the SQL.
• exeela – The elapsed time spent executing the SQL.
• exedsk – The number of physical reads during execution.
• exeqry – The number of consistent block reads during execution.
• execu – The number of current block reads during execution.
• exerow – The number of rows processed during execution.
• exemis – The number of library cache misses during execution.
• fchcnt – The number of fetches performed.
• fchcpu – The CPU time spent fetching rows.
• fchela – The elapsed time spent fetching rows.
• fchdsk – The number of physical disk reads during the fetch.
• fchqry – The number of consistent block reads during the fetch.
• fchcu – The number of current block reads during the fetch.
• fchrow – The number of rows fetched for the query.
For example: sort=exeela,fchela
18.How do you determine the size of the database?
set serveroutput on;
declare
v_data_files_size number :=0;
v_temp_files_size number :=0;
v_redo_log_size number :=0;
v_total_db_size number :=0;
begin
select sum(bytes)/1024/1024/1024
into v_data_files_size
from dba_data_files;
--
select sum(bytes)/1024/1024/1024
into v_temp_files_size
from dba_temp_files;
--
select sum(bytes)/1024/1024/1024
into v_redo_log_size
from v$log;
--
v_total_db_size := v_data_files_size +v_temp_files_size + v_redo_log_size;
dbms_output.put_line('Total Database Size is 'v_total_db_size'GB');
end;
/
19.How do you determine the free space in a tablespace?
select sum(bytes)/1024/1024/1024
from dba_free_space
where tablespace_name='&tablespace_name';
20.How to add space to tablespaces?
alter tablespace tbs_name add datafile '/u01/oracle/oradata/tbs_name_01.dbf'
size 10g autoextend on next 1g maxsize 30g;
We can also resize a data file using the following syntax:
alter database datafile '/u01/oracle/oradata/tbs_name_01.dbf' resize 15g;
21.How to monitor space in udump, bdump, cdump?
Find out the udump, bdump & cdump directory by logging into sqlplus:
show parameter user_dump%
show parameter background_dump%
show parameter core_dump_dest%
Log out of sqlplus and go to the respective directories and do
df –k .
This command will give the free space available in the file system.
22.How to use OS command to find out the files which are greater than certain size?
find . –size +10000 –exec ls –lt {} \;
In the above command, 10000 is the number of OS blocks.
23.How to check the Status of the listener?
lsnrctl status LISTENER_NAME
24.How to start and stop the Listener?
lsnrctl start LISTENER_NAME
lsnrctl stop LISTENER_NAME
25.How to Find out the system privileges assigned to a user?
select * from dba_sys_privs
where grantee='&username or role_name';
26.How to Find out the system privileges assigned to a role?
select * from role_sys_privs
where role='&role_name';
27.How to find out the roles granted to other roles?
select * from role_role_privs
where role='&role_name';
28.How to find out the roles assigned to a user?
select * from dba_role_privs
where grantee='&username or role_name';
29.How to Find out the object privileges granted to a user?
select * from dba_tab_privs
where grantee='&username or role_name';
Posts
