Statspack is a tool available from Oracle 8. It is still an option for gathering workload statistics, if diagnostics pack is not available. Standard Editions of Oracle do not support Diagnostic pack as it is an addon of Enterprise Edition.…
Keep in mind that if normal or extended redundancy are used then additional disk must be added in each filegroup by using FAILGROUP clause.
select df.tablespace_name “Tablespace”,nvl(totalusedspace,0) “Used MB”,(df.totalspace – nvl(tu.totalusedspace,0)) “Free MB”, df.actualspace as “Actual File Space”,df.totalspace “Total MB”,round(100 * ( (nvl(tu.totalusedspace,0))/ df.totalspace),2) “Pct. Used”,round(100 * ( (df.totalspace – nvl(tu.totalusedspace,0))/ df.totalspace),2)“Pct. Free”from(select tablespace_name,round(sum(decode(autoextensible,’YES’,maxbytes,’NO’,bytes)) / 1048576) TotalSpace, round (sum(bytes)/1048576) ActualSpacefrom dba_data_filesgroup by tablespace_name) df,(select…
Enable Unified Auditing To enable Unified Auditing (being available since 12c) stop instance and listener and relink auditing library: Check current value:SELECT value FROM v$option WHERE parameter = ‘Unified Auditing’;Let’s assume it is FALSE.Stop instance. Go to library directory and…
SELECTsql_id, COUNT(DISTINCT plan_hash_value)FROMdba_hist_sqlstat stat,dba_hist_snapshot ssWHEREstat.snap_id = ss.snap_idAND ss.dbid = stat.dbidAND ss.instance_number = stat.instance_numberAND ss.begin_interval_time >= sysdate – 7AND ss.end_interval_time <= sysdate AND stat.plan_hash_value <> 0AND stat.executions_delta > 0AND stat.parsing_schema_name NOT IN ( ‘SYS’, ‘SYSTEM’ )GROUP BYsql_idhaving COUNT(DISTINCT plan_hash_value) >1ORDER BY1;
select s.sid , s.serial#, p.spid ospid, q.sql_text , s.sql_child_numberfrom v$process p, v$session s, v$sqlarea qwhere p.addr = s.paddrand s.sql_id=q.sql_id(+) ;
DECLARE v_sqlid varchar2(20) :='<SQL ID>’;l_sql_tune_task_id VARCHAR2(100);BEGINl_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (sql_id => v_sqlid ,scope => DBMS_SQLTUNE.scope_comprehensive,time_limit => 120,task_name => v_sqlid,description => ‘Tuning task for statement ‘||v_sqlid);DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);END;/ EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘<SQL ID>’); select dbms_sqltune.report_tuning_task(‘v_sqlid ‘<SQL ID>’) from dual;
select * from dba_tab_statistics where STALE_STATS=’YES’; SELECT instance_id,JOB_NAME,ACTUAL_START_DATE,RUN_DURATION,STATUSFROM DBA_SCHEDULER_JOB_RUN_DETAILSWHERE JOB_NAME like ‘%OPT%’order by ACTUAL_START_DATE desc;
A blocking lock is when a resource is locked from one session, and another session needs to acquire a new non compatible lock on the same resource. Find the locking resources with the following statement: SELECT a.sid||decode(request,0,'(holder)','(waiter)'||':blocked by:'||blocking_session) sess_id ,<br>lmode,<br>request,<br>a.type,<br>c.object_name,<br>decode(row_wait_obj#,-1,'Holder…
Find the log switches with following query: SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day",<br>COUNT (1) "Total",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",<br>SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0))…