Re: [转载] JDBC用完了oracle的large pool (memor - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

Re: [转载] JDBC用完了oracle的large pool (memor

Re: [转载] JDBC用完了oracle的large pool (memor# Database - 数据库

n*a2004-01-05 08:01

1 楼

Don't start messing up with your large pool yet.
Check your codes first. Are you using variable
binding, i.e., the setXXX methods in PreparedStatement?
Are you closing all ResultSet, PreparedStatement, Connection
objects properly?
If all the above seem appropriate, and only if you are using
shared server configuration on Oracle, then start looking at
large pool.

a*n2004-01-05 08:01

2 楼

Thank you for your reply. I do think it's my coding problem:(

In the code, I use only simple ResultSet to query sth. with some minor update.
and throughout the application, the db is always connected, Statement is
always opened, but the result set is closed every time a query is finished.
I didn't use PreparedStatement, nor setXXX, but I did use some getXXX methods.
and the db and statement objs are not closed until the end of application.
Is it the problem?

【在 n********a 的大作中提到】

: Don't start messing up with your large pool yet.
: Check your codes first. Are you using variable
: binding, i.e., the setXXX methods in PreparedStatement?
: Are you closing all ResultSet, PreparedStatement, Connection
: objects properly?
: If all the above seem appropriate, and only if you are using
: shared server configuration on Oracle, then start looking at
: large pool.

n*a2004-01-05 08:01

3 楼

update.
methods.
Depends.
It usually good to keep the Connection object open until you are all
done with db. So that doesn't seem a problem for you.
Using Statement than PreparedStatement definitely smells bad. Although
Oracle will try to cache your SQL even with Statement object, but
PreparedStatement requires Oracle to cache SQL.
The most likely problem for you is variable binding.
Do you code something like this:
conn.prepareStatement("select x from foo where y=1");
conn.prepareStatement("sel

a*n2004-01-05 08:01

4 楼

我
conn.createStatement();
然后，中间一直用这个statement执行一系列不同的SQL。
最后才close这个statement。
难道它中间一直用的是cache? 不是执行一条SQL就立即解决，释放cache?

【在 n********a 的大作中提到】

: update.
: methods.
: Depends.
: It usually good to keep the Connection object open until you are all
: done with db. So that doesn't seem a problem for you.
: Using Statement than PreparedStatement definitely smells bad. Although
: Oracle will try to cache your SQL even with Statement object, but
: PreparedStatement requires Oracle to cache SQL.
: The most likely problem for you is variable binding.
: Do you code something like this:

n*a2004-01-05 08:01

5 楼

Are your SQL statements vastly different or just
different in the variables to bind as I have
previously illustrated?
If your SQLs are very different such that you
cannot use variable binding, then that just
says you don't have enough shared pool to cache
all the very different SQLs. You want to size
up your shared pool, NOT large pool.
But again, try very very very hard to do variable
binding.
I don't know about other RDBMS, but for Oracle, it always caches
SQL.

【在 a***n 的大作中提到】

: 我
: conn.createStatement();
: 然后，中间一直用这个statement执行一系列不同的SQL。
: 最后才close这个statement。
: 难道它中间一直用的是cache? 不是执行一条SQL就立即解决，释放cache?

a*n2004-01-05 08:01

6 楼

I didn't know how to use variable binding.
but my sql statement is quite simple:
sql = "select col from table where col1='" + Javastring + "'";
statement.executeQuery(sql);
sql = "update table set col1='" + JavaString + "' where col2='" + s2 + "'";
statement.executeUpdate(sql);
这样做了若干次后(每次都关闭resultset,但statement一直不关),就耗干内存了...
我试了下,如果每次statement运行完就关闭,下次用的时候再create新statement就好了.
我想是因为我statement一直没关的原因吧?

【在 n********a 的大作中提到】

:
: Are your SQL statements vastly different or just
: different in the variables to bind as I have
: previously illustrated?
: If your SQLs are very different such that you
: cannot use variable binding, then that just
: says you don't have enough shared pool to cache
: all the very different SQLs. You want to size
: up your shared pool, NOT large pool.
: But again, try very very very hard to do variable

n*a2004-01-05 08:01

7 楼

PreparedStatement ps = conn.prepareStatement("select col from table where
col1=?");
ps.setString(1, "foo");
ResultSet rs = ps.executeQuery(sql);
// Now iterate through the ResultSet.
// After you finished iteration, close ResultSet object.
rs.close();
// Don't close ps yet if you have another variable to bind
// You can reuse the PreparedStatement.
ps.setString(1, "bar");
rs = ps.executeQuery(sql);
PreparedStatement ps = conn.prepareStatement("update table set col1=? where
col2=?");
ps.setStrin

【在 a***n 的大作中提到】

: I didn't know how to use variable binding.
: but my sql statement is quite simple:
: sql = "select col from table where col1='" + Javastring + "'";
: statement.executeQuery(sql);
: sql = "update table set col1='" + JavaString + "' where col2='" + s2 + "'";
: statement.executeUpdate(sql);
: 这样做了若干次后(每次都关闭resultset,但statement一直不关),就耗干内存了...
: 我试了下,如果每次statement运行完就关闭,下次用的时候再create新statement就好了.
: 我想是因为我statement一直没关的原因吧?

a*n2004-01-05 08:01

8 楼

Thanks so much!
This example works perfect towards my purpose:)
Will the sql memory cache consumption build/grow as the number of queries
increases in this case?
E.G. between "conn.prepareStatement" and "ps.close()", can you have
as many such variable-binded sql queries as you wish? or the sql will
run out of memory after a certain number of queries when the memory
comsumption builds up ?
Actually. my application is a server.
it will open a session to serve each client, and accept sql queries a

【在 n********a 的大作中提到】

:
: PreparedStatement ps = conn.prepareStatement("select col from table where
: col1=?");
: ps.setString(1, "foo");
: ResultSet rs = ps.executeQuery(sql);
: // Now iterate through the ResultSet.
: // After you finished iteration, close ResultSet object.
: rs.close();
: // Don't close ps yet if you have another variable to bind
: // You can reuse the PreparedStatement.

n*a2004-01-05 08:01

9 楼

Shared pool caches distinct SQLs, as you would describe as "pattern query"
below. You can bind as many different values as you want, and if you close
ResultSet properly, you won't run out of memory. Even though you bind
millions of different values, there is only one SQL cached in the shared pool.
whatever)
Multi-user environment is a perfect example. Again, as long as you use
"pattern query", there is only one SQL cached in the shared pool, even
if millions of users are binding with millions o

【在 a***n 的大作中提到】

:
: Thanks so much!
: This example works perfect towards my purpose:)
: Will the sql memory cache consumption build/grow as the number of queries
: increases in this case?
: E.G. between "conn.prepareStatement" and "ps.close()", can you have
: as many such variable-binded sql queries as you wish? or the sql will
: run out of memory after a certain number of queries when the memory
: comsumption builds up ?
: Actually. my application is a server.

a*n2004-01-05 08:01

10 楼

Thanks!
This explanation helps a lot! Now I have a better picture of what's going
on in this kind of application.
I think, as a DBA newbie, I need to read more oracle DBA books to grasp the
ideas ...
To tell the truth, I didn't even know:
What is cached? Query Result or Query Statement or both?
Is this shared pool caching only for JDBC or for any client including
sqlplus online transactions?
If it's caching, why can't it dump/replace the cache automatically

【在 n********a 的大作中提到】

:
: Shared pool caches distinct SQLs, as you would describe as "pattern query"
: below. You can bind as many different values as you want, and if you close
: ResultSet properly, you won't run out of memory. Even though you bind
: millions of different values, there is only one SQL cached in the shared pool.
: whatever)
: Multi-user environment is a perfect example. Again, as long as you use
: "pattern query", there is only one SQL cached in the shared pool, even
: if millions of users are binding with millions o

n*a2004-01-05 08:01

11 楼

Data maybe cached, but not query result.
"pattern query" is cached, but not actual statement, not even the
one with "?".
including
Shared pool caches for any clients that access Oracle server.
Be honest with you, I forgot exactly how that happens on top of my head.
My guess, without referring to the documentations, is since other Statement
objects(may be owned by other users) are still open, thus Oracle has to keep
tons of similar SQLs in the cache. The key may still lie in "pattern query".
I a

【在 a***n 的大作中提到】

: Thanks!
: This explanation helps a lot! Now I have a better picture of what's going
: on in this kind of application.
: I think, as a DBA newbie, I need to read more oracle DBA books to grasp the
: ideas ...
: To tell the truth, I didn't even know:
: What is cached? Query Result or Query Statement or both?
: Is this shared pool caching only for JDBC or for any client including
: sqlplus online transactions?
: If it's caching, why can't it dump/replace the cache automatically

a*n2004-01-05 08:01

12 楼

顺便问句, 既然如此, 那我sqlplus里多运行得几条不同的语句, 它oracle不得死翘翘啦?
那交互式DB终端还能用嘛?
I'm thinking that pool caching is only for programming interface?
not for internal client (e.g. the built-in database query terminal: sqlplus)?

【在 n********a 的大作中提到】

:
: Data maybe cached, but not query result.
: "pattern query" is cached, but not actual statement, not even the
: one with "?".
: including
: Shared pool caches for any clients that access Oracle server.
: Be honest with you, I forgot exactly how that happens on top of my head.
: My guess, without referring to the documentations, is since other Statement
: objects(may be owned by other users) are still open, thus Oracle has to keep
: tons of similar SQLs in the cache. The key may still lie in "pattern query".

n*a2004-01-05 08:01

13 楼

啦?
sqlplus)?
sqlplus is just another client, nothing different than JDBC, ODBC, Pro C, and
the list goes on and on.
Your worry that Oracle cannot servive sqlplus stuffed with tons of
non-reusable SQL is warranted. There are several points to consider here:
1. sqlplus as you said, is an interactive client. A human being will type in
SQLs. Now, how many SQLs can you type in an hour? How long will it take you
to type hundreds of SQL statements? The point is that you are unlikely to
be able to gener

a*n2004-01-05 08:01

14 楼

噢? 跟时间有关? 难道不是跟绝对query数量有关吗?
难道这个cache是1小时(or whatever)一结算?:)
//As long as you agree that sqlplus suffers the same problem, I'm satisfied:)

【在 n********a 的大作中提到】

: 啦?
: sqlplus)?
: sqlplus is just another client, nothing different than JDBC, ODBC, Pro C, and
: the list goes on and on.
: Your worry that Oracle cannot servive sqlplus stuffed with tons of
: non-reusable SQL is warranted. There are several points to consider here:
: 1. sqlplus as you said, is an interactive client. A human being will type in
: SQLs. Now, how many SQLs can you type in an hour? How long will it take you
: to type hundreds of SQL statements? The point is that you are unlikely to
: be able to gener