Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle 10046 Event     - To install and use the Doxygen under Linux (Linux)

- PULL operation mechanism parsing XML Comments (Programming)

- ISO image to use offline upgrade Ubuntu (Linux)

- C language preprocessor command (Programming)

- cursor_sharing induced error ORA-00600 (Database)

- Formatting Java floating-point types (Programming)

- Linux monitoring tools introduced series --smem (Server)

- The easiest 11g Active DataGuard (ADG) to establish the configuration process (Database)

- open V switch port mirror in OpenStack neutron (Server)

- CentOS 7.0 Automatic installation CD-ROM production Comments (Linux)

- Five useful commands to manage file types and system time in linux (Linux)

- Example of use WebSocket (Programming)

- Intel Graphics Installer installation on Ubuntu 15.10 (Linux)

- Python in yield (Programming)

- How to implement Linux host Telnet SSH password Free (Server)

- Java and Python use make way dictionary word search script (Programming)

- 20 Advanced Java interview questions summary (Programming)

- 4 lvcreate example commonly used commands (Linux)

- JDK comes with tools JPS (Linux)

- Easily solve the MySQL database connection error too many (Database)

 
         
  Oracle 10046 Event
     
  Add Date : 2018-11-21      
         
         
         
  10046 Event

Oracle 10046 is an Oracle internal event. The most common is in Session level settings sql_trace (alter session set sql_trace = true) that is turned on level 1 of 10046 debug event. When set 10046 event, Oracle will generate a dump file. Dump file obtained through further analysis can be performed internally Oracle system analysis, call waiting, detailed trace information about bind variables, etc., for performance analysis system plays an important role.

When the SQL statement operation performance issues, we can track the most appropriate use SQL_TRACE or 10046 event. If the decline in the overall performance of the database, you need to use statspack or AWR database for analysis.

1.10046 events related parameters
The event need to set some parameters to control the output dump file:
  TIMED_STATISTICS
    For controlling the timing information, it can be set to true and false. When set to true, the timing information will be added to the trace file.
  
  MAX_DUMP_FILE_SIZE
    Used to control the maximum size of trace files. When using the 10046 event, it is recommended this parameter is set to unlimited.
  
  USER_DUMP_DEST
    Used to set the trace file is written to the file directory which
  
  STATISTICS_LEVEL
    For controlling the collection of statistics. This parameter has three options, baisc, typical, all.
    basic: collect only meet the most basic information needed to trace, like Timed statistics, Object level statistics, as well as some advisory will be ignored.
    typical: This is the default. This setting will add some additional information on the basis of basic statistics on the elapsed time as the operating system statistics, implementation of the plan will be to collect statistical information
    all: When set to all, all information related to the session will all be collected.
  
  TRACEFILE_IDENTIFIER
    Setting Trace file identification string used to facilitate more efficient to find the generated Trace file.
  The above parameters can be modified based on the system level and session level.
    ALTER SESSION / SYSTEM SET timed_statistics = true
    ALTER SESSION / SYSTEM SET max_dump_file_size = unlimited

    ALTER SESSION SET tracefile_identifier = 'trace_sql_example' -> Only session level

Level 2.10046 debug events
 10046 debug event may be divided into a plurality of different levels, different levels of different output trace information.
  Level Role
  0 Disabled debug event
  1 debug event is active. Each database call is processed, the output of SQL statements, APPNAME (application name), PARSING IN CURSOR, PARSE ERROR (SQL parsing) for
      , EXEC (execute), FETCH (Get Data), UNMAP, SORT UNMAP (sorting, temporary segments), ERROR, STAT (implementation plan), XCTEND (transaction) and other lines.
  4 includes an output level 1, plus BIND line (bind variable information)
  8 includes an output level of 1, plus the WAIT line (wait event information). For each waiting process, the following information: name of the waiting time, duration, and additional
        The parameters may indicate resource waits.
  12 Output Level 4 and Level 8 All information

3. Generate trace files
[Oracle @ server8 trace] $ sqlplus / as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Tue Feb 10 16:20:39 2015

sys @ GHSJDB> alter session set tracefile_identifier = '10046';

Session altered.

sys @ GHSJDB> alter session set events '10046 trace name context forever, level 12'; (if no permissions to alter session privilege)

Session altered.

sys @ GHSJDB> select code, name, to_char (parent_code) parent, to_char (connect_by_isleaf) isleaf, lev
  2 from ghsj_xm.v_organdataset
  3 where level = 1 connect by prior code = parent_code start with code = '14142' order siblings by code
  4;

CODE NAME
-------------------------------------------------- ---------------------------------------- ---------- -------------------------
PARENT ISLEAF LEV
-------------------------------------------------- ---------------------------------------- ---------- ------------------------------ -------------------- --------------------
14142 State Grid Hubei Electric Power Company
14000

sys @ GHSJDB> alter session set events '10046 trace name context off';

Session altered.

4. Get trace files
method 1:

sys @ GHSJDB> show parameter dump

NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
background_core_dump string partial
background_dump_dest string / home / u01 / app / oracle / diag / rdbms / ghsjdb / ghsjdb / trace
core_dump_dest string / home / u01 / app / oracle / diag / rdbms / ghsjdb / ghsjdb / cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string / home / u01 / app / oracle / diag / rdbms / ghsjdb / ghsjdb / trace

Go to the appropriate directory:

cd / home / u01 / app / oracle / diag / rdbms / ghsjdb / ghsjdb / trace

Find Identifier 10046:

 

ls | grep 10046

ghsjdb_ora_9481_10046.trc
ghsjdb_ora_9481_10046.trm

 

Method 2:

Get the current sid of users has made tracefile according to ID address

sys @ GHSJDB> select * from v $ mystat where rownum = 1;

  SID STATISTIC # VALUE
----- ---------- ----------
  2470 ##########

sys @ GHSJDB> SELECT tracefile FROM v $ process WHERE addr IN (SELECT paddr FROM v $ session WHERE sid = '247');

TRACEFILE
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
/home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace/ghsjdb_ora_9481_10046.trc

5. formatted output tkprof
[Oracle @ server8 ~] $ tkprof
Usage: tkprof tracefile outputfile [explain =] [table =]
              [Print =] [insert =] [sys =] [sort =]
  table = schema.tablename Use 'schema.tablename' with 'explain =' option.
  explain = user / password Connect to ORACLE and issue EXPLAIN PLAN.
  print = integer List only the first 'integer' SQL statements.
  aggregate = yes | no
  insert = filename List SQL statements and data inside INSERT statements.
  sys = no TKPROF does not list SQL statements run as user SYS.
  record = filename Record non-recursive statements found in the trace file.
  waits = yes | no Record summary for any wait events found in the trace file.
  sort = option Set of zero or more of the following sort options:
    prscnt number of times parse was called
    prscpu cpu time parsing
    prsela elapsed time parsing
    prsdsk number of disk reads during parse
    prsqry number of buffers for consistent read during parse
    prscu number of buffers for current read during parse
    prsmis number of misses in library cache during parse
    execnt number of execute was called
    execpu cpu time spent executing
    exeela elapsed time executing
    exedsk number of disk reads during execute
    exeqry number of buffers for consistent read during execute
    execu number of buffers for current read during execute
    exerow number of rows processed during execute
    exemis number of library cache misses during execute
    fchcnt number of times fetch was called
    fchcpu cpu time spent fetching
    fchela elapsed time fetching
    fchdsk number of disk reads during fetch
    fchqry number of buffers for consistent read during fetch
    fchcu number of buffers for current read during fetch
    fchrow number of rows fetched
    userid userid of user that parsed the cursor

 

tkprof /home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace/ghsjdb_ora_9481_10046.trc 10046.log sys = no

 

[Oracle @ server8 ~] $ more 10046.log

TKPROF: Release 11.2.0.3.0 - Development on Tue Feb 10 17:11:04 2015

Copyright (c) 1982, 2011, Oracle and / or its affiliates. All rights reserved.

Trace file: /home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace/ghsjdb_ora_11266_10046.trc
Sort options: default

************************************************** ******************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call

 

************************************************** ******************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- ------------ ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 2
Fetch 2 0.25 0.26 0 22123 0 1
------- ------ -------- ---------- ---------- --------- ------------ ----------
total 10 0.25 0.26 0 22123 0 3

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on Times Max. Wait Total Waited
  ---------------------------------------- Waited --------- --------------
  SQL * Net message to client 5 0.00 0.00
  SQL * Net message from client 5 18.65 27.67


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- ------------ ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- --------- ------------ ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

    3 user SQL statements in session.
    0 internal SQL statements in session.
    3 SQL statements in session.
************************************************** ******************************
Trace file: /home/u01/app/oracle/diag/rdbms/ghsjdb/ghsjdb/trace/ghsjdb_ora_11266_10046.trc
Trace file compatibility: 11.1.0.7
Sort options: default

      1 session in tracefile.
      3 user SQL statements in trace file.
      0 internal SQL statements in trace file.
      3 SQL statements in trace file.
      3 unique SQL statements in trace file.
    115 lines in trace file.
      27 elapsed seconds in trace file.
     
         
         
         
  More:      
 
- Configuring Sublime Text Python runtime environment 2 (Linux)
- How to install Gnome 3.10 in Ubuntu 13.10 (Linux)
- Crawl use Wireshark packet on remote Linux (Linux)
- Linux system performance and usage activity monitoring tools -Sysstat (Linux)
- Sublime Text 3 (Linux)
- Zombie process under Linux (Linux)
- Redis application of Sina Weibo (Database)
- Linux system security settings after installation (Linux)
- GRUB how to load Linux kernel (Linux)
- Comparison of one-time transaction and CTE insert data (Database)
- Sorting Algorithm (1) Quick Sort C ++ implementation (Programming)
- RHEL7.0 environment Linux kernel upgrade (Linux)
- MySQL 5.6.12 binary log path switching binlog (Database)
- Proper use Core Data multithreaded 3 ways (Programming)
- Sublime Text 3 shortcuts summary (Linux)
- Ubuntu Server security risk checks (Linux)
- Flow control message transmission between RAC (Database)
- ssh using scp: / directory: Permission denied (Server)
- Install Java, Maven, Tomcat under Linux (Linux)
- How to Install Redis server on CentOS 7 (Server)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.