Hi reader,

you find it here

HTH

Karl

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,

after a benchmarking test of Components of a CRM System with Oracle 10.2 and Sun Solaris with either AMD Opteron or Sun SPARC IV+ CPU’s i started to think about the definition of Scalability. Her it is:

Scalability is about how strong the increase 
of the Throughput/decrease of Response Time
of an Application or Software Component is
when additional resources of CPU, Memory and I/O are added;

A second one follows :

Scalability is about how strong the decrease
of the Throughput/increase of Response Time
of an Application or Software Component is
when the workload ( Amount of work, Amount of Concurrency ) increases;

Karl Reitschuster

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,

Inserting on a table with a lot of indexes could cause the wait event db file sequential read. For every row to be inserted the index must be scanned and updated when an indexed column was specified in the DML statement. With about 150 rows a second the insert operation was very slow. I enabled extended trace 10046 event and reviewed the generated raw trace file;

The traditionaly way - trace files

In the 10046 event trace file you will find the Cursor definiton section of the insert statement:

=====================

PARSING IN CURSOR #8 len=693 dep=1 uid=50 oct=2 lid=50 tim=226692485 hv=1579689243 ad=’396bb604‘

INSERT INTO COMPANY (…) VALUES (:B18 , NULL, 0, NULL, NULL, SYS_GUID(), :B17 , NULL, NULL, :B16 , NULL, NULL, NULL, :B15 , NULL, NULL, :B14 , NULL, NULL, NULL, :B13 , :B12 , NULL, :B11 , :B10 , :B9 , :B8 , NULL, NULL, :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )

END OF STMT

Hi reader,

Trivadis a Database oriented solution provider invited a number of oracle experts for two days in Zürich to explore the CBO (Cost Based Optimizer). A very interesting event as you would meet there 

• Jonathan Lewis, JL Computer Consultancy (UK)
• Mohamed Zait, Oracle Corporation (USA)
• Wolfgang Breitling, Centrex Consulting (CA)
• Christian Antognini, Trivadis AG (CH)

Really an outstanding event, more details on Trivadis 2-Days-Seminar on Oracle CBO

Greetings

Karl

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

Hi reader,

With Oracle 10G specially with 10.2 you often hear :

! DO NOT USE HINTS ANY MORE! THEY ARE NOT NEEDED ANY MORE ! 

So thinking of the possibility to use a hint for a statement gives you a bad feeling doing something wrong. It’s clear to use hints only if there is no way to bring the optimizer to the desired execution plan or if the plan stability is most important even independent from gathered table/index stats.

Yesterday i navigated with the Db Console thru some SQL-Statements run in the past. Accidentally i found an interesting statement:

As you see this SELECT statement uses the NO_MERGE and ORDERED hint. It was executed via Db Console well known as an Oracle product ;-). And even the RULE hint seems (it’s not) to be out in Oracle 10.2, with these hints you dictate the query optimizer what plan to generate.

The SQL again - better formatted for discussion:

SELECT Task_List.Task_Id
  FROM (SELECT /*+ NO_MERGE(T) ORDERED */
         t.Task_Id
          FROM (SELECT *
                  FROM Dba_Advisor_Tasks
                 ORDER BY Task_Id DESC) t,
               Dba_Advisor_Parameters_Proj P1,
               Dba_Advisor_Parameters_Proj P2
         WHERE t.Advisor_Name = ‘ADDM’
           AND t.Status = ‘COMPLETED’
           AND t.Execution_Start >= (SYSDATE - 1)
           AND t.How_Created = ‘AUTO’
           AND t.Task_Id = P1.Task_Id
           AND P1.Parameter_Name = ‘INSTANCE’
           AND P1.Parameter_Value = Sys_Context(‘USERENV’,
                                                ‘INSTANCE’)
           AND t.Task_Id = P2.Task_Id
           AND P2.Parameter_Name = ‘DB_ID’
           AND P2.Parameter_Value = To_Char(:B1)
         ORDER BY t.Task_Id DESC) Task_List
 WHERE Rownum = 1

The NO_MERGE hint dictates the optimizer not to merge the sub query

SELECT *
  FROM Dba_Advisor_Tasks
 ORDER BY Task_Id DESC

into the rest of the statement, some kind of rewriting the query for performance reasons. This means the sub query is executed as it is written.

The ORDERED hint dictates the optimizer the join order of the tables.  The join orders of the tables then is depending on the order in the FROM table list . So the execution plan accesses T(Dba_Advisor_Tasks), P1(Dba_Advisor_Parameters_Proj), P2(Dba_Advisor_Parameters_Proj);

Let’s take a look at the EXPLAIN PLAN :

SQL> explain plan for
  2  SELECT Task_List.Task_Id
  3    FROM (SELECT /*+ NO_MERGE(T) ORDERED */
  4           t.Task_Id
  5            FROM (SELECT *
  6                    FROM Dba_Advisor_Tasks
  7                   ORDER BY Task_Id DESC) t,
  8                 Dba_Advisor_Parameters_Proj P1,
  9                 Dba_Advisor_Parameters_Proj P2
 10           WHERE t.Advisor_Name = ‘ADDM’
 11             AND t.Status = ‘COMPLETED’
 12             AND t.Execution_Start >= (SYSDATE - 1)
 13             AND t.How_Created = ‘AUTO’
 14             AND t.Task_Id = P1.Task_Id
 15             AND P1.Parameter_Name = ‘INSTANCE’
 16             AND P1.Parameter_Value = Sys_Context(’USERENV’,
 17                                                  ‘INSTANCE’)
 18             AND t.Task_Id = P2.Task_Id
 19             AND P2.Parameter_Name = ‘DB_ID’
 20             AND P2.Parameter_Value = To_Char(:B1)
 21           ORDER BY t.Task_Id DESC) Task_List
 22   WHERE Rownum = 1
 23  ;

EXPLAIN PLAN ausgef³hrt.

SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
————————————————————————————————————
Plan hash value: 2107564592

———————————————————————————————————–
| Id  | Operation                        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————————–
|   0 | SELECT STATEMENT                 |                        |     1 |    13 |    19   (6)| 00:00:01 |
|*  1 |  COUNT STOPKEY                   |                        |       |       |            |       |
|   2 |   VIEW                           |                        |     1 |    13 |    19   (6)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY         |                        |     1 |   102 |    19   (6)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                        |     1 |   102 |    18   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                |                        |     1 |    78 |    17   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
————————————————————————————————————
|   6 |       VIEW                       |                        |     1 |    54 |    16   (0)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL         | WRI$_ADV_TASKS         |     1 |    29 |    16   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS BY INDEX ROWID| WRI$_ADV_PARAMETERS    |     1 |    24 |     1   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN         | WRI$_ADV_PARAMETERS_PK |     1 |       |     1   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS BY INDEX ROWID | WRI$_ADV_PARAMETERS    |     1 |    24 |     1   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN          | WRI$_ADV_PARAMETERS_PK |     1 |       |     1   (0)| 00:00:01 |
———————————————————————————————————–

The Explain Plan confirms the effectiveness of the described hints. In the example you see another interesting point. The plan starts with TABLE ACCESS FULL OPERATION on WRI$_ADV_TASKS. As next operation after a full table scan i would expect a HASH JOIN Operation. But the Optimizer uses a NESTED LOOPS instead. Why?

It’s all about expected CARDINALITY^[1]! Because only 1 Row is expected via TABLE ACCESS FULL Operation a NESTED LOOPS Join Operation is the correct answer.

I removed the Predicate^[2] Information section for better overview. How would be the Explain Plan without Hints?

———————————————————————————————————–
| Id  | Operation                        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
—————R