Which statement is true?

Examine the values for the following initialization parameters:
STATISTICS_LEVEL = TYPICAL
TIMED_STATISTICS = true
You are managing an online transaction processing (OLTP) system. Application users notice that
some queries have poor response time. You determine that queries from session ID 27, serial
number 60, for user OE are heavy-resource consumers.
To investigate further, you enabled tracing for the session by executing the following command:
SQL> EXECUTE dbms_system.set_sql_trace_in_session (27, 60, true);
Which statement is true?

Examine the values for the following initialization parameters:
STATISTICS_LEVEL = TYPICAL
TIMED_STATISTICS = true
You are managing an online transaction processing (OLTP) system. Application users notice that
some queries have poor response time. You determine that queries from session ID 27, serial
number 60, for user OE are heavy-resource consumers.
To investigate further, you enabled tracing for the session by executing the following command:
SQL> EXECUTE dbms_system.set_sql_trace_in_session (27, 60, true);
Which statement is true?

A.
The tkprof output file for the trace generated would display only the timed statistics for the SQL
statements.

B.
The tkprof output file for the trace generated would display statistics for all the sessions created
by the user OE.

C.
The tkprof output file for the trace generated would display bind variable values if bind variables
are used in the queries executed in the session.

D.
The tkprof output file for the trace generated would not display bind variable values if bind
variables are used in the queries executed in the session.

Explanation:
om 121

Show Hint

Leave a Reply 3

Your email address will not be published. Required fields are marked *

Rodrigo

Rodrigo

Oracle® Database Reference
11g Release 1 (11.1)

STATISTICS_LEVEL = TYPICAL

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

The major statistics are:

– Automatic Workload Repository (AWR) Snapshots
– Automatic Database Diagnostic Monitor (ADDM)
– All server-generated alerts
– Automatic SGA Memory Management
– Automatic optimizer statistics collection
– Object level statistics
– End to End Application Tracing (V$CLIENT_STATS)
– Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
– Service level statistics
– Buffer cache advisory
– MTTR advisory
– Shared pool sizing advisory
– Segment level statistics
– PGA Target advisory
– Timed statistics
– Monitoring of statistics

TIMED_STATISTICS = TRUE

The statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.

ID Doc ID 434437.1
…
In order to capture the values of the bind variables, trace needs to be captured at level 4.
…
SQL>exec sys.dbms_system.set_ev(sid,serial#,10046,4,”);

and, obviously, the trace was enabled for just one, and one only, session in this question.

Reply

Rodrigo

Rodrigo

Answer is D.

Reply

Rodrigo

Rodrigo

For example.

/u01/app/oracle/diag/rdbms///trace

ls -ltr

alert.log

SQL> exec dbms_system.set_sql_trace_in_session(189,21325,true);

ls -ltr

alert.log
-rw-r—– 1 oracle dba 620 Aug 1 10:14 ifrmax1_ora_30318.trm
-rw-r—– 1 oracle dba 40999 Aug 1 10:14 ifrmax1_ora_30318.trc

— A query was executed by that session

SQL> exec dbms_system.set_sql_trace_in_session(189,21325,false);

ls -ltr

alert.log
-rw-r—– 1 oracle dba 674 Aug 1 10:23 ifrmax1_ora_30318.trm
-rw-r—– 1 oracle dba 42452 Aug 1 10:23 ifrmax1_ora_30318.trc

From the trc file it is possible to see that only one session was traced.

vi ifrmax1_ora_30318.trc

…
*** SESSION ID:(189.21325) 2014-08-01 10:14:38.743
…

Reply