Re: Slow data dictionary query from all_synonyms fixsyn script

From: <maxpayne4u2_at_gmail.com>
Date: Tue, 20 Dec 2016 09:55:24 -0800 (PST)
Message-ID: <91fe3b77-30f3-4df7-8da3-dc2cef00d0cf_at_googlegroups.com>

On Wednesday, March 26, 2008 at 7:29:42 AM UTC-5, fitzj..._at_cox.net wrote:
> On Mar 26, 2:54 am, Kirmo Uusitalo <n..._at_exists.com.invalid> wrote:
> > 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>_at_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> _at_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
>
> It's too bad you couldn't be thoughtful enough to post the version of
> Oracle you're running this against (all four to five numbers) as that
> makes a huge difference in how one responds to your interrogatory.
>
>
> David Fitzjarrell

Why do you have to be a STupid F*UCK and be so rude and act as if you are Tom Kyte? Received on Tue Dec 20 2016 - 18:55:24 CET

This message: [ Message body ]
Previous message: Mark D Powell: "Re: How to add the TNS_ADMIN to Windows registry"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message