remote dba support
More
    HomeOracle DBA TipsSQL 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 accomplishments statistics like the 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 the trace file then it’s difficult but using TKPROF we can generate readable reports from the trace file.

    When you enable SQL tracing, then it starts recording the 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.

    How to Enable SQL Tracing:

    You can enable SQL Tracing in different ways.

    - Advertisement -
    dbametrix

    For Database Level:

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

    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);

    - Advertisement -
    dbametrix

    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 the above methods, you can trace every doubtful SQL which causes performance impacts. After tuning SQL again, you can test that SQL for getting the result of performance whether it gets improved or not. For performance and tuning of the Oracle database, it is a must need to know how to trace SQL queries for getting an execution plan. 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. 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.

    - 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 -
    remote dba services

    Most Popular