Mohamed Houri’s Oracle Notes

February 25, 2012

Tuning an insert/select via dblink

Filed under: Trouble shooting — hourim @ 11:40 am

Recently an interesting question came up on an Oracle French Forum about a performance problem during an insert/select operation via a dblink. The poster was suffering from the very long time (several hours without any response) the insert was taking. This is why he came to that forum and posted a question which looks like “what do you think if I commit every 1000 rows? Will my insert speed up”?  That’s not the right way to follow in the process of troubleshooting an Oracle performance problem. I always remember Cary Millsap mantra “Why guess when you can know”. Fortunately Oracle gives us a lot fo tools and trace events to instrument what going on inside the Oracle database. Here below are the steps of discussion we’ve had (the original poster, others and I) in that forum.

I first have asked the poster to trace his insert select using events 10046


alter session set events '10046 trace name context forever, level 12';

INSERT /*+ append */ INTO my_table
(
champs_1,
champs_2,
champs_3,
champs_4,
champs_5,
champs_6,
champs_7
)
SELECT * FROM la_table_cible@mon_dblink ;

commit ;

alter session set events '10046 trace name context off';

I am not going to stress that I have asked the poster to ensure that his direct path load will not be silently ignored by Oracle in case the table “my_table” has trigger or foreign key integrity constraints implemented on it. I have also asked if there will be subsequent deletes (after the insert) on this table making future direct path load not reusing any freed space made by that delete.

The generated trace file profiled via orasrp profiler shows that 98% of the insert/select response time is spent waiting in the event SQL*Net message from dblink.

Up to this step, we persuaded the poster that his initial guess of committing every 1000 records was not the ultimate solution; but instead he should focus on reducing the volume of data transferred from the distant data base to the local one. The poster sends us the explain plan and Statistics of the select part when this one is executed in the distant data base

Statistics
----------------------------------------------
212        recursive calls
3          db block gets
678993     consistent gets
483744     physical reads
592        redo size
124692665  bytes sent via SQL*Net TO client
1391305    bytes received via SQL*Net FROM client
126437     SQL*Net roundtrips TO/FROM client
17         sorts (memory)
0          sorts (disk)
1896535    rows processed

He is selecting 1,896,535 records via 126,437 SQL*Net roundtrips meaning that he is using the default arraysize which is 15 (1,896,535/126,437). For this, he did 0.358 (678,993/1,896,535) logical read for each selected row which seems to be quite acceptable. But, he is sending a very large amount of data 124MB over a data base link and making a hundred of thousands SQL*Net roundtrips. Note, that he is doing also a fairly large amount of physical read (483,744) when compared to the total number of logical reads (678,993).

I’ve then asked the poster to stop selecting all the rows from the distant data base and to start selecting only necessary rows. I asked also to post the explain plan of this query in attempt to help him tuning the select part of his problematic insert. When he said that he can’t intervene into the distant data base, I have then asked him to manage his insert with a PL/SQL stored procedure as shown below:


CREATE type distant_typ AS object (champs_1 number,champs_2 number,champs_3 number, ... champs_7 date);
2  /

CREATE type distant_typ_tab AS TABLE of distant_typ;
2  /

DECLARE
CURSOR get_data_from_db IS
SELECT
     champs_1,
     champs_2,
     champs_3,
     champs_4,
     champs_5,
     champs_6,
     champs_7
FROM la_table_cible@mon_dblink
;

BEGIN

OPEN get_data_from_db;
LOOP
FETCH get_data_from_db BULK collect INTO distant_typ_tab LIMIT 100;
FOR i in 1..distant_typ_tab.COUNT
LOOP
INSERT INTO INSERT /*+ append */ INTO ma_table
(champs_1,
 champs_2,
 champs_3,
 champs_4,
 champs_5,
 champs_6,
 champs_7
)
SELECT
 champs_1,
 champs_2,
 champs_3,
 champs_4,
 champs_5,
 champs_6,
 champs_7
FROM table (distant_typ_tab);
EXIXT when get_data_from_db%notfound;
END LOOP;
END LOOP;
COMMIT;
END;

And magically the poster comes out and said “fantastic my insert/select now is completing in a very acceptable time”.

February 11, 2012

Clustering Factor explored in Moliere Language

Filed under: French-Translation — hourim @ 3:15 pm

It’s has been a great pleasure for me to translate, from English to French, Chapter 5 ( Le-clustering-factor ) of Cost-Based Oracle Fundamentals Book written by Jonathan Lewis. Although I do prefer the original version to the translated one, I was surprisingly happy to see that many French oracle colleagues have been  interested by this translation. May be a sign for a new carrier 🙂

February 9, 2012

/*+ materialized */ hint and ORA-04091 error

Filed under: Uncategorized — hourim @ 12:12 pm

It is funny to know that the /*+ materialized */ hint is, behind the scene, materialized by Oracle with a creation of a global temporary table holding the data generated by the corresponding “with subquery”. What is also funny for me is, that when trouble-shooting oracle issues, I am always cross checking, all what I have read in the past that could have any relation to the problem I am confronted to at the present time. Dom Brook post is what I have recently read and is what “my cross checking” work pointed out to be eventually related to the situation explained below.

So, recently a question came up on an Oracle French forum about an insert/select statement that was working well in 10g while it started failing in error following an upgrade to 11g. There was an excellent discussion in that French forum with valuable remarks from different posters. But my curiosity was catalyzed by the fact that no one was able to clearly show which release is correct? 10g or 11g? Before coming back to the /*+ materialized */ hint which motivates this blog let me reproduce the insert/select test case here below:

10gR2 > create table t_read_consistency (id number, vc varchar2(15));
Table created.
10gR2 > insert into
2        t_read_consistency
3      select
4        rownum id,
5        rpad('a',15,'a')
6      from
7        dual
8      connect by
9        level <= 1000;
1000 rows created.

10gR2 > create type t_read_cs as object (id number,vc varchar2(15));
/
Type created.
mhouri.world > create type t_read_cs_tab  as table of t_read_cs;
/
Type created.
10gR2 > create or replace function f_read_consistency_tab
  2  return t_read_cs_tab
  3    as
  4      lc_t_read_cs_tab t_read_cs_tab := t_read_cs_tab();
  5      j  binary_integer := 0;
  6    begin
  7        for x in (select
  8                      id,
  9                      vc
 10                   from  t_read_consistency trs
 11                  where trs.id <= 10
 12       ) loop
 13 
 14          j := j +1;
 15          lc_t_read_cs_tab.extend;
 16          lc_t_read_cs_tab(j) := t_read_cs(x.id, x.vc);
 17     end loop;
 18     RETURN lc_t_read_cs_tab;
 19   end f_read_consistency_tab;
 20  /

 Function created.

This is the model. Lets now start the insert/select operations both in 10g and 11g

10gR2 > select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0    Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

10gR2 > insert into t_read_consistency
2         (id,vc)
3      select id,vc
4   from table(f_read_consistency_tab)
5  ;
10 rows created.

11gR2 > select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

11gR2 > insert into t_read_consistency
2            (id,vc)
3          select id,vc
4      from table(f_read_consistency_tab)
5     ;
from table(f_read_consistency_tab)
*
ERROR at line 4:
ORA-04091: table MOHAMED.T_READ_CONSISTENCY is mutating, trigger/function may
not see it
ORA-06512: at "MOHAMED.F_READ_CONSISTENCY_TAB", line 7

Spot the difference. What is working well in 10g is throwing an error in 11g!!! Starting from this point I was religiously trying to persuade myself that the 11g release is where the correct situation is in contrast to what were claiming several posters in the French forum. From otn, I got the link to the related documentation:

which clearly states this:

  • When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement. (10g)
  • When invoked from an INSERT, UPDATE, or DELETE statement, the subprogram cannot query or modify any database tables modified by that statement (11g)

The 11g release is, at least, in accordance with its corresponding documentation.

Then the /*+ materialized */ hint entered the scene. From ask tom, I knew that the following slightly modified insert/select is not throwing any error in 11g:


11gR2> insert into t_read_consistency(id,vc)
2          with data as (select /*+ materialize */ id, vc from table(f_read_consistency_tab))
3           select * from data
4          ;

10 rows created.

Consequently, if the above insert is working well this means that the insert is

  1. Either not selecting from the pipelined function and hence from the inserted table
  2. Or it is selecting from this pipelined function but before the start of the insert (exactly if I have used a PL/SQL block in which I would have first declared a local t_read_cs_tab type in which I would have then sent the content of the pipelined function and finally  would have used a select from this local type in my insert/select operation)
11gR2> explain plan for
2  insert into t_read_consistency(id,vc)
3          with data as (select /*+ materialize */ id, vc from table(f_read_consistency_tab))
4           select * from data
5          ;

Explained.

11gR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1931462686
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                    |                             |  8168 |   175K|    27   (0)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION          |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                    | T_READ_CONSISTENCY          |       |       |            |          |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| F_READ_CONSISTENCY_TAB      |       |       |            |          |
|   4 |   VIEW                              |                             |  8168 |   175K|     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                | SYS_TEMP_0FD9D6622_C1854848 |  8168 | 16336 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

 

According to the above explain plan, the first executed operation is operation 3(read from the pipelined function) which feeds back its parent operation 2 followed by a read from that famous temporary table (SYS_TEMP_0FD9D6622_C1854848) created on the fly in response to the /*+ materialized */ hint. The insert is so that, before the first row is inserted, the function is read in its entirety and its returned rows are stored into the temporary table which is used by the insert statement. This is why the insert is not failing when the /*+ materialize */ hint is used.

Well, here below are the chronological steps

  1. I read Dom Brooks blog about the /*+ materialize */ hint where I learn that there is a creation of a temporary table behind the scene
  2. I was confronted to an issue about insert/select that is working well in 10g and not in 11g
  3. I knew that the insert/select will work in 11g if the hint /*+ materialized */ is used
  4. I cross check what I read recently and linked that to Dom Brooks article
  5. Wrote this blog article to put these chronological steps in action

February 6, 2012

How to solve ORA-08102: index key not found

Filed under: Index — hourim @ 2:28 pm

In one of our test databases we encountered the following error during an overnight PL/SQL purge job:

ORA-08102: index key not found, obj# 124885, file 48, block 55492 (2) 01-FEB-12

The purpose of the job is to delete data introduced since more than 6 days respecting for that several hierarchical parent-child relationships.  It has been working without any relatives problems since more than 2 years until it started crashing out with the above ORA-08102 error. I aim via this blog article to show how I have resolved this problem.

The first thing I did is of course to identify the corrupted object via the following select:

mhouri.world> ed
    Wrote file afiedt.buf
  1  select substr(object_name,1,30), object_type
  2  from user_objects
  3* where object_id = 154711
;

 SUBSTR(OBJECT_NAME,1,30)       OBJECT_TYPE
------------------------------ ----------------
XXX_ZZZ_UK                        INDEX

The naming standard (_UK) is as such that I immediately identified this index as an automatic index created to enforce a unique constraint. I thought first that I need to rebuild this unique index:


mhouri.world> alter index XXX_ZZZ_UK rebuild;

Index altered.

Unfortunately, when I launched again the purge batch the same error has been raised again. So my second tentative was to drop and recreate the unique constraint and implicitly the unique index.


mhouri.world> alter table t1 drop constraint XXX_ZZZ_UK;

Table altered.

mhouri.world > alter table t1 add constraint XXX_ZZZ_UK unique (id1, id2, dat1);

Table altered.

Of course that you have first to get the DDL definition of the constraint before dropping it so that you will be able to re-created it again.

When the purge process has been re-started following this last intervention, it, unfortunately, raised again the same ORA-08102 on the same object. Finally I have decided to work on the underneath table instead of its unique constraint and unique index.

mhouri.world>> alter table t1 move;

 Command that has invalidated all attached indexes and which obliged me to re-build them:


mhouri.world > select 'alter index ' || index_name || ' rebuild;'
from user_indexes
where status != 'VALID';

Once all indexes rebuilt, I launched again the purge job which surprisingly completed successfully.

Frankly speaking I don’t know if I would have been able to work around this error if I have tried instead of the move command a rebuild or shrink command. But what I have learnt from this experience is that when an index is corrupted it could be repaired by moving table data instead of dropping and recreating the corrupted index as initially indicated by the ORA-08102 error. I have also to admit that I don’t know yet the side effect of the move command and will certainly write something about that if eventually I will come to learn something about that.
.

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)