Home » RDBMS Server » Performance Tuning » How to stop the Cache Effect ?
How to stop the Cache Effect ? [message #131725] Tue, 09 August 2005 03:42 Go to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
Dudz,

I'm got a query to tune, but it lasts only 2 minutes.
I just have 2 databases which have the good volum.
So in one day I can only test 2 times my tuning.

The only way , i know to stop the cache effect, is to shut down
the databases.
But as it's OLTP databases (7/7), I can't shut down and so , empty the db buffer cache as i whish ...

Any solutions ?
the Hint "NO_CACHE" ? is it a way ?

Best Regards
Gerald


Re: How to stop the Cache Effect ? [message #131765 is a reply to message #131725] Tue, 09 August 2005 06:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
enable tracing for that particualar user?
Re: How to stop the Cache Effect ? [message #131776 is a reply to message #131725] Tue, 09 August 2005 08:08 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
Sorry Masesh, I don't understand ?

You ask me if i can trace my query ?
or is it internal question you ask to the staff ?
Re: How to stop the Cache Effect ? [message #131784 is a reply to message #131776] Tue, 09 August 2005 08:42 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can enable sql tracing (even 10046 level 12)for the particular user (based on sid).
You no need to bounce the system.
Based on this generated trace file, you can all information about the running query (plan, waitevents etc).
with CACHE oracle will Look in the most recently used end of the buffer cache LRU list when performing a full table scan.
with NO_CACHE oracle Look in the least recently used end of the buffer cache LRU list when performing a full table scan.
Re: How to stop the Cache Effect ? [message #131790 is a reply to message #131784] Tue, 09 August 2005 08:56 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
Dear Mahesh,

I'm able to do tkprof files. I use the "SYS=NO" option.

so the result is like this one,

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 14.80 141.72 73255 275598 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 14.82 141.77 73255 275598 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 110 (SQL_OLIVER)


more the plan.
I don't understand "even 10046 level 12" , can you explain me a little more ?
thanks
G

[Updated on: Tue, 09 August 2005 08:58]

Report message to a moderator

Re: How to stop the Cache Effect ? [message #131798 is a reply to message #131790] Tue, 09 August 2005 09:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

http://www.orafaq.com/articles/archives/000022.htm

The above URL should help.
If you are not using any bind variables, you no need to do a level 12. a level 8 would do.
generate the tracefile.
post the trace file (attachment) and the output of tkprof.

and first question is, what are we tuning here? why? why do you want to 'disable cache effect'?

Re: How to stop the Cache Effect ? [message #131804 is a reply to message #131798] Tue, 09 August 2005 09:37 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
I'm starting checking the archive.

I give you the .trc file , will add the .tkp after

- what are we tuning here?
An OLTP query that last more than 3 minutes and so face an WEB timeout.

- Why ?
because of the timeout ?

- Why do you want to 'disable cache effect'?
When i start for the first time the query , it last about 3min40
, when i restart it, it lasts only 2 seconds ...
So when i find some tuning, using specific index ..;i have to wait tomorow to test it , because of the cache effect.

[Updated on: Tue, 09 August 2005 09:37]

Report message to a moderator

Re: How to stop the Cache Effect ? [message #131805 is a reply to message #131725] Tue, 09 August 2005 09:37 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
here is the .tkp

[Updated on: Tue, 09 August 2005 09:38]

Report message to a moderator

Re: How to stop the Cache Effect ? [message #131814 is a reply to message #131805] Tue, 09 August 2005 10:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I am not seeing any wait events!.
did you enable trace with level 8?
Re: How to stop the Cache Effect ? [message #131815 is a reply to message #131725] Tue, 09 August 2005 10:24 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
Well,

I'm still reading the archive ... I will do a level 8 as soon as i understand how to do that Smile

anyway thanks for your help and the time you already have passed on it!

tku
Re: How to stop the Cache Effect ? [message #131817 is a reply to message #131725] Tue, 09 August 2005 10:38 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
I've forgotten to tell that i'm working on Oracle 8.1.7.

It seems that statistics_level doesn't work with it.
(doing a "show parameters" confirm it)
Re: How to stop the Cache Effect ? [message #131825 is a reply to message #131725] Tue, 09 August 2005 11:18 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
here are files with the level 12 option

the query is done , but with the cache effect, so it lasts only
1.61 sec ... (this is the problem)
How can i confirm that the use of my new index will be efficiante enought to last less than 3 minutes.

I must precise that this query is the heart of the full one.
Because it's done twice.

I explain:

on the web site , if the query gives back 5 000 rows, we have a pagination systeme that show only the 20 first one, and then show that we are in the page 1 of 250.

To know how many page we are about to give to the web_user,
we do (quickly) a:

SELECT (count(*) from (the_query),
(the_query)
FROM (...)


So it's done twice a time ...
and it must feet in 3 minutes !
that's the game ...
Re: How to stop the Cache Effect ? [message #131826 is a reply to message #131725] Tue, 09 August 2005 11:19 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
here is the .tkp

[Updated on: Tue, 09 August 2005 11:19]

Report message to a moderator

Re: How to stop the Cache Effect ? [message #131833 is a reply to message #131826] Tue, 09 August 2005 12:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I believe, this has to do with application.
so Before tuning anything,
Please go through this discussion titled

paging through results on the web.
http://asktom.oracle.com/pls/ask/f?p=4950:8:12437389681828441939::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:76812348057

Did you look into scrollable cursors or anything similiar?
I am not really experienced in this aspect.
If you are not getting any solution from the above URL i will move the thread to sql/plsql forums.
thanks

Re: How to stop the Cache Effect ? [message #132031 is a reply to message #131725] Wed, 10 August 2005 08:01 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
Thank you for the help,

I can think about changing the way the Pagination is done, but it's a VB part developped in Spain.

I will try to tune the SQL or use compressed index to reduce the number of blocs in db buffer cache.

Take Care
G
Re: How to stop the Cache Effect ? [message #132287 is a reply to message #131725] Thu, 11 August 2005 13:56 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'm not sure, but I think you are asking how to flush the buffer cache? One way is to look into the alter system command, such as:

alter system flush buffer_cache;

you can also flush the shared pool:

alter system flush shared_pool;

Also, I believe certain operations on tables and tablespaces will purge cached information for their related objects.

file:///E:/Scot/Oracle%2010gR1%20Doc%20Library/B14117_01/B14117_01/server.101/b10759/statements_2013.htm#sthref3330
Re: How to stop the Cache Effect ? [message #132365 is a reply to message #132287] Fri, 12 August 2005 03:47 Go to previous messageGo to next message
deadpoet
Messages: 17
Registered: August 2005
Junior Member
Thanks !

But the Buffer cache Flush exists only from 10g and above, and i'm on 8.1.7.3.
So i've tried the shared pool one; the cache effect still exist.

Your right when you say that we just have to fill the buffer cache with another blocks ... I just have to find a 125 000 blocs query , may be it's the more secure solution !

Take care Gerald

By the way , the solution was found about the tuning of the query (compressed index + (not IN => not Exists)).
Now the query lasts 63 secondes with pagination and counter Smile
Great !!

Thanks for all kind of helps
Take care
G
Re: How to stop the Cache Effect ? [message #136218 is a reply to message #132287] Tue, 06 September 2005 14:45 Go to previous messageGo to next message
nile
Messages: 13
Registered: September 2005
Junior Member
>> Also, I believe certain operations on tables and tablespaces will purge cached information for their related objects.
file:///E:/Scot/Oracle%2010gR1%20Doc%20Library/B14117_01/B14117_01/server.101/b10759/statements_2013.htm#sthref3330

I would reaally appreciate if someone could tell me about thse operations. I know one (taking the table space offline and briging it back up but that is not a very good solution)
Re: How to stop the Cache Effect ? [message #136224 is a reply to message #131725] Tue, 06 September 2005 15:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just curious, but why is offlining it not a good solution? This isn't anything you would do in production anyway.

Also see:

http://asktom.oracle.com/pls/ask/f?p=4950:8:18404911783872799957::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7413988573867
Re: How to stop the Cache Effect ? [message #136249 is a reply to message #136224] Wed, 07 September 2005 00:25 Go to previous messageGo to next message
nile
Messages: 13
Registered: September 2005
Junior Member
Thanks for the response. Even on non-productions systems, offlining will cause inconvenience to currently logged-on users. Moreover, offlining requires more priviledges than a regular user may have in which case running a "checkpoint" would do.

Anyway, the reson why I was curious is that I came across a bunch of web-pages that mention that there are various 'operations' on tables that would force a flush but none of them mentioned specifically what these operations were.

I had also read the article that you mentioned on askTom. I wouldn't say I understood it 100% but got the point. I ran some tests and it seemed that after doing a checkpoint, data is "sync"ed on the disk (as opposed to removing it from the buffer). This seemed inline with Tom's suggestion to not try to remove data from cache.

I would really like to know what operations on Tables would cause a "sync"/flush.

Thanks again!
Re: How to stop the Cache Effect ? [message #136295 is a reply to message #131725] Wed, 07 September 2005 07:55 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I have read references to operations as well, but the offlining one is the only documented one I found yesterday while quickly looking. If I had to guess, I'd say that if you alter a table, like its structure by adding or removing a column, that doing so would cause it to flush from cache. But that is a guess.

Also I'd view flushing and synching as different. Synching meaning dbwr just wrote out dirty blocks to disk datafiles so that data is saved. But in doing so, those blocks (now clean) are still in buffer cache. They don't go anywhere, just a copy is recorded permanately.

Flushing would be literally cleaning out the blocks from cache, causing empty space where on subsequent operations blocks from other objects (or heck, the same object) could take their place.

But Tom raises a good point and should make one question the point in flushing the buffers in the first place. Plus I gotta think that with all the new memory management capabilities, and advisors, all of which came out after Tom's post, that this artificial flushing process has to affect the statistics and algorithms used, making them behave less like the real world and therefore not work as well. Just speculation though.
Re: How to stop the Cache Effect ? [message #136362 is a reply to message #136295] Wed, 07 September 2005 14:19 Go to previous message
nile
Messages: 13
Registered: September 2005
Junior Member
thanks!
Previous Topic: How I can remove the table from cache? Which command I should execute
Next Topic: error while taking snapshot..!!
Goto Forum:
  


Current Time: Fri Mar 29 05:52:48 CDT 2024