| Keep Pool [message #585175] |
Wed, 22 May 2013 13:49  |
 |
orauser001
Messages: 13
Registered: April 2013
Location: us
|
Junior Member |
|
|
Two of the tables in our system are
a) Company (4 million rows, 812 MB size)
b) Company_Alias (1million rows, 178 MB size)
Being master data we expect sizing to stay fairly consistent over time.
Our system (using Oracle 11g) needs to support a Search feature using LIKE searches on Company Name
(in Company Table) and Company Alias Name (in Company_Alias Table).
LIKE forces a Full Table Scan on these tables and the results can take more than a 3 minutes which
is causing a lot of problems. The execution times for the query (as expected) are very dependent on
data being in cache.
So we are considering keeping these two tables in Keep Pool and have some questions in this regard.
a) Since we are not using KEEP Pool currently, if we only assign these two tables to the Keep Pool
and size it sufficiently to hold data from these two tables, it is fair to assume that these two
tables will not be aged out and will always be available in the Keep Pool?
b) Since the two tables are about 1 GB, how big of a KEEP pool should we plan. I read that the KEEP
pool should be atlease twice the table size - is that correct?
c) Since the LIKE work only on the Party Name and Alias Name, can we create indexes on those
columns and put those indexes in the KEEP Pool (so that we dont have to allocate large amount of
space)
for example if we have a query such as
select *
from company join address on ....
join identifiers on ...
where company_id in (select company_id from company where company_name like '%IBM%')
or company_id in (select company_id from company_alias where company_name like '%IBM%')
the two inner queries that have only the company_name and company_alias may be able to use the
index from the keep pool
once the rows are identified then for the small number of selected rows it can fetch the data from
the company tanle and any other tables using the normal method (physical io/default pool). does
this make sense?
d) Other approach we are looking for is to create text indexes on these columns and use the oracle
text based searches. Ifwe were to go that route which of the DR$ tables would be most useful to put
in the keep pool
Thanks in advance
|
|
|
|
|
|
| Re: Keep Pool [message #585181 is a reply to message #585175] |
Wed, 22 May 2013 14:51  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I do not think you are taking the correct approach. Much better would be to create a context index and use that. For example,create index texti on company(name) indextype is ctxsys.context;
select * from company where contains(name,'IBM') > 0;
--
update: typo
[Updated on: Wed, 22 May 2013 16:19] Report message to a moderator |
|
|
|
| Re: Keep Pool [message #585263 is a reply to message #585181] |
Thu, 23 May 2013 04:32 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
If you're FTSing under 11g, you might be ignoring (oracle) caching anyway - 11R2 favours serial direct reads for tables under the "small table threshold" (i.e. most).
Something to keep in mind...although Johns idea is a good one.
[Updated on: Thu, 23 May 2013 04:33] Report message to a moderator |
|
|
|
| Re: Keep Pool [message #585577 is a reply to message #585263] |
Mon, 27 May 2013 14:21 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Keep pool is to eliminate physical reads. Keep pool is an advanced feature and in its case is intended for use in situations where extreme performance is required and this performance might be achieved because of elimination of physical reads by having needed pages (e.g. rows) in memory all the time. If you can show this is your problem then keep pool may be a valid response to your performance needs and you should try it. Sizing should be large enough to ensure that all pages you will visit during your database instance life will fit in memory. If you expect to visit every page of the table then you need to size to hold the entire table. If the table does not fit then you may experience movement of pages in and out of memory along with the corresponding physical reads and keep pool can become nothing more than regular SGA Data Cache and what would be the point of that...
In one sense Keep Pool is a waste. Oracle's caching algorithms are very advanced and they keep improving as you go up database versions. So some people consider keep pool a bad move because it runs counter to letting Oracle cache data based on use pattern. If you use a Keep Pool badly, then these people are right. But... these people are also wrong. They are wrong because the need being addressed should be by definition "special case". Someone for whatever reason has decided that a specific query(s) require fast response times. These queries would be considered "very important" and I do mean VERY IMPORTANT. How you arrive at these queries being so critical is your call but given such a set of queries, taking extra measures for them is appropriate and thus creating a keep pool even though it may reduce the overall efficiency of the buffer cache, can provide the stable fast performance for the critical queries. This is why advanced features exist, to address special needs.
However there are other considerations. I would suggest the following to your for thought.
1) you should first be able to demonstrate the extreme need. There should be a business manager you can point to as the person who has identified the need and can articulate it in a way that other business people can understand. Without this you are creating a future problem for both your business and your IT team. Sounds to me like you have this but you should write it down. Your boss should know the manager with the need and a description of the effect on the business both before and after should be written down too. Information Technology is about how solutions benefit the business and when you are doing something different and special you need to write it down in a little more detail than usual.
Quote:"We need these queries to go fast"
is not an acceptable description of the need.
Quote:"Insurance Agents external to the company are waiting up to three minutes to get a list of companies, using the name search located in theXXX online application and these long search times at this point in our online apps causes them so much frustration that they abandon their search before it completes and leave our website we presume seeking other vendors. Thus this slow search is causing us to loose business. Web monitoring tools (specific tool should be named) tell us that 20% of all inquires are being terminated before the name search completes. Given our rate of successful quotes, this mean a loss of written premium to us of $$$ per day which translates to a loss of market share of %%% and reduced profit for our division of $$$."
This is a good business description and unless you are fighting a fire, is the kind of thing you should have for such a case. Notice the use of monitoring tools by the business to get the required business impact. This is the difference between professional IT and someone looking for a quick fix. I am not suggesting you need this level of detail for everything you do, only that when you start looking at advanced features for solving special situations, you need to take the extra step with your business team stepping along side you.
Consider that if you are successful, this kind of solution goes on your review for year end, and/or your resume should that be required. It is a great talking point and one you should have documented for your own selfish needs as well for your business and IT teams. Your boss and business manager with the need can do the same, promoting your success across the business. But you won't be able to do any of that if you don't write it down first in the way suggested above.
2) there are other solutions to consider. You suggested one already which is the CONTEXT INDEX (goes by different names depending upon your version of Oracle). This sounds like a good one to me as was indicated by others on this post.
3) you must spend some time with the problem space understanding it before you take action. For example, you have noted that your queries are doing a FTS but you have not indicated how man rows ultimately return from a typical query. I am guessing %IBM% does not return thousands of rows? Likely less than 10 out of your millions? This kind of understanding will lead you to better solutions. Given 10 rows, why cache Gigabytes of data if you can cache a much smaller index and then only get the 10 rows once you know what rows they are?
4) sometimes a little solution redesign is in order. In your case I see two searches for the same term because you have two lists (name/alias). Why not combine these lists and then only do one search.
5) none of these ideas are mutually exclusive. You can combine them. Use one searchable list, create a context index on it, then cache the index. It all works.
6) also there is no need to do all idea eithers. You may find that a context index on each of the two search lists gives you the performance you wanted in which case there is no need to redesign or use a keep pool. This is where testing comes in and having a game plan that tells you when it is OK to stop trying to make things go faster.
Good luck. Kevin
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
