Forum Stats

  • 3,874,142 Users
  • 2,266,672 Discussions
  • 7,911,738 Comments

Discussions

Simple insert into table hanging

13

Answers

  • Unknown
    edited Nov 10, 2016 4:05PM
    Why should it matter, they are all just pluggable database on the SAME oracle server.

    I told you in my reply why it matters.

    Each PDB is a separate and distinct database regardless of where they are physically located.

    If you follow the example I provided and answer the question I ask then you will understand why it 'matters'.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,405 Bronze Crown
    edited Nov 10, 2016 4:16PM

    @rp0428 gave us a very good example at his first reply. you might be carrying all the rows from the db where tables located to database that you will make your insert. at first I thought that OFFERS and LIST tables are on different databases too but if they are on the same db, then this could be your problem, your insert statement works on the remote db (not at the db that offers and list are in it).

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 10, 2016 4:25PM
    Mustafa KALAYCI wrote:rp0428 gave us a very good example at his first reply. you might be carrying all the rows from the db where tables located to database that you will make your insert. at first I thought that OFFERS and LIST tables are on different databases too but if they are on the same db, then this could be your problem, your insert statement works on the remote db (not at the db that offers and list are in it).

    The plan tells us that for the insert statement:

    All the rows from offers that match the simple predicate (SOURCE = 'PST') are pulled across from the remote DB to the local DB

    For each row it then fires another query against the remote DB to check the existence in LIST (FILTER operation)

    It then does the group by on the local DB

    For the select statement, the entire query was passed to the remote DB which has unnested the not in subquery into an antijoin. The results were then passed to the local DB. This was much better because it was able to unnest the not in subquery rather than execute an existence check for each row.

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 10, 2016 5:03PM

    How can I get it to use same the plan for insert as for the select?

    Why does it have a cardinality of 1 for LIST in the insert plan as opposed to the 1786967 in the select, is this because it is fetching a single row at a time?

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 10, 2016 5:09PM
    Amarprem wrote:How can I get it to use same the plan for insert as for the select?Why does it have a cardinality of 1 for LIST in the insert plan as opposed to the 1786967 in the select, is this because it is fetching a single row at a time?

    I've recommended two ways, if you're going to just ignore them then why should we bother to help further. I've even allowed you to be lazy enough and just use copy and paste some code.

    If you want more help then you will have to do some of the work yourself, if you're struggling to understand what has been recommended then try googling first and then asking for clarification. Repeating the same question will only lead to the same response.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 10, 2016 5:11PM

    You can lead some folks to knowledge, but you can't make them think.

  • Unknown
    edited Nov 10, 2016 6:03PM
    How can I get it to use same the plan for insert as for the select?

    How can we get YOU to answer our questions?

    You may not be able to get Oracle to use the same plan. The only possible option is to try using the DRIVING_SITE hint but I wouldn't expect that to work.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Nov 10, 2016 6:38PM

    Andrew Sayer has given you one of the solutions to the problem - try it.

    A distributed (or fully remote) select statement can be optimised and executed at a site other than the one where the statement was issued - returning the result to the original site. A CTAS or an "insert as select" MUST (for no good reason offered by Oracle Corp.) execute at the site where the table is to be created or insert into - which leads to the type of issue you're seeing.

    See: https://jonathanlewis.wordpress.com/2008/12/05/distributed-dml/  (and its comments and pingbacks - which will lead you to other solutions)

    Regards

    Jonathan Lewis

  • jgarry
    jgarry Member Posts: 13,844 Gold Crown
    edited Nov 10, 2016 7:10PM
    Amarprem wrote:No they are on the same pluggable database LIVE, the are being accessed for reporting database.Why should it matter, they are all just pluggable database on the SAME oracle server.

    It matters because Oracle needs to communicate back and forth between the databases, and must have limits on how it does that (which don't have to make sense on the surface, though normally there are reasons when you dig enough).  If you established a dblink to the same database, it would still have to obey those limits, it doesn't magically know where a remote database actually is.  Sometimes hiding things in views can have additional limitations.  So in addition to rp0428's concrete blocks analogy, you have to pass the concrete through a drainage pipe.

    It's also not necessarily a good sign that two full table accesses are the faster way to do something that is not a cartesian join.  In addition to the questions others have asked, you might describe things like how many rows you expect to select update out of how many there are.  There's probably some way to limit the unnecessary work being done, and that's the secret to making it go faster.  You might also search asktom.oracle.com on the subject of exists versus in.  There may also be indexing solutions for "things not in."

  • AParm
    AParm Member Posts: 354 Blue Ribbon
    edited Nov 11, 2016 8:00AM

    Okay, I have run explain plans, I have found that, it does NOT always run the select locally, as you can see in the second plan. What I did here was do a CTAS to create the table, which took less than a second, and the subsequent insert also execute as quick.

    Can somebody shed some light what the execution plans are different

    PLAN_TABLE_OUTPUT                                                                                                                                                                                     

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    SQL_ID  8tah0qth71hdp, child number 1                                                                                                                                                                  

    -------------------------------------                                                                                                                                                                  

    INSERT INTO "DATQ"."OFFERS_CHECK" (EXTRCT,                                                                                                                                     

    ISSUE, A_COUNT, SITE, SOURCE) SELECT 'OFFERS'                                                                                                                                   

    EXTRCT, 'MISSING OFFERS' ISSUE, COUNT(*) A_COUNT,                                                                                                                                       

    OFFERS.SITE,OFFERS.SOURCE                                                                                                                                   

    FROM "DATQ".OFFERS WHERE "OFFERS".EDL_ID                                                                                                                               

    NOT IN (SELECT "EDL_ID" FROM "DATQ".LIST) AND SOURCE = 'PST'

    GROUP BY OFFERS.SITE, OFFERS.SOURCE                                                                                                                                

                                                                                                                                                                                                           

    Plan hash value: 371604923                                                                                                                                                                             

                                                                                                                                                                                                           

    -----------------------------------------------------------------------------------------------------------------------                                                                                

    | Id  | Operation                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|                                                                                

    -----------------------------------------------------------------------------------------------------------------------                                                                                

    |   0 | INSERT STATEMENT         |                            |       |       |    12G(100)|          |        |      |                                                                                

    |   1 |  LOAD TABLE CONVENTIONAL | OFFERS_CHECK               |       |       |            |          |        |      |                                                                                

    |   2 |   HASH GROUP BY          |                            |     6 |   228 |    12G  (1)|130:33:00 |        |      |                                                                                

    |*  3 |    FILTER                |                            |       |       |            |          |        |      |                                                                                

    |   4 |     REMOTE               | OFFERS                     |   983K|    35M|  3924   (1)| 00:00:01 | PDBPR~ | R->S |                                                                                

    |   5 |     REMOTE               | LIST                       |     1 |    25 | 12260   (1)| 00:00:01 | PDBPR~ | R->S |                                                                                

    -----------------------------------------------------------------------------------------------------------------------                                                                                

                                                                                                                                                                                                           

    Predicate Information (identified by operation id):                                                                                                                                                    

    ---------------------------------------------------                                                                                                                                                    

                                                                                                                                                                                                           

       3 - filter( IS NULL)                                                                                                                                                                                

                                                                                                                                                                                                           

    Remote SQL Information (identified by operation id):                                                                                                                                                   

    ----------------------------------------------------                                                                                                                                                   

                                                                                                                                                                                                           

       4 - SELECT /*+ OPAQUE_TRANSFORM */ "SOURCE", "EDL_ID", "SITE" FROM                                                                                                

           "DATQ"."OFFERS" "OFFERS" WHERE "SOURCE"='PST' (accessing 'PDBLIVE' )                                                                                              

                                                                                                                                                                                                           

       5 - SELECT /*+ OPAQUE_TRANSFORM */ "EDL_ID" FROM "DATQ"."LIST"                                                                                           

           "A1" WHERE LNNVL("EDL_ID"<>:1) (accessing 'PDBLIVE' )         

    This is the table I first create using a CTAS, and the truncate, followed by the insert to match the scenario above....

    ----------------------------------------------------------------------------------------

    PLAN_TABLE_OUTPUT                                                                                                                                                                                     

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    SQL_ID  6y057jm6wt29p, child number 0                                                                                                                                                                  

    -------------------------------------                                                                                                                                                                  

    insert into OFFERS_CHECK2 select  'OFFERS' EXTRCT, 'MISSING OFFERS'                                                                                                                                     

    ISSUE,  count (*) A_Count, OFFERS.SITE, OFFERS.SOURCE from OFFERS                                                                                                                                      

    WHERE OFFERS.EDL_ID not in (select EDL_ID from LIST) and SOURCE = 'PST'                                                                                                                                

    GROUP BY OFFERS.SITE, OFFERS.SOURCE                                                                                                                                                                    

                                                                                                                                                                                                           

    Plan hash value: 1788691278                                                                                                                                                                            

                                                                                                                                                                                                           

    -------------------------------------------------------------------------                                                                                                                              

    | Id  | Operation                | Name         | Cost  | Inst   |IN-OUT|                                                                                                                              

    -------------------------------------------------------------------------                                                                                                                              

    |   0 | INSERT STATEMENT         |              |     1 |        |      |                                                                                                                              

    |   1 |  LOAD TABLE CONVENTIONAL | OFFERS_CHECK |       |        |      |                                                                                                                              

    |   2 |   REMOTE                 |              |       | PDBPR~ | R->S |                                                                                                                              

    -------------------------------------------------------------------------                                                                                                                              

                                                                                                                                                                                                           

    Remote SQL Information (identified by operation id):                                                                                                                                                   

    ----------------------------------------------------                                                                                                                                                   

                                                                                                                                                                                                           

       2 - SELECT 'OFFERS','MISSING OFFERS',COUNT(*),"A1"."SITE","A1"."SOURC                                                                                                                               

           E" FROM "DATQ"."OFFERS" "A1" WHERE "A1"."EDL_ID"<>ALL (SELECT                                                                                                                               

           "A2"."EDL_ID" FROM "DATQ"."LIST" "A2") AND "A1"."SOURCE"='PST'                                                                                                                              

           GROUP BY "A1"."SITE","A1"."SOURCE" (accessing 'PDBLIVE' )                                                                                                                                       

                                                                                                                                                                                                           

                                                                                                                                                                                                           

    Note                                                                                                                                                                                                   

    -----                                                                                                                                                                                                  

       - cpu costing is off (consider enabling it)    

This discussion has been closed.