Hello,

I've developed a script file which fixes (creates or drops) synonym
statements for all users which have been granted access for current
user's objects. It can even trace the privileges inherited through
roles.

It works OK but the performance is way too slow if the database is
big.

I have traced the problem to this sql satement:

SELECT owner
FROM all_synonyms
WHERE table_name = UPPER ('EMP')
AND synonym_name = UPPER ('EMP')
AND table_owner = 'SCOTT'
AND (owner = 'SYSTEM' OR owner = 'PUBLIC');

The explain plan for this rather simple query looks like this!
(I am using Oracle 10.2.0.2.0 on Windows platform:

Am I doing something wrong as the explain plan looks rather complex
for this simple query?

Operation Object Name Rows Bytes Cost Object Node
In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 2
323
VIEW SYS.ALL_SYNONYMS 2 136 323
SORT UNIQUE 2 185 323
UNION-ALL
FILTER
NESTED LOOPS 1 86 6
NESTED LOOPS 1 52 5
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID SYS.USER$ 1
18 2
INDEX RANGE SCAN SYS.I_USER1 1
1
TABLE ACCESS BY INDEX ROWID SYS.OBJ$ 1
34 3
INDEX RANGE SCAN SYS.I_OBJ2 1
2
TABLE ACCESS BY INDEX ROWID SYS.SYN$ 1 34
1
INDEX UNIQUE SCAN SYS.I_SYN1 1 0
FILTER
FILTER
NESTED LOOPS 1 61 5
NESTED LOOPS 1 49 4
TABLE ACCESS BY INDEX ROWID SYS.USER$ 1
18 1
INDEX UNIQUE SCAN SYS.I_USER1 1
0
TABLE ACCESS BY INDEX ROWID SYS.OBJ$ 1
31 3
INDEX RANGE SCAN SYS.I_OBJ2 1
2
INDEX RANGE SCAN SYS.I_OBJAUTH1 1 12
1
FIXED TABLE FULL SYS.X$KZSRO 1 13 0
FIXED TABLE FULL SYS.X$KZSPR 1 26 0
HASH JOIN 1 99 315
NESTED LOOPS 1 86 6
NESTED LOOPS 1 52 5
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID SYS.USER$ 1
18 2
INDEX RANGE SCAN SYS.I_USER1 1
1
TABLE ACCESS BY INDEX ROWID SYS.OBJ$ 1
34 3
INDEX RANGE SCAN SYS.I_OBJ2 1
2
TABLE ACCESS BY INDEX ROWID SYS.SYN$ 1 34
1
INDEX UNIQUE SCAN SYS.I_SYN1 1 0
VIEW SYS._ALL_SYNONYMS_TREE 83 1 K 309
CONNECT BY WITH FILTERING
FILTER
COUNT
HASH JOIN 83 7 K 309
TABLE ACCESS FULL SYS.USER$ 139 556
3
NESTED LOOPS 83 7 K 305
HASH JOIN 82 6 K 223
TABLE ACCESS FULL SYS.SYN$ 21 K
713 K 30
HASH JOIN 21 K 1 M 192
TABLE ACCESS FULL SYS.USER$ 139
2 K 3
TABLE ACCESS FULL SYS.OBJ$ 21 K
722 K 188
TABLE ACCESS BY INDEX ROWID SYS.OBJ$
1 8 1
INDEX UNIQUE SCAN SYS.I_OBJ1 1
0
FILTER
NESTED LOOPS 1 95 7
NESTED LOOPS 1 83 6
NESTED LOOPS 1 52 3
TABLE ACCESS BY INDEX ROWID SYS.SYN$
1 34 2
INDEX UNIQUE SCAN SYS.I_SYN1 1
1
TABLE ACCESS BY INDEX ROWID SYS.USER$
139 2 K 1
INDEX UNIQUE SCAN SYS.I_USER1 1
0
TABLE ACCESS BY INDEX ROWID SYS.OBJ$
1 31 3
INDEX RANGE SCAN SYS.I_OBJ2 1
2
INDEX RANGE SCAN SYS.I_OBJAUTH1 1 12
1
FIXED TABLE FULL SYS.X$KZSRO 1 13
0
HASH JOIN
CONNECT BY PUMP
COUNT
HASH JOIN 83 7 K 309
TABLE ACCESS FULL SYS.USER$ 139 556
3
NESTED LOOPS 83 7 K 305
HASH JOIN 82 6 K 223
TABLE ACCESS FULL SYS.SYN$ 21 K
713 K 30
HASH JOIN 21 K 1 M 192
TABLE ACCESS FULL SYS.USER$ 139
2 K 3
TABLE ACCESS FULL SYS.OBJ$ 21 K
722 K 188
TABLE ACCESS BY INDEX ROWID SYS.OBJ$
1 8 1
INDEX UNIQUE SCAN SYS.I_OBJ1 1
0
COUNT
HASH JOIN 83 7 K 309
TABLE ACCESS FULL SYS.USER$ 139 556
3
NESTED LOOPS 83 7 K 305
HASH JOIN 82 6 K 223
TABLE ACCESS FULL SYS.SYN$ 21 K 713 K
30
HASH JOIN 21 K 1 M 192
TABLE ACCESS FULL SYS.USER$ 139
2 K 3
TABLE ACCESS FULL SYS.OBJ$ 21 K
722 K 188
TABLE ACCESS BY INDEX ROWID SYS.OBJ$ 1
8 1
INDEX UNIQUE SCAN SYS.I_OBJ1 1
0

I'd appreciate your suggestions for enhancing the performance and the
script in general.

There is a drawback which I would like to get rid of - the script
requires select privilege in dba_role_privs thus preventing its use
from normal non dba users. Also to do: drop synonyms from users who no
longer have the privilege

The script follows:
save it as fixsyn.sql
SET echo off
REM Description: Create missing create synonym statements for
REM tables/views/sequences/procedures/packages/functions
REM for which select or execute
REM privilege for a user or a role have been given.
REM
REM
REM Usage: SQL>@fixsyn table_name execute0|1
REM First parameter, Table_name may also contain wildcards.
REM
REM second parameter: execute
REM 0=> only display what synonyms to create/drop
REM 1=> execute the statements also.
REM
REM NOTE that if you don't specify 1 as the second parameter this
only
REM creates the create synonym statements,
REM but it doesn't run them! You'll need to copy / paste them to
REM sql*plus.
REM
REM example:
REM SQL> connect table_owner/*****
REM SQL> @fixsyn EMP% 1
REM Spooling - please wait...
REM create synonym SCOTT.EMP2 for TABLE_OWNER.EMP2;
REM --^ EMPLOYEE_ADMIN -> SCOTT
REM drop synonym SCOTT.EMP3
REM --^ TABLE_OWNER.EMP3 no longer exists.
REM drop synonym HR.EMP3
REM --^ TABLE_OWNER.EMP3 no longer exists.
REM -- Above synonym statements have been executed.
REM
REM
REM
SET pagesize 0
SET line 200
SET feedback off
SET verify off
SET recsep each
SET echo off
COLUMN cre format a130
COLUMN why format a130
SET serveroutput on size 100000
PROMPT -- Creating synonym statements for &1 - please wait...


DECLARE
PROCEDURE exec (stmt IN VARCHAR2)
IS
exec_cursor INTEGER DEFAULT DBMS_SQL.open_cursor;
rows_processed NUMBER DEFAULT 0;
BEGIN
DBMS_SQL.parse (exec_cursor, stmt, DBMS_SQL.native);
rows_processed := DBMS_SQL.EXECUTE (exec_cursor);
DBMS_SQL.close_cursor (exec_cursor);
EXCEPTION
WHEN OTHERS
THEN
IF DBMS_SQL.is_open (exec_cursor)
THEN
DBMS_SQL.close_cursor (exec_cursor);
END IF;

-- raise;
DBMS_OUTPUT.put_line (SQLERRM);
END;

PROCEDURE fix_synonym (
p_userrole IN STRING, -- =>
role/user
p_tablename IN STRING, -- =>
tablename
p_table_owner IN STRING, -- =>
table owner
p_toplevel_grantee IN STRING, -- => for
comments
p_immediate IN NUMBER
)
IS -- => 1=>execute
synonym create
foo VARCHAR2 (32);
found_users NUMBER;
BEGIN
IF (p_userrole = USER)
THEN
RETURN; -- don't create synonyms for
table owner
END IF;

SELECT COUNT (*)
INTO found_users
FROM all_users
WHERE username = p_userrole;

IF (1 = found_users)
THEN
BEGIN
SELECT owner
INTO foo
FROM all_synonyms
WHERE table_name = UPPER (p_tablename)
AND synonym_name = UPPER (p_tablename)
AND table_owner = p_table_owner
AND (owner = p_userrole OR owner = 'PUBLIC');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- no synonym exist, create it.
DBMS_OUTPUT.put_line ( 'create synonym '
DBMS_OUTPUT.put_line ( ' --^ Privilege granted to '

IF (1 = p_immediate)
THEN
exec ( 'create synonym '
END IF;
END;
ELSE
-- find all users/roles this role has been granted to, fix
synonyms for them.
FOR l_username IN (SELECT grantee
FROM dba_role_privs
WHERE granted_role = p_userrole)
LOOP
fix_synonym (l_username.grantee,
p_tablename,
p_table_owner,
p_toplevel_grantee || ' -> ' ||
l_username.grantee,
p_immediate
);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--procedure fix_synonym
BEGIN
-- actual code;
FOR tab_privs IN (SELECT DISTINCT table_name, grantee
FROM user_tab_privs_made
WHERE table_name LIKE UPPER ('&1')
ORDER BY table_name, grantee)
LOOP
fix_synonym (tab_privs.grantee,
tab_privs.table_name,
USER,
tab_privs.grantee,
&2
);
END LOOP;

-- create drop synonym statements for objects which no longer exist
FOR stmt IN
(SELECT 'drop '
exists.' why
FROM all_synonyms s
WHERE s.table_owner = USER
AND s.table_name LIKE UPPER ('&1')
AND s.table_name NOT IN (
SELECT object_name
FROM all_objects
WHERE object_name = s.table_name
AND owner = s.table_owner
AND object_type IN
('FUNCTION',
'PACKAGE',
'PROCEDURE',
'SEQUENCE',
'TABLE',
'VIEW'
))
ORDER BY synonym_name)
LOOP
IF (1 = &2)
THEN
exec (stmt.cre);
END IF;

DBMS_OUTPUT.put_line (stmt.cre);
DBMS_OUTPUT.put_line ('--^ ' || stmt.why);
END LOOP;
-- to do: drop synonyms from users who no longer have the privilege
for the object
END;
/

SELECT DECODE
('&2',
'1', '-- Above synonym statements (if any) have been
executed.',
'-- Run the above statements (if any) to actually create /
delete synonyms.'
)
FROM DUAL;
SET feedback on
SET verify on
SET head on
SET recsep wrapped
CLEAR columns
SET pagesize 60

On Mar 26, 12:54*am, Kirmo Uusitalo <n... (AT) exists (DOT) com.invalid> wrote:
Remember, all_synonyms is a view, with exists and unions. You may
have more luck dealing with the underlying tables. Or maybe David's
suggestion will be good enough for the optimizer to sort through all
that stuff.

jg
--
@home.com is bogus.
Hey, that's what I started on: http://www.nytimes.com/2008/03/23/te...=1&oref=slogin

On Mar 26, 12:54*am, Kirmo Uusitalo <n... (AT) exists (DOT) com.invalid> wrote:
Remember, all_synonyms is a view, with exists and unions. You may
have more luck dealing with the underlying tables. Or maybe David's
suggestion will be good enough for the optimizer to sort through all
that stuff.

jg
--
@home.com is bogus.
Hey, that's what I started on: http://www.nytimes.com/2008/03/23/te...=1&oref=slogin

On Mar 26, 12:54*am, Kirmo Uusitalo <n... (AT) exists (DOT) com.invalid> wrote:
Remember, all_synonyms is a view, with exists and unions. You may
have more luck dealing with the underlying tables. Or maybe David's
suggestion will be good enough for the optimizer to sort through all
that stuff.

jg
--
@home.com is bogus.
Hey, that's what I started on: http://www.nytimes.com/2008/03/23/te...=1&oref=slogin

On Mar 26, 12:54*am, Kirmo Uusitalo <n... (AT) exists (DOT) com.invalid> wrote:
Remember, all_synonyms is a view, with exists and unions. You may
have more luck dealing with the underlying tables. Or maybe David's
suggestion will be good enough for the optimizer to sort through all
that stuff.

jg
--
@home.com is bogus.
Hey, that's what I started on: http://www.nytimes.com/2008/03/23/te...=1&oref=slogin

On Wed, 26 Mar 2008 17:28:45 -0700 (PDT), joel garry
<joel-garry (AT) home (DOT) com> wrote:

Yes I am aware of this. But as far as I know the views stay the same
between Oracle versions but the underlying table structures may
change. This is why I prefer using the view instead.

I tried David Fitzjarrell's suggestion, running this)
dbms_stats.gather_fixed_objects_stats(NULL).

It seemed to improve running time a little,
but still running this fixsyn.sql against one table in a small
database (around 50 users) takes about 2,5 seconds which seems to me
quite a long time for such a simple task.

Thank you all for these!

Kirmo Uusitalo

On Wed, 26 Mar 2008 17:28:45 -0700 (PDT), joel garry
<joel-garry (AT) home (DOT) com> wrote:

Yes I am aware of this. But as far as I know the views stay the same
between Oracle versions but the underlying table structures may
change. This is why I prefer using the view instead.

I tried David Fitzjarrell's suggestion, running this)
dbms_stats.gather_fixed_objects_stats(NULL).

It seemed to improve running time a little,
but still running this fixsyn.sql against one table in a small
database (around 50 users) takes about 2,5 seconds which seems to me
quite a long time for such a simple task.

Thank you all for these!

Kirmo Uusitalo

On Wed, 26 Mar 2008 17:28:45 -0700 (PDT), joel garry
<joel-garry (AT) home (DOT) com> wrote:

Yes I am aware of this. But as far as I know the views stay the same
between Oracle versions but the underlying table structures may
change. This is why I prefer using the view instead.

I tried David Fitzjarrell's suggestion, running this)
dbms_stats.gather_fixed_objects_stats(NULL).

It seemed to improve running time a little,
but still running this fixsyn.sql against one table in a small
database (around 50 users) takes about 2,5 seconds which seems to me
quite a long time for such a simple task.

Thank you all for these!

Kirmo Uusitalo

On Wed, 26 Mar 2008 17:28:45 -0700 (PDT), joel garry
<joel-garry (AT) home (DOT) com> wrote:

Yes I am aware of this. But as far as I know the views stay the same
between Oracle versions but the underlying table structures may
change. This is why I prefer using the view instead.

I tried David Fitzjarrell's suggestion, running this)
dbms_stats.gather_fixed_objects_stats(NULL).

It seemed to improve running time a little,
but still running this fixsyn.sql against one table in a small
database (around 50 users) takes about 2,5 seconds which seems to me
quite a long time for such a simple task.

Thank you all for these!

Kirmo Uusitalo

On Mar 28, 1:15*am, Kirmo Uusitalo <n... (AT) exists (DOT) com.invalid> wrote:
You might start with http://asktom.oracle.com/pls/asktom/...74900346092274

But it's never going to be faster if you are processing 3 full table
scans of sys.obj$ one row at a time.

jg
--
@home.com is bogus.
Access denied for user 'aquarist_yanton'@'localhost' (using password:
YES) in /home/tomsiebe/public_html/phrase_tracer.php on line 54
I cannot connect to the database because: Access denied for user
'aquarist_yanton'@'localhost' (using password: YES)