Toad World® Forums

Some questions for Roman (optimization?)


#1

Hi

These are some question for Roman
(at least, I think he is the one that can answer these questions)
NOTE: My aim is to help improve SQLNav and not criticise…

I used sql monitor to find these.

1- from time to time SQLNav issues
DECLARE
intval BINARY_INTEGER;
strval VARCHAR2(256);
partyp BINARY_INTEGER;
BEGIN
partyp := dbms_utility.get_parameter_value(‘O7_DICTIONARY_ACCESSIBILITY’, intval, strval);
:DICT_ACCESS := partyp;
END;

Why is it checking that (after the first fetch) and not reading from a variable? (O7_DICTIONARY_ACCESSIBILITY is a static parameter and should ond be check on connect/reconnect)

2- In the same second I have several
select ‘x’ from session_privs
where privilege=‘SELECT ANY DICTIONARY’

I know that this priv could change over time but I think it needs reconnect after being issue to make it work. Even if that is not true its issuing several queries in a short time.

3- when I digit in a new SCRIPT tab “select name”
I got these queries

Timestamp: 16:10:45.907
select /*+ ALL_ROWS */ object_name,
object_type,
decode(object_type, ‘PROCEDURE’, 0, ‘FUNCTION’, 1, ‘PACKAGE’, 2, ‘PACKAGE BODY’, 3, ‘TYPE’, 4, ‘TYPE BODY’, 5, ‘TRIGGER’,
6, ‘VIEW’, 7, ‘TABLE’, 9, ‘INDEX’, 10, ‘SYNONYM’, 25, ‘SEQUENCE’, 19, 16) image_index
from sys.all_objects
where owner = :owner
AND object_name like :name_mask escape ‘’
owner = ‘F’
name_mask = ‘A’

Timestamp: 16:10:46.001
select /*+ ALL_ROWS */ object_name,
object_type,
decode(object_type, ‘PROCEDURE’, 0, ‘FUNCTION’, 1, ‘PACKAGE’, 2, ‘PACKAGE BODY’, 3, ‘TYPE’, 4, ‘TYPE BODY’, 5, ‘TRIGGER’,
6, ‘VIEW’, 7, ‘TABLE’, 9, ‘INDEX’, 10, ‘SYNONYM’, 25, ‘SEQUENCE’, 19, 16) image_index
from sys.all_objects
where owner = :owner
AND object_name like :name_mask escape ‘’
owner = ‘P’
name_mask = ‘A’

does that make sense?

  1. when I digit in a new SCRIPT tab “select name from varchar2_util.”
    I got these queries

Timestamp: 16:15:42.363
select s.table_owner,s.table_name,o.object_type from sys.all_synonyms s,sys.all_objects o
where s.owner=:owner and s.synonym_name=:name
and o.owner=s.table_owner and o.object_name=s.table_name
owner = ‘FILIPE’
name = ‘VARCHAR2_UTIL’

Timestamp: 16:15:42.378
select s.table_owner,s.table_name,o.object_type from sys.all_synonyms s,sys.all_objects o
where s.owner=:owner and s.synonym_name=:name
and o.owner=s.table_owner and o.object_name=s.table_name
owner = ‘PUBLIC’
name = ‘VARCHAR2_UTIL’

Timestamp: 16:15:42.391
select /*+ ALL_ROWS */ distinct a.object_name,decode(sum(decode(a.position,0,1,0)),0,0,1) entry_type
from sys.all_arguments a,sys.all_objects o
where o.owner=:owner and o.object_name=:name and a.object_id=o.object_id
group by a.object_name,a.overload order by a.object_name
owner = ‘FILIPE’
name = ‘VARCHAR2_UTIL’

I just do not understand the issue of the 2nd query if the first one gets a result (unless the first 2 queries are issued in parallel)

Thanks
again: my aim is to help improve SQLNav

Filipe Silva


#2

Hi Filipe,

Thank you very much for your input. I need to investigate before I can tell you if queries 1-2 can be avoided. Query 3 is produced by Code Completion, but SQL Monitor for some reason doesn’t display the parameters correctly. Queries 4 must both be issued because we don’t know if the entered name is a schema name or a public object name; there’s a possibility that both exist, so we can’t omit one query if the other returns results.

I’ll investigate your remarks and will come back later.

Thank you again,
Roman


#3

Hi Roman

4- If the object exists in my schema how can I use the public one?

Thanks
Filipe