test_11g is ~3.7x slower than 23c: ALL_* dictionary queries in structure_dump dominate

[yahonda]

Summary

The test_11g CI workflow is consistently ~3.7× slower than the 23c workflow (test.yml) on the same commit, despite running the same test suite:

Workflow	Ruby 4.0	Ruby 3.4	Ruby 3.3	jruby-10.0.5.0
23c (test.yml)	3:53	3:56	3:46	5:43
11g (test_11g.yml)	14:27	14:34	14:43	15:36

Profiling locally (Apple Silicon, Docker gvenzl/oracle-xe:11 under amd64 emulation on port 1522, vs existing 23c FREEPDB1 on port 1521) using bundle exec rspec --profile 30 amplifies the ratio (emulation penalty) but preserves the signal:

DB	Total	Examples
23c	1 min 12.79 s	430 (6 pending)
11g	17 min 13 s	430 (9 pending)

The gap is concentrated in a handful of example groups that exercise data-dictionary queries repeatedly.

Total elapsed time per example group

Top slowest example groups on 11g (local run) vs the same groups on 23c:

Group	11g total	23c total	Examples
structure_dump_spec.rb	275.13 s	~31 s	32
schema_dumper_spec.rb	223.92 s	~35 s	31
schema_statements_spec.rb	171.55 s	~15 s	80
context_index_spec.rb	104.31 s	~25 s	23
connection_adapters/oracle_enhanced/database_tasks_spec.rb	37.66 s	~10 s	6

The slowest individual examples on 11g (all from spec/active_record/connection_adapters/oracle_enhanced/structure_dump_spec.rb):

Example (line)	11g	23c
should dump virtual columns (130)	28.23 s	~0.6 s
should dump RAW virtual columns (143)	28.06 s	~0.6 s
should dump NCLOB columns (156)	28.00 s	~0.6 s
should dump foreign keys when reference column name is not 'id' (75)	25.76 s	~0.6 s
should dump indexes (180)	25.44 s	~0.6 s
should dump unique keys (168)	24.98 s	~0.6 s
should dump composite primary keys (50)	24.06 s	2.42 s
should dump views (123)	24.01 s	~0.6 s
should dump single primary key (45)	23.60 s	~0.6 s
should dump foreign keys (65)	23.05 s	~0.6 s

Per-query elapsed time

SQL categories aggregated from debug.log, ranked by 11g-vs-23c delta:

Query	Calls	11g total	11g / call	23c total	23c / call	11g ÷ 23c
SELECT synonym_name, table_owner, table_name FROM all_synonyms WHERE owner = SYS_CONTEXT('userenv','current_schema')	143	44 262 ms	310 ms	1 605 ms	11 ms	28×
SELECT owner, table_name FROM all_tables WHERE owner = :owner AND table_name = :table_name	633	28 034 ms	44 ms	547 ms	0.9 ms	51×
SELECT r.table_name, rc.column_name, cc.column_name, c.constraint_name, c.delete_rule FROM all_constraints c, all_cons_columns cc, all_constraints r, all_cons_columns rc … (FK lookup)	175	16 961 ms	97 ms	5 885 ms	34 ms	3×
SELECT LOWER(i.table_name), LOWER(i.index_name), i.uniqueness, i.index_type, … FROM all_indexes i JOIN all_ind_columns c ON … LEFT OUTER JOIN …	537	50 917 ms	95 ms	8 293 ms	15 ms	6×
SELECT a.constraint_name, a.column_name, a.position FROM all_cons_columns a JOIN all_constraints c ON a.constraint_name = c.constraint_name WHERE c.table_name = … AND constraint_type IN ('P','U')	229	7 685 ms	34 ms	551 ms	2.4 ms	14×
SELECT temporary FROM all_tables WHERE table_name = :table_name AND owner = SYS_CONTEXT(...)	153	5 723 ms	37 ms	216 ms	1.4 ms	26×
SELECT table_name FROM all_tables WHERE owner = SYS_CONTEXT(...)	29	4 787 ms	165 ms	383 ms	13 ms	12×
SELECT DECODE(table_name, UPPER(...), LOWER(...), table_name) FROM all_tables … MINUS SELECT DECODE(mview_name, …) FROM all_mviews …	147	28 262 ms	192 ms	1 014 ms	6.9 ms	28×
SELECT LOWER(default_tablespace) FROM user_users WHERE username = SYS_CONTEXT(...) (mixed into SELECT LOWER bucket)	537 total	(part of 50.9 s above)
CREATE TABLE DDL	221	19 194 ms	87 ms	1 175 ms	5.3 ms	16×
CREATE INDEX DDL	25	14 799 ms	592 ms	6 154 ms	246 ms	2.4×

Ruby hotspot

Most of the dictionary-view calls originate in lib/active_record/connection_adapters/oracle_enhanced/structure_dump.rb:

• structure_dump fetches all_sequences, then all_tables (with NOT EXISTS subqueries against all_mviews/all_mview_logs), then for every table loops and issues all_tab_columns, PK join, UK join, indexes(table) (→ the big all_indexes LOWER join), structure_dump_check_constraints (all_constraints), structure_dump_table_comments, structure_dump_column_comments.
• structure_dump_fk_constraints re-fetches all_tables and calls foreign_keys(table_name) per table (→ the big FK join).
• structure_dump_views (→ all_views), structure_dump_synonyms (→ all_synonyms, the single slowest statement category on 11g).

The 32 examples in structure_dump_spec.rb each invoke structure_dump once, paying this N-tables × per-table-query cost. On 11g the ALL_* plan choice is dramatically worse than on 23c.

Reproducing locally

docker run -d --name oracle-xe11 --platform linux/amd64 \
  -p 1522:1521 -e ORACLE_PASSWORD=Oracle18 -e TZ=Europe/Riga \
  gvenzl/oracle-xe:11

# wait for container health, then:
DATABASE_NAME=//localhost:1522/XE DATABASE_SYS_PASSWORD=Oracle18 \
  ./ci/setup_accounts.sh

# copy v14 timezone files from container to client so OCI matches 11g
docker cp oracle-xe11:/u01/app/oracle/product/11.2.0/xe/oracore/zoneinfo/timezlrg_14.dat "$ORACLE_HOME/oracore/zoneinfo/"
docker cp oracle-xe11:/u01/app/oracle/product/11.2.0/xe/oracore/zoneinfo/timezone_14.dat  "$ORACLE_HOME/oracore/zoneinfo/"

DATABASE_NAME=XE DATABASE_PORT=1522 DATABASE_HOST=localhost \
DATABASE_SYS_PASSWORD=Oracle18 DATABASE_VERSION=11.2.0.2 \
TZ=Europe/Riga ORA_TZFILE=$ORACLE_HOME/oracore/zoneinfo/timezlrg_14.dat \
bundle exec rspec --profile 30

Possible directions (not a proposal yet)

• Batch the per-table dictionary reads in structure_dump (one query returning all columns / PKs / UKs / indexes keyed by table instead of N queries per table).
• Prefer USER_* views over ALL_* where the tests operate in a single schema — USER_* is typically faster on 11g because it doesn't include the ALL_* cross-schema visibility logic.
• Consider whether test_11g needs to run the full suite on every push, or only the subset that touches SQL/DDL generation paths we care about per-version.

[yahonda]

Fixed by #2521