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