This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Jun 2, 2008 11:47 AM by Aravind N Go to original post RSS
  • 15. Re: Query Hangs while Parsing
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    few additions: the V$SESS_TIME_MODEL corresponding stats for parsing are "parse elapsed time" and "hard parse elapsed time".

    Btw, an easy way for sampling such performance stats is by using my Snapper script ;-)

    http://blog.tanelpoder.com/2007/12/06/oracle-session-snapper-v106-released/
  • 16. Re: Query Hangs while Parsing
    Aravind N Newbie
    Currently Being Moderated
    V$SESS_TIME_MODEL query returned 0 for all the stats even after minutes


    SID STAT_ID STAT_NAME VALUE
    ---------- ---------- -------------------------------------------------- ----------
    223 3649082374 DB time 0
    223 2748282437 DB CPU 0
    223 4157170894 background elapsed time 0
    223 2451517896 background cpu time 0
    223 4127043053 sequence load elapsed time 0
    223 1431595225 parse time elapsed 0
    223 372226525 hard parse elapsed time 0
    223 2821698184 sql execute elapsed time 0
    223 1990024365 connection management call elapsed time 0
    223 1824284809 failed parse elapsed time 0
    223 4125607023 failed parse (out of shared memory) elapsed time 0
    223 3138706091 hard parse (sharing criteria) elapsed time 0
    223 268357648 hard parse (bind mismatch) elapsed time 0
    223 2643905994 PL/SQL execution elapsed time 0
    223 290749718 inbound PL/SQL rpc elapsed time 0
    223 1311180441 PL/SQL compilation elapsed time 0
    223 751169994 Java execution elapsed time 0
    223 1159091985 repeated bind elapsed time 0
    223 2411117902 RMAN cpu time (backup/restore) 0
  • 17. Re: Query Hangs while Parsing
    jgarry Guru
    Currently Being Moderated
    There is a bug fixed in 10.2.0.4 having to do with rollup and group by, maybe they accidentally fixed your problem with it.

    Bug 5942097, not publicly accessible, of course.
  • 18. Re: Query Hangs while Parsing
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    Is your statistics_level = basic by any chance?

    You need to have it at least TYPICAL for V$SESS_TIME_MODEL to be populated.

    Anyway, a stack trace would tell you definitevily where in kernel codepath your process is spending its time.

    --
    Tanel Poder
    http://blog.tanelpoder.com
  • 19. Re: Query Hangs while Parsing
    26741 Oracle ACE
    Currently Being Moderated
    Interesting. I didn't know about bugs which cause Oracle to go into a loop during
    the parse phase itself !
  • 20. Re: Query Hangs while Parsing
    jgarry Guru
    Currently Being Moderated
    :-) I don't know that that is what is really happening, Tanel's advice is good for that. The implications I was making were that it should be tested in the latest release, and we can't really know what unpublished bugs are doing or what their patches are really fixing (without some in depth research, anyways). But we can see that various things with analytics are fixed at various times.

    Also see Note:285913.1 suggests Alter session set events '10119 trace name context forever, level 12'; of course, that's about an ORA-600, but hey, we're in discovery mode here, trying to figure out if that analytics problem may be related to this one.
  • 21. Re: Query Hangs while Parsing
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    Time model stats should allow you to measure and confirm where the time is spent. Otherwise taking few stack dumps will definitely show you where the execution is....
  • 22. Re: Query Hangs while Parsing
    Aravind N Newbie
    Currently Being Moderated
    Hi,

    The stack trace is as follows

    21820: oracleCSS1CLDR (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
    00000001021f5f58 kkosbn (ffffffff7ad79910, ff, ffffffff7ad96f58, ffffffff7ad96f58, 10000, ffffffff7ad96f58) + 2d8
    00000001021eb278 kkobmp (ffffffff7ad790f0, 10000, ffffffff7ad73e18, 0, 0, ffffffff7ad797a0) + 4d8
    00000001021ad348 kkotap (0, 0, 0, ffffffff7ad79910, ffffffff7ad73e18, ffffffff7ad790f0) + 4e8
    000000010218fd4c kkojnp (382660a28, 0, ffffffff7ad790f0, 10218f160, 1065327d0, ffffffff7ad797a0) + bac
    000000010218e828 kkocnp (106400, 200, ffffffff7ad790f0, 1, 0, 1065327d0) + e8
    00000001021757a4 kkooqb (102174, 0, 3, 0, ffffffff7af15d00, ffffffff7ad790f0) + c04
    0000000102165ac0 kkoqbc (0, ffffffff7af15d00, 0, 1065327d0, 6, 102165380) + 720
    00000001022f3700 apakkoqb (ffffffff7fff6810, ffffffff7af15d00, 3843e26e0, 0, c200080, 0) + 80
    00000001022f4110 apaqbd (ffffffff7fff6810, ffffffff7af15d00, 3843e26e0, ffffffff7ad7fd38, 381cbf738, 200000) + 170
    00000001022f1014 apadrv (3843e26e0, 106532, ffffffff7af43f28, 106531b28, ffffffff7af15d00, 106532) + 294
    0000000102529dc8 opitca (3, 3843e26e0, ffffffff7af43f28, 0, 1065327d0, 106525000) + 9c8
    00000001019f1f8c kksFullTypeCheck (ffffffff7fff7b70, 1056d8c28, 106525898, ffeffc00, 1065253d8, 1056d8c18) + c
    000000010034c894 rpiswu2 (0, 106525898, 0, ffffffff7fff72a8, 1056d8000, 106525) + 1f4
    00000001019f8ca8 kksLoadChild (106400, ffffffff7fff7b70, ffffffff7af43f28, 106525898, ffffffff7fff91c0, 3843e26e0) + 22e8
    00000001018f767c kxsGetRuntimeLock (0, 0, 180, 3834b2578, e9e733ed, 106525898) + 5bc
    0000000101a18f24 kksfbc (106531, 0, 108, 0, 0, 382d0d758) + 3de4
    0000000101a12618 kkspsc0 (1000, fffdffff, 0, 108, 10000000, 0) + 1838
    0000000101a13844 kksParseCursor (ffffffff7fffa8b8, 3c305e100, 0, 0, ffffffff7af32260, 3) + e4
    0000000102515d3c opiosq0 (1056d8c18, ffffffff7af32260, 1056d8, 20a, 1056d8000, 20b) + 85c
    00000001024b1078 kpooprx (ffffffff7fffe07c, ffffffff7fffbec0, 106400, ffffffff7fffabd0, 0, 0) + d8
    00000001024ad6d8 kpoal8 (ffffffff7fffbe88, 106528, a4, 8061, 1, 106532860) + 2b8
    0000000100346ccc opiodr (1057687c0, 5e, 106525600, 1, ffffffff7af26738, 106528) + 60c
    0000000103d74124 ttcpip (34, ffffffff7fffcb50, 1056beb2c, 1056bc310, 0, 106525718) + 504
    0000000100341238 opitsk (106532868, 1, 0, 106532860, 105882f78, fffffffd) + 598
    0000000100345888 opiino (106400, 106532860, 0, 40002801, 106400, 106532860) + 468
    0000000100346ccc opiodr (1065319d8, 3c, 106400, 1065327e0, 106532, 106528) + 60c
    000000010033f660 opidrv (106534f60, 0, 3c, 106531c98, 3c, 0) + 380
    0000000100339c30 sou2o (ffffffff7ffff2a8, 3c, 4, ffffffff7ffff2d0, 105e0c000, 105e0c) + 50
    00000001002fbf5c opimai_real (2, ffffffff7ffff3a8, 104050b4c, 1064cd848, 247cbfc, 14800) + 7c
    00000001002fbe18 main (2, 8, 0, ffffffff7ffff3b8, ffffffff7ffff4c8, ffffffff7b500140) + 98
    00000001002fbd3c _start (0, 0, 0, 0, 0, 0) + 17c                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 23. Re: Query Hangs while Parsing
    Aravind N Newbie
    Currently Being Moderated
    Yes, we have put statistics_level to basic. I have posted the stack trace and it shows the kksParseCursor function. Can you tell me what can be interpreted form this?
  • 24. Re: Query Hangs while Parsing
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    Hi Aravind,

    Yes, this process is hard parsing.

    (reading the stack from bottom up):

    kksParseCursor shows you are parsing.
    kksLoadChild you are hard parsing (loading a new child cursor to library cache)

    apa* functions are hard parse driver functions
    kko* functions are ones who are responsible for query compilation, transformations and validations etc and it looks like that's where the loop is happening (if these functions are on top of the stack in most of samples taken).

    As I said previously, parse times in minutes mean you've hit a bug, so Oracle support should be able to help you (send them the same stack trace and optimzer environment trace/10053).

    If you want to experiment around you could set some optimizer's cost based transformation parameters fals and see if you still hit this bug.

    Like for example set "_optimizer_cost_based_transformation"=false and set "_optimizer_push_pred_cost_based"=false .

    However I'm pretty sure it's a known bug by now so the best thing to do is send this information to Oracle Support!

    Cheers,
    --
    Tanel Poder
    http://blog.tanelpoder.com
  • 25. Re: Query Hangs while Parsing
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    Also you can set statistics_level=typical at your session level for the experiment. And then query V$SESS_TIME_MODEL.

    However this should not be needed as the stack trace already has proven (for sure) that the process was in parsing code when the stack sample was taken.

    --
    Tanel Poder
    http://blog.tanelpoder.com
  • 26. Re: Query Hangs while Parsing
    Aravind N Newbie
    Currently Being Moderated
    Thanks Tanel Poder,

    I will create an SR and see what Oracle Says.

    Thanks a lot.
    Aravind
  • 27. Re: Query Hangs while Parsing
    Tanel Poder Oracle ACE Director
    Currently Being Moderated
    Please let us know how it turns out :)

    I wrote a blog entry about this too:

    http://blog.tanelpoder.com/2008/06/03/advanced-oracle-troubleshooting-guide-part-4-diagnosing-a-long-parsing-issue/

    cheers :)
  • 28. Re: Query Hangs while Parsing
    Aravind N Newbie
    Currently Being Moderated
    Hi Tanel Poder,

    As expected, the Oracle pointed to a published bug (Bug:5667683 PARSE QUERY SPIN IN KKOSBN)

    Thanks for the Help. A nice blog as well :)

    Aravind
1 2 Previous Next