Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Unanswered: A question for you dba's out there

    Is there some service / driver whatever that has anything what so ever to do with ODBC connections to Oracle?!

    Possibly in a default of 2 minutes?

    I have finaly found that after some people (our dba's) did some work on the server "all of a sudden" next morning my ODBC dont work anymore as fast as they used to.
    Reaction time went from less than 1 sec till max 10 secs per ODBC to 1:30 till max 2:00 response time. Where Oracle is kicking my but out at the 2 minute limit, causing my access app to crash...

    Anyone PLZ?

    Regards

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    What kind of work did they do ??? What version of Oracle and ODBC drivers are you using ... If you are using Oracle 8.1.7 ... You need to download the "Patched" ODBC drivers from Metalink ... 8.1.7.4

    HTH
    Gregg

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    There was some work done to upgrade both front and backend.... to a newer version of the software on the Oracle DB.

    It is 8.1. something...

    Not sure what ODBC drivers, XP default?!
    Point is we didnt change anything on our side, except for the upgrade. I am guessing here, my knowledge of oracle server is to limmited. Allthought i am a knowledgable user, building queries even PL/SQL. Using odbc and stuff, but server? Not my peace of cake.... but the upgrade happening overnight and my users having problems the next day... to much of a happy cercumstance...

    Regards

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Quote Originally Posted by gbrabham
    What kind of work did they do ??? What version of Oracle and ODBC drivers are you using ... If you are using Oracle 8.1.7 ... You need to download the "Patched" ODBC drivers from Metalink ... 8.1.7.4

    HTH
    Gregg
    It indeed is 8.1.7 (i think, thats what it says in the sql/plus anyway.

    Where do i get the Patched ODBC drivers from Metalink?

    Regards

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Go to Oracle's Web site and look for downloads ... most patches are found on the OTN side ...

    HTH
    Gregg

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    It could be code changes causing the slow performance, statistics changes. You better get the slow queries, run the explain plan to see what is the excution plan before messing with the ODBC driver. If no one has access to the Oracle installation the ODBC driver might be intact.

    But if you still want to get the latest driver, check this location: http://otn.oracle.com/software/tech/...dbc/index.html


    HTH,

    clio_usa - OCP 8/8i/9i DBA

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    It took some doing but finaly got the latest drivers installed. Still no success, I have been looking at the runtime it takes for an entire batch. This has been multiplied by 4! ! ! ! ! ! ! ! ! ! ! If one batch used to take 15 minutes, it now takes OVER an hour! (in A97)

    I am guessing someone did some tinkering with some table somewhere bodging the Analysis of the tables or causing some index to go haywire....

    What would make an index go crazy? Or cause any other slowness in querying multiple tables in 1 query? Indexes and stuff right? So this is where i am shooting my arrows at this moment. Tho the DBA's keep saying "we didnt do anything to the tables", evenso I have not been trusting these people for a while now. I dont have enough knowledge and/or rights on the system to check, check, double check and will have to take them on their word.

    So anymore ideas out there?! Please keep em comming, cause this is a major pain in the royal hiny.... The users are on XP and that dont work no more, so i have to go back to the old A97 to get were we need to be....

    Please?!

    Thanx

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    As CLIO_USA said ... Now you need to run explain plans on the queries .... take the 1 you mentioned for example ... If you can, run it thru SQLPlus setting autotrace on and look at the path Oracle is now taking to return the result set. You may find that it is no longer using indexes ... Post the explain plan if you need to ... Also, have the statistics been updated since the upgrade ? You may find the the upgrade has changed the queries and now they are using a different "Driving" table ... Are you getting "disk" sorts as opposed to "memory" sorts ????

    Gregg

  9. #9
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Hmz, yes those darnest traces... I had been thinking that way as well. Only this morning did i get my user account (or 1 of them) the proper rights to produce them....

    so i looked at the execution plan, and i am no wizard at that (see this thread i started just for that), but i found a TABLE ACCESS (FULL) on the table i am querying. The field i am using in the where cluase IS INDEXED, but its not using it.... How can that be?

    I got mad a bit and someone asked me: Have you tried used oracle hints?
    I said, never have and never should. Thats what the optimizer is for isnt it?
    After a long talk, we tried the hint '/*+ ORDERED */' this seems to have increased the reaction time from allmost 2 minutes in SQL*Plus to UNDER A SECOND for the same query

    Tho i am very happy to have it working agian (saves me an hour work every day), I am still not confinced at the reached solution. Why o why do i need to resort to hints.... (I obviously dont know all the hints, cause Ordered is new to me, is there a list somewhere?)

    Thanx for your input guys, this oracle wanabee has learned a few new tricks. Now if only my company would fork over some money for me to become a really trully Oracle DBA like person...

    Thanx again

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    I concur with Clio and Gregg, it sounds as if the table and statistics got dropped. Have you got a DEV or TEST instance where you can experiment with dropping and then re-creating the statistics?

    Cheers
    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.

  11. #11
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    A t... what? A D... what? We dont have those, only production!

    Why do you think this poped up? Cause we didnt test! DUH! but we dont need to test do we.... *ahum* ........

    Then yet another question, what happenes to oracle hints through odbc? The query's run under a second in sql*Plus, but now with the hint it has been running ODBC for over 5! minutes.... Is there no end to my suffering?

    Regards

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Is it possible that the ODBC driver is stripping assumed comments from the SQL being passed? The hint is of course within a comment?

    Try this
    SELECT /*mycomment*/ * from sometable
    through ODBC. Then (if you have access to sys or select privs on SYS.V$SQLAREA...

    select * from v$sqlarea where instr(sql_text,'mycomment') > 0

    You should see an entry for your initial query. If not, then it would appear that the hints are being stripped.

    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.

  13. #13
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    The Ordered hint tells the optimizer to look at the tables in the order they are given in the query ...
    ex: 3 tables ... 1 with 10 rows, 1 with 100 rows and 1 with 1000 rows....
    You would like to have the table with 10 rows and the driving table to the other 2...

    You can find information on hints in the Oracle Doc set - Performance manuals...

    I agree with you ... In most cases, you shouldn't have to use hints... but there are occasions where you really do what the optimizer to look at the query path a different way ...

    Gregg

  14. #14
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Thumbs up

    When using hints through a clean ODBC i freezzzzzz up on any query...

    I am running multiple, but only one is giving me headackes...

    I have thought up my own workaround. It sloppy but seems to work.
    I query the *blieping* table ONLY i need for my where, get the Primary Key. Then do another query with the primary key, this is lightning fast!!!
    If you have any idea then please explain... cause i surely dont....

    I have just done a test on A97 (cause atleast that still works good old 97 ) and now using this newly thought up workaround it seems like processtimes are back to normal ---> or even faster than used to be....

    There must be something of with this one table, i am sure. But the DBA is saying no... Users are also starting to complain about slowness in the system, he has to go belly up (I hope). Then they can make me DBA and double my salary cause i feel like an ass when my apps bug because of an error of the DBA which he wont even confes to...
    Hey dont get me wrong, we all make mistakes... no biggy, just take your responsibility...

    BillM, I dont see the query... so its probably stripped...

    So its about quiting time for me, I will go home and sleep on it. Then tomorrow try to finish the rebuild (i did it Q&D to see if it would work). Then try it in XP. Keep your fingers crossed for me will you?!

    Also if anyone has any ideas on how i can do anything to proof my point to the people in charge & DBA. Or any ideas on how to get around this latest bug... please dont hesitate to add to this thread....

    Thanx again (so far)

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

    Execute this query, the last three columns should all be populate if statistics exist, or will be empty if they've been dropped.

    Code:
    select 'TABLE' as otype, 
           table_name as oname,
           num_rows,
           sample_size,
           last_analyzed
    from user_tables
    union all
    select 'INDEX' as otype,
           index_name as oname,
           num_rows,
           sample_size,
           last_analyzed
    from   user_indexes
    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.

Posting Permissions

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