Source: DMF/PuC/AIF/DBA.sql
select * from all_tables where table_name = 'RCIB_INTEGRATION_ITEM_FULL';
SELECT * FROM all_scheduler_job_run_details WHERE status <> 'SUCCEEDED' and job_name not like 'SAP_ENRICH_DELTA_TRANSFER_JOB%' ORDER BY log_date DESC;
SELECT * FROM all_scheduler_jobs WHERE jOB_NAME LIKE 'SEND_TO_MFCS_ITEM_%';
SELECT * FROM all_scheduler_job_run_details WHERE job_name like 'SEND_TO_MFCS_ITEM_%' ORDER BY log_date DESC;
select distinct 'exec dbms_stats.gather_table_stats(''' ||owner||''','''||table_name||''',granularity=>''ALL'',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>''for all columns size auto'',degree=>8,cascade=>true,no_invalidate=>FALSE);' from DBA_TAB_STATISTICS where stale_stats='YES' and table_name not like 'BIN$%' and owner in ('WKSP_RCIB') and stattype_locked is null
union
select distinct 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',granularity=>''ALL'',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>''for all columns size auto'',degree=>8,cascade=>true,no_invalidate=>FALSE);' from DBA_IND_STATISTICS where stale_stats='YES' and table_name not like 'BIN$%' and owner in ('WKSP_RCIB') and stattype_locked is null
union
select distinct 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',granularity=>''ALL'',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>''for all columns size auto'',degree=>8,cascade=>true,no_invalidate=>FALSE);' from DBA_TAB_STATISTICS where stale_stats='YES' and table_name not like 'BIN$%' and owner in ('DMF') and stattype_locked is null
union
select distinct 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',granularity=>''ALL'',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>''for all columns size auto'',degree=>8,cascade=>true,no_invalidate=>FALSE);' from DBA_IND_STATISTICS where stale_stats='YES' and table_name not like 'BIN$%' and owner in ('DMF') and stattype_locked is null
union
select distinct 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',granularity=>''ALL'',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>''for all columns size auto'',degree=>8,cascade=>true,no_invalidate=>FALSE);' from DBA_TAB_STATISTICS where stale_stats='YES' and table_name not like 'BIN$%' and owner in ('DMF2') and stattype_locked is null
union
select distinct 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',granularity=>''ALL'',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>''for all columns size auto'',degree=>8,cascade=>true,no_invalidate=>FALSE);' from DBA_IND_STATISTICS where stale_stats='YES' and table_name not like 'BIN$%' and owner in ('DMF2') and stattype_locked is null order by 1;
exec dbms_stats.gather_table_stats('DMF','ITEM_LOC_CTRL',granularity=>'ALL',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'for all columns size auto',degree=>8,cascade=>true,no_invalidate=>FALSE);
exec dbms_stats.gather_table_stats('DMF','ITEM_LOC_SOH_CTRL',granularity=>'ALL',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'for all columns size auto',degree=>8,cascade=>true,no_invalidate=>FALSE);
SELECT
event,
--wait_class,
p1text,
COUNT(*) seconds_waited,
round(COUNT(*) /600 * 100,2) as pct_of_time
FROM gv$active_session_history ash
WHERE ash.user_id = (
SELECT user_id
FROM dba_users
WHERE username = 'DMF'
)
AND ash.session_state = 'WAITING'
AND ash.sample_time >= SYSDATE - (5/1440)
GROUP BY event, p1text
ORDER BY seconds_waited DESC;
-- SKUs
-- 77 ~21538
-- Barcodes
-- 99 ~28023
-- 82 ~23023
SELECT
event,
wait_class,
COUNT(*) samples,
ROUND(COUNT(*) * 100 /
SUM(COUNT(*)) OVER (), 2) pct
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY event, wait_class
ORDER BY samples DESC;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'RCIB_DMF_GATHER_STATS',
job_type => 'STORED_PROCEDURE',
job_action => 'PRC_RCIB_DMF_GATHER_STATS',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE,
comments => 'Gather stale statistics every hour'
);
DBMS_SCHEDULER.ENABLE(NAME => 'RCIB_DMF_GATHER_STATS');
END;
/
BEGIN
DBMS_SCHEDULER.DROP_JOB (
job_name => 'RCIB_DMF_GATHER_STATS',
force => TRUE
);
END;
/
create or replace PROCEDURE PRC_RCIB_DMF_GATHER_STATS
IS
BEGIN
FOR r IN (
SELECT DISTINCT owner, table_name
FROM ALL_TAB_STATISTICS
WHERE stale_stats = 'YES'
AND table_name NOT LIKE 'BIN$%'
AND owner IN ('WKSP_RCIB','DMF')
AND stattype_locked IS NULL
UNION
SELECT DISTINCT owner, table_name
FROM ALL_IND_STATISTICS
WHERE stale_stats = 'YES'
AND table_name NOT LIKE 'BIN$%'
AND owner IN ('WKSP_RCIB','DMF')
AND stattype_locked IS NULL
)
LOOP
DBMS_OUTPUT.PUT_LINE('Gathering stats for ' || r.owner || '.' || r.table_name);
DBMS_STATS.GATHER_TABLE_STATS(
ownname => r.owner,
tabname => r.table_name,
granularity => 'ALL',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => 8,
cascade => TRUE,
no_invalidate => FALSE
);
END LOOP;
END;