Hi All,
When you enable sqltrace on your user session it will create a trace file on server. Trace file will give you all the statistics of the sql like consistent gets , Cpu utilization , memory usage. Consider you have a procedure to delete data which is deleting 35 million records and it takes huge time. Now you can do some thinking and find what is going wrong .
1) Is the select statement that you are running inside procedure to identify records is taking time. Like you are using select * from t1 exists ( select * from t2 where t1.a =t2.a) now if t1 is big you will be in lot of trouble with a lot of consistent gets and high cpu usage. Now considering you did not know this and you had to investigate what is going wrong then you need sql trace.
Auto trace and explain plan will not work for procedure to identify where the bottleneck. So sql trace is like autotrace for procedure gives all info like consistent gets and CPU utilization. If you are not familiar with autotrace you can read my article on reading explain plans Here
ALTER SESSION SET sql_trace = true;
run the procedure for which you want to find the trace
ALTER SESSION SET sql_trace = false;
Now formating the output of sqltrace as it is quite unreadable.
Oracle has formating utility for sqltrace called tkprof. You cant run the statement from sql developer run in command prompt
tkprof C:\Dummy_folder_for_informatica_textfiles\orcl_ora_4432.trc C:\Dummy_folder_for_informatica_textfiles\output.prf EXPLAIN=DWBUILD/DWBUILD SYS=NO
Again a Good article on ASK tom on Understanding TKProf
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:969160000346108326
Below are some question that ASK tom asks us to consider
PARSE
Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE
Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH
Retrieves rows returned by a query. Fetches are only performed for SELECT statements.
Query --- is your consistent gets
Disk -- Physical reads
db sequential read --- means reading indexes
db scattered read -- full table scans
When saying sequential read takes time we should think how much time it is taking to load data into SGA in short your RAM .
Using a high consistent get will be accompanied by huge time in db sequential read -- i need to read more why it gives lot of time for sequential read
http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::%3AP11_QUESTION_ID:6265095774206
Alternative for TKPROF ( Poor developers vesion)
You can use V$SQL to get some of the information. Like Disk reads and Buffer gets . Autotrace can also be used for some of the information
SELECT sql_id,
Elapsed_time,
Cpu_time,
Fetches,
executions,
Buffer_gets,
disk_reads,
Direct_writes,
Physical_read_bytes,
Physical_write_bytes,
concurrency_wait_time,
user_io_wait_time,
rows_processed,
optimizer_mode,
((IO_interconnect_bytes)/1024)/1024 Interconnect_MB
FROM v$sql
http://docs.oracle.com/cd/B10501_01/server.920/a96533/autotrac.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6071779300346659903
When you enable sqltrace on your user session it will create a trace file on server. Trace file will give you all the statistics of the sql like consistent gets , Cpu utilization , memory usage. Consider you have a procedure to delete data which is deleting 35 million records and it takes huge time. Now you can do some thinking and find what is going wrong .
1) Is the select statement that you are running inside procedure to identify records is taking time. Like you are using select * from t1 exists ( select * from t2 where t1.a =t2.a) now if t1 is big you will be in lot of trouble with a lot of consistent gets and high cpu usage. Now considering you did not know this and you had to investigate what is going wrong then you need sql trace.
Auto trace and explain plan will not work for procedure to identify where the bottleneck. So sql trace is like autotrace for procedure gives all info like consistent gets and CPU utilization. If you are not familiar with autotrace you can read my article on reading explain plans Here
ALTER SESSION SET sql_trace = true;
run the procedure for which you want to find the trace
ALTER SESSION SET sql_trace = false;
Now formating the output of sqltrace as it is quite unreadable.
Oracle has formating utility for sqltrace called tkprof. You cant run the statement from sql developer run in command prompt
tkprof C:\Dummy_folder_for_informatica_textfiles\orcl_ora_4432.trc C:\Dummy_folder_for_informatica_textfiles\output.prf EXPLAIN=DWBUILD/DWBUILD SYS=NO
Again a Good article on ASK tom on Understanding TKProf
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:969160000346108326
Below are some question that ASK tom asks us to consider
|
PARSE
Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE
Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH
Retrieves rows returned by a query. Fetches are only performed for SELECT statements.
Query --- is your consistent gets
Disk -- Physical reads
db sequential read --- means reading indexes
db scattered read -- full table scans
When saying sequential read takes time we should think how much time it is taking to load data into SGA in short your RAM .
Using a high consistent get will be accompanied by huge time in db sequential read -- i need to read more why it gives lot of time for sequential read
http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::%3AP11_QUESTION_ID:6265095774206
Alternative for TKPROF ( Poor developers vesion)
You can use V$SQL to get some of the information. Like Disk reads and Buffer gets . Autotrace can also be used for some of the information
SELECT sql_id,
Elapsed_time,
Cpu_time,
Fetches,
executions,
Buffer_gets,
disk_reads,
Direct_writes,
Physical_read_bytes,
Physical_write_bytes,
concurrency_wait_time,
user_io_wait_time,
rows_processed,
optimizer_mode,
((IO_interconnect_bytes)/1024)/1024 Interconnect_MB
FROM v$sql
http://docs.oracle.com/cd/B10501_01/server.920/a96533/autotrac.htm
|
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6071779300346659903
No comments:
Post a Comment