| How to stop the Cache Effect ? [message #131725] |
Tue, 09 August 2005 03:42  |
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 #131790 is a reply to message #131784] |
Tue, 09 August 2005 08:56  |
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 #131804 is a reply to message #131798] |
Tue, 09 August 2005 09:37 |
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 #131825 is a reply to message #131725] |
Tue, 09 August 2005 11:18 |
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 #132031 is a reply to message #131725] |
Wed, 10 August 2005 08:01 |
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 #136249 is a reply to message #136224] |
Wed, 07 September 2005 00:25 |
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
