>Sql Trace

>–TRACE Kullanımı
alter system set timed_statistics=true;
alter session set max_dump_file_size=20000;
show parameter user_dump_dest

select * from v$parameter where name like ‘%user_dump_dest%’

SQL> alter session set SQL_TRACE true;

SQL> select MUSTERI_ADI from MUSTERI where MUSTERI_NO=1;

SQL> select * from dual;

SQL> alter session set SQL_TRACE false;

select SID, SERIAL# from v$session where username=’SAPSR3′

execute dbms_system.set_sql_trace_in_session(’52’,’2′,true);

execute dbms_system.set_sql_trace_in_session(’52’,’2′,false);

–V$SQL_PLAN Kullanımı
select SID from v$session where username=’SYSTEM’

select sql_id, sql_child_number from v$session where sid=79;

SQL> set pages 20000
SQL> set lines 120
SQL> column operations format a30
SQL> column object_name format a25
SQL> column options format a15

select lpad(‘ ‘, level ) || operation operations,
object_name, options, cardinality
from (select * from v$sql_plan
where sql_id=’4gjjtxgzsmw13′
and child_number=0)
connect by prior id=parent_id
start with id=0
order by id, position;

select * from v$sql where SQL_ID=’4gjjtxgzsmw13′

if bind;

col name format a10
col value_string format a50

select name, datatype, value_string
from v$sql_bind_capture
where sql_id=’4gjjtxgzsmw13′ and child_number=0
order by position;

SQL> variable A1 number;
SQL> variable A2 varchar2(20);
SQL> execute :A1 := 34;
SQL> execute :A2 := ‘fer’;

set autotrace trace exp stat
select count(*) from MUSTERI
Where bolge=:A1 and musteri_adi=:A2;

–AUTOTRACE Kullanımı

SET AUTOTRACE ON

Sorgu çalıştırılır, sonucu, explain plan’ı ve istatistikleri görüntülenir

SET AUTOTRACE ON EXP

Sadece explain plan görüntülenir

SET AUTOTRACE ON STAT

Sadece istatistikler görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)

SET AUTOTRACE TRACE

Sorgunun explain plan’ı ve istatistikleri görüntülenir (sorgu çalıştırılır ama sonucu görüntülenmez)

—EXPLAIN PLAN Kullanımı

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s