Shulou(Shulou.com)06/01 Report--

Want to use SCOTT users to do the execution plan experiment, found that can not be used; in theory, any user who can use sqlplus can enable the autotrace function under session, but some need to make the following settings

Sys user

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

SQL > select username,account_status from dba_users where username='SCOTT'

USERNAME ACCOUNT_STATUS

SCOTT EXPIRED & LOCKED

SQL > alter user scott account unlock identified by tiger

User altered.

SQL >

SQL > select username,account_status from dba_users where username='SCOTT'

USERNAME ACCOUNT_STATUS

SCOTT OPEN

SCOTT user login

SQL > set autotrace on / / prompt to check whether the PLUSTRACE role is enabled

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

Solution process

1. Log in as SYS user and run utlxplan.sql. Establish plan table

SQL > @? / rdbms/admin/utlxplan.sql

Table created.

2. Log in as sys user, run the installation script for plustrce.sql, database to enable autotrace function, mainly to create plustrace role and authorize, the main purpose of owning plustrace is to access the following VIEW:

SQL > @? / 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

SQL > grant plustrace to scott

Grant succeeded.

3. Log in as SCOTT

SQL > set autotrace on

SQL >

SQL > select * from dept

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

Execution Plan

Plan hash value: 3383998547

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 4 | 80 | 3 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0) | 00:00:01 |

Statistics

2 recursive calls

0 db block gets

10 consistent gets

4 physical reads

0 redo size

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

4 rows processed

SQL >

Commands for setting up Autotrace

Serial number

Command

explain

one

SET AUTOTRACE OFF

This is the default value, that is, turn off Autotrace

two

SET AUTOTRACE ON

Generate result sets and explain plans and list statistics

three

SET AUTOTRACE ON EXPLAIN

Show result sets and explain that plans do not display statistics

four

SET AUTOTRACE TRACEONLY

Show explanation plans and statistics, although you execute this statement, you will not see the result set

five

SET AUTOTRACE TRACEONLY STATISTICS

Show statistics only

PS:SET AUTOTRACE ON, set timing on, alter session set time_statistics=true

The meaning of each column of the Autotrace implementation plan

Serial number

Column name

explain

one

ID_PLUS_EXP

Line number of each step

two

PARENT_ID_PLUS_EXP

The level number of Parent for each step

three

PLAN_PLUS_EXP

Actual every step

four

OBJECT_NODE_PLUS_EXP

Only used for Dblink or parallel queries

Autotrace Statistics commonly used column explanation

Serial number

Column name

explain

one

Db block gets

The number of block read from buffer cache

two

Consistent gets

The number of block of undo data read from buffer cache

three

Physical reads

Number of block read from disk

four

Redo size

The size of the redo generated by DML

five

Sorts (memory)

The amount of sort performed in memory

six

Sorts (disk)

The amount of sort performed on disk

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.