Queries

Queries

ORDER BY 1 desc;

Tuning

Create profile with the best plan-

1. alter session set nls_date_format = 'DD/MM/YY HH24:MI:SS';

2. select sql_id,plan_hash_value,timestamp,sum(cpu_cost),sum(io_cost),sum(cost) from DBA_HIST_SQL_PLAN where sql_id='&SQL_ID' group by sql_id,timestamp,plan_hash_value;

3. select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '&SQL_ID'; 

4. exec DBMS_SHARED_POOL.PURGE ('&addr, &hashvalue', 'C'); -- Provide details of the above query 

Profiling - 

5. Zap query with attached zap.sql with Parameters - SQL_ID and Plan Hash Value

zap.sql-

6. Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

and then Flush the shared pool..

7. exec DBMS_SHARED_POOL.PURGE ('&addr, &hashvalue', 'C'); -- Provide details of the above query

8. Drop profile with - BEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'coe_SQLID_HASH'); end;

9. Disable profile - EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>:pname,attribute_name=>'STATUS',value=>'DISABLED');

10. Enable profile - EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>:pname,attribute_name=>'STATUS',value=>'ENABLED');

9. Check all profiles created - 

select NAME, SIGNATURE, SQL_TEXT, FORCE_MATCHING from dba_sql_profiles where NAME='&SQL_Profile_name';

select trim(to_char(sysdate, 'Day'))||to_char(sysdate, ' dd ')||trim(to_char(sysdate, 'Month'))||to_char(sysdate, ' yyyy') from dual;

Number of Rows in a table from old stats -

select n.object_name as Obj_Name,o.ROWCNT,o.BLKCNT,to_char(o.SAVTIME,'dd/mm/yyyy HH:MI') as time from dba_objects n,WRI$_OPTSTAT_TAB_HISTORY o where o.obj#=n.object_id and n.object_name='&OBJECT_NAME';

select INSERTS, UPDATES, DELETES, count(1) from dba_tab_modifications where TABLE_OWNER='$OWNER' group by INSERTS, UPDATES, DELETES;

select count(1) from dba_tab_modifications where TABLE_OWNER='$OWNER';

select TABLE_NAME, count(1) from dba_tab_modifications where TABLE_OWNER='$OWNER' and TIMESTAMP like '14-JUN%' group by TABLE_NAME;

LOB details - 

select OWNER,TABLE_NAME from dba_lobs where SEGMENT_NAME=’&LOB'

No of execution details from AWR - 

col begin_time for a25

col end_time for a11

col inst for 99999

col snapid for 999999

set lines 200

set pages 20000

select snap_id snapid,

(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time

,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time

,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,

EXECUTIONS_DELTA Executions,

ROWS_PROCESSED_DELTA rows1,

round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,

round( ELAPSED_TIME_DELTA /1000000,0) elapsed

from wrh$_sqlstat a where sql_id in('&SQL_ID')

order by snap_id, INSTANCE_NUMBER;

Execution time of SQL from AWR-

column program format a30

SELECT query_runs.*,

ROUND ( (end_time - start_time) * 24, 2) AS duration_hrs

FROM ( SELECT u.username,

ash.program,

ash.sql_id,

ASH.SQL_PLAN_HASH_VALUE as plan_hash_value,

ASH.SESSION_ID as sess#,

ASH.SESSION_SERIAL# as sess_ser,

CAST (MIN (ASH.SAMPLE_TIME) AS DATE) AS start_time,

CAST (MAX (ash.sample_time) AS DATE) AS end_time

FROM dba_hist_active_sess_history ash, dba_users u

WHERE u.user_id = ASH.USER_ID AND ash.sql_id = lower(trim('&sql_id'))

GROUP BY u.username,

ash.program,

ash.sql_id,

ASH.SQL_PLAN_HASH_VALUE,

ASH.SESSION_ID,

ASH.SESSION_SERIAL#) query_runs

ORDER BY sql_id, start_time;

Long Running SQL - 

select m.INST_ID, TO_CHAR(TRUNC((sysdate-SQL_EXEC_START)*86400/3600),'FM9900') || ':' ||TO_CHAR(TRUNC(MOD((sysdate-SQL_EXEC_START)*86400,3600)/60),'FM00') || ':' ||TO_CHAR(MOD((sysdate-SQL_EXEC_START)*86400,60),'FM00') duration,

sid,m.session_serial#,m.sql_id,sql_plan_hash_value,username,px_maxdop parallel,m.cpu_time/1000000, to_char(s.sql_fulltext), q.rows_processed

from gv$sql_monitor m,gv$sql s, gv$sqlarea q where m.sql_id = s.sql_id and q.sql_id = s.sql_id and status = 'EXECUTING' and username is not null

No of Distinct values for col - 

SELECT column_name, num_distinct, num_buckets, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name = '&TAB_NAME' AND column_name = '&COL_NAME';

DBTIMEZONE details -

select dbtimezone from dual;

Exadata - Flashcache details - 

select inst_id, name,value from gv$sysstat where name in ('physical read total IO requests','cell flash cache read hits');

select segment_name,cell_flash_cache from dba_segments;

Invisible indexes usages -

alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

to use the INVISIBLE indexes...

alter session set optimizer_index_cost_adj=1; ---- set val 1 to 100

Flasdback shared pool -

alter system flush SHARED_POOL;

alter system flush buffer_cache;

SGA resize stats -

select component, oper_type, oper_mode, initial_size/1024/1024/1024 "Initial in GB" , TARGET_SIZE/1024/1024/1024 "Target in GB", FINAL_SIZE/1024/1024/1024 "Final in GB", status from v$sga_resize_ops;

SGA occupants - 

SELECT o.object_name object_name, o.object_type object_type, COUNT(1) num_blocks FROM dba_objects o, v$bh bh WHERE o.object_id = bh.objd AND o.owner NOT IN ('SYS','SYSTEM') GROUP BY o.object_name, o.object_type ORDER BY COUNT(1) DESC;

SGA - Object wise Read / status- V$BH displays the status and number of pings for every buffer in the SGA.

SELECT t.name AS tablespace_name, o.object_name, SUM(DECODE(bh.status, 'free', 1, 0)) AS NotcurrentlyINuse, SUM(DECODE(bh.status, 'xcur', 1, 0)) AS Exclusivee, SUM(DECODE(bh.status, 'scur', 1, 0)) AS Sharedcurrent, SUM(DECODE(bh.status, 'cr', 1, 0)) AS ConsistentRead, SUM(DECODE(bh.status, 'read', 1, 0)) AS BeingReadFromDisk, SUM(DECODE(bh.status, 'mrec', 1, 0)) AS InMediaRecoveryMode, SUM(DECODE(bh.status, 'irec', 1, 0)) AS IninstanceRecoveryMode FROM v$bh bh JOIN dba_objects o ON o.object_id = bh.objd JOIN v$tablespace t ON t.ts# = bh.ts# GROUP BY t.name, o.object_name ORDER BY 4 DESC;

Select O.Owner Object_Owner, O.Object_Name Object_Name, O.Object_Type Object_Type, Count(1) Num_Blocks, Count(1)*(8192)/(1024*1024*1024) From Dba_Objects O, V$bh Bh Where O.Object_Id = Bh.Objd And O.Owner Not In ('SYS','SYSTEM') Group By O.Owner, O.Object_Name, O.Object_Type Order By Count(1) Desc; --- 8192 is a block size

HIT ratio -

SELECT name, physical_reads, db_block_gets, consistent_gets, 1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS;

Set Trace -

ALTER SESSION SET events '10053 trace name context forever';

ALTER SESSION SET events '10053 trace name context off';

General Queries -

select owner, table_name, num_rows, last_analyzed, temporary from dba_tables where table_name = '&TABLE_NAME';

select 'alter table ' || table_owner || '.' || table_name || ' drop partition ' || partition_name || ' ;' from all_tab_partitions where table_name like '&TABLE_NAME' AND TABLE_OWNER = '&TABLE_OWNER'and tablespace_name like '&Tablespace_Name';

select 'alter table SCOTT.' || TABLE_NAME || ' move partition ' || PARTITION_NAME || ' tablespace ABC;' from dba_tab_partitions where TABLE_OWNER='&OWNER';

select 'ALTER TABLE ' || '&TABLE_OWNER' || '.' || '&TABLE_NAME' || 'RENAME PARTITION ' || '&OLD_PartName' || 'TO ' || ' &NEW_PartName' || ';' from dual;

select SQL_ID,LAST_SQL_ACTIVE_TIME,SQL_TEXT from gv$open_cursor where sid='&SiD' and upper(SQL_TEXT) like '%&Some_TEXT%';

select table_name, index_name, column_position, column_name from dba_ind_columns where table_name = '&TABLE_NAME' and table_owner = '&OWNER' order by 1,2,3 ;

Change the maxsize

alter database datafile 'DB_File_Name' AUTOEXTEND ON MAXSIZE 30g;

SELECT address, child_address, sql_text, sql_id FROM v$sql WHERE sql_fulltext LIKE '%&SQL_ID%';

SELECT fetches, executions, parse_calls, disk_reads FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';

SELECT application_wait_time, user_io_wait_time, cpu_time FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';

SELECT optimizer_mode, optimizer_cost, sorts FROM V$SQLAREA WHERE sql_id LIKE '%&SQL_ID%';

alter system set db_recovery_file_dest_size=35G;

select count(*) from dba_tab_partitions;

select count(*) from dba_tab_subpartitions;

select count(*) from dba_ind_partitions;

select * from dba_dependencies where name in ('ABC');

select * from v$active_instances;

alter system flush shared_pool;

alter system flush buffer_cache;

SELECT * FROM V$RESULT_CACHE_OBJECTS WHERE name like '%&obj_name%' ORDER BY CREATION_TIMESTAMP DESC;

SELECT a.type, Substr(a.owner,1,30) owner,a.sid,Substr(a.object,1,30) object FROM gv$access a WHERE a.owner NOT IN ('SYS','PUBLIC') ORDER BY 1,2,3,4;

Col Usages from specific table -

SELECT c.name, cu.timestamp, cu.equality_preds AS equality, cu.equijoin_preds AS equijoin, cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range, cu.like_preds AS "LIKE", cu.null_preds AS "NULL"

FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u

WHERE c.obj#=cu.obj# (+) AND c.intcol# = cu.intcol# (+)

AND c.obj# = o.obj# AND o.owner#=u.user#

AND o.name = '&TABLE_NAME' AND u.name = '&SCHEMA_NAME' ORDER BY c.col#;

Who locked account from Audit trail -

SELECT username,userhost, os_username, TIMESTAMP FROM dba_audit_session WHERE username='&USER_NAME' and returncode=1017 order by TIMESTAMP; ==== Returncode - 1017 - invalid id/pass, 2004 - security violation and 0 - success

or set Trace as below and check the trace file generated on trace file / background dump destination -

alter system set events '1017 trace name errorstack level 10';

alter system set events '1017 trace name errorstack off';

To get the resouce limits from DB -

select * from v$resource_limit;

Number of session per user -

select inst_id, username, osuser, count(*) from gv$session where type <> 'BACKGROUND' group by inst_id, username, osuser having count(*) > 20 order by count(*);

Collect Stats - Compute Stats -

ANALYZE TABLE <TRANS> COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE,OPTIONS=>'GATHER STALE');

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => '&OWNER', tabname => '&TABLE_NAME', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade => false, degree => DBMS_STATS.DEFAULT_DEGREE) ;

DBMS_STATS.GATHER_TABLE_STATS (NULL,'EMP', method_opt => 'FOR COLUMNS sal SIZE 10');

ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;

Getting the system statistics - 

select * from aux_stats$

Manipulating the stats -

The following example assumes that your production WINNERS table is going to have 1,000,000 rows in 6,000 blocks:

EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>'HROA_DEV', TABNAME=>'WINNERS', NUMROWS=> 1000000, NUMBLKS=> 6000);

Monitoring Table -

ALTER TABLE WINNERS MONITORING;

Estimate Stats -

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

Delete stats

exec dbms_stats.delete_table_stats(ownname => 'table-owner>', tabname => '<table-name>');

Locked statistics - 

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Lock / unlock stats

exec dbms_stats.unlock_schema_stats('&Schema_name'); 

exec dbms_stats.unlock_table_stats('&OWNER','&Table_name');

Copying Statistics Using DBMS_STATS

DBMS_STATS gives you the ability to copy statistics from one schema to another, or from one database to another, using the following procedure:

Step 1. Create a table to store the statistics, if you have not already done so:

EXECUTE SYS.DBMS_STATS.CREATE_STATS_TABLE (OWNNAME=>'HROA', STATTAB=>'HROA_STAT_TABLE');

Step 2. Populate the table with the statistics from the schema that you are copying from:

EXECUTE SYS.DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME=> 'HROA', STATTAB=>'HROA_STAT_TABLE', STATID=>'HROA_21SEP_2001');

Step 3. If you are copying statistics to a different database, such as from production to development, export and import that statistics table as required:

exp hroa/secret@prod file=stats tables=hroa_stat_table

imp hroa/secret@dev file=stats tables=hroa_stat_table

Step 4. Populate the statistics in the target schema's dictionary. In the following example, statistics are being loaded for the schema HROA_TEST from the table named HROA_STAT_TABLE:

EXECUTE SYS.DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME=>'HROA_TEST', STATTAB=>'HROA_STAT_TABLE', STATID=>'HROA_21SEP_2001', STATOWN=> 'HROA');

Get the Session level OPTIMIZER Hint set -

SELECT S.SID, SUBSTR(S.PROGRAM,1,8) PROGRAM, SOE.NAME, SOE.VALUE, SOE.ISDEFAULT FROM V$SESSION S, V$SES_OPTIMIZER_ENV SOE WHERE S.SID=SOE.SID and SOE.ISDEFAULT='NO' and (SOE.NAME like '%&PARAMETER%' or S.SID= '&SID' ) ORDER BY NAME, PROGRAM;

Flasdback recovery area usgaes -

SELECT Name, (SPACE_LIMIT/1024/1024/1024) Space_Limit_GB, SPACE_USED/1024/1024/1024 Space_Used_GB, SPACE_RECLAIMABLE, NUMBER_OF_FILES FROM V$RECOVERY_FILE_DEST;

SELECT * from V$FLASH_RECOVERY_AREA_USAGE;

RMAN backup details -

select RECID, STAMP, COMPLETION_TIME, INCREMENTAL_LEVEL from v$backup_set where INCREMENTAL_LEVEL=&DBACKUP_LEVE

select start_time, END_Time, input_bytes/(1024*1024), output_bytes/(1024*1024), status from v$rman_backup_job_details order by 1;

select * from v$database_block_corruption;

select * from V$backup_corruption;

Getting the BLOCK details -

select rowid, dbms_rowid.rowid_block_number(rowid) blockno, dbms_rowid.rowid_relative_fno(rowid) fno from &SCHEMA.TABLE where rownum < 100

Select segment_name,segment_type,owner from dba_extents where file_id=&file_number and &block_number between block_id and block_id+blocks-1;

Getting the data of block details above - //

select * from owner.table_name where dbms_rowid.rowid_block_number(rowid)=4770 and dbms_rowid.rowid_relative_fno(rowid)=0

$rman target / 

RMAN>run { 

-- allocate channels 

backup validate check logical database; -- Backup time is time taken to read + write blocks where as validate is the time only to read the block.. so validate would take less time than backup.

} 

RMAN> run {blockrecover corruption list;}

To do individual block recovery we can use 

RMAN> run {blockrecover datafile file# block block_number;} 

Example: 

RMAN> run {blockrecover datafile 5 block 114;} 

RMAN>RESTORE ARCHIVELOG ALL VALIDATE; " - --- This will validate archived logs before using them for recovery. 

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#

, greatest(e.block_id, c.block#) corr_start_block# 

, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block# 

, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 

- greatest(e.block_id, c.block#) + 1 blocks_corrupted 

, null description 

FROM dba_extents e, v$database_block_corruption c 

WHERE e.file_id = c.file# 

AND e.block_id <= c.block# + c.blocks - 1 

AND e.block_id + e.blocks - 1 >= c.block# 

UNION 

SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# 

, header_block corr_start_block# 

, header_block corr_end_block# 

, 1 blocks_corrupted 

, 'Segment Header' description 

FROM dba_segments s, v$database_block_corruption c 

WHERE s.header_file = c.file# 

AND s.header_block between c.block# and c.block# + c.blocks - 1 

UNION 

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# 

, greatest(f.block_id, c.block#) corr_start_block# 

, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# 

, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 

- greatest(f.block_id, c.block#) + 1 blocks_corrupted 

, 'Free Block' description 

FROM dba_free_space f, v$database_block_corruption c 

WHERE f.file_id = c.file# 

AND f.block_id <= c.block# + c.blocks - 1 

AND f.block_id + f.blocks - 1 >= c.block# 

order by file#, corr_start_block#; 

Check oracle owned files with below command - eg.. provide your local passwd

suexec -u oracle /usr/bin/crontab -l export PATH=$PATH:/usr/local/bin:.

Get sql_id and child_number for the preceding statement:

SELECT sql_id, child_number FROM gv$sql WHERE sql_text LIKE '%TOTO%';

SELECT sql_id, child_number, optimizer_mode, plan_hash_value FROM gv$sql WHERE sql_ID like '&SQL_ID';

Display the execution plan of all cursors matching the string 'TOTO':

SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';

To get an execution plan -

select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'));

To get Report of perticular sql -

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.

• The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
• The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
• The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
• The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.
• The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
• The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

To get an execution plan -

explain plan set statement_id='TTT' for

select * from dual;

set long 2000

set linesize 200

set pagesize 2000

set trimspool on

select * from table(sys.dbms_xplan.display('','TTT','ALL'));

To get an execution plan ALL STATS-

select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));

select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats last'));

select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats all'));

select * from table(dbms_xplan.display_cursor('&sql_id','&child_no',Format