This discussion is archived
10 Replies Latest reply: Dec 10, 2012 12:45 AM by user11689469 RSS

Oracle 11G - Update is very slow on View

user11689469 Newbie
Currently Being Moderated
I have big trouble with some Update query on Oracle 11G.
I have a set of tables (5) of identical structures and a view that consists in an UNION ALL of the 5 tables.
None of this table contains more than 20 000 rows.
Let's call the view V_INTE_NE. Each of the basic table has a PRIMARY KEY defined on 3 NUMBERS(10,0) -> INTE_REF / NE_REF / INSTANCE.

Now, I get 6 rows in another table and I want to update my view from the data of this small table (let's call it SMALL). This table has the 3 columns INTE_REF / NE_REF / INSTANCE.

When I try to join the two tables :
SELECT * FROM T_INTE_NE T2
WHERE EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )
I get the 6 lines in 0.037 seconds

When I try to update the view (I have an INSTEAD OF trigger that does nothing (just return for testing even without modifying anything), I execute the following query :
UPDATE T_INTE_NE T2
SET INTE_STATE = -11 WHERE
EXISTS ( SELECT 1 FROM SMALL T1 WHERE T2.INTE_REF = T1.INTEREF AND T2.NE_REF = T1.NEREF AND T2.INTE_INST = T1.INSTANCE )
The 6 rows are updated (at least TRIGGER is called) in 20 seconds.
However, in the execution plan, I can't see where Oracle takes time to achieve the query :
Plan hash value: 907176690

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 6 | 36870 | 153 (1)| 00:00:02 |
| 1 | UPDATE | T_INTE_NE | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 6 | 36870 | 153 (1)| 00:00:02 |
| 3 | TABLE ACCESS FULL | SMALL | 6 | 234 | 9 (0)| 00:00:01 |
| 4 | VIEW | T_INTE_NE | 6 | 36636 | 143 (0)| 00:00:02 |
| 5 | VIEW | X_V_T_INTE_NE | 6 | 18636 | 143 (0)| 00:00:02 |
| 6 | UNION-ALL | | | | | |
| 7 | TABLE ACCESS FULL| SECNODE1_T_INTE_NE | 1 | 3106 | 60 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| SECNODE2_T_INTE_NE | 1 | 3106 | 60 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL| SECNODE3_T_INTE_NE | 1 | 3106 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL| SECNODE4_T_INTE_NE | 1 | 3106 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL| SECNODE5_T_INTE_NE | 1 | 3106 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL| SYS_T_INTE_NE | 1 | 3106 | 17 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T2"."INTE_REF"="T1"."INTEREF" AND "T2"."NE_REF"="T1"."NEREF" AND
"T2"."INTE_INST"="T1"."INSTANCE")

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
-----------------------------------------------------------
3 user calls
0 physical read total bytes
0 physical write total bytes
0 spare statistic 3
0 commit cleanout failures: cannot pin
0 TBS Extension: bytes extended
0 total number of times SMON posted
0 SMON posted for undo segment recovery
0 SMON posted for dropping temp segment
0 segment prealloc tasks

What could explain the difference ?
I get exactly the same execution plan (when autotrace is ON).

Furthermore, if I try to do the same update on each of the basic tables, I get the rows updated instantaneously.

Is there any reason for avoiding this kind of query ?
Any help would be greatly appreciated :-)

Regards,
Patrick
  • 1. Re: Oracle 11G - Update is very slow on View
    mseberg Guru
    Currently Being Moderated
    Withdrawn

    Edited by: mseberg on Dec 7, 2012 8:28 AM
  • 2. Re: Oracle 11G - Update is very slow on View
    moreajays Pro
    Currently Being Moderated
    Hi,

    First of all disable trigger & try .

    Also avoid exist clause & pass where clause directly to update query , check explain plan also

    Not seeing index getting used here , hope they are present



    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 3. Re: Oracle 11G - Update is very slow on View
    Dom Brooks Guru
    Currently Being Moderated
    SQL Trace and run the resulting trace file through tkprof.

    The details in the execution plan above are just estimates.
    If you trace it with event 10046, e.g.
    ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
    You can find the actual execution plan, the actual row source cardinalities and exactly where the time was spent.
  • 4. Re: Oracle 11G - Update is very slow on View
    user11689469 Newbie
    Currently Being Moderated
    Hi,

    As I'm trying to update a view, I am not able to disable the INSTEAD OF UPDATE trigger or the query will fail.
    However, my trigger code is only : RETURN
    I get exactly the same performance with executing the query :

    UPDATE T_INTE_NE
    SET INTE_STATE = -11
    WHERE ( INTE_REF, NE_REF, INTE_INST ) IN ( SELECT INTEREF, NEREF, INSTANCE FROM SMALL )

    Patrick
  • 5. Re: Oracle 11G - Update is very slow on View
    user11689469 Newbie
    Currently Being Moderated
    I agree that my problem may be that Oracle does not seem to use the Primary index on tables which consists in the 3 columns of the Join.
    However, it does not seem to use it for SELECT as well and SELECT is very fast....
  • 6. Re: Oracle 11G - Update is very slow on View
    sb92075 Guru
    Currently Being Moderated
    user11689469 wrote:
    I agree that my problem may be that Oracle does not seem to use the Primary index on tables which consists in the 3 columns of the Join.
    However, it does not seem to use it for SELECT as well and SELECT is very fast....
    why should index be used when single digit row count all fit in single block?
  • 7. Re: Oracle 11G - Update is very slow on View
    user11689469 Newbie
    Currently Being Moderated
    My table structure is much more complex than this.
    My data is separated into different tables to fullfill Encryption / Data separation among entities.
    So, each table contains a set of Encrypted data, each table having it's own Encryption key.
    Then, the View provided decrypted data... (I know this seems meaningless but this is what I was asked for...) and the decryption is only allowed for special accounts / applications...
    However, if the index is not used, I'm afraid that Oracle tries to decrypt all the records that he get access too even if doesn't need to decrypt them.
    Then, if Oracle would use the index, it would only access / decrypt 6 records.
    If Oracle does Full Scan, it will decrypt all the records -> which will be time consuming.

    However it does for the UPDATE but not for the SELECT which is identical....

    Patrick
  • 8. Re: Oracle 11G - Update is very slow on View
    sb92075 Guru
    Currently Being Moderated
    user11689469 wrote:
    My table structure is much more complex than this.
    My data is separated into different tables to fullfill Encryption / Data separation among entities.
    So, each table contains a set of Encrypted data, each table having it's own Encryption key.
    Then, the View provided decrypted data... (I know this seems meaningless but this is what I was asked for...) and the decryption is only allowed for special accounts / applications...
    However, if the index is not used, I'm afraid that Oracle tries to decrypt all the records that he get access too even if doesn't need to decrypt them.
    Then, if Oracle would use the index, it would only access / decrypt 6 records.
    If Oracle does Full Scan, it will decrypt all the records -> which will be time consuming.

    However it does for the UPDATE but not for the SELECT which is identical....

    Patrick
    we only know what you post here.
    If what you post does NOT accurately reflect your reality; then Garbage In, Garbage Out.
  • 9. Re: Oracle 11G - Update is very slow on View
    Dom Brooks Guru
    Currently Being Moderated
    No point in conjecture or guesswork.
    Just trace it as mentioned and you can know exactly where you time is going.
  • 10. Re: Oracle 11G - Update is very slow on View
    user11689469 Newbie
    Currently Being Moderated
    Sorry for this, I lost myself in conjonctures and I didn't think I would have to explain the whole case.
    So, I wrote a small piece of PL/SQL that reproduces the same issue.
    It seems that my issue is not due to the UPDATE but to the use of the IN predicate.
    As you can see at the end of the script, I try to join the 2 tables using different technics.
    The first query is very fast, the second is very slow.
    I need the second one if I want to do any update.

    DROP TABLE Part1;
    DROP TABLE Part2;
    DROP TABLE Part3;
    DROP TABLE Part4;

    CREATE TABLE Part1 ( Key1 NUMBER(10, 0), Key2 NUMBER(10, 0), Key3 NUMBER(10, 0), PartId NUMBER(10, 0) DEFAULT( 1 ) NOT NULL, Data1 VARCHAR2(1000), X_Data2 VARCHAR2(2000) NULL, X_Data3 VARCHAR2(2000) NULL, CONSTRAINT PK_Part1 PRIMARY KEY( Key1, Key2, Key3 ) );
    CREATE TABLE Part2 ( Key1 NUMBER(10, 0), Key2 NUMBER(10, 0), Key3 NUMBER(10, 0), PartId NUMBER(10, 0) DEFAULT( 2 ) NOT NULL, Data1 VARCHAR2(1000), X_Data2 VARCHAR2(2000) NULL, X_Data3 VARCHAR2(2000) NULL, CONSTRAINT PK_Part2 PRIMARY KEY( Key1, Key2, Key3 ) );
    CREATE TABLE Part3 ( Key1 NUMBER(10, 0), Key2 NUMBER(10, 0), Key3 NUMBER(10, 0), PartId NUMBER(10, 0) DEFAULT( 3 ) NOT NULL, Data1 VARCHAR2(1000), X_Data2 VARCHAR2(2000) NULL, X_Data3 VARCHAR2(2000) NULL, CONSTRAINT PK_Part3 PRIMARY KEY( Key1, Key2, Key3 ) );
    CREATE TABLE Part4 ( Key1 NUMBER(10, 0), Key2 NUMBER(10, 0), Key3 NUMBER(10, 0), PartId NUMBER(10, 0) DEFAULT( 4 ) NOT NULL, Data1 VARCHAR2(1000), X_Data2 VARCHAR2(2000) NULL, X_Data3 VARCHAR2(2000) NULL, CONSTRAINT PK_Part4 PRIMARY KEY( Key1, Key2, Key3 ) );


    CREATE OR REPLACE FUNCTION Decrypt
    (
    x_in IN VARCHAR2
    ) RETURN VARCHAR2
    AS
    x_out VARCHAR2(2000);
    BEGIN
    SELECT REVERSE( x_in ) INTO x_out FROM DUAL;
    RETURN ( x_out );
    END;


    CREATE OR REPLACE VIEW AllParts AS
    SELECT Key1, Key2, Key3, PartId, Data1, Decrypt( X_Data2 ) AS Data2, Decrypt( X_Data3 ) AS Data3 FROM Part1
    UNION ALL
    SELECT Key1, Key2, Key3, PartId, Data1, Decrypt( X_Data2 ) AS Data2, Decrypt( X_Data3 ) AS Data3 FROM Part2
    UNION ALL
    SELECT Key1, Key2, Key3, PartId, Data1, Decrypt( X_Data2 ) AS Data2, Decrypt( X_Data3 ) AS Data3 FROM Part3
    UNION ALL
    SELECT Key1, Key2, Key3, PartId, Data1, Decrypt( X_Data2 ) AS Data2, Decrypt( X_Data3 ) AS Data3 FROM Part4;

    DROP TABLE Small;
    CREATE TABLE Small ( Key1 NUMBER(10, 0), Key2 NUMBER(10, 0), Key3 NUMBER(10, 0), Data1 VARCHAR2(1000) );


    BEGIN
    DECLARE
    n_Key NUMBER(10, 0 ) := 0;

    BEGIN
    WHILE ( n_Key < 50000 )
    LOOP
    INSERT INTO Part1( Key1, Key2, Key3 )
    VALUES( n_Key, FLOOR( n_Key / 10 ), FLOOR( n_Key / 100 ) );
    INSERT INTO Part2( Key1, Key2, Key3 )
    VALUES( n_Key, FLOOR( n_Key / 10 ), FLOOR( n_Key / 100 ) );
    INSERT INTO Part3( Key1, Key2, Key3 )
    VALUES( n_Key, FLOOR( n_Key / 10 ), FLOOR( n_Key / 100 ) );
    INSERT INTO Part4( Key1, Key2, Key3 )
    VALUES( n_Key, FLOOR( n_Key / 10 ), FLOOR( n_Key / 100 ) );
    n_Key := n_Key + 1;
    END LOOP;

    INSERT INTO Small( Key1, Key2, Key3, Data1 ) VALUES ( 1000, 100, 10, 'Test 1000' );
    INSERT INTO Small( Key1, Key2, Key3, Data1 ) VALUES ( 3000, 300, 30, 'Test 3000' );
    INSERT INTO Small( Key1, Key2, Key3, Data1 ) VALUES ( 5000, 500, 50, 'Test 5000' );
    COMMIT;
    END;
    END;

    SELECT T2.*
    FROM Small T1, AllParts T2
    WHERE T2.Key1 = T1.Key1 AND T2.Key2 = T1.Key2 AND T2.Key3 = T1.Key3;

    SELECT T1.*
    FROM AllParts T1
    WHERE ( T1.Key1, T1.Key2, T1.Key3 ) IN ( SELECT T2.Key1, T2.Key2, T2.Key3 FROM Small T2 );

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points