9 Replies Latest reply on Mar 20, 2013 12:58 PM by EdStevens

    Small Doubt about particular script.

    Nitesh.
      My oracle version is

      BANNER

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production


      My doubt is about performance .. I just want to know difference between this 2 queries as whether it gives any difference in performance or not...

      1. select global_name into gname from global_name; --- global_name column size is varchar2(4000) and gname which is a local variable whose size is varchar2(30) ...

      2.select substr(global_name,1,30) into gname from global_name;


      Kindly let me know as whether performance is better if we make use of 2nd query whereas result is same ...
      Regds..
        • 1. Re: Small Doubt about particular script.
          Karthick2003
          My doubt is about performance .. I just want to know difference between this 2 queries as whether it gives any difference in performance or not...
          The question is not about performance. Its about buggy code.
          1. select global_name into gname from global_name; --- global_name column size is varchar2(4000) and gname which is a local variable whose size is varchar2(30) ...
          This code will hit below error if the size of global_name.global_name exceeds 30.

          <pre>
          ORA-06502: PL/SQL: numeric or value error: character string buffer too small
          </pre>
          2.select substr(global_name,1,30) into gname from global_name;
          This will cut of a part of the string returning incorrect value.

          In both the case you get screwed UP!! So modify the size of the local variable to match the size of the column in the table. Better define the local variable as TABLE.COLUMN%TYPE.
          • 2. Re: Small Doubt about particular script.
            Nitesh.
            Tats what it wont exceed more than 30 its for sure as name of database wont be more than 30 and i user that %type only but i got to know as it will affect performance .. Is it so ???
            • 3. Re: Small Doubt about particular script.
              EdStevens
              180589 wrote:
              Tats what it wont exceed more than 30 its for sure as name of database wont be more than 30 and i user that %type only but i got to know as it will affect performance .. Is it so ???
              What prevents you from testing it to see for yourself?
              • 4. Re: Small Doubt about particular script.
                Frank Kulash
                Hi,
                180589 wrote:
                ... My doubt is about performance .. I just want to know difference between this 2 queries as whether it gives any difference in performance or not...

                1. select global_name into gname from global_name; --- global_name column size is varchar2(4000) and gname which is a local variable whose size is varchar2(30) ...

                2.select substr(global_name,1,30) into gname from global_name;


                Kindly let me know as whether performance is better if we make use of 2nd query whereas result is same ...
                The statements are the same, except that one calls SUSBSTR (global_name, ...) and the other just use global_name.
                Calling a function always takes some time, so the one that calls SUBSTR will be a little slower. How you get the rows is a lot more significant than what you do (such as calling SUBSTR) AFTER YOU GET THEM.
                • 5. Re: Small Doubt about particular script.
                  Nitesh.
                  Ofcourse i tested but and explain plan also same for both queries so only i came to forum to get more ideas from experts here if anything possible means ..
                  • 6. Re: Small Doubt about particular script.
                    EdStevens
                    180589 wrote:
                    Ofcourse i tested but and explain plan also same for both queries so only i came to forum to get more ideas from experts here if anything possible means ..
                    For that simple query, I doubt the explain plan would show any difference. If you want to measure actual performance, you'd need to trace the actual execution. Hint: google 'oracle sql trace'.

                    However, if the usage is as simple as the example you give, you have already spent many many orders of magnitude more time on this than you will ever save by choosing the faster (if any) of the two options. If a query returns the result set in 0.2 seconds, how much effort is justified to get even a 90% decrease in that time?
                    • 7. Re: Small Doubt about particular script.
                      Nitesh.
                      This one is just an example i have given you but the same query going to get execute in a procedure which is scheduled to run every hour and respective people will be receiving mails as who is accessing and on which environment in that way got it ... So only i am seeing performance .. Got it .. If u need i can post that procedure here ..
                      • 8. Re: Small Doubt about particular script.
                        Nitesh.
                        CREATE OR REPLACE PROCEDURE ap_pr_apex_workspace_challan (iv_d_sysdate IN DATE)
                        IS
                        gname VARCHAR2 (30); --- Let me know as why i cant use %type here as datatype.. I mean i used that but my sir asked me to user varchar2(30)

                        CURSOR main
                        IS
                        SELECT *
                        FROM apex_workspace_access_log a
                        WHERE a.workspace = 'CHALLAN'
                        AND a.application_id = '4550'
                        AND TRUNC (a.access_date) = TRUNC (iv_d_sysdate)
                        AND a.application_name = 'Oracle APEX Login'
                        AND NOT EXISTS (SELECT 'x'
                        FROM ap_tb_ip_valid_addr b
                        WHERE a.ip_address = b.ip_address)
                        ORDER BY access_date ASC;

                        iv_v_body VARCHAR2 (10000);
                        iv_v_header CHAR (1) := 'Y';
                        iv_v_sender VARCHAR2 (1000);
                        iv_v_recipients VARCHAR2 (1000);
                        iv_v_cc VARCHAR2 (4000);
                        BEGIN
                        SELECT sender, recipients, cc
                        INTO iv_v_sender, iv_v_recipients, iv_v_cc
                        FROM ap_tb_apex_workspace_email;

                        SELECT SUBSTR (GLOBAL_NAME, 1,30) --- Now i cant understand as why my sir asked me to give 30 here as no need to mention that know..
                        INTO gname
                        FROM GLOBAL_NAME;

                        FOR i IN main
                        LOOP
                        IF iv_v_header = 'Y'
                        THEN
                        iv_v_body :=
                        RPAD ('Access_date ', 22, ' ')
                        || ' '
                        || RPAD ('Application_name', 22, ' ')
                        || ' '
                        || RPAD ('User_name', 15, ' ')
                        || ' '
                        || RPAD ('IP_Address', 15, ' ')
                        || ' '
                        || RPAD ('Authentication_result', 25, ' ')
                        || CHR (10)
                        || CHR (10);
                        iv_v_header := 'N';
                        END IF;

                        iv_v_body :=
                        iv_v_body
                        || RPAD (TO_CHAR (i.access_date, 'DD-MON-YYYY HH24:MI:SS'), 22, ' ')
                        || ' '
                        || RPAD (i.application_name, 22, ' ')
                        || ' '
                        || RPAD (i.user_name, 15, ' ')
                        || ' '
                        || RPAD (i.ip_address, 15, ' ')
                        || ' '
                        || RPAD (i.authentication_result, 25, ' ')
                        || CHR (10)
                        || CHR (10);
                        END LOOP;

                        IF iv_v_body IS NULL
                        THEN
                        iv_v_body :=
                        'Sir,
                        No Users found other than config team members and DBA team members in '
                        || UPPER ('challan')
                        || ' workspace.
                        This is for your information';
                        END IF;

                        iv_v_body :=
                        iv_v_body
                        || CHR (10)
                        || CHR (10)
                        || 'Note: This is an auto generated email.Please do not reply ';
                        iv_v_body := iv_v_body || CHR (10) || CHR (10) || 'Regards,';
                        iv_v_body := iv_v_body || CHR (10) || 'PS DBA Team';
                        UTL_MAIL.send (sender => iv_v_sender,
                        recipients => iv_v_recipients,
                        cc => iv_v_cc,
                        subject => 'Monitoring '
                        || UPPER ('challan')
                        || ' workspace in '
                        || UPPER ('gname')
                        || 'environment on '
                        || TO_CHAR (SYSDATE, 'dd-mon-yyyy'),
                        MESSAGE => iv_v_body
                        );
                        END
                        /


                        Regards...
                        • 9. Re: Small Doubt about particular script.
                          EdStevens
                          180589 wrote:
                          This one is just an example i have given you but the same query going to get execute in a procedure which is scheduled to run every hour and respective people will be receiving mails as who is accessing and on which environment in that way got it ... So only i am seeing performance .. Got it .. If u need i can post that procedure here ..
                          OK -- and referencing your next post, with the full procedure ...

                          If an operation is performed many, many times -- like in a loop -- even small individual gains can add up. But that is not the case here. I'd say your question is reduced to one of academic curiosity. You have already spent more of your time on this question than this query will account for within your lifetime. To put it in accounting terms, you have already passed the point to which there will ever be a return on your investment. In fact, you passed that point somwhere between the time you started writing your first post and the time you pressed the "Post Message" key on that first post.