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 ,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))…
To create AWR reports based on the automated metrics gathered:Connect to the correct Pluggable: select * from v$pdbs; Find the dbid:select * from v$database; If you want to create manually a AWR snapshot use the following: select sys.dbms_workload_repository.create_snapshot(‘ALL’) from dual;…
with cols as (select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,casewhen t.name in (‘datetime’,’datetime2′,’date’) then ‘DATE’end oracle_typefrom sys.all_columns ac join sys.types t on t.system_type_id=ac.system_type_idjoin sys.tables st on st.object_id=ac.object_idjoin sys.schemas s on s.schema_id=st.schema_idwhere st.name!=’sysdiagrams’),grp as(select upper(table_name) table_name, col= upper(STUFF ((SELECT ‘,’ + column_name+’ ‘+isnull(oracle_type,”)…
Fast Recovery Area is used for various components such as duplicates of control files and redo logs, backups, archive logs and flashback logs. If a database is in ARCHIVELOG mode and there is no space left in FRA, the database…