1 2 Previous Next 29 Replies Latest reply: Apr 2, 2009 8:49 AM by 296950 Go to original post RSS
      • 15. Re: Parsing
        But these work only if people take care to follow a certain standard while writing these sql statements and use bind variables etc...
        Exactly.This is the only thing that has to be taken care.Being on the other side ofthe fence(in the db room),there is very little that can be done to make the queries similar.That too is possible in the case of literals where bind variables come for rescue.
        cant anything be done to avoid additional burden or hard parsing caused due to not following a standard for the sql statements ???? Cant everything be converted into upper or lower case then calculate the hash values for them ? Isn't that a feasible solution ?
        With the answer,"no it can't be", I guess it would be an additional burder over db as it would be the first step to compare the case of the statement than start doing the processing. This is something that would query take much longer time to parse.I am not sure about any other way than to put the queries in the stored programs so that their case is not tempered.As like its a proverb now,whatever is related to db, do it inside db.
        • 16. Re: Parsing
          Thanks for the replies Aman....But I still cant believe that with kind of memory,hardware and resources available for us Converting a sql statement into upper/lower case...then hashing it ..... would put additional burden on the database that it wouldn't be able to cope with .. ????
          • 17. Re: Parsing
            Tanel Poder
            That's why I brought out the other alternatives in my earlier post... the alternatives are even worse.

            And if you want to get performance, you need to design your application properly, e.g. open cursor for a statement once and just change the bind variable values and re-execute it later. If you have a cursor open, then in UGA there is a pointer directly to that open cursor in SGA, which means that you don't need to go through that hashing and hash chain traversing process anymore but can go and directly execute the cursor (as long as you reuse the open cursor handle, not parse a new statement every time).

            I think it was Tom Kyte who once said, that hard parses are very very very very bad, soft parses are just very very bad :) So keep your frequently used cursors open!

            Tanel Poder
            • 18. Re: Parsing
              Converting a sql statement into upper/lower case...
              Like I said in my earier post (which you apparently didn't read) just converting everything to uppercase may change the meaning of the query.
              • 19. Re: Parsing
                But oracle is not case sensitive...so is sql and pl/sql .
                Then how does it make a difference if you convert every statement to either upper or lower case and then hash and try to find out that sql in the library cache ?
                • 20. Re: Parsing
                  That explanation got me excited . But I guess I need to know more details regarding the same . Could you please provide links for the same .
                  Are there any really good sites where I can find more details on parsing in oracle .

                  So when you say soft parse can be bad it means you are closing your cursor "after each execution" even when you are using bind variables and hence lost a pointer/handle to that cursor (sql statement) ?
                  So what exactly do we have to do to keep that cursor running and open ?

                  Thanks Tanel !!!!!!!!!

                  • 21. Re: Parsing
                    But oracle is not case sensitive...so is sql and pl/sql .
                    Oh yeah?
                    SQL> create table "emp" ("ename" varchar2(20));

                    Table created.

                    SQL> create table "Emp" ("Ename" varchar2(30));

                    Table created.

                    SQL> desc emp
                    ORA-04043: object emp does not exist

                    SQL> desc "emp"
                    Name                    Null?    Type
                    ----------------------- -------- ----------------
                    ename                            VARCHAR2(20)

                    SQL> desc "Emp"
                    Name                    Null?    Type
                    ----------------------- -------- ----------------
                    Ename                            VARCHAR2(30)
                    If you had a sql statement like this:

                    select * from "Emp";

                    and forced it to uppercase:

                    SELECT * FROM "EMP";

                    The statement now points to a different table.
                    • 22. Re: Parsing
                      SORRY !!!!!! and thanks!!!! Should have crosschecked myself !!!!!!
                      • 23. Re: Parsing
                        Hi !!

                        SQL> create table emp(name varchar(9),sal number(9));

                        Table created.

                        SQL> create table EMP(name varchar(9),sal number(9));
                        create table EMP(name varchar(9),sal number(9))
                        ERROR at line 1:
                        ORA-00955: name is already used by an existing object


                        • 24. Re: Parsing
                          Oracle always store the object name as Upper case even if the object created as lower case,Until u specify "" in the object name.
                          create table emp(name varchar(9),sal number(9));

                          Table created.

                          SMALIK@ICDEV-db62-2053-1146960>select table_name from user_tables where table_name='EMP';


                          Elapsed: 00:00:00.06
                          SMALIK@ICDEV-db62-2053-1146960>select table_name from user_tables where table_name='emp';

                          no rows selected

                          create table "emp"(name varchar(9),sal number(9));
                          Table created.
                          select table_name from user_tables where table_name='emp';
                          • 25. Re: Parsing
                            And, to borrow a phrase from Cary Millsap, "A soft parse is a parse that never needed to happen in the first place."

                            • 26. Re: Parsing
                              Anything better that can be done to avoid a soft parse????!!!

                              • 27. Re: Parsing
                                A soft "soft parse"
                                • 28. Re: Parsing
                                  Sweet explanation Tanel :)

                                  Amardeep Sidhu
                                  • 29. Re: Parsing
                                    Converting everything, yes, that could change the meaning. You give one such example: '... from "Emp"' and 'from "EMP"'. But you're using quoted identifiers here. Ofcourse, any halfway decent SQL preprocessor/prettifier would not uppercase a quoted identifier. Same for (string) literals ofcourse.

                                    On first sight, it does seem very possible for a simple SQL preprocessor to:
                                    - Leave literals, quoted identifiers and comments untouched
                                    - Uppercase 'regular' text
                                    - Replace adjacent whitespace (space, tab, newline chars) by a single space
                                    - Maybe remove non-functional whitespace
                                    - Maybe change the names of bind values to system-generated names

                                    So, for example, the two statements

                                    select SALARY
                                    from "Emp"
                                    where DEPT = :1
                                    and NAME = 'John'


                                    select salary from "Emp" where dept = :dept and name = 'John'

                                    would preprocess to the same basic SQL statement (and following that, the same hash value).

                                    Ofcourse, this should be done before generating the hash value for the statement, thus making the whole "because Oracle uses hashes and not string comparisons for library cache lookup"-explanation from Tanel moot. Question remains how much extra CPU-time such a preprocessor would incur.

                                    Yes, in an ideal world, there would be no use for something like this, because statements are always generated by applications in a consistent way. But in the less than ideal world, I can definitely remember some cases were this could have helped...
                                    1 2 Previous Next