Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

Cypher on Oracle: a list literal of 1,001 items read three times through a WITH spends 815 MB of PGA and keeps 217 MB

Aperta
  • Qualcosa non va?
#241 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
45/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
python
Ambito
databases, performance, testing

Direzione di ricerca

Start by reproducing the Oracle memory measurements described here, especially the 1,001-item list carried through WITH and read three times. Compare the generated SQL and PGA use with the single-read cases, then trace the Oracle compiler and connection execution path; the issue says the Oracle json_array and json_object compilation is unchanged. Done means the statement no longer causes excessive PGA use or ORA-04036 under the reported test-suite conditions, while the list literal test remains meaningful.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

bug

Found while porting liberation-data/drivine4j#362 (#192).

What happens. On Oracle, WITH <list of 1,001 items> AS xs RETURN [size(xs), xs[-1], xs[999]] makes a session spend 815 MB of PGA. The list mixes seven kinds of item, a null among them: [0, 's1', null, 3.5, true, [5, 'n'], {k: 6}, 7, ...]. The compiled statement is only 34 KB. After the statement ends, the session still holds 217 MB. Two such sessions, or one beside the rest of the test suite's sessions, pass Oracle Free's 2 GB PGA_AGGREGATE_LIMIT, and the statement fails with ORA-04036.

In two full runs of ORACLE_TESTS=true uv run pytest, the sync copy of #192's test failed on exactly this step with ORA-04036. Its async copy, run earlier in the same suite, passed both times, and so does the test run on its own.

Measured on a fresh gvenzl/oracle-free:23-faststart session, the statement prepared with cache_statement=False as OracleConnection runs it. Values are session pga memory max and, after the statement, session pga memory:

statement items SQL PGA peak PGA after
RETURN <list> 250 8 KB 51 MB 17 MB
WITH <list> AS xs RETURN [size(xs), xs[-1], xs[248]] 250 10 KB 214 MB 60 MB
RETURN <list> 1,001 33 KB 181 MB 50 MB
UNWIND <list> AS x RETURN x 1,001 33 KB 182 MB 50 MB
RETURN [x IN <list> WHERE x IS NOT NULL | x] 1,001 33 KB 182 MB 50 MB
WITH <list> AS xs RETURN [size(xs), xs[-1], xs[999]] 1,001 34 KB 815 MB 217 MB

Reading the carried list three times in the projection costs about 4.5 times what reading it once does. Each read is a CASE JSON_VALUE(w2.c1, '$.type()') ... over the whole array. Even once, a 1,001-item literal costs 181 MB.

Not caused by #192. That change leaves Oracle's SQL for these statements as it was (the Oracle compiler's json_array/json_object are untouched).

Workaround in the test. #192's test_a_list_literal_of_more_than_100_items_... asks Oracle no list of 1,001 items (see the comment below).

Other libraries. drivine (TS) compiles nearly the same Oracle SQL for this statement (42,170 vs 42,196 characters), so it should behave the same. Its port of the test passed in its full suite, where the Oracle sessions happen to leave enough headroom. drivine4j isn't measured.

Lingua principale
Python
Stelle
1
Fork
0
Merge medio
1h 29m
PR unite (30g)
136

Preparare l'ambiente

Questo progetto non fornisce container di sviluppo, Dockerfile né guida per i contributori, quindi l'ambiente è a tuo carico: parti dal suo README e consulta la nostra guida al primo contributo per i passaggi generali.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di liberation-data/drivine-python

Tutte le issue di liberation-data/drivine-python

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.