Set autotrace on

Tutorials, Solutions
1

Sqlplus ашиглаж байгаа үед зарим query -ийн execution plan-ийг харахыг хүсвэл

set autotrace on

коммандыг ашиглана.

Дараах жишээн дээр ажиллацгаая. hr хэрэглэгчийг ашиглав.

[oracle@node214 database]$ sqlplus hr/h
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 25 04:19:25 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Дээрхи алдаа нь хэрэглэгчийн эрх хүрэхгүй байна гэсэн үг. Тиймээс sys эрхээр орж тохирох эрхийг өгөх хэрэгтэй. Үүний тулд дараах коммандыг ашиглана.

SQL> conn / as sysdba
Connected.
SQL> grant PLUSTRACE to hr;
grant PLUSTRACE to hr
      *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

Уг эрх нь нэмэлт эрх бөгөөд идэвхжүүлэхийн тулд дараах скриптыг ажиллуулна.

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off

Одоо hr эрхэд харгалзах эрхийг оноох боломж бүрдоээ. Тиймээс дараах коммандыг ашиглаад hr эрхээр нэвтэр.

SQL> grant plustrace to hr;

Grant succeeded.

SQL> conn hr/h
Connected.

SQL> select * from jobs where rownum< 2;

JOB_ID	   JOB_TITLE			       MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President				    20080      40000

SQL>

SQL>  set autotrace on
SQL> 
SQL> select * from jobs where rownum< 2;

JOB_ID	   JOB_TITLE			       MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES    President				    20080      40000


Execution Plan
----------------------------------------------------------
Plan hash value: 2277050181

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    33 |	2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY	   |	  |	  |	  |	       |	  |
|   2 |   TABLE ACCESS FULL| JOBS |	1 |    33 |	2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<2)


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  7  consistent gets
	  0  physical reads
	  0  redo size
	766  bytes sent via SQL*Net to client
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
SQL>

Дээрхи мэдээллүүд дээр үндэслэн асуудлуудыг тодорхойлж SQL tuning хийх, ажиллагаа сайжирсан эсэхийг харах боломжтой.

1 Like