| Oracle Tuning [message #583105] |
Thu, 25 April 2013 20:27  |
 |
orauser001
Messages: 13
Registered: April 2013
Location: us
|
Junior Member |
|
|
I have often faced a tuning dillema when you are trying to tune a statement with respect to data getting cached in oracle memory and impacting results, for example say you want to assess advantage of using text index by comparing
a) LIKE SQL
select * FROM c_party
INNER JOIN
c_ind_restr_flag
ON c_ind_restr_flag.ind_restr_flag_cd = c_party.ind_restr_flag_cd
INNER JOIN
c_party_type
ON c_party_type.party_type_cd = c_party.party_type_cd
inner JOIN am_c_party_alias am_c_party_alias
ON (c_party.rowid_object = am_c_party_alias.party_rowid AND
am_c_party_alias.hub_state_ind = 1)
where party_alias like '%PRUDENTIAL%'
vs
b) CONTAINS SQL
select * FROM c_party
INNER JOIN
c_ind_restr_flag
ON c_ind_restr_flag.ind_restr_flag_cd = c_party.ind_restr_flag_cd
INNER JOIN
c_party_type
ON c_party_type.party_type_cd = c_party.party_type_cd
inner JOIN am_c_party_alias am_c_party_alias
ON (c_party.rowid_object = am_c_party_alias.party_rowid AND
am_c_party_alias.hub_state_ind = 1)
where contains( party_alias, 'PRUDENTIAL' ) > 0;
now as you are tuning, and running these SQL's again and again - oracle i am sure is caching all the data in memory and both queries run in almost identical time.
we could try changing the seach parameter - but with a large chunk of the table data probaly in memory - those two also run quite fast.
Is there a way to get rid of that side impact of large chunk of table getting loaded in memory ? We have sometimes gone as far as stopping and starting oracle instance! obviously not a particularly optimal method.
I am sure there is s better way (some hint / some way to clear the memory etc), can you please share how to tackle this issue
Thanks in advance for your help
|
|
|
|
|
|
| Re: Oracle Tuning [message #583128 is a reply to message #583105] |
Fri, 26 April 2013 02:12  |
John Watson
Messages: 8963
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:I am sure there is s better way (some hint / some way to clear the memory etc), can you please share how to tackle this issue ALTER SYSTEM FLUSH BUFFER_CACHE;
|
|
|
|
| Re: Oracle Tuning [message #583130 is a reply to message #583128] |
Fri, 26 April 2013 02:18 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Keep in mind, if this is run more than a few times in production - it'll cache it there. Secondly depending on your blocks you hit, even a first run may be cached. Thirdly (sorry) there's a very reasonable chance your storage has a cache too - flushing the oracle cache wont clear that.
What I'm inclined to do in situations like this, assuming the query runs for a decent length of time (i.e. a few seconds or more) is to examine the auto trace stats because even if the blocks are cached, you can see the number of gets/sorts/etc rise/fall.
You'll never wholly eliminate caching, or if you do it wont be representative of the real world, but you can make a reasonable estimate as to if it'll be an improvement or not irrespective of caching by working out the amount of "work" the database needs to do to facilitate the different access paths.
|
|
|
|
|
|
|
|
|
|
| Re: Oracle Tuning [message #583409 is a reply to message #583366] |
Tue, 30 April 2013 08:58 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Yes thanks Ross.
Indeed, your commentary is very important, given the explosion of EXADATA I see happening in the next few years. FULL TABLE SCANS and HASH JOINS will become the predominant method of working with the data and thus LOGICAL I/O may be falling out of favor as an good indicator of workload.
I may have taken 25 years, but nothing stays the same eh!
Thanks for keeping it honest Ross.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
