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…
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,”)…
