Home » RDBMS Server » Performance Tuning » whats syntax to keep table in memory? (AIX 10G )
whats syntax to keep table in memory? [message #362816] Thu, 04 December 2008 03:36 Go to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
hey

and whats the syntax to release it?

thanks
Re: whats syntax to keep table in memory? [message #362823 is a reply to message #362816] Thu, 04 December 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None, you can't.

Regards
Michel
Re: whats syntax to keep table in memory? [message #362828 is a reply to message #362823] Thu, 04 December 2008 03:56 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
why not?
Re: whats syntax to keep table in memory? [message #362829 is a reply to message #362816] Thu, 04 December 2008 03:59 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
i mean in SGA
Re: whats syntax to keep table in memory? [message #362842 is a reply to message #362829] Thu, 04 December 2008 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because Oracle is smarter than that.

Oracle will cache blocks of data that it reads from tables in a list. Blocks are removed from the least recently used end of this list as new blocks are added.

So, if you have a process that scans through all the blocks in a table, and reads data rom each block multiple times, then the first time a block is accessed, it will be read from disk, but in subsequent reads, it will be found in the memory cache.

The instinctive response to this is 'Ah - my database is quicker than that - it's already got the table in memory and doesn't have to read it in'
There are 3 responses to this:
1) You are ignoring the time it took you to read the table into memory in the first place.
2) You are ignoring the effects on the whole system of having your cached tables occupying memory all the time, even when they're not being used
3) It is possible to define a seperate buffer pool so that you can avoid having the blocks from your reference tables flushed out of the cache if a large batch process runs.
Re: whats syntax to keep table in memory? [message #363007 is a reply to message #362816] Thu, 04 December 2008 15:16 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
You can also look up the CACHE hint which may alleviate your problem somewhat.

However, if it is a small table, you can keep them in "memory" for the duration of any session by loading them into an array, and building a package to supply the information to conosuming packages.

[Updated on: Thu, 04 December 2008 15:18]

Report message to a moderator

Re: whats syntax to keep table in memory? [message #363045 is a reply to message #362842] Thu, 04 December 2008 23:16 Go to previous messageGo to next message
gaurav.mcabanti
Messages: 5
Registered: May 2007
Junior Member
Hi to all


i am inserting data in a table...it is taking 10 minutes for 10 rows...this table has a after insert trigger for each row.....
how i can decrease the insertion time...

what is the use of /*+ append */ .....???
Re: whats syntax to keep table in memory? [message #363047 is a reply to message #363045] Thu, 04 December 2008 23:18 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't hijack a topic with a question that has nothing to do with it.
Create a new topic.

Regards
Michel

[Updated on: Thu, 04 December 2008 23:18]

Report message to a moderator

Previous Topic: LOCKWAIT
Next Topic: Reg Reorg activity
Goto Forum:
  


Current Time: Sat Dec 03 09:47:13 CST 2016

Total time taken to generate the page: 0.04500 seconds