hi,

everybody tells you to use bind variables (reduced parses) and that’s ok. But in some circumstances you get a lot of problems with it. Using bind variables with the LIKE operator could cause horrible performance even you index that column and you do not use a ‘%’ at the start of the searched string.

Case 1 comparing with = operator :

WHERE Company.Customer = :P02

                  TABLE ACCESS BY LOCAL INDEX ROWID Depth=18 Object owner=ISIS Object name=COMPANY Cost=4 CPU cost=28254 IO cost=4 Cardinality=1 Bytes=289   Options=BY LOCAL INDEX ROWID  Operation=TABLE ACCESS Optimizer=ANALYZED Partition id=18 Partition start=2 Partition stop=2
                   INDEX RANGE SCAN Depth=19 Object owner=ISIS Object name=COMPANY_IX01 Cost=3 CPU cost=21764 IO cost=3 Cardinality=1  Access predicates="COMPANY"."CUSTOMER"=:P02  Options=RANGE SCAN  Operation=INDEX Optimizer=ANALYZED Partition id=19 Partition start=2 Partition stop=2

Yeah!! the optimizer chooses the index - very fast response - in this case.

Case 2 comparing with LIKE operator :

WHERE Company.Customer LIKE :P02

                  TABLE ACCESS FULL Depth=18 Object owner=ISIS Object name=COMPANY Cost=45241 CPU cost=8354827075 IO cost=44959 Cardinality=346919 Bytes=100259591  Filter predicates="COMPANY"."CUSTOMER" LIKE :P02 Options=FULL  Operation=TABLE ACCESS Optimizer=ANALYZED Partition id=27 Partition start=2 Partition stop=2

Oh my god!!! what is Oracle doing here???? Full Tablescan over 6.6m rows. The reason for is that dummy 5% assumption. Oracle assumes to scan 5% rows (look at the expected cardinality it’s about 5% of 6.8m) of the table using LIKE with bind var. Unfortunately this is the limit for a full table scan.

We fixed that with a higher affinity to index blocks. Also there is an internal parameter known to cahnge globally the way oracle calculate the expected cardinality of the LIKE operator. But a better way in the future release of the software will be: If the queried field is full qualified (means full length without ‘%’ or ‘_’) then the comparison operator is used instead of like.

cheers

Karl

Hi reader,

Once an Oracle Block is read from the disk it gets a place in the buffer cache. How long the block remains cached depends on following parameters.

• if it’s an index block - they are stronger bound to the DEFAULT cache then table blocks resulting in a longer period of cache life cycle.
• if it’s a table block - it will be cached but earlier freed from cache then an index block
• if it’s a frequently accessed (LRU mechanism) a block frequently access will remain in the buffer cache
• if the table block is originated by a long full table scan - a block read by a scattered read on a table which does not fit in a scan window of the buffer cache - it will be displaced immediately from the next blocks.

To guarantee a longer life cycle for a table/index block another cache type i think was introduced with Oracle 8i. The KEEP pool. It tends to keep (to pin) the blocks even they are not frequently accessed and enables caching of all blocks of a segment read with scattered read and not only a subset of it. Thus it is guaranteed that access to a segment cached in KEEP pool will not have any physical I/O. Usually i learnt to keep frequently small objects for example lookup tables into the KEEP pool.

We faced a problem on our company table querying specific company types a full table scan could not prevented. If many columns are used in some reports/lists to filter the result you cannot put an index on every column of the table. The company table is about 1.7G large with about 6.5M of rows. On our test database server two concurrent queries on the company table resulted in  > 60sec instead of 25sec. And even 25 sec are too long.

Having a 64 bit system we decided to upgrade memory and to pin the Company table into the KEEP pool.

Following steps to load the company table int o the KEEP pool:

• set SGA_TARGET to 2.8G - Database is in automatic SGA sizing mode - a restart of the database will be needed
  ALTER SYSTEM SET sga_target=2800M SCOPE=SPFILE
  ;
  
• and then set DBA_KEEP_POOL_SIZE to 1.8G - This reduces available memory for other SGA (Shared pool + DEFAULT buffer cache) components to 1G 
  ALTER SYSTEM SET db_keep_cache_size=1800M SCOPE=BOTH
  ;
  
• Altered the tables buffer cache
  ALTER TABLE COMPANY STORAGE (BUFFER_POOL KEEP)
  ;
  
• Pin the Company Table with a FULL table scan into the keep pool. ( You must use a FULL Hint to prevent an index full scan)
  SQL> SELECT /*+FULL (COMPANY)*/ COUNT(*) FROM COMPANY;
    COUNT(*)
  ———-
     6764758
  SQL>

once loaded the table into the KEEP buffer cache the result of the query response time was < 5sec even with concurrent sessions;

Monitor Buffer Pool Usage (the physical reads on the KEEP pool were caused by the inital load (pin) of the COMPANY Table):

SQL> SELECT Vps.NAME,
  2         Vps.Consistent_Gets,
  3         Vps.Physical_Reads
  4    FROM V$BUFFER_POOL_STATISTICS Vps;

NAME                 CONSISTENT_GETS PHYSICAL_READS
——————– ————— ————–
KEEP                        35762385         194722
DEFAULT                    306683092        3568628

SQL>

To go this way successfully you need following resources

• fast CPU; for high LIO-Rate - on our Dual Xeon’s we had a rate of 1.4G/sec
• higher number of CPU >= 4
• Server optimized Mainboard/Memory access
• fast Memory

Conclusion

If your site drives a 64bit System. You could benefit from loading very large objects into the KEEP pool. Sure - this is not an all round optimisation for your weak SQL and your weak physical and logical design of your database/schema
;-)

HTH
Karl Reitschuster

Hi reader,

Gerhard is a colleague in the current project i work. An upload of documents via .NET was very slow (1M in 40 seconds). We found a workaround and reached 1M in 2 seconds. Here the article published in Gerhard’s Blog;

Slow Oracle BLOB upload with C# .NET and how to speed up.

Greetings
Karl