Results 1 to 13 of 13

Thread: Explain Plan

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    10

    Thumbs up Unanswered: Explain Plan

    Hi Gurus,
    I have two database
    1. Test and
    2. Production

    Both database are exactly same only Production Db is having more data.
    My Question is why oracle has different excution plan for the same query in both the databases (production and Test).

    Is it the Explain Plan depends on the the amount of data.?

    Thanks in Advance,
    Parag
    parag

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Explain Plan

    Analyse the tables used in query in both databases and then check the explain plan if those are same.


    Originally posted by parag_cg
    Hi Gurus,
    I have two database
    1. Test and
    2. Production

    Both database are exactly same only Production Db is having more data.
    My Question is why oracle has different excution plan for the same query in both the databases (production and Test).

    Is it the Explain Plan depends on the the amount of data.?

    Thanks in Advance,
    Parag
    Oracle can do wonders !

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    10
    Thanks for the reply !!!
    Yes i checked, both test and production database tablea are analyze at the same time and very frequently.

    Thanks,
    Parag
    parag

  4. #4
    Join Date
    Dec 2003
    Location
    Macedonia (nobody's former republic)
    Posts
    8
    Check if optimization parameters in INIT.ORA files are equal (when saying "same", does it stand for configuration too?) - like HASH_JOIN_ENABLED ore QUERY_REWRITE and similar parameters ... Sometime they get forgotten when creating the DB.

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: Explain Plan

    Originally posted by parag_cg
    Is it the Explain Plan depends on the the amount of data.?
    Yes, it depends also on that.

    For example, say you have an almost-empty table with only 2-3 blocks used - Oracle will tend to full-table-scan the table, even if indexes are defined on it, since it costs less to read the 2-3 blocks and hunt for rows rather than reading the blocks of the index(es) and then get one or more table blocks.

    Say you have the same table with 10,000 blocks used - in this case it may be more appropriate to use the indexes, instead of full-scanning the table. I say "may" because it depends on the query (it may be still more appropriate to full-scan).

    I would bet that you have almost-empty tables in test, but not in pro. Oracle is simply using (since you confirm you have statistics collected) the most appropriate path in each case.

    My advice : use the same data data both in test and production.

    HTH
    Alberto

  6. #6
    Join Date
    Dec 2003
    Location
    Kekaha, Kauai, Hawaii
    Posts
    22
    The cost based optimizer changes the plan based on the amount of data in the table. If you are analyzing the tables then you are probably using the cost based optimizer. If you are concerned about Explain Plan and optimization of qeuries, the only way to properly test is with production data. You can get a pretty good estimate (with a lot of work) - if you load up your tables with sample data, up to the size that you anticipate for your production database size but you still can't be sure that your sample data has the same distribution that your production data has. If you already have production data from the system which you are converting - or if you already have a live production database - then create a copy of the database for optimization testing.

    Good luck. Query optimization is difficult enough in a production environment, it's much more difficult during development - just because of what you ran into - how the queries run changes as you load more data.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Come on. We don't know diddly-squat unless you trace both queries and then TKPROF the trace files.

    Then post the TKPROF results here and we'll take a look.
    Other than that there is no point since we cannot see what Oracle is doing until you trace the queries.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Nov 2003
    Location
    India
    Posts
    10
    Thanks every one !
    Since i dont have administrative access, i can not provide Trace File.
    Well, i have explain plan for both production as well as test database.
    May be this will give you good idea.
    Test has around one million rows and production has 8 million rows.

    Test Explain Plan
    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    SELECT STATEMENT
    SORT GROUP BY
    MERGE JOIN
    SORT JOIN
    NESTED LOOPS
    HASH JOIN
    TABLE ACCESS FULL DB_MASTER
    HASH JOIN
    TABLE ACCESS FULL FISCAL_WEEK
    TABLE ACCESS BY GLOBAL INDEX INVOICE_LINE
    ROWID

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------

    NESTED LOOPS
    INLIST ITERATOR
    TABLE ACCESS BY INDEX ROWID ITEM_MASTER
    INDEX RANGE SCAN I_ITEM_HPL
    INDEX RANGE SCAN I_INDEXTEST
    TABLE ACCESS BY INDEX ROWID CUSTOMER_MASTER
    INDEX UNIQUE SCAN PK_CUSTOMER_MASTER
    FILTER
    SORT JOIN
    TABLE ACCESS FULL FISCAL_WEEK


    Production Explain Plan

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    SELECT STATEMENT
    SORT GROUP BY
    HASH JOIN
    TABLE ACCESS FULL CUSTOMER_MASTER
    HASH JOIN
    INLIST ITERATOR
    TABLE ACCESS BY INDEX ROWID ITEM_MASTER
    INDEX RANGE SCAN I_ITEM_HPL
    HASH JOIN
    TABLE ACCESS FULL FISCAL_WEEK
    HASH JOIN

    OPERATION OPTIONS OBJECT_NAME
    ------------------------------ --------------- --------------------
    MERGE JOIN CARTESIAN
    TABLE ACCESS FULL FISCAL_WEEK
    BUFFER SORT
    TABLE ACCESS FULL DB_MASTER
    PARTITION RANGE ALL
    TABLE ACCESS FULL INVOICE_LINE
    parag

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by parag_cg
    Thanks every one !
    Since i dont have administrative access, i can not provide Trace File.
    How do you shut down the db or look for alerts in the alert file or access archived-logs or run scheduled tasks on the server or (I could go on and on). You need access to the db server in order to do these things.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Are you sure all indexes that are on TEST are also on PROD ???
    (in particular I_INDEXTEST)

    I would start looking at statistics on indexes and tables (CUSTOMER
    and INVOICE_LINE) ... These are table scans in production -vs- index lookups in test ...

    Are you sure INVOICE_LINE has a global index on PROD ?
    I would ensure stats are up-to-date ... Look at the extents and leaf levels on indexes and rebuild them if necessary ...

    You can run the same query (set autotrace trace) in PROD and trace the path the optimizer will use. Try applying index "HINTS" to see if using the indexes works better ...

    Also, are the database parameters the same in both databases (I would hope not ...). Check "db_file_multiblock_read_count". The larger the value, the more possiblity Oracle will attempt to FULL SCAN tables.

    Bottom line ... as THE_DUCK said ... You need to have access to the trace files etc ...

    HTH
    Gregg

  11. #11
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    This may help to 'explain' things further :-) (no pun intended!)

    http://www.billmagee.co.uk/oracle/sqltune/index.html

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  12. #12
    Join Date
    Nov 2003
    Location
    India
    Posts
    10

    Thumbs up

    Once again thanks to every one
    Well, i am new to this company. I will get access to the Database
    after some time. Moreover my production database is located remotly.

    Index Pk_INVOICE are local in both database, then why explain plan
    showing that table is access by Global indexes in test database.

    I_indextest was created by me on test database, it is not there in Production Database. Other indexes are same in both test and production database.

    Even the explain plan, in absence of I_indextest is different.

    I dont have access to 'set autotrace on ' on Production Database .

    Since Invoice_line table is big table, full scan is taking much time to execute. So i have to force the query to used Hints. But i want to avoid hint usage as the query is used in Bussiness objects for reports.

    db_file_multiblock_read_count is set to 16 (how to calculates db_file_multiblock_read_count value)

    Index Information
    --------------------
    .INDEX INFORMATION
    Table: INVOICE_LINE
    COLUMN_NAME INDEX_NAME
    CUST_BILL_ID I_INVOICE_BILL_TO
    CUST_SOLD_ID I_INVOICE_SOLD_TO
    DB_CODE I_INVOICE_DB_CODE_F_MANUF
    DB_CODE I_INVOICE_ITEM
    DB_CODE I_INVOICE_ORDER
    DB_CODE I_INVOICE_WEEK_2
    DB_CODE PK_INVOICE
    DB_CODE SYS_C003802
    DUE_DATE I_INVOICE_DUE_DATE
    FISCAL_WEEK I_INVOICE_WEEK
    FISCAL_WEEK I_INVOICE_WEEK_2
    F_MANUF I_INVOICE_DB_CODE_F_MANUF
    INV_LINE I_INVOICE_ORDER
    INV_LINE PK_INVOICE
    INV_LINE SYS_C003802
    INV_NB PK_INVOICE
    INV_NB SYS_C003802
    ITEM_ID I_INVOICE_ITEM
    ITEM_NUMBER I_INVOICE_ITEM
    ORDER_NUMBER I_INVOICE_ORDER
    ORDER_NUMBER PK_INVOICE
    ORDER_NUMBER SYS_C003802

    Table: ITEM_MASTER
    COLUMN_NAME INDEX_NAME
    DB_CODE IU_ITEM_MASTER_DB_CODE
    DB_CODE I_ITEM_MASTER_DBCODE_ITEMNO
    DB_CODE PK_ITEM_MASTER
    HPL_CODE I_ITEM_HPL
    ITEM_DESC1 I_ITEM_DESC1
    ITEM_ID PK_ITEM_MASTER
    ITEM_NUMBER I_ITEM_MASTER_DBCODE_ITEMNO
    ITEM_NUMBER PK_ITEM_MASTER
    PL_CODE I_ITEM_PL

    Table: FISCAL_WEEK
    FISCAL_MONTH I_FISCAL_MONTH
    FISCAL_WEEK PK_FISCAL_WEEK
    FISCAL_YEAR I_FISCAL_YEAR
    REL_WEEK I_FISCAL_REL_WEEK

    Table: DB_MASTER
    DB_CODE PK_DB_MASTER

    Table: CUSTOMER_MASTER
    COLUMN_NAME INDEX_NAME
    CUST_CODE IU_CUST_CODE
    CUST_CODE PK_CUSTOMER_MASTER
    CUST_ID PK_CUSTOMER_MASTER
    DB_CODE IU_CUST_CODE
    DB_CODE PK_CUSTOMER_MASTER
    parag

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    Sorry, I am still confused.

    log through sql-plus, then issue:
    Code:
    alter session set sql_trace=TRUE;
    then run all your code you want, then exit session.

    Log onto db server through telnet/putty/whatever:
    telnet 192.168.0.22
    username: oracle
    password: (oracle server password)

    goto UDUMP directory for your sid which should be at:
    $ORACLE_BASE/admin/SID/udump

    find the latest trace file and tkprof it:
    tkprof dev1_ora_3527.trc test explain=system/manager

    look at the contents of test.prf and see the light.

    there is no reason whatsoever that you should not have a username/password to log into the database server. The user and privs should already be set up if it was done correctly.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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