Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2003
    Location
    toronto
    Posts
    10

    Angry Unanswered: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    I migrate my sybase12 on AIX 4.3 raw device to sybase12.5 on AIX5.1 jfs, one query is extremly slow then. from 4 seconds to 1 minute.

    I have "update all statistics" all involved tables, "data/procedure cache" is bigger, "dsync off" on sybase device. I assume query plan should not change.

    I also run sp_sysmon for a minute period , it persistently tell me on sybase12.5 "total disk i/o checks count" is 400000, and "network checks" is 80000. but on sybase12, those number is less than 500. I have tried to change "i/o polling process" from 10->100->10000, "runnable process search count" from 3->2000->20000. the same thing.

    I am really confused, what's wrong?

    Any idea?

    Thanks,

    Jiulu Sun

  2. #2
    Join Date
    Apr 2003
    Posts
    18

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by jiulusun
    I migrate my sybase12 on AIX 4.3 raw device to sybase12.5 on AIX5.1 jfs, one query is extremly slow then. from 4 seconds to 1 minute.

    I have "update all statistics" all involved tables, "data/procedure cache" is bigger, "dsync off" on sybase device. I assume query plan should not change.

    I also run sp_sysmon for a minute period , it persistently tell me on sybase12.5 "total disk i/o checks count" is 400000, and "network checks" is 80000. but on sybase12, those number is less than 500. I have tried to change "i/o polling process" from 10->100->10000, "runnable process search count" from 3->2000->20000. the same thing.

    I am really confused, what's wrong?

    Any idea?

    Thanks,

    Jiulu Sun
    Hey,
    Try dropping the indexes and then recreating them. It will automatically run run updatestat's after completion of indexing. Have you checked the query plan and can you please include the query plan before and after???

    Thanks

    DB

  3. #3
    Join Date
    Apr 2003
    Posts
    18

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by dba_sybase2003
    Hey,
    Try dropping the indexes and then recreating them. It will automatically run run updatestat's after completion of indexing. Have you checked the query plan and can you please include the query plan before and after???

    Thanks

    DB
    Also try running vmstat and check for drive speed bottlenecks.

  4. #4
    Join Date
    Apr 2003
    Posts
    18

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by dba_sybase2003
    Also try running vmstat and check for drive speed bottlenecks.
    I did not mean to put that icon over there. It was a slip of the pointer.
    sorry again.

  5. #5
    Join Date
    Jul 2003
    Location
    toronto
    Posts
    10

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by dba_sybase2003
    Also try running vmstat and check for drive speed bottlenecks.
    I drop the table and recreate it, bcp in data, recreate indexes, "update all statistics" on the table, query plan not change too much, and the query is still running over 1 minute.

    here is the before and after of dbcc 302.

    Thanks,
    Jiulu
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2003
    Location
    toronto
    Posts
    10

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by dba_sybase2003
    I did not mean to put that icon over there. It was a slip of the pointer.
    sorry again.
    Here is the dbcc 302 output after rebuild table.

    Thanks,

    Jiulu
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2003
    Posts
    18

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by jiulusun
    Here is the dbcc 302 output after rebuild table.

    Thanks,

    Jiulu
    The showplan show that the optimiser is reading the data from the disk and not the cache. Also, can you please run just showplan and not dbcc traceon's. Can you also post the query please??? How did you rebuild the table and is any of the table's partitioned???

    Thanks,

    Regards

    DB

  8. #8
    Join Date
    Jul 2003
    Location
    toronto
    Posts
    10

    Lightbulb Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by dba_sybase2003
    The showplan show that the optimiser is reading the data from the disk and not the cache. Also, can you please run just showplan and not dbcc traceon's. Can you also post the query please??? How did you rebuild the table and is any of the table's partitioned???

    Thanks,

    Regards

    DB
    Here is the showplan output and the query, tables are not partitioned, I dropped table and recreate table and index, update all statistics on the table.

    I wonder how you find out it's disk reading instead of cache read? (it seems to be true !!!) sorry I don't have many experience with Sybase Query plan.

    Thank you very much.

    Jiulu
    Attached Files Attached Files

  9. #9
    Join Date
    Jul 2003
    Location
    toronto
    Posts
    10

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by dba_sybase2003
    The showplan show that the optimiser is reading the data from the disk and not the cache. Also, can you please run just showplan and not dbcc traceon's. Can you also post the query please??? How did you rebuild the table and is any of the table's partitioned???

    Thanks,

    Regards

    DB
    Here is the query source code

    Thanks,

    Jiulu
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2003
    Posts
    18

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by jiulusun
    Here is the query source code

    Thanks,

    Jiulu
    Hey,
    I would create a ncindex on business.inactive,systemcodevalue.code,relationman ager.relationcode

    After that see whether the showplan has changed or not. Hope, this helps.

    Regards

    DB

  11. #11
    Join Date
    Jul 2003
    Location
    toronto
    Posts
    10

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by dba_sybase2003
    Hey,
    I would create a ncindex on business.inactive,systemcodevalue.code,relationman ager.relationcode

    After that see whether the showplan has changed or not. Hope, this helps.

    Regards

    DB
    Thanks for the input, unfortunately it does not work.

  12. #12
    Join Date
    Apr 2003
    Posts
    18

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by jiulusun
    I migrate my sybase12 on AIX 4.3 raw device to sybase12.5 on AIX5.1 jfs, one query is extremly slow then. from 4 seconds to 1 minute.

    I have "update all statistics" all involved tables, "data/procedure cache" is bigger, "dsync off" on sybase device. I assume query plan should not change.

    I also run sp_sysmon for a minute period , it persistently tell me on sybase12.5 "total disk i/o checks count" is 400000, and "network checks" is 80000. but on sybase12, those number is less than 500. I have tried to change "i/o polling process" from 10->100->10000, "runnable process search count" from 3->2000->20000. the same thing.

    I am really confused, what's wrong?

    Any idea?

    Thanks,
    Hi,
    Did you get to reduce the response time of the query which was slow?? I am curious how you solved it??????????

    Regards

    Jaideep

    Jiulu Sun

  13. #13
    Join Date
    Jul 2003
    Location
    toronto
    Posts
    10

    Red face Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by dba_sybase2003
    I have not solved it yet. Sybase Tech Support are working on this too. I short the time from 1 minute to 20 seconds by rewriting the query (the rewrite is suggested by Sybase SQL Expert), but that's not the solution for the serverwide query problem?

    Hope Sybase tech support will have the answer soon...

    Jiulu

  14. #14
    Join Date
    Apr 2003
    Posts
    18

    Re: query slow from 4 seconds to 1 minute after upgrade from sybase12 to sybase12.5

    Originally posted by jiulusun
    I migrate my sybase12 on AIX 4.3 raw device to sybase12.5 on AIX5.1 jfs, one query is extremly slow then. from 4 seconds to 1 minute.

    I have "update all statistics" all involved tables, "data/procedure cache" is bigger, "dsync off" on sybase device. I assume query plan should not change.

    I also run sp_sysmon for a minute period , it persistently tell me on sybase12.5 "total disk i/o checks count" is 400000, and "network checks" is 80000. but on sybase12, those number is less than 500. I have tried to change "i/o polling process" from 10->100->10000, "runnable process search count" from 3->2000->20000. the same thing.

    I am really confused, what's wrong?

    Any idea?

    Thanks,
    Hi,
    Did you get to reduce the response time of the query which was slow?? I am curious how you solved it??????????

    Regards

    Jaideep

    Jiulu Sun

  15. #15
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Did you make sure to alter the new memory management settings? There were a number of new changes in 12.5 which can have significant performance impacts.
    Thanks,

    Matt

Posting Permissions

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