Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Question Unanswered: Create Materialized view statement hanging

    Hi,
    I am trying to create a materialized view but the statement seems to hang. I am accessing tables from multiple schemas with the help of DB links in my select statement. I have done this for other MVs successfully but this seems to just hang.

    Any suggestions on what I should be looking at for trouble shooting this? I have been told there could be waits on the DB links. How can I check this?

    Thank you for your help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am accessing tables from multiple schemas with the help of DB links
    DBLINK is required to access tables in different database/instance.
    If these "multiple schemas" exist within a single database/instance, then use of DBLINK is unwarranted & highly inefficient.

    post EXPLAIN PLAN from your SQL.

    ALTER SESSION SET SQL_TRACE=TRUE
    can provide clues as to what is happening behind the scene.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    76

    explain plan

    Hi Anacedent,
    Here is the plan for the sql script. If you want I can e-mail you the script also.

    Plan
    SELECT STATEMENT ALL_ROWSCost: 12,326 Bytes: 198,792 Cardinality: 396
    10 HASH UNIQUE Cost: 12,326 Bytes: 198,792 Cardinality: 396
    9 TABLE ACCESS BY INDEX ROWID TABLE SR.APSR_SYSTEM_ITEMS Cost: 3 Bytes: 72 Cardinality: 4
    8 NESTED LOOPS Cost: 12,325 Bytes: 198,792 Cardinality: 396
    6 NESTED LOOPS Cost: 12,028 Bytes: 47,916 Cardinality: 99
    4 HASH JOIN Cost: 10,826 Bytes: 440 Cardinality: 1
    1 REMOTE REMOTE SERIAL_FROM_REMOTE APSRTOAPPS Cost: 1,367 Bytes: 915,840 Cardinality: 9,540
    3 VIEW VIEW SR.APSR_PRE_PROMOTIONS_VW Cost: 5,200 Bytes: 5,506,224 Cardinality: 114,713
    2 REMOTE REMOTE SERIAL_FROM_REMOTE APSRTOAPPS
    5 REMOTE REMOTE SERIAL_FROM_REMOTE MTL_SYSTEM_ITEMS_B APSRTOAPPS Cost: 1,202 Bytes: 21,824 Cardinality: 496
    7 INDEX RANGE SCAN INDEX (UNIQUE) SR.APSR_SYSTEM_ITEMS_PK Cost: 2 Cardinality: 4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •