Asdf Asdf - 9 days ago 5
Java Question

Oracle JDBC flush shared pool is not working

I have the following peace of java code that compares the performance of these two SQL queries.
the

getNamedStatistics()
prints some columns from the v$session table, like consistent gets, execute count etc.

Although i cant flush oracle's memory so I can compare the performance of these two queries.

clearOracleMemory();
start = System.nanoTime();
System.out.println("Named statistics --> " + getNamedStatistic(statistics, conn));
query1UsingStatement(conn);
System.out.println("Named statistics --> " + getNamedStatistic(statistics, conn));
end = System.nanoTime();
System.out.println("Took: " + ((end - start) / 1000000) + "ms");

clearOracleMemory();
start = System.nanoTime();
System.out.println("Named statistics --> " + getNamedStatistic(statistics, conn));
query2UsingStatement(conn);
System.out.println("Named statistics --> " + getNamedStatistic(statistics, conn));
end = System.nanoTime();
System.out.println("Took: " + ((end - start) / 1000000) + "ms");


ClearOracleMemory looks like that:

private static void clearOracleMemory() {
try {
PreparedStatement stmt = conn.prepareStatement("alter system flush shared_pool");
ResultSet rs = stmt.executeQuery();
System.out.println("shared pool flushed");
}
catch (SQLException e) {
System.out.println("error clearing shared pool");
System.out.println(e.getMessage());
}
}


Example output:

shared pool flushed
Named statistics --> {CPU used by this session=45, consistent gets=5613, parse count (total)=194, parse time elapsed=14, parse time cpu=14, execute count=1544}

Named statistics --> {CPU used by this session=326, consistent gets=936322, parse count (total)=724, parse time elapsed=58, parse time cpu=60, execute count=3651}
Took: 3676ms

shared pool flushed
Named statistics --> {CPU used by this session=344, consistent gets=942088, parse count (total)=851, parse time elapsed=69, parse time cpu=73, execute count=5319}

Named statistics --> {CPU used by this session=2374, consistent gets=1019846, parse count (total)=20975, parse time elapsed=1728, parse time cpu=1732, execute count=26948}
Took: 28038ms

Answer

What is it that makes you think that the shared pool doesn't get flushed?

I have a suspicion that you confuse the shared pool and the session statistics? Session statistics tally the consumption of different ressources for your session. Flushing the shared pool affects how many ressources your session consumes (since it will for instance require statements to be parsed), but this obviously would not reset the ressource consumption counters.

I recommend that you take a fresh look at the readings assigned, where this is spelled out in some detail.

Best regards