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

This article is about how to use 10046 to view the execution plan and read the trace file. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

There are many ways to view the sql execution plan, and the 10046 event is one of them. Unlike other sql execution plans, when we encounter more complex sql statements, we can track the sql through 10046 to get the logical read, physical read and time spent of each step in the execution plan. This fine-grained trace is especially useful for us to analyze sql performance.

In general, the steps to get the sql execution plan using the 10046 event are as follows:

1. Activate the current session 10046 event

two。 Execute sql statements in the current session

3. Close the current session 10046 event

After performing the above steps, a trace file is usually generated automatically. In oracle 11g, trace files are typically placed in the $ORACLE_BASE/diag/rdbms/ {database_name} / $ORACLE_SID/trace directory. If you activate trace 10046 with oradebug, you can use oradebug tracefile_name to get the full path of the newly generated trace file.

I think most dba will use the oradebug activation tracking 10046 event just mentioned. Oradebug is a powerful and easy-to-use tool. Using oradebug help, you will see that it has many functions.

SQL > oradebug help

HELP [command] Describe one or all commands

SETMYPID Debug current process

SETOSPID Set OS pid of process to debug

SETORAPID ['force'] Set Oracle pid of process to debug

SETORAPNAME Set Oracle process name to debug

SHORT_STACK Get abridged OS stack

CURRENT_SQL Get current SQL

DUMP [addr] Invoke named dump

DUMPSGA [bytes] Dump fixed SGA

DUMPLIST Print a list of available dumps

EVENT Set trace event in process

SESSION_EVENT Set trace event in session

DUMPVAR [level] Print/dump a fixed PGA/SGA/UGA variable

DUMPTYPE Print/dump an address with type info

SETVAR Modify a fixed PGA/SGA/UGA variable

PEEK [level] Print/Dump memory

POKE Modify memory

WAKEUP Wake up Oracle process

SUSPEND Suspend execution

RESUME Resume execution

FLUSH Flush pending writes to trace file

CLOSE_TRACE Close trace file

TRACEFILE_NAME Get name of trace file

LKDEBUG Invoke global enqueue service debugger

NSDBX Invoke CGS name-service debugger

-G Parallel oradebug command prefix

-R Parallel oradebug prefix (return output

SETINST Set instance list in double quotes

SGATOFILE Dump SGA to file; dirname in double quotes

DMPCOWSGA Dump & map SGA as COW; dirname in double quotes

MAPCOWSGA Map SGA as COW; dirname in double quotes

HANGANALYZE [level] [syslevel] Analyze system hang

FFBEGIN Flash Freeze the Instance

FFDEREGISTER FF deregister instance from cluster

FFTERMINST Call exit and terminate instance

FFRESUMEINST Resume the flash frozen instance

FFSTATUS Flash freeze status of instance

SKDSTTPCS Helps translate PCs to names

WATCH Watch a region of memory

DELETE watchpoint Delete a watchpoint

SHOW watchpoints Show watchpoints

DIRECT_ACCESS Fixed table access

CORE Dump core without crashing process

IPC Dump ipc information

UNLIMIT Unlimit the size of the trace file

PROCSTAT Dump process statistics

CALL [arg1]... [argn] Invoke function with arguments

Thank you for reading! On "how to use 10046 to view the implementation plan and read the trace file" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

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.