Thanks for the question, Gaurav.
Asked: January 06, 2020 - 1:56 pm UTC
Last updated: January 07, 2020 - 4:10 am UTC
Version: 12.1.0.2.0
Viewed 1000+ times
You Asked
Dear Team,
We are running a SE version on linux. We have SGA of 60 GB size. When the database starts operation after a shutdown it starts with a shared pool size of around 10 GB. But with passing of time it keeps growing and after around 1 month it grows more than 30 GB of size and its eating up simply buffer cache space which keeps reducing by that amount of space. We have a automatic memory management configuration. Is it a normal behavior? Is anything we can do to stop this apart from going for a manual configuration.
My other query is related to distributed query performance. I am using a global temporary table to generate a report. This table and other transaction tables all residing at remote database and I am inserting rows into local database. Before the main query runs I do populate the global table which contains more than 200000 rows. But the plan for the query shows the GTT is having only 1 row that why its going for a nested loop join and resulting into slow performance. The same query when I run at the remote database connecting at that end its showing correct cardinality for the GTT and performing a hash join. Is there any way whereby I can set statistics for this remote GTT. Also to mention that this table is being used by many other procedures, so we can not allow the statistics to be shared by all procedures.
Thanks and a very happy new year for the entire team.
and Connor said...
Time to call Oracle Support. You might have run into this:
"KGLH0 Growth Leading to ORA04031"
which means the shared pool leaks and hence grows without bound, eventually leading to ora-4031.
There are patches available.
For your GTT, there are some options you could consider
1) using the driving_site hint to perform the join remotely if that's a possiblity
2) dynamic_sampling hint
3) manually tracking how many rows you insert into the table, then explicitly using hints to nominate the appropriate plan based on the size of the GTT
4) ensuring you are using private GTT statistics
We need to see some test cases before recommending a specific course of action
Is this answer out of date? If it is, please let us know via a Comment