Skip to main content

Performance Related Queries

Performance Related Queries

How to get  AWR plan change list:

SELECT STAT.SQL_ID, STAT.INSTANCE_NUMBER inst,
         SQL_TEXT,
         PLAN_HASH_VALUE,
         PARSING_SCHEMA_NAME,
         ELAPSED_TIME_DELTA/1000000 as sec,
         STAT.SNAP_ID,
         SS.END_INTERVAL_TIME,
         executions_delta, rows_processed_delta
    , round(elapsed_time_delta/executions_delta/1000000,3) as avg_per_exec
    FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS
   WHERE     STAT.SQL_ID = TXT.SQL_ID
         AND STAT.DBID = TXT.DBID
         AND SS.DBID = STAT.DBID
         AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER
         AND STAT.SNAP_ID = SS.SNAP_ID
         and elapsed_time_delta > 0
    --     AND STAT.DBID = 946615210
       --  AND STAT.INSTANCE_NUMBER = 1
         AND SS.BEGIN_INTERVAL_TIME >= SYSDATE - 15
      --AND ( stat.SQL_ID, plan_hash_value)  in (select sql_id, plan_hash_value from dba_hist_sql_plan where object_name in 'FK_ACT_TRANSACTION5' )
       --and upper(sql_text) like upper('INSERT%MINUS%')
       and stat.sql_id in '&SQL_ID'
ORDER BY snap_id DESC;



How to get Explain Plan for SQL_ID from cursor/AWR

set lines 250

select * from table (dbms_xplan.display_cursor('&&sql_id',&&child_number,'TYPICAL'));

set lines 250
set pages 500

select * from table (dbms_xplan.display_awr('&&sql_id'));


How to get PlanHashValues for SQL_ID


SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/


How to find Bind variables:

SET LINES 300 PAGES 1000
COLUMN sql_id      FORMAT a32
COLUMN bind_name   FORMAT a20
COLUMN bind_string FORMAT a40
COLUMN sql_text    FORMAT a50

-- show all the peeked binds
SELECT DISTINCT t.sql_id, b.name BIND_NAME, t.users_executing, b.value_string BIND_STRING,b.DATATYPE_STRING, b.last_captured, substr(t.sql_text,1,50) SQL_TEXT
  FROM gv$sql t , gv$sql_bind_capture b
 WHERE t.sql_id = b.sql_id
   AND b.value_string is not null
   AND t.sql_id='&sql_id'
   AND last_captured > trunc(sysdate) - 400 --last couple of days
 ORDER BY last_captured, bind_name

/

For AWR:

select INST_ID,SQL_ID,POSITION,NAME,VALUE_STRING from GV$SQL_BIND_CAPTURE where sql_id='<sql_id>' order by POSITION;

How to find Statistics are available or not.

SELECT name, value
FROM v$mystat, v$statname
WHERE v$mystat.statistic#=v$statname.statistic#
AND name IN ('consistent gets','db block gets','cleanouts and rollbacks - consistent read gets','cleanouts only - consistent read gets')
ORDER BY 1;

Stale stats:

select m.TABLE_OWNER,
 m.TABLE_NAME,
 m.INSERTS,
 m.UPDATES,
 m.DELETES,
 m.TRUNCATED,
 m.TIMESTAMP as LAST_MODIFIED,
 round((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0),2) as EST_PCT_MODIFIED,
 t.num_rows as last_known_rows_number,
 t.last_analyzed
From dba_tab_modifications m,
 dba_tables t
where m.table_owner=t.owner
and m.table_name=t.table_name
and table_owner not in ('SYS','SYSTEM','SQLTXPLAIN','STDBYPERF')
and ((m.inserts+m.updates+m.deletes)*100/NULLIF(t.num_rows,0) > 10 or t.last_analyzed is null)
order by timestamp desc;

column stats:


select * from DBA_TAB_COL_STATISTICS where owner ='SCHEMA' and table_name='TABLE_NAME';


======================================================================

How to find rollback (Undo Blocks) if we kill large trasaction:

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", 
undoblockstotal-undoblocksdone "ToDo", 
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / 
(undoblocksdone / cputime)) / 86400)) 
"Finish at" 
FROM v$fast_start_transactions; 

Reference Oracle Notes: Doc ID 464246.1 & Doc ID 238507.1

Comments

Popular posts from this blog

How to unmount and mount ACFS FILESYSTEM

How to unmount and mount ACFS FileSystem in Linux Unmounting ACFS File Systems ACFS file systems can be used by Oracle RAC for hosting software files for the database. It can also be used as a general purpose file system for non-database files. The ACFS file system is managed and administered by the Oracle Grid Infrastructure, So ACFS file systems will be impacted when shutting down the GI stack for patching GI homes. Shut down the processes (including non-Oracle processes) that use the files on ACFS, then unmount the ACFS file system. If the ACFS file system is used by Oracle database software, then perform Steps 1 and 2. 1.      Execute the following command to find the names of the CRS managed ACFS file system resource.  # crsctl stat res -w "TYPE = ora.acfs.type" -p | grep VOLUME 2.      Execute the following command to stop the CRS managed ACFS file system resource with the resource name found from Step 1. ...

How to perform GRID/RDBMS patching of DB systems deployed in OCI using dbcli command line utility

This procedure covers OS, Grid and RDBMS home patching for Oracle Cloud Infrastructure (OCI) DB systems using the dbcli utility. Run every step as root . Advantages of dbcli patching Simple command line, with no manual patch download or copy to the nodes. Patches are downloaded automatically if the service gateway can reach Oracle Object Storage and the YUM repos. Applied in a rolling fashion, so no downtime is needed. Disadvantages You can't choose the patch version; the latest available in OCI is applied. Rollback isn't possible from the console, and dbcli support is limited (see the note at the end). Steps 1. Backup Make sure a recent full backup exists before patching. 2. Blackout Create a blackout for both nodes (console or emctl ) to avoid unnecessary alerts. 3. Check patch availability # dbcli describe-component 4. Pre-checks Grid home: # dbcli update-server -p # dbcli describe-job -i <jobId> RDBMS home: # dbcli list-dbhomes...

To fix huge lag between primary and DR using 12c feature: 12c Recover from service

Scope If transport or apply lag is huge difference, then we can fix using 12c new feature “Recover from Service”. Prerequisites : Oracle Net connectivity is established between the physical standby database and the primary database. You can do this by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database. The password files on the primary database and the physical standby database are the same. The COMPATIBLE parameter in the initialization parameter file of the primary database and physical standby database is set to 12.0 or higher. Start RMAN and connect as target to the physical standby database. Detailed Steps: Step 1: Stop the MRP process in standby database. If dg broker: Edit database <dbname> set state=’APPLY-OFF’; Step 2:   Shut down the standby database and mount the same. Shut immediate;   (srvctl stop database –d <dbname>)  ...