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

    Unanswered: Optimize query in 817

    I have a table which has 3.500.000 + records and growing by 20k every day.

    This table has an index on the timestamp, also it has an index on type.
    There are approx 140 different types of which i need to know about only 13 the last time one of those 13 happened.

    A "select max(timestamp) from table" takes about 31 milliseconds, no problem there....

    But i need it for 13 types only, so i am doing a "select max(timestamp) from table where Type in (.... 13 types ...)" this takes 5 seconds, which is to long.

    I cannot seem to trim down the time in any way.... Also i have tried to limit the search to today adding " and timestamp > trunc(sysdate)" to the where clause. This did not do anything for the speed, if anything it became even slower.

    My question by now may be obvious. Is there any hint or anything I can use to speed things along?
    I cannot do anything about the table, size and indexes. It is part of a 3rd party package for which we only have select rights. I allready suggested to them to add a trigger to the table but that suggestion was shot down (hard).

    Regards & TIA

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The obvious thing is to have an index on (type, timestamp). Also at that size partitioning would be a good option.

    Anyway make sure it is using the type index if not put an index hint in and see if it helps. Also is the table/indexes being analyzed regularly?

    Alan

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

    Lightbulb

    Hey allan,

    Thanx for responding, We have had this a while now and did have the entire server (or schema atleast) re-ananlyzed, did not help any

    Creating a new index is not an option to the provider, the package is as is...

    I tried forcing the index beeing used on using with the " and timestamp > trunc(sysdate)" We only need it anyway for max today. So I thought this would help a lot, however appearently it worked the reverse, it slowed down...
    I used:
    Select --+ Index_desc(tablename indexname)
    ....
    from ....

    You however did get me thinking down another road which i just tested....
    I used a subselect!
    Code:
    Select 
    from     (select  --+ Index_desc(tablename indexname)
                  * 
              from table 
              where timestamp > trunc(sysdate))
    where
        Type in (.... 13 types ....)
    Now it flies! in 30 millisecs You gotta love the forum thanx man

    I gotta get that tuning stuff down, performance is KEY a lot of the time. And appearently its one thing to get something working then another to get it working proper like!

    Greets

    One happy camper

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    what if one fo the types does not have an entry for "today"?
    or
    what if it is 12:05 AM? do you only want the types that have an entry in the last 5 minutes (which may be none)?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Yep all that...

    if it is 12:05 am i only want/need the previous 5 minutes
    If none have an entry then none have an entry. This query is part of a function, a null will return the default value "none".

    Regards

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Just two suggestions.

    You only need "timestamp" and "type" in your subquery, so you should only select timestamp, not * . This is a bad habit to select * even if you only need 1 or 2 columns. There is no problem here if you only select max(timestamp) in your outer query, but in general, try to avoid select *. Always try to send only the relevant data to the client. The network as well as the client will be grateful .

    Another point, what does EXPLAIN PLAN say about the execution plans when it did not work well, and now that it does ? Does the hint really improve things ? Except for a few exceptions, I agree with Tom Kyte about the overuse of hints : only some hints should be used, and only with parcimony. Here is Tom Kyte's good hints list :

    ALL_ROWS
    FIRST_ROWS(n) or FIRST_ROWS
    CHOOSE
    (NO)REWRITE
    DRIVING_SITE
    (NO)PARALLEL
    (NO)APPEND
    CURSOR_SHARING_EXACT
    DYNAMIC_SAMPLING
    CARDINALITY

    These are only hints that give more information to the optimizer so that it can choose the best plan on its own, not ones that force the optimizer to choose a particular plan (which will very often not be the best one). Yours try to force the use of a particular index, which should not be necessary if stats were rightly collected and up-to-date.

    Concerning you other thread, have you checked that you are using the CBO and not the RBO ?

    Regards,

    RBARAER

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    One other point, The vendor that provided the package will not build a new index to help you out, however no application code needs to be changed to add an index to see if it will help. Have your own DBA insert the index and see what happens. If it adversly effects the application, have him remove the index. The database is (i assume) yours, not the vendors. If it is provided by the vendor, then never mind.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Have to agree with beilstwh, adding indexes wont affect application code in terms of results so you should be able to add them. After all does your vendor vet all your database configuration parameters or OS configuration???

  9. #9
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Quote Originally Posted by RBARAER
    This is a bad habit to select * even if you only need 1 or 2 columns. .... Always try to send only the relevant data to the client. The network as well as the client will be grateful .
    I offcourse agree, but to stamp out the whole sql here is "to much" i think. I do need more fields for the output .... Also the * pertains to the subquery which should only be executed on the server, not dealing with the network.
    I do know that using a * is "wastefull" versus a full listing of all fields, it will take an extra 0,1 of a millisecond (or something like that...
    Quote Originally Posted by RBARAER
    Another point, what does EXPLAIN PLAN say about the execution plans when it did not work well, and now that it does ?
    No EXPLAIN PLAN Its a third party package, no tables for explain plans as yet and we only have select rights They seem to agree on one thing, they should do as much as the can to "block" the inhouse developers. Also I have not worked (at all or very little) with Explain plans and am not quite sure how to read them and if I did I would still have the problem "what is the proper solution" to help Oracle make a better "execute plan"
    Quote Originally Posted by RBARAER
    Does the hint really improve things ?
    Yes it does, without the hint the query takes 5 seconds, with the hint 30 millisecs. I would definately say IT DOES
    Quote Originally Posted by RBARAER
    Except for a few exceptions, I agree with Tom Kyte about the overuse of hints : only some hints should be used, and only with parcimony. Here is Tom Kyte's good hints list :

    ALL_ROWS
    FIRST_ROWS(n) or FIRST_ROWS
    CHOOSE
    (NO)REWRITE
    DRIVING_SITE
    (NO)PARALLEL
    (NO)APPEND
    CURSOR_SHARING_EXACT
    DYNAMIC_SAMPLING
    CARDINALITY

    These are only hints that give more information to the optimizer so that it can choose the best plan on its own, not ones that force the optimizer to choose a particular plan (which will very often not be the best one). Yours try to force the use of a particular index, which should not be necessary if stats were rightly collected and up-to-date.

    Concerning you other thread, have you checked that you are using the CBO and not the RBO ?

    Regards,

    RBARAER
    Who is Tom Kyte? (or is that a dumb question?) Where would i be able to read more about these hints and for example HASH, which i have used succesfully in the past.

    Stats, I dont think they are updated but then again i would not know how to check that. Then again, its a third party package, one would think they would have "their shit together". e.g. we are not the only ones using it. Tho we may be their biggest/baddest user of the system (meaning the most users and the most data and the most systems "hacking" into theirs to retrieve data)

    In the intrest of keeping a thread to a thread I posted something back in the other thread about CBO and stuff....

    Lastly the hint does make sence to me, the index is sorted Ascending. With 3.5+ mio records and at the moment 20+k new records a day (and growing) I can emagine an asc search will take "a while" to get to the last day. Doing a desc search on same index should be (much) faster.

    By the way, the developers of the package had something simular in their package but they took it out. Why? Because their query took well over 20 secs!!! to run. So I was feeling OK with the 5 secs beeing 25% of their time, (even so, I felt it could be done even better re: this thread) but now I can really "rub their nosses in it" and maybe even "sell it" to them. "You can have the query if you do this this that and that for me"

    Regards

    The Mailman

  10. #10
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Quote Originally Posted by beilstwh
    One other point, The vendor that provided the package will not build a new index to help you out, however no application code needs to be changed to add an index to see if it will help. Have your own DBA insert the index and see what happens. If it adversly effects the application, have him remove the index. The database is (i assume) yours, not the vendors. If it is provided by the vendor, then never mind.
    I agree, but.....
    they are a pain and a big one at that. (read my prev post) I could maybe have a real DBA add an index, but I had some stuff in place previously. Then they had a new version and "cleaned out" all my stuff. I feel i would have to rebuild everything everytime they come here for some little problem. Because they allways start by blaming our stuff. Then remove that only to notice, it was not our stuff. But some unforseen cercomstance or something. If it were up to me ..... ..... The DB is provided by the vendor, officially it is supported by our DBA(s) and us, but they dont want us to touch it!!

    Did you notice yet that i am more than a little upset about this ?

    Regards

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

    There was some discussion about Tom Kyte's "good and bad" list some time ago, check this thread if of any interest - http://www.dbforums.com/t1073878.html

    I do understand your frustrations with the vendor, but look at it from their point of view. You don't know how to execute EXPLAIN PLAN and (I assume) how to interpret them. You also don't know how to find out if you're using the CBO or the RBO. You think index_desc will be quicker than index_asc (because the rows are recent) which suggests a lack of understanding about how BTree indexes are formed/structured (we are assuming they're BTrees).

    If it is at all possible, trying to foster an environment of co-operation between you and the vendor would be far more productive. My application customers often come to me with report queries and I am more than happy to help - what i cannot do though, is educate them about how or why my changes are 'better'.

    If someone were to gather statistics the traditional route (ie outside of my presupplied package to do so) it would almost certainly kill my application performance. I don't have time to explain the details of this to the customers. In this case a little knowledge is dangerous.

    I'm just playing devils advocate here, at the end of the day your firm has requirements, if the vendor won't help then you're the poor sod stuck between a rock and a hard place.

    Incidentally, an intro to explain plans and statistics here... http://www.billmagee.co.uk/oracle/sqltune/index.html. Experiment with this stuff on a test/backup schema though :-)

    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
    Aug 2004
    Location
    France
    Posts
    754
    OK concerning select *, it was just to be sure .

    Who is Tom Kyte? (or is that a dumb question?)
    No, no question is dumb, and this one is really not dumb .Thomas Kyte is one of the top Oracle experts. It was The_Duck on this forum that pointed me to 2 of his books : "Expert One on One Oracle" and "Effective Oracle By Design", but have just read 60% of the second one (big books ). I think it was AlanP who pointed me to the asktom website : here. I really recommend you to check it : it is an incredible source of very interesting info about many Oracle issues. There are many other Oracle experts, but from what I've read so far, I really appreciate Tom Kyte's explanations, proof by example and his will to kill many old Oracle myths with numbers. And "Effective Oracle by design" is GREAT : it's a "summary" of do's and don'ts about building Oracle applications. I love his way to write, explain and prove things. Well it's just my opinion , and I'm not paid a lonely cent to say that .

    I'd say just read some articles on asktom and judge by yourself.

    Concerning hints, of course Tom Kyte supposes that you use the CBO (I'm not sure it's your case) and stats are up-to-date (not sure either). What he says is : use the CBO, help it as much as you can, but don't force it. And if it still does not make the best decision for performance, then it has a good reason (according to it) to do so. At this point, according to Tom Kyte, it's better to understand WHY the optimizer took that decision and help it a little more than simply force it to always do something which, maybe, will not be the right way at all in some months, simply because it will follow the hint instead of adapt to new situations (data distribution...). Of course we speak in a general way, and there are always exceptions, but exceptions are not the rule, so when deciding to use "bad" hints, be sure that it's the last thing you try to improve things : your last resort.

    Now, if you use the RBO or can't insure that stats are up-to-date because of your vendor, then I think that some "bad" hints don't become so bad, because :

    - The RBO won't ever adapt to new situations except when you add indexes or so (no info on data distribution, cluster factor...)

    - Bad stats may completely flaw the CBO choices

    In your case, hints may well be your last resort because it's about the only thing you have control on . Beware when your vendor will come next time though, because they will certainly blame your hints as the cause of THEIR app not to work well, you EVIL developer .

    Regards,

    RBARAER
    Last edited by RBARAER; 02-01-05 at 07:01. Reason: Use of hints

  13. #13
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Quote Originally Posted by billm
    I do understand your frustrations with the vendor, but look at it from their point of view. You don't know how to execute EXPLAIN PLAN and (I assume) how to interpret them. You also don't know how to find out if you're using the CBO or the RBO. You think index_desc will be quicker than index_asc (because the rows are recent) which suggests a lack of understanding about how BTree indexes are formed/structured (we are assuming they're BTrees).
    I agree, i dont have enough knowledge on explain plan and tuning. On the other hand I am OK in building an SQL and have not produced a cartagian product in years (in contrast to other people i know). To tune my queries i would need some form of "formal" education or atleast someone to help me along. I have been trying to read up and such but the time is simply lacking and my employer is unwilling to 'invest' in this.
    On the otherhand "they" dont seem to be any better at this then i am. If at all i feel convident that i am the more experienced person in this.

    Quote Originally Posted by billm
    If it is at all possible, trying to foster an environment of co-operation between you and the vendor would be far more productive. My application customers often come to me with report queries and I am more than happy to help - what i cannot do though, is educate them about how or why my changes are 'better'.
    Agree again and i do, "one can catch more flys with honey than with vinager". However they seem uncooperative even when I am trying to help them....

    Quote Originally Posted by billm
    If someone were to gather statistics the traditional route (ie outside of my presupplied package to do so) it would almost certainly kill my application performance. I don't have time to explain the details of this to the customers. In this case a little knowledge is dangerous.
    On a full blown oracle server one or 2 "passable" queries of max 30 secs should not be that big a deal should it? I and my colleage are the only ones doing so and allways "off peak" hours as well. Well over 70% of all transactions on this system are compressed withing 3 or 4 hours out of the 12.
    Anyway, we have been granted the select access. Ergo we are using it...

    Quote Originally Posted by billm
    I'm just playing devils advocate here, at the end of the day your firm has requirements, if the vendor won't help then you're the poor sod stuck between a rock and a hard place.
    Yep thats me "the poor sod"

    Quote Originally Posted by billm
    Incidentally, an intro to explain plans and statistics here... http://www.billmagee.co.uk/oracle/sqltune/index.html. Experiment with this stuff on a test/backup schema though :-)
    Yep a test environment thats what i have been asking / demanding for a while now (meaning well over a year ) still have not got one still trying tho

    All this makes me happy tho that my co-workers at this nice forum are here to help me out where my knowledge is lacking and to sometimes take the flack for me beeing angry with other party's /people....

    Regards

    The Mailman

  14. #14
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Quote Originally Posted by namliam
    I have been trying to read up and such but the time is simply lacking and my employer is unwilling to 'invest' in this.
    That's a shame. If it's something you're willing to do off your own back you can download a free Oracle Personal for your own use/testing.

    Quote Originally Posted by namliam
    On a full blown oracle server one or 2 "passable" queries of max 30 secs should not be that big a deal should it?
    No, that shouldn't bother Oracle. In my earlier post about gathering statistics outside the app, it would have an ongoing effect on performance - not just a short term problem.

    Quote Originally Posted by namliam
    Yep a test environment thats what i have been asking / demanding for a while now (meaning well over a year ) still have not got one still trying tho
    I would suggest a test environment is a priority. You really, really shouldn't develop you queries on production - albeit only select statements. It is possible to bring Oracle to it's knees with approximately 5 lines of code. I've done it in the past, but luckily on a DEV system, not production.

    Quote Originally Posted by namliam
    All this makes me happy tho that my co-workers at this nice forum are here to help me out where my knowledge is lacking and to sometimes take the flack for me beeing angry with other party's /people....
    If you were to install Oracle personal on your own PC or at home, I'm sure people here could talk you through (or point you at the right information) to take an export of the production database, and then import into your own. Assuming total data size isn't horrendous etc. Possibly your dba would do the export for you?

    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.

  15. #15
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Without a test environment things are very difficult

    However if their app is very slow you have no alternative but to try out stuff on prod. However before you do that
    1) get a tool like TOAD which will help you tune your queries much more easily if you havent already.
    2) Understand execution plans
    3) Does your app vendor understand databases, I've come across numerous cases where the developers havent a clue about Oracle. Also how the hell do they know how to tune YOUR schema/database unless they are administrating it themselves. The thing with Oracle is if your using the CBO Oracle will decide how to run queries DEPENDING on what data is in there (something the developers arent likely to know beforehand).
    4) You need to work with your DBAs to do tuning or what are they being paid for.
    5) If you app vendor is still reluctant to let you do stuff then ask them to tune it

    So what I would suggest is to try out things which you can easily back out i.e. indexes are quite easy to create or to drop. Then if they complain backout your changes, if the problem goes away identify which change caused the problem, if it doesnt its their fault.

    Ideally you would have a test system with production data where you can try out stuff before PROD but if you dont have that then you have no choice.

    Alan

Posting Permissions

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