Slow data dictionary query from all_synonyms fixsyn script
From: Kirmo Uusitalo <not_at_exists.com.invalid>
Date: Wed, 26 Mar 2008 09:54:34 +0200
Message-ID: <bavju3h3riesvvndjb52s2uekmcoe4b63v@4ax.com>
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...
END;
--procedure fix_synonym
BEGIN
-- actual code;
END LOOP;
-- to do: drop synonyms from users who no longer have the privilege for the object
END;
/
FROM DUAL;
SET feedback on
SET verify on
SET head on
SET recsep wrapped
CLEAR columns
SET pagesize 60 Received on Wed Mar 26 2008 - 02:54:34 CDT
Date: Wed, 26 Mar 2008 09:54:34 +0200
Message-ID: <bavju3h3riesvvndjb52s2uekmcoe4b63v@4ax.com>
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 '
|| p_userrole
|| '.'
|| p_tablename
|| ' for '
|| p_table_owner
|| '.'
|| p_tablename
|| ';'
);
DBMS_OUTPUT.put_line ( ' --^ Privilege granted to '
|| p_toplevel_grantee
);
IF (1 = p_immediate)
THEN
exec ( 'create synonym '
|| p_userrole
|| '.'
|| p_tablename
|| ' for '
|| p_table_owner
|| '.'
|| p_tablename
);
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 ' || DECODE (owner, 'PUBLIC', 'PUBLIC', '') || ' synonym ' || DECODE (owner, 'PUBLIC', ' ', owner || '.') || synonym_name cre, table_owner || '.' || table_name || ' no longer 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 Received on Wed Mar 26 2008 - 02:54:34 CDT