Wednesday, September 23, 2020
dbametrix
More
    Home Oracle DBA Tips SQL Tracing-Overview

    SQL Tracing-Overview

    Detail explanation of how to enable and disable SQL tracing in a database with database level or session level with how to understand the result of a trace.

    How to trace SQL for improving Performance

    • SQL Tracing records much information regarding actual query execution into a trace file. It describes sequel query accomplishment statistics like a number of logical I/O , physical I/O , the CPU and lapsed timings, the number of rows processed, and including query plans with row calculations at each query level, details on wait events statistics, etc.
    • If we need to read trace file then it’s difficult but using TKPROF we can generate the readable reports from the trace file.

    When you enable SQL tracing then it starts recording below information on executed SQL Statements.

    • Parse, execute, and fetch counts.
    • CPU and elapsed times.
    • Physical reads and logical reads.
    • The number of rows processed.
    • Misses on the library cache,
    • Optimizer mode.
    • Parsing user id.
    • A number of plan statistics captured.
    • Each commit and rollback.
    • Wait for event data for each SQL statement.
    • Row operations showing the actual execution plan of each SQL statement
    • Number of rows, number of consistent reads, number of physical reads, number of physical writes, and time elapsed for each operation on a row.

    Parameters Related to SQL Tracing:

     

    Parameter Description
    SQL_TRACE It enables or disables the SQL Trace facility. When you set the parameter to true that time it delivers detail on tuning that you can use to increase performance. You can pass on these variables as the value using the DBMS_SYSTEM package.
    USER_DUMP_DEST Destination of trace files.
    MAX_DUMP_FILE_SIZE Maximum size of Trace File.
    TIMED_STATISTICS This parameter enables and disables the collection of timed statistics, such as CPU and elapsed times, various statistics in the dynamic performance tables. The value can be true or false.
    - Advertisement -
    dbametrix

     

    How to Enable SQL Tracing:

    You can enable SQL Tracing in different ways.

    For Database Level:

    DBMS_MONITOR.DATABASE_TRACE_ENABLE(
    waits          IN BOOLEAN DEFAULT TRUE,
    binds          IN BOOLEAN DEFAULT FALSE,
    instance_name  IN VARCHAR2 DEFAULT NULL);

    - Advertisement -
    dbametrix

    EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE (WAITS => TRUE, BINDS => TRUE);

    EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE (WAITS => TRUE, BINDS => TRUE, INSTANCE_NAME => 'optional');

     

    For Session Level & Disable SQL_Tracing:

     

    EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 12, serial_num => 343, waits => TRUE, binds => FALSE);

    EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);

    EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

     

    EXEC DBMS_SESSION.SET_SQL_TRACE (TRUE);

    EXEC DBMS_SESSION.SET_SQL_TRACE (FALSE);

     

    ALTER SESSION SET SQL_TRACE = TRUE;

    ALTER SESSION SET SQL_TRACE = FALSE;

     

    SETUP TKPROF

    alter session set tracefile_identifier = 'orageek_20302567_TK_TEST';

    above file gets stored in –> USER_DUMP_DEST (You can modify directory as per your need)

    alter session set timed_statistics=true;

    EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE (WAITS => TRUE, BINDS => TRUE);

    Now executed statement to trace and after statement execution finish disable SQL Tracing.

    EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

    Generate TKPROF Report

    Now open command prompt goes to USER_DUMP_DEST directory and copy the name of your generated trace file “orageek_20302567_TK_TEST.trc” and execute below command:

    CMD>tkprof orageek_20302567_TK_TEST.trc TKREPORT_TEST.prf (name of report )

    Output format will be like below:

    call          count    cpu   elapsed    disk   query     current         rows
    ——-         —–       ——-      ——-         ——-     ——-         ——             ——-
    Parse          1       0.02    0.02           0             0               0               0
    Execute     1       0.00    0.00          0             0               0                0
    Fetch          2      0.00    0.00          0             1                4                 1
    ——-        —-      ———     ——-         ——-      ——-         ——–            ——-
    total           4      0.02     0.02           0             1                 4               1

     

    Misses in library cache during parse: 1

    Optimizer goal: ALL_ROWS

    Parsing user id: 121

    Rows     Row Source Operation
    ——-  —————————————————
    1  SORT AGGREGATE
    1   TABLE ACCESS FULL DUAL
    Understanding Output:
    Parse: Parsing of Statement happened
    Execute: It gives information regarding insert, update, delete operations
    Fetch: It gives information regarding select operations
    Count: Number of time it undergoes any specific stage
    CPU: Total CPU time at the specific Stage
    Elapsed: Total time elapsed by a statement
    disk: Physical I/O in database      
    query: Logical I/O in database
    current: How many logical I/O performed until the time
    rows: Number of rows processed in any particular phase
    Parsing user id: User who parsing the statement

    Using above methods you can trace every doubtful SQL which are causes performance impacts. After tuning SQL again you can test that SQL for getting result of performance either it gets improved or not. This is the best practice.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    Consider Reading to these articles:

     

    - Advertisement -
    dbametrix

    1 COMMENT

    1. Very easy to understand thanks for sharing this overview of SQL trace including database level tracing and session level tracing. I am learning too many things from this site.

    Comments are closed.

    - Advertisment -
    dbametrix

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments