DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Performance issue

  1. #11
    Join Date
    Oct 2000
    Posts
    57
    something interesting .

    when i run above query , it select arount 30 rows , then i t hangs for ever .




  2. #12
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, not to be too difficult, but I would then need to see the view's query as well, please.

    - Chris

  3. #13
    Join Date
    Oct 2000
    Posts
    57

    Chris,

    Here is view query ,

    select /*+ INDEX(FD_TABLE) */ df_num,col2,col3, col4
    from fd_table
    where test_date between to_date('18-JAN-2001','DD-MON-YYYY') and to_date('17-FEB-2001','DD-MON-YYYY')


    The above hint we put when local index was there on the table and it is still there with global index.

    thanks


  4. #14
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, we obviously must be mis-communicating somewhere here.

    In order for me to help you diagnose this query, I will need *the entire query*.

    What you originally posted as a query:

    select df_num from finx_tab where tst_date between '03-FEB-2001' and '09-FEB-2001' and df_num like 'KLX%' .

    You further said that the following plan was generated from this statement:
    SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=4 Bytes=168)

    HASH JOIN (Cost=57 Card=4 Bytes=168)
    ---TABLE ACCESS (FULL) OF PD_IN (Cost=2 card=9 Bytes=144)
    ---TABLE ACCESS (BY GLOBAL INDEX ROWID) OF FD_TABLE (Cost=54 Card=4 Bytes=104)
    ------INDEX (RANGE SCAN) OF GL_DF_NUM_IDX (NON-UNIQUE) (Cost=4 Card=4) .

    Unfortunately, neither FD_TABLE nor PD_IN were referenced in the query.

    Then you tell me they came from the view. Ah, finx_tab is actually a view. I asked for the view definition and you gave me:

    select /*+ INDEX(FD_TABLE) */ df_num,col2,col3, col4
    from fd_table
    where test_date between to_date('18-JAN-2001','DD-MON-YYYY') and to_date('17-FEB-2001','DD-MON-YYYY')

    Now, there are 2 problems with this last reply:

    - There is *still* no reference to PD_IN. Obviously, there is still a piece you are not giving me.
    - When replacing the view into the query, we end up with:

    SELECT
    ---DF_NUM
    FROM
    ---(
    ---SELECT /*+ INDEX(FD_TABLE) */
    ------DF_NUM,
    ------COL2---,
    ------COL3---,
    ------COL4
    ---FROM
    ------FD_TABLE
    ---WHERE
    ------TEST_DATE---BETWEEN---TO_DATE('18-JAN-2001','DD-MON-YYYY')
    ------------------AND------TO_DATE('17-FEB-2001','DD-MON-YYYY')
    ---)
    WHERE
    ---TST_DATE---BETWEEN---'03-FEB-2001'
    ------------AND------'09-FEB-2001'---AND
    ---DF_NUM---LIKE------'KLX%'


    Which has all sorts of problems, including the fact that the date range is restricted twice and there is no column tst_date being returned from the innermost query, so how can it appear in the outermost WHERE clause?

    It is starting to look like I may not be able to help you, but I will give this one more try. If you want me to be able to attempt to even start diagnosing this particular problem, you will need to provide:

    - ALL the SQL for this issue:
    - The EXACT original query
    - The EXACT definition of all involved views
    - The EXACT explain plan for the query
    - Any and all relevant info.
    - FD_TABLE is a table partitioned by TEST_DATE (what ranges?) with a global non-unique index on DF_NUM. Is there any other relevant info we should know about this table. What were the exact indexes like before, when performance was okay? Why did you switch them?
    - What is PD_IN? What are its indexes like? Did they change? If so, why?

    I hate to sound like such a stickler, but this is obviously taking way too long to get the the root of the problem.

    - Chris

Posting Permissions

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


Click Here to Expand Forum to Full Width