| SQL Id | SQL Text |
|---|
| 0v3dvmc22qnam | insert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, nonequijoin_preds, range_preds, like_preds, null_preds, timestamp) values ( :objn, :coln, decode(bitand(:flag, 1), 0, 0, 1), decode(bitand(:flag, 2), 0, 0, 1), decode(bitand(:flag, 4), 0, 0, 1), decode(bitand(:flag, 8), 0, 0, 1), decode(bitand(:flag, 16), 0, 0, 1), decode(bitand(:flag, 32), 0, 0, 1), :time) |
| 1gu8t96d0bdmu | select t.ts#, t.file#, t.block#, nvl(t.bobj#, 0), nvl(t.tab#, 0), t.intcols, nvl(t.clucols, 0), t.audit$, t.flags, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln, t.analyzetime, t.samplesize, t.cols, t.property, nvl(t.degree, 1), nvl(t.instances, 1), t.avgspc_flb, t.flbcnt, t.kernelcols, nvl(t.trigflag, 0), nvl(t.spare1, 0), nvl(t.spare2, 0), t.spare4, t.spare6, ts.cachedblk, ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) |
| 1jd97ny5h507n | SELECT /*FC:BadQuery1*/
DISTINCT
C.cust_id
, C.cust_last_name
, P.prod_id
, P.prod_name
, S.channel_id
, S.promo_id
, S.quantity_sold
, S.amount_sold
FROM
sh.sales S
, sh.customers C
, sh.products P
WHERE S.cust_id = C.cust_id
AND ((C.cust_last_name LIKE 'Smith%') OR (S.amount_sold BETWEEN 1000 AND 5000))
ORDER BY
8, 1 DESC, 3, 4 DESC, 5, 6, 2 DESC, 7
|
| 1qhza1uh1xcf9 |
/* OracleOEM */
DECLARE
l_log_mode VARCHAR2(32);
l_flashback_on VARCHAR2(32);
l_flash_recovery_area VARCHAR2(512);
l_flash_recovery_size NUMBER;
l_usable_area NUMBER;
l_space_used NUMBER;
l_reclaimable_area NUMBER;
l_nonreclaimable_area NUMBER;
l_oldest_flashback_time VARCHAR2(512);
TYPE data_cursor_type IS REF CURSOR;
data_cursor data_cursor_type;
v_db_version VARCHAR2(10);
db_version_102 CONSTANT VARCHAR2(10) := '10.2.0.0.0';
BEGIN
-- Database version
SELECT LPAD(version, 10, '0') INTO v_db_version
FROM v$instance;
-- Log Mode, Flashback On
SELECT log_mode, flashback_on INTO l_log_mode, l_flashback_on
FROM v$database;
-- Flash Recovery Area/Size
SELECT value INTO l_flash_recovery_area
FROM v$parameter WHERE name='db_recovery_file_dest';
SELECT value INTO l_flash_recovery_size
FROM v$parameter WHERE name='db_recovery_file_dest_size';
-- Usable Flash Recovery Area (10gR2)
-- Reclaimable Flash Recovery Area (10gR2)
-- Nonreclaimable Flash Recovery Area
l_usable_area := NULL;
l_reclaimable_area := NULL;
l_nonreclaimable_area := NULL;
l_space_used := NULL;
IF (v_db_version >= db_version_102) THEN
IF (length(l_flash_recovery_area) > 0) THEN
EXECUTE IMMEDIATE
'SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE
FROM (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED FROM V$FLASH_RECOVERY_AREA_USAGE)'
INTO l_usable_area;
EXECUTE IMMEDIATE
'SELECT SUM(PERCENT_SPACE_USED), SUM(PERCENT_SPACE_RECLAIMABLE) from v$flash_recovery_area_usage' INTO l_space_used, l_reclaimable_area;
l_nonreclaimable_area := l_space_used - l_reclaimable_area;
END IF;
END IF;
-- Oldest Flashback Time
BEGIN
SELECT to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS')
INTO l_oldest_flashback_time FROM v$flashback_database_log;
EXCEPTION
WHEN OTHERS THEN l_oldest_flashback_time := '';
END;
OPEN data_cursor FOR
SELECT l_log_mode, l_flash_recovery_area, l_usable_area, l_reclaimable_area, l_nonreclaimable_area, l_flashback_on, l_oldest_flashback_time, l_flash_recovery_size from dual;
:1 := data_cursor;
END;
|
| 2b064ybzkwf1y | BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; |
| 2nszajb0qbyvp | DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'), wwv_flow_platform.get_preference('SMTP_HOST_PORT')); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; |
| 2q93zsrvbdw48 | select grantee#, privilege#, nvl(col#, 0), max(mod(nvl(option$, 0), 2))from objauth$ where obj#=:1 group by grantee#, privilege#, nvl(col#, 0) order by grantee# |
| 3972rvxu3knn3 | delete from sdo_geor_ddl__table$$ |
| 39m4sx9k63ba2 | select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#, length, piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# |
| 3am9cfkvx7gq1 | CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:target_guid, :metric_guid, :metric_values) |
| 3c1kubcdjnppq | update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag, 1), 0, 0, 1), equijoin_preds = equijoin_preds + decode(bitand(:flag, 2), 0, 0, 1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag, 4), 0, 0, 1), range_preds = range_preds + decode(bitand(:flag, 8), 0, 0, 1), like_preds = like_preds + decode(bitand(:flag, 16), 0, 0, 1), null_preds = null_preds + decode(bitand(:flag, 32), 0, 0, 1), timestamp = :time where obj# = :objn and intcol# = :coln |
| 3ktacv9r56b51 | select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# |
| 3nkd3g3ju5ph1 | select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null |
| 3w4qs0tbpmxr6 | select con#, obj#, rcon#, enabled, nvl(defer, 0), spare2, spare3 from cdef$ where robj#=:1 |
| 5na6ha0drtzhf |
select severity_code, message, b.collection_timestamp, d.value, d.collection_timestamp, c.metric_name, metric_column, b.key_value, c.metric_label, c.column_label, a.target_name, b.message_nlsid, b.message_params, case when c.metric_name != 'adrAlertLogIncidentError' then NULL else (select p.string_value from mgmt_current_metrics p, mgmt_metrics q where p.metric_guid = q.metric_guid and q.metric_name = 'adrAlertLogIncidentError' and q.metric_column = 'impact' and p.key_value = b.key_value and p.target_guid = b.target_guid and q.type_meta_ver = a.type_meta_ver and (q.category_prop_1 = a.category_prop_1 or q.category_prop_1 = ' ') and (q.category_prop_2 = a.category_prop_2 or q.category_prop_2 = ' ') and (q.category_prop_3 = a.category_prop_3 or q.category_prop_3 = ' ') and (q.category_prop_4 = a.category_prop_4 or q.category_prop_4 = ' ') and (q.category_prop_5 = a.category_prop_5 or q.category_prop_5 = ' ')) end as impact, e.metric_label_nlsid, c.column_label_nlsid from mgmt_targets a, mgmt_current_severity b, mgmt_metrics c, mgmt_current_metrics d, (select category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, metric_name, target_type, type_meta_ver, metric_label_nlsid from mgmt_metrics where metric_column = ' ' and target_type =
'oracle_listener') e where a.target_guid = b.target_guid and b.metric_guid = c.metric_guid and b.metric_guid = d.metric_guid(+) and b.target_guid = d.target_guid(+) and e.metric_name = c.metric_name and b.key_value = d.key_value(+) and a.host_name = :1 and a.target_type = 'oracle_listener' and c.type_meta_ver = e.type_meta_ver AND c.type_meta_ver = a.type_meta_ver AND (c.category_prop_1 = a.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = a.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = a.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = a.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = a.category_prop_5 OR c.category_prop_5 = ' ') AND (e.category_prop_1 = a.category_prop_1 OR e.category_prop_1 = ' ') AND (e.category_prop_2 = a.category_prop_2 OR e.category_prop_2 = ' ') AND (e.category_prop_3 = a.category_prop_3 OR e.category_prop_3 = ' ') AND (e.category_prop_4 = a.category_prop_4 OR e.category_prop_4 = ' ') AND (e.category_prop_5 = a.category_prop_5 OR e.category_prop_5 = ' ') ORDER BY 1 desc |
| 6aq34nj2zb2n7 | select col#, grantee#, privilege#, max(mod(nvl(option$, 0), 2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee# |
| 6gvch1xu9ca3g | DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; |
| 6v7n0y2bq89n8 | BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME, :1); MGMT_JOB_ENGINE.get_scheduled_steps(:2, :3, :4, :5); EMDW_LOG.set_context; END; |
| 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# |
| 81ky0n97v4zsg | /* OracleOEM */ select s.sid, s.serial# from v$session s where s.sid = (select sid from v$mystat where rownum=1) |
| 83taa7kaw59c1 | select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127/*MAXSB1MINAL*/), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), null$, fixedstorage, nvl(deflength, 0), default$, rowid, col#, property, nvl(charsetid, 0), nvl(charsetform, 0), spare1, spare2, nvl(spare3, 0) from col$ where obj#=:1 order by intcol# |
| 8fx6pqqbpra0s | begin emd_database.getDBSiteMapInfo(:1, :2, :3); end; |
| 8swypbbr0m372 | select order#, columns, types from access$ where d_obj#=:1 |
| 8u5gujrwh4wf4 |
/* OracleOEM */
declare
TYPE data_cursor_type IS REF CURSOR;
data_cursor data_cursor_type;
cap_count number;
apply_count number;
propagation_count number;
cap_error_count number;
apply_error_count number;
prop_error_count number;
total_prop_errors number;
sqlstmt varchar2(32767);
begin
SELECT COUNT(*) into cap_count FROM SYS.DBA_CAPTURE;
SELECT COUNT(*) into apply_count FROM SYS.DBA_APPLY;
SELECT COUNT(*) into propagation_count FROM SYS.DBA_PROPAGATION;
SELECT COUNT(*) into cap_error_count FROM SYS.DBA_CAPTURE WHERE ERROR_NUMBER IS NOT NULL;
SELECT COUNT(*) into apply_error_count FROM SYS.DBA_APPLY WHERE ERROR_NUMBER IS NOT NULL;
SELECT COUNT(*) into prop_error_count FROM SYS.DBA_PROPAGATION WHERE ERROR_MESSAGE IS NOT NULL;
SELECT NVL(SUM(FAILURES), 0) into total_prop_errors FROM SYS.DBA_QUEUE_SCHEDULES;
sqlstmt := 'select '||cap_count||' CAPTURE_COUNT, '||apply_count||' APPLY_COUNT, '||propagation_count||' PROP_COUNT, '||cap_error_count||' CAPTURE_ERROR_COUNT, '||apply_error_count||' APPLY_ERROR_COUNT, '||prop_error_count||' PROP_ERROR_COUNT, '||total_prop_errors||' TOTAL_PROP_ERRORS from dual';
OPEN data_cursor FOR sqlstmt;
:1 := data_cursor;
end;
|
| 8u809k64x3nzd | begin DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); end; |
| 96g93hntrzjtr | select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 |
| 9b7pdpgf2znkk | SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE
FROM (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED FROM V$FLASH_RECOVERY_AREA_USAGE) |
| 9kzwsazsktuzv | SELECT end_time, status FROM v$rman_backup_job_details WHERE end_time = (select max(end_time) from v$rman_backup_job_details) |
| 9tgj4g8y4rwy8 | select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0), NVL(bitmapranges, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3 |
| a4ct9tx8f9d4a | begin execute immediate 'alter session set NLS_NUMERIC_CHARACTERS = ''., '''; end; |
| a6u3yjca29nqc | declare
m_stmt varchar2(512);
begin
m_stmt:='delete from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt;
EXCEPTION
WHEN OTHERS THEN
NULL;
end; |
| a8j39qb13tqkr |
SELECT :B1 TASK_ID, F.FINDING_ID FINDING_ID, DECODE(RECINFO.TYPE, NULL, 'Uncategorized', RECINFO.TYPE) REC_TYPE, RECINFO.RECCOUNT REC_COUNT, F.PERC_ACTIVE_SESS IMPACT_PCT, F.MESSAGE MESSAGE, TO_DATE(:B3 , 'MM-DD-YYYY HH24:MI:SS') START_TIME, TO_DATE(:B2 , 'MM-DD-YYYY HH24:MI:SS') END_TIME, HISTORY.FINDING_COUNT FINDING_COUNT, F.FINDING_NAME FINDING_NAME, F.ACTIVE_SESSIONS ACTIVE_SESSIONS FROM DBA_ADDM_FINDINGS F, (SELECT FINDING_ID, COUNT(R.REC_ID) RECCOUNT, R.TYPE FROM DBA_ADVISOR_RECOMMENDATIONS R WHERE TASK_ID=:B1 GROUP BY R.FINDING_ID, R.TYPE) RECINFO, (SELECT COUNT(F_ALL.TASK_ID) FINDING_COUNT, F_CURR.FINDING_NAME FROM (SELECT FINDING_NAME FROM DBA_ADVISOR_FINDINGS WHERE TASK_ID=:B1 ) F_CURR, (SELECT T.TASK_ID, I.LOCAL_TASK_ID, T.END_TIME, T.BEGIN_TIME FROM DBA_ADDM_TASKS T, DBA_ADDM_INSTANCES I WHERE T.END_TIME>SYSDATE -1 AND T.TASK_ID=I.TASK_ID AND I.INSTANCE_NUMBER=SYS_CONTEXT('USERENV', 'INSTANCE') AND T.REQUESTED_ANALYSIS='INSTANCE' ) TASKS, DBA_ADVISOR_FINDINGS F_ALL WHERE F_ALL.TASK_ID=TASKS.TASK_ID AND F_ALL.FINDING_NAME=F_CURR.FINDING_NAME AND F_ALL.TYPE<>'INFORMATION' AND F_ALL.TYPE<>'WARNING' AND F_ALL.PARENT=0 GROUP BY F_CURR.FINDING_NAME) HISTORY WHERE F.TASK_ID=:B1 AND F.TYPE<>'INFORMATION' AND F.TYPE<>'WARNING' AND F.FILTERED<>'Y' AND F.PARENT=0 AND F.FINDING_ID=RECINFO.FINDING_ID (+) AND F.FINDING_NAME=HISTORY.FINDING_NAME ORDER BY F.FINDING_ID
|
| ab3swhv5g138y |
select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority + decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :1, -1, 1))) PRI, 1 JOBTYPE, decode(a.schedule_limit, NULL, decode(bitand(a.flags, 4194304), 4194304, p.schedule_limit, NULL), a.schedule_limit) SCHLIM, a.job_weight WT, decode(a.running_instance, NULL, 0, a.running_instance) INST, decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW, decode(bitand(a.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM from sys.scheduler$_job a, sys.scheduler$_program p, v$database v, v$instance i where a.program_oid = p.obj#(+) and bitand(a.job_status, 515) = 1 and bitand(a.flags, 1048576) = 0 and ((bitand(a.flags, 134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <> 0)) and bitand(a.flags, 4096) = 0 and (a.next_run_date <= :2 or bitand(a.flags, 16384) <> 0) and a.instance_id is null and (a.class_oid is null or (a.class_oid is not null and a.class_oid in (select b.obj# from sys.scheduler$_class b where b.affinity is null))) and (a.database_role = v.database_role or (a.database_role is null and v.database_role = 'PRIMARY'
)) and ( i.logins = 'ALLOWED' or bitand(a.flags, 17179869184) <> 0 ) union all select l.obj#, l.class_oid, decode(bitand(l.flags, 16384), 0, l.next_run_date, l.last_enabled_time), (2*decode(bitand(l.flags, 8589934592), 0, q.priority, pj.priority) + decode(bitand(l.job_status, 4), 0, 0, decode(l.running_instance, :3, -1, 1))), 1, decode(bitand(l.flags, 8589934592), 0, q.schedule_limit, decode(pj.schedule_limit, NULL, q.schedule_limit, pj.schedule_limit)), decode(bitand(l.flags, 8589934592), 0, q.job_weight, pj.job_weight), decode(l.running_instance, NULL, 0, l.running_instance), decode(bitand(l.flags, 16384), 0, 0, 1), decode(bitand(l.job_status, 8388608), 0, 0, 1) from sys.scheduler$_lightweight_job l, sys.scheduler$_program q, (select sl.obj# obj#, decode(bitand(sl.flags, 8589934592), 0, sl.program_oid, spj.program_oid) program_oid, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.priority) priority, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.job_weight) job_weight, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.schedule_limit) schedule_limit from sys.scheduler$_lightweight_job sl, scheduler$_job spj where sl.program_oid = spj.obj#(+)) pj , v$instance i where pj.obj# = l.ob
j# and pj.program_oid = q.obj#(+) and (:4 = 0 or l.running_instance = :5) and bitand(l.job_status, 515) = 1 and ((bitand(l.flags, 134217728 + 268435456) = 0) or (bitand(l.job_status, 1024) <> 0)) and bitand(l.flags, 4096) = 0 and (l.next_run_date <= :6 or bitand(l.flags, 16384) <> 0) and l.instance_id is null and (l.class_oid is null or (l.class_oid is not null and l.class_oid in (select w.obj# from sys.scheduler$_class w where w.affinity is null))) and ( i.logins = 'ALLOWED' or bitand(l.flags, 17179869184) <> 0 ) union all select c.obj#, 0, c.next_start_date, 0, 2, c.duration, 1, 0, 0, 0 from sys.scheduler$_window c , v$instance i where bitand(c.flags, 1) <> 0 and bitand(c.flags, 2) = 0 and bitand(c.flags, 64) = 0 and c.next_start_date <= :7 and i.logins = 'ALLOWED' union all select d.obj#, 0, d.next_start_date + d.duration, 0, 4, numtodsinterval(0, 'minute'), 1, 0, 0, 0 from sys.scheduler$_window d , v$instance i where bitand(d.flags, 1) <> 0 and bitand(d.flags, 2) = 0 and bitand(d.flags, 64) = 0 and d.next_start_date <= :8 and i.logins = 'ALLOWED' union all select f.obj#, 0, e.attr_tstamp, 0, decode(bitand(e.flags, 131072), 0, 2, 3), e.attr_intv, 1, 0, 0, 0 from sys.scheduler$_global_attribute e, sys.obj$ f, sys.obj$ g, v$instance i where e.obj# = g.obj#
and g.name = 'CURRENT_OPEN_WINDOW' and e.value = f.name and f.type# = 69 and e.attr_tstamp is not null and e.attr_intv is not null and i.logins = 'ALLOWED' union all select i.obj#, 0, h.attr_tstamp + h.attr_intv, 0, decode(bitand(h.flags, 131072), 0, 4, 5), numtodsinterval(0, 'minute'), 1, 0, 0, 0 from sys.scheduler$_global_attribute h, sys.obj$ i, sys.obj$ j, v$instance ik where h.obj# = j.obj# and j.name = 'CURRENT_OPEN_WINDOW' and h.value = i.name and i.type# = 69 and h.attr_tstamp is not null and h.attr_intv is not null and ik.logins = 'ALLOWED') order by RUNTIME, JOBTYPE, CLSOID, PRI, WT DESC, OBJOID |
| akrp274fw4fnf | begin emd_database.getDBSiteMapCommonInfo(:1, :2, :3); end; |
| asvzxj61dc5vs | select timestamp, flags from fixed_obj$ where obj#=:1 |
| b1wc53ddd6h3p | select audit$, options from procedure$ where obj#=:1 |
| b2gnxm5z6r51n | lock table sys.col_usage$ in exclusive mode nowait |
| b47nt8buxb5z5 |
SELECT /*FC:HeavySorting1*/ DISTINCT
cust_id
, prod_id
, SUM(qty)
, SUM(amt)
FROM (SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 1 and 15000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 90000 and 105000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 20000 and 35000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 80000 and 95000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 30000 and 45000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 70000 and 85000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 40000 and 55000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id > 100000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 50000 and 65000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 10000 and 25000
GROUP BY cust_id, prod_id
UNION
SELECT
cust_id
, prod_id
, SUM(quantity_sold) qty
, SUM(amount_sold) amt
FROM sh.sales
WHERE cust_id BETWEEN 60000 and 75000
GROUP BY cust_id, prod_id
)
GROUP BY ROLLUP(cust_id, prod_id)
ORDER BY
1 DESC
, 2 ASC
, 3 DESC
, 4 ASC
|
| c6awqs517jpj0 | select /*+ index(idl_char$ i_idl_char1) +*/ piece#, length, piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece# |
| cm5vu20fhtnq1 | select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 |
| crwtm662ycm4w | select count(*) from sys.job$ where next_date < :1 and (field1 = :2 or (field1 = 0 and 'Y' = :3)) and ((dbms_logstdby.db_is_logstdby = 0 and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1 and job >= 1000000000)) |
| cubcqrdcjb3mz | select q.eventid, q.oid, t.schema, t.name, q.table_objno, q.usage, t.udata_type, q.enable_flag, q.max_retries, q.retry_delay, q.properties, q.ret_time, t.flags, t.sort_cols, q.memory_threshold, a.owner_instance, q.service_name, q.network_name, t.timezone from system.aq$_queues q, system.aq$_queue_tables t, sys.aq$_queue_table_affinities a where q.table_objno = t.objno and q.table_objno = a.table_objno and t.schema= :1 and q.name= :2 |
| cvn54b7yz0s8u | select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#, length, piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece# |
| d4v0hx96hn1kp | begin setEMUserContext(:1, :2); end; |
| d5xxfguffwpxh | select end_time, wait_class#, (time_waited_fg)/(intsize_csec/100), (time_waited)/(intsize_csec/100), 0 from v$waitclassmetric union all select fg.end_time, -1, fg.value, bg.value, dbtime.value from v$sysmetric fg, v$sysmetric bg, v$sysmetric dbtime where bg.metric_name = 'Background CPU Usage Per Sec' and bg.group_id = 2 and fg.metric_name = 'CPU Usage Per Sec' and fg.group_id = 2 and dbtime.metric_name = 'Average Active Sessions' and dbtime.group_id = 2 and bg.end_time = fg.end_time and fg.end_time = dbtime.end_time order by end_time, wait_class# |
| db78fxqxwxt7r | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket |
| fndjrj10u6q7d | select end_time, wait_class#, (time_waited_fg)/(intsize_csec/100), (time_waited)/(intsize_csec/100), 0 from v$waitclassmetric_history union all select fg.end_time, -1, fg.value, bg.value, dbtime.value from v$sysmetric_history bg, v$sysmetric_history fg, v$sysmetric_history dbtime where bg.metric_name = 'Background CPU Usage Per Sec' and bg.group_id = 2 and fg.metric_name = 'CPU Usage Per Sec' and fg.group_id = 2 and dbtime.metric_name = 'Average Active Sessions' and dbtime.group_id = 2 and bg.end_time = fg.end_time and fg.end_time = dbtime.end_time order by end_time, wait_class# |
| ga9j9xk5cy9s0 | select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#, length, piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece# |
| grwydz59pu6mc | select text from view$ where rowid=:1 |
| gx4mv66pvj3xz | select con#, type#, condlength, intcols, robj#, rcon#, match#, refact, nvl(enabled, 0), rowid, cols, nvl(defer, 0), mtime, nvl(spare1, 0), spare2, spare3 from cdef$ where obj#=:1 |
| gyp9pgmvwu659 |
select severity_code, message, b.collection_timestamp, d.value, d.collection_timestamp, c.metric_name, metric_column, b.key_value, c.metric_label, c.column_label, a.target_name, b.message_nlsid, b.message_params, case when c.metric_name != 'adrAlertLogIncidentError' then NULL else (select p.string_value from mgmt_current_metrics p, mgmt_metrics q where p.metric_guid = q.metric_guid and q.metric_name = 'adrAlertLogIncidentError' and q.metric_column = 'impact' and p.key_value = b.key_value and p.target_guid = b.target_guid and q.type_meta_ver = a.type_meta_ver and (q.category_prop_1 = a.category_prop_1 or q.category_prop_1 = ' ') and (q.category_prop_2 = a.category_prop_2 or q.category_prop_2 = ' ') and (q.category_prop_3 = a.category_prop_3 or q.category_prop_3 = ' ') and (q.category_prop_4 = a.category_prop_4 or q.category_prop_4 = ' ') and (q.category_prop_5 = a.category_prop_5 or q.category_prop_5 = ' ')) end as impact, e.metric_label_nlsid, c.column_label_nlsid from mgmt_targets a, mgmt_current_severity b, mgmt_metrics c, mgmt_current_metrics d, (select category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, metric_name, target_type, type_meta_ver, metric_label_nlsid from mgmt_metrics where metric_column = ' ') e where a.target_
guid = b.target_guid and b.metric_guid = c.metric_guid and b.metric_guid = d.metric_guid(+) and b.target_guid = d.target_guid(+) and e.target_type = a.target_type and e.metric_name = c.metric_name and b.key_value = d.key_value(+) and a.target_name = :1 and a.target_type = :2 and c.type_meta_ver = e.type_meta_ver AND c.type_meta_ver = a.type_meta_ver AND (c.category_prop_1 = a.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = a.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = a.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = a.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = a.category_prop_5 OR c.category_prop_5 = ' ') AND (e.category_prop_1 = a.category_prop_1 OR e.category_prop_1 = ' ') AND (e.category_prop_2 = a.category_prop_2 OR e.category_prop_2 = ' ') AND (e.category_prop_3 = a.category_prop_3 OR e.category_prop_3 = ' ') AND (e.category_prop_4 = a.category_prop_4 OR e.category_prop_4 = ' ') AND (e.category_prop_5 = a.category_prop_5 OR e.category_prop_5 = ' ') ORDER BY 1 desc |
| Statistic | Total | per Second | per Trans |
|---|
| Batched IO (bound) vector count | 6 | 0.00 | 0.02 |
| Batched IO (full) vector count | 0 | 0.00 | 0.00 |
| Batched IO block miss count | 191 | 0.15 | 0.74 |
| Batched IO buffer defrag count | 3 | 0.00 | 0.01 |
| Batched IO double miss count | 6 | 0.00 | 0.02 |
| Batched IO same unit count | 87 | 0.07 | 0.34 |
| Batched IO single block count | 1 | 0.00 | 0.00 |
| Batched IO slow jump count | 3 | 0.00 | 0.01 |
| Batched IO vector block count | 101 | 0.08 | 0.39 |
| Batched IO vector read count | 10 | 0.01 | 0.04 |
| Block Cleanout Optim referenced | 3 | 0.00 | 0.01 |
| CCursor + sql area evicted | 2,434 | 1.92 | 9.43 |
| CPU used by this session | 5,428 | 4.29 | 21.04 |
| CPU used when call started | 4,970 | 3.92 | 19.26 |
| CR blocks created | 46 | 0.04 | 0.18 |
| Commit SCN cached | 6 | 0.00 | 0.02 |
| DB time | 415,251 | 327.85 | 1,609.50 |
| DBWR checkpoint buffers written | 1,350 | 1.07 | 5.23 |
| DBWR checkpoints | 1 | 0.00 | 0.00 |
| DBWR transaction table writes | 40 | 0.03 | 0.16 |
| DBWR undo block writes | 247 | 0.20 | 0.96 |
| HSC Heap Segment Block Changes | 2,074 | 1.64 | 8.04 |
| Heap Segment Array Inserts | 91 | 0.07 | 0.35 |
| Number of read IOs issued | 20 | 0.02 | 0.08 |
| RowCR - row contention | 0 | 0.00 | 0.00 |
| RowCR attempts | 837 | 0.66 | 3.24 |
| RowCR hits | 837 | 0.66 | 3.24 |
| SMON posted for undo segment shrink | 1 | 0.00 | 0.00 |
| SQL*Net roundtrips to/from client | 50,171 | 39.61 | 194.46 |
| active txn count during cleanout | 46 | 0.04 | 0.18 |
| application wait time | 4 | 0.00 | 0.02 |
| background checkpoints completed | 1 | 0.00 | 0.00 |
| background checkpoints started | 1 | 0.00 | 0.00 |
| background timeouts | 5,782 | 4.56 | 22.41 |
| buffer is not pinned count | 104,164 | 82.24 | 403.74 |
| buffer is pinned count | 39,878 | 31.48 | 154.57 |
| bytes received via SQL*Net from client | 1,494,662 | 1,180.06 | 5,793.26 |
| bytes sent via SQL*Net to client | 30,489,658 | 24,072.01 | 118,176.97 |
| calls to get snapshot scn: kcmgss | 35,341 | 27.90 | 136.98 |
| calls to kcmgas | 1,391 | 1.10 | 5.39 |
| calls to kcmgcs | 4,637 | 3.66 | 17.97 |
| cell physical IO interconnect bytes | 993,363,968 | 784,274.75 | 3,850,247.94 |
| change write time | 105 | 0.08 | 0.41 |
| cleanout - number of ktugct calls | 47 | 0.04 | 0.18 |
| cleanouts and rollbacks - consistent read gets | 0 | 0.00 | 0.00 |
| cleanouts only - consistent read gets | 1 | 0.00 | 0.00 |
| cluster key scan block gets | 4,111 | 3.25 | 15.93 |
| cluster key scans | 3,560 | 2.81 | 13.80 |
| commit batch performed | 0 | 0.00 | 0.00 |
| commit batch requested | 0 | 0.00 | 0.00 |
| commit batch/immediate performed | 0 | 0.00 | 0.00 |
| commit batch/immediate requested | 0 | 0.00 | 0.00 |
| commit cleanout failures: callback failure | 4 | 0.00 | 0.02 |
| commit cleanout failures: cannot pin | 0 | 0.00 | 0.00 |
| commit cleanouts | 2,640 | 2.08 | 10.23 |
| commit cleanouts successfully completed | 2,636 | 2.08 | 10.22 |
| commit immediate performed | 0 | 0.00 | 0.00 |
| commit immediate requested | 0 | 0.00 | 0.00 |
| commit txn count during cleanout | 20 | 0.02 | 0.08 |
| concurrency wait time | 2,845 | 2.25 | 11.03 |
| consistent changes | 351 | 0.28 | 1.36 |
| consistent gets | 151,501 | 119.61 | 587.21 |
| consistent gets - examination | 47,963 | 37.87 | 185.90 |
| consistent gets direct | 1,458 | 1.15 | 5.65 |
| consistent gets from cache | 150,043 | 118.46 | 581.56 |
| consistent gets from cache (fastpath) | 91,057 | 71.89 | 352.93 |
| cursor authentications | 321 | 0.25 | 1.24 |
| data blocks consistent reads - undo records applied | 51 | 0.04 | 0.20 |
| db block changes | 16,063 | 12.68 | 62.26 |
| db block gets | 13,930 | 11.00 | 53.99 |
| db block gets direct | 4 | 0.00 | 0.02 |
| db block gets from cache | 13,926 | 10.99 | 53.98 |
| db block gets from cache (fastpath) | 3,467 | 2.74 | 13.44 |
| deferred (CURRENT) block cleanout applications | 1,815 | 1.43 | 7.03 |
| enqueue conversions | 283 | 0.22 | 1.10 |
| enqueue releases | 16,713 | 13.20 | 64.78 |
| enqueue requests | 16,714 | 13.20 | 64.78 |
| enqueue timeouts | 0 | 0.00 | 0.00 |
| enqueue waits | 0 | 0.00 | 0.00 |
| execute count | 29,628 | 23.39 | 114.84 |
| failed probes on index block reclamation | 0 | 0.00 | 0.00 |
| file io service time | 0 | 0.00 | 0.00 |
| file io wait time | 27,338,066 | 21,583.79 | 105,961.50 |
| free buffer inspected | 195 | 0.15 | 0.76 |
| free buffer requested | 11,371 | 8.98 | 44.07 |
| heap block compress | 24 | 0.02 | 0.09 |
| immediate (CR) block cleanout applications | 1 | 0.00 | 0.00 |
| immediate (CURRENT) block cleanout applications | 378 | 0.30 | 1.47 |
| index crx upgrade (positioned) | 1,996 | 1.58 | 7.74 |
| index fast full scans (full) | 20 | 0.02 | 0.08 |
| index fetch by key | 16,878 | 13.33 | 65.42 |
| index scans kdiixs1 | 27,873 | 22.01 | 108.03 |
| leaf node 90-10 splits | 16 | 0.01 | 0.06 |
| leaf node splits | 20 | 0.02 | 0.08 |
| lob reads | 1 | 0.00 | 0.00 |
| lob writes | 20 | 0.02 | 0.08 |
| lob writes unaligned | 20 | 0.02 | 0.08 |
| logons cumulative | 99 | 0.08 | 0.38 |
| max cf enq hold time | 1,250 | 0.99 | 4.84 |
| messages received | 1,921 | 1.52 | 7.45 |
| messages sent | 1,921 | 1.52 | 7.45 |
| min active SCN optimization applied on CR | 0 | 0.00 | 0.00 |
| no buffer to keep pinned count | 0 | 0.00 | 0.00 |
| no work - consistent read gets | 92,735 | 73.22 | 359.44 |
| non-idle wait count | 65,925 | 52.05 | 255.52 |
| non-idle wait time | 18,714 | 14.77 | 72.53 |
| opened cursors cumulative | 26,193 | 20.68 | 101.52 |
| parse count (describe) | 0 | 0.00 | 0.00 |
| parse count (failures) | 13 | 0.01 | 0.05 |
| parse count (hard) | 1,246 | 0.98 | 4.83 |
| parse count (total) | 12,735 | 10.05 | 49.36 |
| parse time cpu | 885 | 0.70 | 3.43 |
| parse time elapsed | 5,735 | 4.53 | 22.23 |
| physical read IO requests | 8,004 | 6.32 | 31.02 |
| physical read bytes | 201,777,152 | 159,305.88 | 782,081.98 |
| physical read total IO requests | 10,648 | 8.41 | 41.27 |
| physical read total bytes | 374,372,864 | 295,572.61 | 1,451,057.61 |
| physical read total multi block requests | 235 | 0.19 | 0.91 |
| physical reads | 24,631 | 19.45 | 95.47 |
| physical reads cache | 10,775 | 8.51 | 41.76 |
| physical reads cache prefetch | 5,157 | 4.07 | 19.99 |
| physical reads direct | 13,856 | 10.94 | 53.71 |
| physical reads direct temporary tablespace | 12,391 | 9.78 | 48.03 |
| physical reads prefetch warmup | 971 | 0.77 | 3.76 |
| physical write IO requests | 2,166 | 1.71 | 8.40 |
| physical write bytes | 228,761,600 | 180,610.48 | 886,672.87 |
| physical write total IO requests | 4,484 | 3.54 | 17.38 |
| physical write total bytes | 370,812,416 | 292,761.59 | 1,437,257.43 |
| physical write total multi block requests | 955 | 0.75 | 3.70 |
| physical writes | 27,925 | 22.05 | 108.24 |
| physical writes direct | 26,036 | 20.56 | 100.91 |
| physical writes direct (lob) | 4 | 0.00 | 0.02 |
| physical writes direct temporary tablespace | 26,025 | 20.55 | 100.87 |
| physical writes from cache | 1,889 | 1.49 | 7.32 |
| physical writes non checkpoint | 27,030 | 21.34 | 104.77 |
| pinned buffers inspected | 195 | 0.15 | 0.76 |
| pinned cursors current | 1 | 0.00 | 0.00 |
| prefetch warmup blocks flushed out before use | 1,460 | 1.15 | 5.66 |
| process last non-idle time | 16,178 | 12.77 | 62.71 |
| recursive calls | 294,255 | 232.32 | 1,140.52 |
| recursive cpu usage | 2,077 | 1.64 | 8.05 |
| redo blocks checksummed by FG (exclusive) | 1,470 | 1.16 | 5.70 |
| redo blocks written | 7,314 | 5.77 | 28.35 |
| redo buffer allocation retries | 0 | 0.00 | 0.00 |
| redo entries | 9,072 | 7.16 | 35.16 |
| redo k-bytes read total | 116,059 | 91.63 | 449.84 |
| redo ordering marks | 0 | 0.00 | 0.00 |
| redo size | 3,580,052 | 2,826.50 | 13,876.17 |
| redo size for direct writes | 32,912 | 25.98 | 127.57 |
| redo synch time | 206 | 0.16 | 0.80 |
| redo synch writes | 153 | 0.12 | 0.59 |
| redo wastage | 140,556 | 110.97 | 544.79 |
| redo write time | 594 | 0.47 | 2.30 |
| redo writes | 616 | 0.49 | 2.39 |
| rollback changes - undo records applied | 7 | 0.01 | 0.03 |
| rollbacks only - consistent read gets | 46 | 0.04 | 0.18 |
| rows fetched via callback | 7,369 | 5.82 | 28.56 |
| session connect time | 0 | 0.00 | 0.00 |
| session cursor cache hits | 22,336 | 17.63 | 86.57 |
| session logical reads | 165,431 | 130.61 | 641.21 |
| shared hash latch upgrades - no wait | 6,765 | 5.34 | 26.22 |
| sorts (disk) | 4 | 0.00 | 0.02 |
| sorts (memory) | 8,736 | 6.90 | 33.86 |
| sorts (rows) | 3,410,864 | 2,692.92 | 13,220.40 |
| sql area evicted | 2,846 | 2.25 | 11.03 |
| sql area purged | 25 | 0.02 | 0.10 |
| switch current to new buffer | 53 | 0.04 | 0.21 |
| table fetch by rowid | 45,590 | 35.99 | 176.71 |
| table fetch continued row | 699 | 0.55 | 2.71 |
| table scan blocks gotten | 26,874 | 21.22 | 104.16 |
| table scan rows gotten | 11,345,316 | 8,957.29 | 43,974.09 |
| table scans (direct read) | 1 | 0.00 | 0.00 |
| table scans (long tables) | 1 | 0.00 | 0.00 |
| table scans (short tables) | 1,642 | 1.30 | 6.36 |
| temp space allocated (bytes) | 155,189,248 | 122,524.08 | 601,508.71 |
| total cf enq hold time | 2,960 | 2.34 | 11.47 |
| total number of cf enq holders | 90 | 0.07 | 0.35 |
| total number of times SMON posted | 1 | 0.00 | 0.00 |
| transaction rollbacks | 0 | 0.00 | 0.00 |
| undo change vector size | 1,308,348 | 1,032.96 | 5,071.12 |
| user I/O wait time | 12,513 | 9.88 | 48.50 |
| user calls | 51,380 | 40.57 | 199.15 |
| user commits | 258 | 0.20 | 1.00 |
| user rollbacks | 0 | 0.00 | 0.00 |
| workarea executions - onepass | 8 | 0.01 | 0.03 |
| workarea executions - optimal | 4,562 | 3.60 | 17.68 |
| write clones created in background | 289 | 0.23 | 1.12 |
