anthony-tuininga
ExportObjects slow
Hi Anthony!
I'm trying to export tables from a schema and running into performance issues. When I set the log-level to DEBUG, I saw that this query was taking 30+ seconds on average to execute per object.
o.owner,
o.index_name,
o.table_name,
o.tablespace_name,
o.uniqueness,
o.initial_extent,
o.next_extent,
o.min_extents,
o.max_extents,
o.pct_increase,
o.index_type,
o.partitioned,
o.temporary,
o.compression,
o.prefix_length,
o.ityp_owner,
o.ityp_name,
o.parameters
from all_indexes o
where o.owner = :owner and o.table_name = :name
and o.index_type in ('NORMAL', 'NORMAL/REV', 'IOT - TOP', 'BITMAP',
'FUNCTION-BASED NORMAL', 'FUNCTION-BASED NORMAL/REV',
'DOMAIN')
and not exists
( select 1
from all_constraints
where owner = o.owner
and constraint_name = o.index_name
)
order by o.owner, o.index_name
This is what the explain plan looks like for that query:
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 | 13 | 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 290 | 13 | 00:00:01 |
| * 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 290 | 11 | 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 274 | 10 | 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 270 | 9 | 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 229 | 8 | 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 205 | 7 | 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 179 | 6 | 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 147 | 5 | 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 102 | 4 | 00:00:01 |
| 11 | NESTED LOOPS | | 43 | 2709 | 2 | 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 16 | 1 | 00:00:01 |
| * 13 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 1 | 00:00:01 |
| * 14 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 43 | 2021 | 1 | 00:00:01 |
| * 15 | INDEX RANGE SCAN | I_OBJ2 | 43 | | 1 | 00:00:01 |
| * 16 | FILTER | | | | | |
| 17 | PX COORDINATOR | | | | | |
| 18 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 250 | 1 | 00:00:01 |
| 19 | PX PARTITION LIST ALL | | 1 | 250 | 1 | 00:00:01 |
| * 20 | EXTENDED DATA LINK FULL | INT$INT$DBA_CONSTRAINTS | 1 | 250 | 1 | 00:00:01 |
| 21 | NESTED LOOPS SEMI | | 1 | 15 | 1 | 00:00:01 |
| * 22 | FIXED TABLE FULL | X$KZSRO | 1 | 6 | 0 | 00:00:01 |
| * 23 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 | 00:00:01 |
| * 24 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 39 | 1 | 00:00:01 |
| * 25 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 | 00:00:01 |
| * 26 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 45 | 1 | 00:00:01 |
| * 27 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 | 00:00:01 |
| 28 | TABLE ACCESS CLUSTER | SEG$ | 1 | 32 | 1 | 00:00:01 |
| * 29 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 | 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | DEFERRED_STG$ | 1 | 26 | 1 | 00:00:01 |
| * 31 | INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | | 1 | 00:00:01 |
| 32 | TABLE ACCESS CLUSTER | TS$ | 1 | 24 | 1 | 00:00:01 |
| * 33 | INDEX UNIQUE SCAN | I_TS# | 1 | | 1 | 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 41 | 1 | 00:00:01 |
| * 35 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 | 00:00:01 |
| * 36 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 | 00:00:01 |
| 37 | TABLE ACCESS CLUSTER | USER$ | 1 | 16 | 1 | 00:00:01 |
| * 38 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 | 00:00:01 |
| * 39 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 1 | 00:00:01 |
| * 40 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 | 00:00:01 |
| 41 | NESTED LOOPS SEMI | | 1 | 15 | 1 | 00:00:01 |
| * 42 | FIXED TABLE FULL | X$KZSRO | 1 | 6 | 0 | 00:00:01 |
| * 43 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 9 | 1 | 00:00:01 |
Any help would be appreciated. Thanks!
Note, I was able to get around this issue by providing the following arguments --no-comments --no-grants --no-related --no-triggers --dont-merge-grants --default-tablespace --default-storage
I merged the PR you created in cx_PyOracleLib -- but this has been addressed differently in the current main. There I am making use of the new driver (oracledb) in thin mode and modernizing the code at the same time. The process is slow but you can see what I have done so far if that is of interest!
