Tracing and performance hit
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Tracing and performance hit

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    Tracing and performance hit

    Hi ,

    Could somebody tell me, how much of impact will the tracing have on the entire transaction.
    I have a update sql, which updates 40M records, what will be the performance hit on the update to enabling the tracing.

    Thanks
    Badrinath
    There is always a better way to do the things.

  2. #2
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    Ofcourse performance will be effected, but to what extent, I wish somebody can tell us
    Agasimani
    OCP(10g/9i/8i/8)

  3. #3
    I think it can be ignored for a single transaction.
    As you see, sql_trace only write limited information to the tracefile, like parse tree etc.
    Once a sql begin to run , there is no further burden to the oracel server process.
    Even for event 10046 trace with level 12, I did not see much performance slowdown.

    The following is some simple test result.
    SQL> @test
    SQL> alter tablespace rawtest offline;

    Tablespace altered.

    Elapsed: 00:00:00.24
    SQL> alter tablespace rawtest online;

    Tablespace altered.

    Elapsed: 00:00:00.18
    SQL> alter session set events '10046 trace name context off';

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /*+FULL(A)*/ count(*) from account.testbig a;

    COUNT(*)
    ----------
    1707754

    1 row selected.

    Elapsed: 00:00:20.72
    SQL> alter tablespace rawtest offline;

    Tablespace altered.

    Elapsed: 00:00:00.24
    SQL> alter tablespace rawtest online;

    Tablespace altered.

    Elapsed: 00:00:00.15
    SQL> alter session set events '10046 trace name context forever,level 12';

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /*+FULL(A)*/ count(*) from account.testbig a;

    COUNT(*)
    ----------
    1707754

    1 row selected.

    Elapsed: 00:00:20.69
    SQL>
    SQL> set timing on
    SQL> alter tablespace rawtest offline;

    Tablespace altered.

    Elapsed: 00:00:00.29
    SQL> alter tablespace rawtest online;

    Tablespace altered.

    Elapsed: 00:00:00.18
    SQL> alter session set events '10046 trace name context off';

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /*+FULL(A)*/ count(*) from account.testbig a;

    COUNT(*)
    ----------
    1707754

    1 row selected.

    Elapsed: 00:00:20.79
    SQL> alter tablespace rawtest offline;

    Tablespace altered.

    Elapsed: 00:00:00.25
    SQL> alter tablespace rawtest online;

    Tablespace altered.

    Elapsed: 00:00:00.16
    SQL> alter session set events '10046 trace name context forever,level 12';

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /*+FULL(A)*/ count(*) from account.testbig a;

    COUNT(*)
    ----------
    1707754

    1 row selected.

    Elapsed: 00:00:20.70
    SQL>
    SQL> set timing on
    SQL> alter tablespace rawtest offline;

    Tablespace altered.

    Elapsed: 00:00:00.31
    SQL> alter tablespace rawtest online;

    Tablespace altered.

    Elapsed: 00:00:00.15
    SQL> alter session set events '10046 trace name context off';

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /*+FULL(A)*/ count(*) from account.testbig a;

    COUNT(*)
    ----------
    1707754

    1 row selected.

    Elapsed: 00:00:20.69
    SQL> alter tablespace rawtest offline;

    Tablespace altered.

    Elapsed: 00:00:00.25
    SQL> alter tablespace rawtest online;

    Tablespace altered.

    Elapsed: 00:00:00.19
    SQL> alter session set events '10046 trace name context forever,level 12';

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /*+FULL(A)*/ count(*) from account.testbig a;

    COUNT(*)
    ----------
    1707754

    1 row selected.

    Elapsed: 00:00:20.67
    SQL>
    SQL> set timing on
    SQL> alter tablespace rawtest offline;

    Tablespace altered.

    Elapsed: 00:00:00.26
    SQL> alter tablespace rawtest online;

    Tablespace altered.

    Elapsed: 00:00:00.16
    SQL> alter session set events '10046 trace name context off';

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /*+FULL(A)*/ count(*) from account.testbig a;

    COUNT(*)
    ----------
    1707754

    1 row selected.

    Elapsed: 00:00:20.67
    SQL> alter tablespace rawtest offline;

    Tablespace altered.

    Elapsed: 00:00:00.26
    SQL> alter tablespace rawtest online;

    Tablespace altered.

    Elapsed: 00:00:00.16
    SQL> alter session set events '10046 trace name context forever,level 12';

    Session altered.

    Elapsed: 00:00:00.00
    SQL> select /*+FULL(A)*/ count(*) from account.testbig a;

    COUNT(*)
    ----------
    1707754

    1 row selected.

    Elapsed: 00:00:20.76
    SQL>
    I have to say that the tablespace is on raw device, so query again and again does not affected by filesystem cache.
    www.cnoug.org

Posting Permissions

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


Click Here to Expand Forum to Full Width