A Distributed Availability Group (DAG) is an Availability Group that instead of nodes it consiss of two or more other Availability Groups. It is quite flexible and Availability Groups can be of different architecture, version even OS. For example it…
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(+) ;
Find which table references a sequence qith the following statement: SELECT s.object_id AS sequence_object_id,s.name AS sequence_name,OBJECT_SCHEMA_NAME(o.parent_object_id) + ‘.’+ OBJECT_NAME(o.parent_object_id) AS table_name,r.*FROM sys.sequences sCROSS APPLY sys.dm_sql_referencing_entities(OBJECT_SCHEMA_NAME(s.object_id) + ‘.’ + s.name,’OBJECT’) rJOIN sys.objects oON o.object_id = r.referencing_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 ,lmode,request,a.type,c.object_name,decode(row_wait_obj#,-1,’Holder…
Find the log switches with following query: SELECT TRUNC (first_time) “Date”, TO_CHAR (first_time, ‘Dy’) “Day”,COUNT (1) “Total”,SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’00’, 1, 0)) “h0”,SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’01’, 1, 0)) “h1”,SUM (DECODE (TO_CHAR (first_time, ‘hh24′), ’02’, 1, 0))…
SELECT o.name as table_name, rv.value as partition_range,fg.name as file_groupName, p.partition_number,max(p.rows) as number_of_rows,sum(au.total_pages) *8 [Total MB],sum(au.used_pages)*8 [Used MB]FROM sys.partitions pINNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_idINNER JOIN sys.objects o ON p.object_id = o.object_idINNER JOIN sys.system_internals_allocation_units au…