For example we have such sql in the sql window:
When we type any character (for exaple to correct any name) PLD emits HUGE amount of such queries:
Everithing are VERY slowly
There was no such behaviour in 11 version
Version: 12.0.2.1818 (32 bit)
Thank you
SQL:
select listagg(w.reason) within group (order by w.reason), w.sql_id
from (select distinct s.sql_id,
decode(stb_object_mismatch, 'Y', ' STB_OBJECT_MISMATCH') ||
decode(crossedition_trigger_mismatch, 'Y', ' CROSSEDITION_TRIGGER_MISMATCH') ||
decode(pq_slave_mismatch, 'Y', ' PQ_SLAVE_MISMATCH') ||
decode(top_level_ddl_mismatch, 'Y', ' TOP_LEVEL_DDL_MISMATCH') ||
decode(multi_px_mismatch, 'Y', ' MULTI_PX_MISMATCH') ||
decode(bind_peeked_pq_mismatch, 'Y', ' BIND_PEEKED_PQ_MISMATCH') ||
decode(mv_rewrite_mismatch, 'Y', ' MV_REWRITE_MISMATCH') ||
decode(roll_invalid_mismatch, 'Y', ' ROLL_INVALID_MISMATCH') ||
decode(optimizer_mode_mismatch, 'Y', ' OPTIMIZER_MODE_MISMATCH') || decode(px_mismatch, 'Y', ' PX_MISMATCH') ||
decode(mv_staleobj_mismatch, 'Y', ' MV_STALEOBJ_MISMATCH') ||
decode(flashback_table_mismatch, 'Y', ' FLASHBACK_TABLE_MISMATCH') ||
decode(litrep_comp_mismatch, 'Y', ' LITREP_COMP_MISMATCH') || decode(plsql_debug, 'Y', ' PLSQL_DEBUG') ||
decode(load_optimizer_stats, 'Y', ' LOAD_OPTIMIZER_STATS') || decode(acl_mismatch, 'Y', ' ACL_MISMATCH') ||
decode(flashback_archive_mismatch, 'Y', ' FLASHBACK_ARCHIVE_MISMATCH') ||
decode(lock_user_schema_failed, 'Y', ' LOCK_USER_SCHEMA_FAILED') ||
decode(remote_mapping_mismatch, 'Y', ' REMOTE_MAPPING_MISMATCH') ||
decode(load_runtime_heap_failed, 'Y', ' LOAD_RUNTIME_HEAP_FAILED') ||
decode(hash_match_failed, 'Y', ' HASH_MATCH_FAILED') || decode(purged_cursor, 'Y', ' PURGED_CURSOR') ||
decode(bind_length_upgradeable, 'Y', ' BIND_LENGTH_UPGRADEABLE') ||
decode(use_feedback_stats, 'Y', ' USE_FEEDBACK_STATS') || decode(unbound_cursor, 'Y', ' UNBOUND_CURSOR') ||
decode(sql_type_mismatch, 'Y', ' SQL_TYPE_MISMATCH') || decode(optimizer_mismatch, 'Y', ' OPTIMIZER_MISMATCH') ||
decode(outline_mismatch, 'Y', ' OUTLINE_MISMATCH') || decode(stats_row_mismatch, 'Y', ' STATS_ROW_MISMATCH') ||
decode(literal_mismatch, 'Y', ' LITERAL_MISMATCH') || decode(force_hard_parse, 'Y', ' FORCE_HARD_PARSE') ||
decode(explain_plan_cursor, 'Y', ' EXPLAIN_PLAN_CURSOR') ||
decode(buffered_dml_mismatch, 'Y', ' BUFFERED_DML_MISMATCH') ||
decode(pdml_env_mismatch, 'Y', ' PDML_ENV_MISMATCH') || decode(inst_drtld_mismatch, 'Y', ' INST_DRTLD_MISMATCH') ||
decode(slave_qc_mismatch, 'Y', ' SLAVE_QC_MISMATCH') || decode(typecheck_mismatch, 'Y', ' TYPECHECK_MISMATCH') ||
decode(auth_check_mismatch, 'Y', ' AUTH_CHECK_MISMATCH') || decode(bind_mismatch, 'Y', ' BIND_MISMATCH') ||
decode(describe_mismatch, 'Y', ' DESCRIBE_MISMATCH') || decode(language_mismatch, 'Y', ' LANGUAGE_MISMATCH') ||
decode(translation_mismatch, 'Y', ' TRANSLATION_MISMATCH') ||
decode(bind_equiv_failure, 'Y', ' BIND_EQUIV_FAILURE') || decode(insuff_privs, 'Y', ' INSUFF_PRIVS') ||
decode(insuff_privs_rem, 'Y', ' INSUFF_PRIVS_REM') || decode(remote_trans_mismatch, 'Y', ' REMOTE_TRANS_MISMATCH') ||
decode(logminer_session_mismatch, 'Y', ' LOGMINER_SESSION_MISMATCH') ||
decode(incomp_ltrl_mismatch, 'Y', ' INCOMP_LTRL_MISMATCH') ||
decode(overlap_time_mismatch, 'Y', ' OVERLAP_TIME_MISMATCH') || decode(edition_mismatch, 'Y', ' EDITION_MISMATCH') ||
decode(mv_query_gen_mismatch, 'Y', ' MV_QUERY_GEN_MISMATCH') ||
decode(user_bind_peek_mismatch, 'Y', ' USER_BIND_PEEK_MISMATCH') ||
decode(typchk_dep_mismatch, 'Y', ' TYPCHK_DEP_MISMATCH') ||
decode(no_trigger_mismatch, 'Y', ' NO_TRIGGER_MISMATCH') || decode(flashback_cursor, 'Y', ' FLASHBACK_CURSOR') ||
decode(anydata_transformation, 'Y', ' ANYDATA_TRANSFORMATION') ||
decode(pddl_env_mismatch, 'Y', ' PDDL_ENV_MISMATCH') || decode(top_level_rpi_cursor, 'Y', ' TOP_LEVEL_RPI_CURSOR') ||
decode(different_long_length, 'Y', ' DIFFERENT_LONG_LENGTH') ||
decode(logical_standby_apply, 'Y', ' LOGICAL_STANDBY_APPLY') || decode(diff_call_durn, 'Y', ' DIFF_CALL_DURN') ||
decode(bind_uacs_diff, 'Y', ' BIND_UACS_DIFF') || decode(plsql_cmp_switchs_diff, 'Y', ' PLSQL_CMP_SWITCHS_DIFF') ||
decode(cursor_parts_mismatch, 'Y', ' CURSOR_PARTS_MISMATCH') as reason
from gv$sql_shared_cursor s
) w
where trim(w.reason) is not null
group by w.sql_idWhen we type any character (for exaple to correct any name) PLD emits HUGE amount of such queries:
SQL:
declare
t_owner varchar2(30);
t_name varchar2(30);
procedure check_mview is
dummy integer;
begin
if :object_type = 'TABLE' then
select 1 into dummy
from sys.dba_objects
where owner = :object_owner
and object_name = :object_name
and object_type = 'MATERIALIZED VIEW'
and rownum = 1;
:object_type := 'MATERIALIZED VIEW';
end if;
exception
when others then null;
end;
begin
:sub_object := null;
if :deep != 0 then
begin
if :part2 is null then
select constraint_type, owner, constraint_name
into :object_type, :object_owner, :object_name
from sys.dba_constraints c
where c.constraint_name = :part1 and c.owner = :cur_schema
and rownum = 1;
else
select constraint_type, owner, constraint_name, :part3
into :object_type, :object_owner, :object_name, :sub_object
from sys.dba_constraints c
where c.constraint_name = :part2 and c.owner = :part1
and rownum = 1;
end if;
if :object_type = 'P' then :object_type := 'PRIMARY KEY'; end if;
if :object_type = 'U' then :object_type := 'UNIQUE KEY'; end if;
if :object_type = 'R' then :object_type := 'FOREIGN KEY'; end if;
if :object_type = 'C' then :object_type := 'CHECK CONSTRAINT'; end if;
return;
exception
when no_data_found then null;
end;
end if;
:sub_object := :part2;
if (:part2 is null) or (:part1 != :cur_schema) then
begin
select object_type, :cur_schema, :part1
into :object_type, :object_owner, :object_name
from sys.dba_objects
where owner = :cur_schema
and object_name = :part1
and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
and rownum = 1;
if :object_type = 'SYNONYM' then
select s.table_owner, s.table_name
into t_owner, t_name
from dba_synonyms s
where s.synonym_name = :part1
and s.owner = :cur_schema
and rownum = 1;
select o.object_type, o.owner, o.object_name
into :object_type, :object_owner, :object_name
from sys.dba_objects o
where o.owner = t_owner
and o.object_name = t_name
and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
and rownum = 1;
end if;
:sub_object := :part2;
if :part3 is not null then
:sub_object := :sub_object || '.' || :part3;
end if;
check_mview;
return;
exception
when no_data_found then null;
end;
end if;
begin
select s.table_owner, s.table_name
into t_owner, t_name
from dba_synonyms s
where s.synonym_name = :part1
and s.owner = 'PUBLIC'
and rownum = 1;
select o.object_type, o.owner, o.object_name
into :object_type, :object_owner, :object_name
from sys.dba_objects o
where o.owner = t_owner
and o.object_name = t_name
and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
and rownum = 1;
check_mview;
return;
exception
when no_data_found then null;
end;
:sub_object := :part3;
begin
select o.object_type, o.owner, o.object_name
into :object_type, :object_owner, :object_name
from sys.dba_objects o
where o.owner = :part1
and o.object_name = :part2
and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM')
and rownum = 1;
check_mview;
return;
exception
when no_data_found then null;
end;
begin
if :part2 is null and :part3 is null
then
select 'USER', null, :part1
into :object_type, :object_owner, :object_name
from sys.dba_users u
where u.username = :part1
and rownum = 1;
return;
end if;
exception
when no_data_found then null;
end;
begin
if :part2 is null and :part3 is null and :deep != 0
then
select 'ROLE', null, :part1
into :object_type, :object_owner, :object_name
from sys.session_roles r
where r.role = :part1
and rownum = 1;
return;
end if;
exception
when no_data_found then null;
end;
:object_owner := null;
:object_type := null;
:object_name := null;
:sub_object := null;
end;Everithing are VERY slowly
There was no such behaviour in 11 version
Version: 12.0.2.1818 (32 bit)
Thank you
