Oracle: a compiled Cypher statement kept in the driver's statement cache holds megabytes of PGA, and a few dozen run the instance out (ORA-04036)
维护者通常 1 天内回复
已经有一个关联 PR 被合并了。
- #221 来自 @jasperblues —— 已合并
评估
- 难度
- 4/5
- 预计耗时
- 3-5 天
- 新手友好度
- 57/100
- Issue 类型
- 缺陷
- 描述清晰度
- 描述清楚
- 活跃度
- 活跃
- 技术栈
- postgresql, python
- 领域
- database, performance
调研方向
Start with drivine/connection/oracle/oracle_connection.py and drivine/sync/connection/oracle/oracle_connection.py, then check how OracleConnection.query and open_cursor prepare and execute Cypher statements. Port the described long-text memory test, measuring session PGA through v$sesstat as system; done means Cypher statements avoid the driver's statement cache and the test stays below 200 MB.
由索引模型根据 Issue 内容生成。
描述
Port of liberation-data/drivine4j#313.
What happens here now. On Oracle, Oracle keeps a cursor's memory for as long as the driver keeps its statement in its statement cache. In drivine4j a compiled Cypher statement held about 4 MB of PGA while cached, 7 MB at 5,000 characters, and 10 to 24 MB when it read a text past 32 KB. A few dozen distinct statements took the instance from 300 MB to over 1.3 GB on one session. With more sessions that runs past Oracle Free's 2 GB PGA_AGGREGATE_LIMIT (ORA-04036). The memory is held on the server, not in the client, so any driver that caches the statement does the same.
OracleConnection.query and open_cursor (drivine/connection/oracle/oracle_connection.py:61 and :88, and drivine/sync/connection/oracle/oracle_connection.py:63 and :90) run a compiled Cypher statement with python-oracledb's default statement cache (stmtcachesize 20 per connection). The pool is set up in oracle_connection_provider.py with max=pool_max. So up to 20 compiled statements per session stay cached, each holding megabytes. This isn't measured here yet, but the cause is on the server side.
The fix in drivine4j (liberation-data/drivine4j#352): a statement compiled from Cypher isn't kept in the driver's cache on Oracle (statement.isPoolable = false), so it is closed when it ends. Drivine's own statements stay cached. The python-oracledb equivalent is cursor.prepare(statement, cache_statement=False) before execute, done only when the statement came from Cypher.
Tests to port.
OracleLongTextMemoryTest: 90 distinct long-text statements (givenWITH $t AS t, storedMATCH (n) WITH n.t AS t, madeWITH $a + $b AS t, each withleft(t, size(t) - i)) on one session. It reads that session'ssession pga memoryfromv$sesstatassystemand asserts it grew by less than 200 MB. In drivine4j it grew by 1,134 MB before the fix and by 37 MB after.- The long-text test now runs stored and made texts at 40,000 characters too, not only given ones.
- 主要语言
- Python
- 星标
- 1
- 派生
- 0
- 平均合并
- 1 小时 21 分钟
- 30 天内合并 PR
- 128
环境准备
这个项目没有提供开发容器、Dockerfile 或贡献指南，环境需要你自己搭建：先看它的 README，通用步骤见我们的新手贡献指南。
从这里开始
- 先读完整个 Issue，再读项目的贡献指南。
- 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
- Fork 仓库，在一个分支上完成修改。
- 提交 Pull Request，并在描述里引用这个 Issue 编号。
liberation-data/drivine-python 的其他 Issue
-
难度 2/5 1-3 小时 新手友好度 78/100
liberation-data/drivine-python#250 ·
维护者通常 1 天内回复
-
bug
难度 1/5 1 小时以内 新手友好度 88/100
liberation-data/drivine-python#209 ·
维护者通常 1 天内回复
-
CI: three Oracle-only tests in test_cypher_compiled.py never run, as -k oracle doesn't select them未关闭
难度 1/5 1 小时以内 新手友好度 88/100
liberation-data/drivine-python#178 ·
维护者通常 1 天内回复
-
bug
难度 4/5 3-5 天 新手友好度 45/100
liberation-data/drivine-python#280 ·
维护者通常 1 天内回复
-
bug
难度 4/5 3-5 天 新手友好度 68/100
liberation-data/drivine-python#279 ·
维护者通常 1 天内回复
查看 liberation-data/drivine-python 的全部 Issue
相似的 Issue
-
难度 2/5 1-3 小时 新手友好度 79/100
aws-samples/sample-per-model-bedrock#639 ·
维护者通常 1 天内回复
-
bug
难度 1/5 1 小时以内 新手友好度 86/100
awslabs/visual-asset-management-system#937 ·
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 62/100
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 79/100
project-codeflare/codeflare-sdk#1195 ·
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 65/100
pandas-dev/pandas-stubs#2051 ·
维护者通常 1 天内回复