Performance Tuning

Performance Tuning
1-Top database performance issue
2-What is the function of query optimizer? 
3-View Current Session with its SQL Text 
4-Kill Session 
5-How to find Execution time to complete the job
6-Show duration of execution time in sql plus 
7-Use Parallel query hint in oracle 
8-Show CPU usage for each user 
9-SQL Tuning Advisor
10-show history of Advisor log
11-show advisor task 
12-execute advisor task.
13-Show report of advisor 
14-update table statistic 
15-Bitmap Index 
16-Oracle Performance Check

---

1-Top database performance issue

• Bad connection management
• Bad sql
• I/O issue
• Nonstandard init parameter
• Long full table scans
• In-disk sorts
• Poor use of cursor
• High amount of recursive SQL
• Schema error and Optimizer problem

2-What is the function of query optimizer?

• It determines the most efficient execution plan for processing SQL statement
• Evaluate on expression and condition
• Decides how to access data
• Decides how to join table
• Decides which path is most efficient 
• Use object & System Statistics

3-View Current Session with its SQL Text

      select * from v$sql where SQL_ID in
      (select SQL_ID from v$session )

 4-Kill Session 

     Alter system kill session 'sid,serial' immediate;

 5-How to find Execution time to complete the job


Work with : DLL, DML 


Ex: Create table X as select 

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,opname,target,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE",TIME_REMAINING
FROM V$SESSION_LONGOPS
where OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;


SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,opname,target,message,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE",TIME_REMAINING
FROM V$SESSION_LONGOPS
where sid=81

6-Show duration of execution time in sql plus 
sql> set timing on
sql>select count(*) from employees;
count(*)
100000
Elapsed: 00:01:23.33

7-Use Parallel query hint in oracle 

SELECT /*+ PARALLEL(Employees , 8) */
count(*) from Employees x
where snapshotdate='16222'

8-Show CPU usage for each user 

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

9-SQL Tuning Advisor
The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

10-show history of Advisor log
select * from dba_advisor_log
show complete and incomplete advisor you have generated
11-show advisor task

select * from dba_advisor_tasks 

12-execute advisor task

exec dbms_sqltune.execute_tuning_task('SQL_TUNING_StockReconsile');

13-show report of advisor

select dbms_sqltune.report_tuning_task('SQL_TUNING_StockReconsile') from dual;

 14-update table statistic

begin
    SYS.dbms_stats.gather_table_stats(ownname => 'BILLING', tabname =>
            'TBL_QB_SALETRACKER_ST_REQUEST', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
end;
15-BitMap Index

Item	1	2	3	4	5	6
Sale	0	0	0	1	0	1
Account	0	0	1	0	1	0
Finance	1	0	0	0	0	0
Cleark	0	1	0	0	0	0
ID	Item
1	Finance			Bitmap index
2	Cleark
3	Account
4	Sale
5	Account
6	Sale

16-Oracle Performance Check
A-System Tuning
1-tuning log switches
view alert log file to check for log switch duration, if the log switch duration is very fast than
increase the log file size , so the switch time might not as fast as before