Technical Track Open Source

Inefficient queries to ALL_SYNONYMS

by Timur Akhmadeev
12 min read
Jul 26, 2018 12:00:00 AM
Short summary: Queries to ALL_SYNONYMS cause FTS of SYS.OBJ$ which can't be avoided. Let's have a look at a simple query and its execution plan in my test 12.1.0.2 instance. The plan is pretty big, adaptive and uses dynamic sampling. It is composed of two UNION ALL branches. The first branch starts at plan step ID 4, and is not a big deal - just 4 buffer gets and no rows returned right in the start of the branch execution, lines 12-16. The second part is more interesting. It is composed by the _ALL_SYNONYMS_TREE view, and, as the name suggests, it's a CONNECT BY on top of a multi-table join. And where are the top query conditions applied? On step 27, after _ALL_SYNONYMS_TREE is fully instantiated. This is the only way to execute such a query, since there's no good START WITH condition in the CONNECT BY, and the top query conditions logically can't be pushed into START WITH. [sourcecode lang="sql" gutter="false" collapse="true"] SELECT TABLE_NAME, TABLE_OWNER, DB_LINK FROM ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'X'; Plan hash value: 4035506875 ---------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.04 | 398 | | 1 | VIEW | ALL_SYNONYMS | 1 | 3 | 0 |00:00:00.04 | 398 | | 2 | SORT UNIQUE | | 1 | 3 | 0 |00:00:00.04 | 398 | | 3 | UNION-ALL | | 1 | | 0 |00:00:00.04 | 398 | | * 4 | FILTER | | 1 | | 0 |00:00:00.01 | 4 | |- * 5 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 4 | | 6 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 4 | | 7 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 4 | |- 8 | STATISTICS COLLECTOR | | 1 | | 0 |00:00:00.01 | 4 | |- * 9 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 4 | | 10 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 4 | |- 11 | STATISTICS COLLECTOR | | 1 | | 0 |00:00:00.01 | 4 | | 12 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 4 | | 13 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 |00:00:00.01 | 2 | | * 14 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 1 | | 15 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 1 | 0 |00:00:00.01 | 2 | | * 16 | INDEX RANGE SCAN | I_OBJ5 | 1 | 1 | 0 |00:00:00.01 | 2 | | * 17 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | |- 18 | INDEX FULL SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 19 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 1 | 0 |00:00:00.01 | 0 | | 20 | TABLE ACCESS BY INDEX ROWID | SYN$ | 0 | 1 | 0 |00:00:00.01 | 0 | |- 21 | TABLE ACCESS FULL | SYN$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 22 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | 23 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | * 24 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 25 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 26 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 27 | VIEW | _ALL_SYNONYMS_TREE | 1 | 2 | 0 |00:00:00.04 | 394 | | * 28 | CONNECT BY NO FILTERING WITH START-WITH | | 1 | | 0 |00:00:00.04 | 394 | | * 29 | FILTER | | 1 | | 0 |00:00:00.04 | 394 | | * 30 | HASH JOIN | | 1 | 89 | 0 |00:00:00.04 | 394 | | 31 | TABLE ACCESS FULL | USER$ | 1 | 76 | 76 |00:00:00.01 | 6 | | * 32 | HASH JOIN | | 1 | 89 | 0 |00:00:00.04 | 388 | | 33 | INDEX FULL SCAN | I_USER2 | 1 | 76 | 76 |00:00:00.01 | 1 | |- * 34 | HASH JOIN | | 1 | 89 | 0 |00:00:00.03 | 387 | | 35 | NESTED LOOPS | | 1 | 89 | 0 |00:00:00.03 | 387 | | 36 | NESTED LOOPS | | 1 | | 0 |00:00:00.03 | 387 | |- 37 | STATISTICS COLLECTOR | | 1 | | 0 |00:00:00.03 | 387 | | * 38 | HASH JOIN | | 1 | 89 | 0 |00:00:00.03 | 387 | | 39 | INDEX FULL SCAN | I_USER2 | 1 | 76 | 76 |00:00:00.01 | 1 | | * 40 | HASH JOIN | | 1 | 89 | 0 |00:00:00.03 | 386 | | * 41 | TABLE ACCESS FULL | OBJ$ | 1 | 5182 | 5182 |00:00:00.01 | 349 | | * 42 | HASH JOIN | | 1 | 5102 | 5178 |00:00:00.01 | 37 | | 43 | TABLE ACCESS FULL | USER$ | 1 | 76 | 76 |00:00:00.01 | 6 | | 44 | TABLE ACCESS FULL | SYN$ | 1 | 5102 | 5182 |00:00:00.01 | 31 | | * 45 | INDEX RANGE SCAN | I_OBJ1 | 0 | | 0 |00:00:00.01 | 0 | | 46 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | |- * 47 | TABLE ACCESS FULL | OBJ$ | 0 | 5182 | 0 |00:00:00.01 | 0 | | * 48 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | 49 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | * 50 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 51 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 52 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 53 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | 54 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | * 55 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 56 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 57 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 58 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | * 59 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | 60 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | 61 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | 62 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | 63 | TABLE ACCESS BY INDEX ROWID | SYN$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 64 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 1 | 0 |00:00:00.01 | 0 | | 65 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 66 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 67 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 68 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 69 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | * 70 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | 71 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | 72 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | 73 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | 74 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 75 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 0 | | 76 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 77 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 78 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 79 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 80 | FIXED TABLE FULL | X$KZSRO | 0 | 1 | 0 |00:00:00.01 | 0 | | * 81 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | 82 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | * 83 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 84 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 85 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | * 86 | FIXED TABLE FULL | X$KZSPR | 0 | 23 | 0 |00:00:00.01 | 0 | | * 87 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | 88 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | * 89 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 90 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | * 91 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | ---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR (((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL))) 5 - access("O"."OBJ#"="S"."OBJ#") 9 - access("O"."OWNER#"="U"."USER#") 14 - access("U"."NAME"='PUBLIC') 16 - access("O"."SPARE3"="U"."USER#" AND "O"."NAME"='X' AND "O"."TYPE#"=5) filter("O"."TYPE#"=5) 17 - access("O"."OWNER#"="U"."USER#") 19 - access("O"."OBJ#"="S"."OBJ#") 22 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2)) 24 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))) 25 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 26 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)) 27 - filter(("ST"."SYN_OWNER"='PUBLIC' AND "ST"."SYN_SYNONYM_NAME"='X')) 28 - access("S"."BASE_SYN_ID"=PRIOR NULL AND "S"."ORIGIN_CON_ID"=PRIOR NULL) filter( IS NOT NULL) 29 - filter(((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR (((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)) AND (( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR (((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))) 30 - access("O"."SPARE3"="U"."USER#") 32 - access("O"."OWNER#"="U"."USER#") 34 - access("O"."OBJ#"="S"."OBJ#") 38 - access("O"."OWNER#"="U"."USER#") 40 - access("BU"."USER#"="O"."SPARE3" AND "S"."NAME"="O"."NAME") 41 - filter("O"."TYPE#"=5) 42 - access("S"."OWNER"="BU"."NAME") 45 - access("O"."OBJ#"="S"."OBJ#" AND "O"."TYPE#"=5) filter("O"."TYPE#"=5) 47 - filter("O"."TYPE#"=5) 48 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2)) 50 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))) 51 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 52 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)) 53 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2)) 55 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))) 56 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 57 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)) 58 - filter((( IS NOT NULL OR ("S"."NODE" IS NULL AND IS NOT NULL)) AND (( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR (((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))) 59 - filter(TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) IS NOT NULL) 64 - access("S"."OBJ#"=:B1) 66 - access("O"."OBJ#"=:B1 AND "O"."TYPE#"=5) filter("O"."TYPE#"=5) 67 - access("O"."SPARE3"="U"."USER#") 68 - access("O"."OWNER#"="U"."USER#") 69 - filter((( IS NOT NULL OR "BA"."GRANTOR#"=USERENV('SCHEMAID')) AND (( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR (((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))) 70 - filter(:B1 IS NULL) 75 - access("BU"."NAME"=:B1) 77 - access("BU"."USER#"="O"."SPARE3" AND "O"."NAME"=:B1) 78 - access("O"."OWNER#"="U"."USER#") 79 - access("BA"."OBJ#"="O"."OBJ#") 80 - filter("KZSROROL"=:B1) 81 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2)) 83 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))) 84 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 85 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)) 86 - filter((((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-397) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)) AND "INST_ID"=USERENV('INSTANCE'))) 87 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2)) 89 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))) 90 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 91 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)) Note ----- - dynamic statistics used: dynamic sampling (level=2) - this is an adaptive plan (rows marked '-' are inactive) [/sourcecode] Because my test database is very small - only 22K objects created - such a query to ALL_SYNONYMS is pretty fast, and it takes just under 400 buffer gets to execute. Let's look at the same query execution statistics in an EBS 12.2 database. Although the plan is slightly different, its shape is still the same, the hierarchy is still there, and SYS.OBJ$ full scan is present. You can see that OBJ$ is much bigger, more than 600MB. FTS of OBJ$ was pretty fast because of the buffered reads, and all OBJ$'s blocks were cached already. Occasionally though, Oracle switches to direct path reads while running this query, and it means reading 600MB+ data off disk. In such a case, the execution time can go up; I've seen a few cases where it was as high as 30s. On top of the issue with FTS, there are multiple subqueries which drive buffer gets up to 720K. This is a side effect of filtering _ALL_SYNONYMS_TREE data too late on step 23. [sourcecode lang="sql" gutter="false" collapse="true"] ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:04.78 | 720K| 2387 | | 1 | VIEW | ALL_SYNONYMS | 1 | 3 | 1 |00:00:04.78 | 720K| 2387 | | 2 | SORT UNIQUE | | 1 | 3 | 1 |00:00:04.78 | 720K| 2387 | | 3 | UNION-ALL | | 1 | | 1 |00:00:04.78 | 720K| 2387 | |* 4 | FILTER | | 1 | | 1 |00:00:00.01 | 15 | 2 | | 5 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 12 | 2 | | 6 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 11 | 2 | | 7 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 9 | 1 | | 8 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 | 1 | | 9 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | |* 10 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | 11 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 1 | 1 |00:00:00.01 | 4 | 1 | |* 12 | INDEX RANGE SCAN | I_OBJ5 | 1 | 1 | 1 |00:00:00.01 | 3 | 1 | |* 13 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | |* 14 | INDEX UNIQUE SCAN | I_SYN1 | 1 | 1 | 1 |00:00:00.01 | 2 | 1 | | 15 | TABLE ACCESS BY INDEX ROWID | SYN$ | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | |* 16 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | |* 17 | INDEX RANGE SCAN | I_USER_EDITIONING | 1 | 12 | 2 |00:00:00.01 | 2 | 0 | |* 18 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |* 19 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 12 | 0 |00:00:00.01 | 0 | 0 | | 20 | NESTED LOOPS SEMI | | 0 | 5 | 0 |00:00:00.01 | 0 | 0 | |* 21 | INDEX RANGE SCAN | I_OBJ4 | 0 | 5 | 0 |00:00:00.01 | 0 | 0 | |* 22 | INDEX RANGE SCAN | I_USER2 | 0 | 266 | 0 |00:00:00.01 | 0 | 0 | |* 23 | VIEW | _ALL_SYNONYMS_TREE | 1 | 2 | 0 |00:00:04.78 | 720K| 2385 | |* 24 | CONNECT BY NO FILTERING WITH START-WITH | | 1 | | 10075 |00:00:04.78 | 720K| 2385 | |* 25 | FILTER | | 1 | | 10058 |00:00:01.41 | 333K| 0 | |* 26 | HASH JOIN | | 1 | 70 | 10058 |00:00:00.63 | 112K| 0 | |* 27 | HASH JOIN | | 1 | 70 | 10058 |00:00:00.61 | 112K| 0 | | 28 | NESTED LOOPS | | 1 | 70 | 10058 |00:00:00.60 | 112K| 0 | | 29 | NESTED LOOPS | | 1 | 70 | 10058 |00:00:00.59 | 105K| 0 | |* 30 | HASH JOIN | | 1 | 70 | 10058 |00:00:00.46 | 87654 | 0 | |* 31 | HASH JOIN | | 1 | 70 | 10058 |00:00:00.45 | 87634 | 0 | | 32 | TABLE ACCESS FULL | USER$ | 1 | 3293 | 3293 |00:00:00.01 | 173 | 0 | |* 33 | HASH JOIN | | 1 | 193K| 198K|00:00:00.42 | 87461 | 0 | | 34 | TABLE ACCESS FULL | SYN$ | 1 | 174K| 174K|00:00:00.04 | 1086 | 0 | |* 35 | TABLE ACCESS FULL | OBJ$ | 1 | 174K| 174K|00:00:00.28 | 86375 | 0 | | 36 | INDEX FULL SCAN | I_USER2 | 1 | 3293 | 3293 |00:00:00.01 | 20 | 0 | |* 37 | INDEX RANGE SCAN | I_OBJ1 | 10058 | 1 | 10058 |00:00:00.13 | 17611 | 0 | | 38 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 10058 | 1 | 10058 |00:00:00.01 | 6803 | 0 | | 39 | INDEX FULL SCAN | I_USER2 | 1 | 3293 | 3293 |00:00:00.01 | 20 | 0 | | 40 | TABLE ACCESS FULL | USER$ | 1 | 3293 | 3293 |00:00:00.01 | 173 | 0 | |* 41 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 4 | 1 | 3 |00:00:00.01 | 11 | 0 | |* 42 | INDEX RANGE SCAN | I_USER_EDITIONING | 4 | 12 | 9 |00:00:00.01 | 8 | 0 | |* 43 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 3 | 1 | 3 |00:00:00.01 | 9 | 0 | |* 44 | INDEX RANGE SCAN | I_USER_EDITIONING | 3 | 12 | 9 |00:00:00.01 | 6 | 0 | | 45 | NESTED LOOPS SEMI | | 9419 | 5 | 9419 |00:00:00.39 | 113K| 0 | |* 46 | INDEX RANGE SCAN | I_OBJ4 | 9419 | 5 | 81193 |00:00:00.26 | 22421 | 0 | |* 47 | INDEX RANGE SCAN | I_USER2 | 81193 | 266 | 9419 |00:00:00.11 | 90613 | 0 | |* 48 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 42 | 1 | 42 |00:00:00.01 | 121 | 0 | |* 49 | INDEX RANGE SCAN | I_USER_EDITIONING | 42 | 12 | 122 |00:00:00.01 | 79 | 0 | |* 50 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 41 | 1 | 41 |00:00:00.01 | 118 | 0 | |* 51 | INDEX RANGE SCAN | I_USER_EDITIONING | 41 | 12 | 120 |00:00:00.01 | 77 | 0 | | 52 | NESTED LOOPS SEMI | | 10053 | 5 | 10053 |00:00:00.35 | 107K| 0 | |* 53 | INDEX RANGE SCAN | I_OBJ4 | 10053 | 5 | 69782 |00:00:00.24 | 27630 | 0 | |* 54 | INDEX RANGE SCAN | I_USER2 | 69782 | 266 | 10053 |00:00:00.09 | 79952 | 0 | |* 55 | FILTER | | 9429 | | 9424 |00:00:03.29 | 387K| 2385 | |* 56 | FILTER | | 9429 | | 9424 |00:00:00.37 | 68334 | 211 | | 57 | NESTED LOOPS | | 9429 | 1 | 9424 |00:00:00.37 | 68334 | 211 | | 58 | NESTED LOOPS | | 9429 | 1 | 9424 |00:00:00.35 | 58613 | 211 | | 59 | NESTED LOOPS | | 9429 | 1 | 9424 |00:00:00.33 | 49184 | 211 | | 60 | TABLE ACCESS BY INDEX ROWID | SYN$ | 9429 | 1 | 9424 |00:00:00.24 | 21904 | 211 | |* 61 | INDEX UNIQUE SCAN | I_SYN1 | 9429 | 1 | 9424 |00:00:00.22 | 12480 | 211 | | 62 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 9424 | 1 | 9424 |00:00:00.08 | 27280 | 0 | |* 63 | INDEX RANGE SCAN | I_OBJ1 | 9424 |
On this page
Let DataGuard Broker Do ALL The Work
Google Cloud Platform

Let DataGuard Broker Do ALL The Work

Feb 18, 2020 12:00:00 AM 2 min read
Oracle Database: Query to List All Statistics Tables
Oracle

Oracle Database: Query to List All Statistics Tables

Jun 25, 2014 12:00:00 AM 1 min read
In depth: MySQL 5.6+ DDL
Kubernetes

In depth: MySQL 5.6+ DDL

Mar 29, 2016 12:00:00 AM 8 min read

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

Follow Us     Pythian-LinkedIn   Pythian-Twitter   Pythian-FB

 

Pythian-logo-White

© 2025 All rights reserved.