Performance Tunning

Sql Tuning Example

PT - Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] examples

https://varunyadav27.blogspot.com/2019/08/pt-usage-set-autotrace-off-on-traceonly.html

PT- oracle tunning advisor example
https://varunyadav27.blogspot.com/2019/05/oracle-tunning-advisor-example.html

PT- Enable plustrace for Autotrace  schema

https://varunyadav27.blogspot.com/2019/08/pt-enable-plustrace-for-autotrace-of.html

PT - Execution Plan using EXPLAIN PLAN FOR Command

https://varunyadav27.blogspot.com/2019/08/pt-displaying-execution-plan-using.html

PT - Displaying Execution Plan using dbms_xplan.display_cursor

https://varunyadav27.blogspot.com/2019/08/pt-displaying-execution-plan-using.html

PT : Hints Covered in the Lecture

https://varunyadav27.blogspot.com/2019/10/pt-hints-covered-in-lecture.html

Table Access and joins

PT - SQL Operators and joins

PT - Table Access Examples while executing queries

PT - Joins Methods and Examples

Hints 

PT - Optimizer using SQL hints

PT - Hints /* + */ using Optimizer Goal:

PT - OPTIMIZER Hints using Table and Index Access

PT - Hints Using ORDERED and LEADING Examples

PT - Hints using Join Method examples

STATS GATHER

PT - Optimizer Statistics Collection Automatic and Manual examples

PT - Statistics Gathering Optimizer Table Example

PT - Managing Automatic Optimizer Statistics Scheduler window

PT - Gathering Optimizer Statistics Manually Table examples with stats and explain plan

PT - Histograms are Automatically Gathered example

PT - Lock | Unlock Optimizer Statistics Examples ORA-20005: object statistics are locked (stattype = ALL)

PT - EXPDP IMPDP using EXPORT| IMPORT _TABLE_STATS Optimizer Statistics


Cursor Sharing

PT - Improving Performance Through Literals, Bind Variables and Cursor Sharing

PT - Change parameter CURSOR_SHARING from EXACT to FORCE

Log Miner

LOGMINER - Based on SCN

LOGMINER - Based on Date and time generation

NEW LOGMINER - Based on archivelog generation

Performance Tuning Examples 

Trace 

PT - Extracting Session level trace based on sid and serial#

Use Case: Using Session Waiting Events with Hung or Very Slow Sessions

PT - Row level lock contention TX blockers and waiters

PT- Wait Class overview and query output examples

Row Migration and Row chaining

PT - Row migration and Row Chaining 

Correcting Row Migration by Increasing PCTFREE

Correcting Row Chaining by using Larger Block Size

Row Migration Using Online Redefinition

EXPDP | IMPDP - table_exists_action=tuncate  |  Or drop and recreate expdp | impdp 

AWR 

AWR- Managing Space Consumed by AWR Snapshots

AWR - Set AWR Retention period from 1hr to 30 minutes

AWR - Script execution's For non and RAC Database AWR report

AWR - Delete AWR Snapshots ID

Table Compression 

PT - Oracle Compression Basic and advanced table example

PT - OS performance Monitoring using VMSTAT

PT - OS TOP Monitoring commands

PT - Monitoring PS command

PT - IOSTAT MPSTAT Performance Monitoring commands

######################################################################

Active-inactive sessions

select  ses.sid, ses.username, optimizer_mode, cpu_time, elapsed_time, executions, ses.sql_exec_start, client_info, PROGRAM_ID, PROGRAM_LINE#,  machine, process, p.program,  p.spid, ses.osuser , status 

from v$sql sqlarea, v$session ses, v$process p

where ses.sql_hash_value = sqlarea.hash_value

and ses.sql_address = sqlarea.address

and ses.paddr = p.addr

and ses.username is not null

and Elapsed_time/greatest(executions,1)>12000000000

and status='ACTIVE';

######################################################################

Cursor Percentage 

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 

s.username, s.machine

from v$sesstat a, v$statname b, v$session s 

where a.statistic# = b.statistic#  and s.sid=a.sid

and b.name = 'opened cursors current' 

group by s.username, s.machine

order by 1 desc;

###########################################################

COL STAT_NAME FORMAT A43

col TIME_MICRO_S for a30

SELECT STAT_NAME, TO_CHAR(VALUE,'999,999,999,999') TIME_MICRO_S

, ROUND(VALUE/(SELECT VALUE FROM V$SYS_TIME_MODEL WHERE STAT_NAME='DB time')*100,2) PCT

FROM V$SYS_TIME_MODEL

WHERE VALUE <>0 AND STAT_NAME NOT IN ('background elapsed time', 'background cpu time')

ORDER BY VALUE DESC

FETCH FIRST 7 ROWS ONLY;

SQL> /

STAT_NAME                                   TIME_MICRO_S                          PCT

------------------------------------------- ------------------------------ ----------

DB time                                     ################                      100

sql execute elapsed time                    ################                    99.88

DB CPU                                      ################                    92.05

RMAN cpu time (backup/restore)                93,483,315,598                     2.11

connection management call elapsed time        2,501,706,815                      .06

parse time elapsed                             1,863,142,548                      .04

hard parse elapsed time                        1,110,112,961                      .03

7 rows selected.

SQL>

###########################################################

select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;'

FROM v$session se, v$sesstat ss, v$statname st

WHERE ss.statistic# = st.statistic#

   AND name LIKE  '%CPU used by this session%'

   AND se.sid = ss.SID 

   AND se.username IS NOT NULL

   and se.sql_id='b0a0vf29z9416'

  ORDER BY 4 DESC;

  

  select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where ;

  

  

  select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where sql_id='1amhfbhjmfn6y'; and username='DBSNMP';

--------------------------------------------- UNDO ---------------------------------------------

select max(MAXQUERYLEN) from dba_hist_undostat;

select max(UNDOBLKS / ((end_time - begin_time) * 60 * 60 * 24)) undo_blk_per_sec from dba_hist_undostat;

select max(UNDOBLKS / ((end_time - begin_time) * 60 * 60 * 24)) undo_blk_per_sec from dba_hist_undostat;

select sum(bytes)/1048576 MB from dba_data_files where file_name like '%undo%' or file_name like '%UNDO%';

-------------------------------------- Temp Tablespace Utilization -----------------------------

--Sort usage by amount sort and sqlid

set lines 200 pages 1000

col USERNAME format a10

col TABLESPACE format a15

col SQL_TEXT format a20

col SID format 999999

col EVENT format a15

col PROGRAM format a15

col serial# format 99999

col used format 99999

select su.username,

            su.sql_id,

            su.tablespace,

            sq.sql_text, 

            se.sid,

            se.program,

            se.serial#,

            se.event,

            (su.blocks)*(tb.block_size/1048576) usedMB

from v$sort_usage su

inner join v$sqlarea sq on su.sql_id=sq.sql_id

inner join v$session se on su.session_addr=se.saddr

inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name

order by su.username;

--process id which consume high sort

set lines 200 pages 1000

col USERNAME format a10

col TABLESPACE format a10

col SQL_TEXT format a20

col SID format 999999

col EVENT format a15

col PROGRAM format a15

col serial# format 99999

col used format 99999

col spid format 999999

col sorts format 999

col SORT_CNT for 999

col USEDMB for 999999

select su.username,

       su.sql_id,

       su.tablespace,

       p.spid,

       count(*) sort_cnt,            

       se.sid,  

       se.program,

       se.serial#,

       se.event,

       sum(su.blocks)*(tb.block_size/1048576) usedMB

from v$sort_usage su

inner join v$session se on su.session_addr=se.saddr

inner join v$process p on p.addr=se.paddr

inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name

group by su.username,su.sql_id,su.tablespace,p.spid,se.sid,se.program,se.serial#,se.event,tb.block_size/1048576

order by su.username;

col table_name for a20

select table_name, stale_stats, last_analyzed from dba_tab_statistics where table_name='XTBL';

exec dbms_stats.set_table_prefs('KISH','XTBL','STALE_PERCENT',4.5);