Can you see Execution Plan of a statement?

Can you see Execution Plan of a statement?
by

1 Answer

Answer :

YES. In many ways, for example from GUI based tools like TOAD, Oracle SQL Developer. Configuring AUTOTRACE, a SQL*Plus facility
AUTOTRACE is a facility within SQL*Plus to show us the explain plan of the queries we've executed, and the  resources they used.
Once the PLAN_TABLE has been installed in the database, You can control the report by setting the AUTOTRACE  system variable.
▪ SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default.
▪ SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path. ▪ SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution  statistics.
▪ SET AUTOTRACE ON - The AUTOTRACE report includes both the optimizer execution path and the SQL  statement execution statistics.
▪ SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query  output, if any.
by

Related questions

What is Execution Plan ?

Description : What is Execution Plan ?

Last Answer : The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.

What are the different approaches used by Optimizer in choosing an execution plan ?

Description : What are the different approaches used by Optimizer in choosing an execution plan ?

Last Answer : Rule-based and Cost-based.

What are two virtual tables available during database trigger execution ?

Description : What are two virtual tables available during database trigger execution ?

Last Answer : The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available. For triggers related to UPDATE only OLD. ... values only available. For triggers related to DELETE only OLD.column_name values only available.

At what point of report execution is the before Report trigger fired?

Description : At what point of report execution is the before Report trigger fired?

Last Answer : After the query is executed but before the report is executed and the records are displayed.

Give the sequence of execution of the various report triggers?

Description : Give the sequence of execution of the various report triggers?

Last Answer : Before form , After form , Before report, Between page, After report.

Is the After report trigger fired if the report execution fails?

Description : Is the After report trigger fired if the report execution fails?

Last Answer : Yes.

Complete the following statement. As the project progresses into the execution phase: Select one: a. risks need to be checked off on activities that have been performed. b. the total risk on the project typically increases. c. risks are identified with each major group of activities. d. a risk response plan can be used to identify increasing levels of detailed risk analysis. e. the risk breakdown structure needs to be examined to be sure all the risk events have been avoided.

Description : Complete the following statement. As the project progresses into the  execution phase: Select one: a. risks need to be checked off on activities that have been performed. b. the total risk on ... risk breakdown structure needs to be examined to be sure all the risk events have  been avoided.

Last Answer : a. risks need to be checked off on activities that have been performed.

How would you go about generating an EXPLAIN plan?

Description : How would you go about generating an EXPLAIN plan?

Last Answer : Create a plan table with utlxplan.sql. Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement Look at the explain plan with utlxplp.sql or utlxpls.sql

What are the different plan types available in Fusion Absence Management? Accrual

Description : What are the different plan types available in Fusion Absence Management?

Last Answer : There are three different plan types available in Fusion Absence Management, which are given below: Accrual Use this type to create absence plans that enable workers to accrue time for the ... plan to pay workers if they are not eligible for a standard maternity absence qualification plan.

What is the most critical aspect in developing a project plan that meets project specifications within the timeframe and at the lowest costs? Select one: a. Assessing risk managemenc. Emphasizing error prevention and customer satisfaction d. Requiring commitment to quality by all the employees and business partners e. Developing a project execution plan that matches the complexity level of the projectt b. Providing documents that specifically comply with the quality standards in use

Description : What is the most critical aspect in developing a project plan that meets project  specifications within the timeframe and at the lowest costs? Select one: a. Assessing risk management b ... business partners e. Developing a project execution plan that matches the complexity level of the project

Last Answer : e. Developing a project execution plan that matches the complexity level of the project

Fill in the blank. Work breakdown structure (WBS) is a ______________ of the work with increasing detail in each layer. Select one: a. layered description b. step change c. ballpark estimate d. project execution plan e. project logic diagram

Description : Fill in the blank. Work breakdown structure (WBS) is a ______________ of the work  with increasing detail in each layer. Select one: a. layered description b. step change c. ballpark estimate d. project execution plan e. project logic diagram

Last Answer : a. layered description

Incorrect information results in unsatisfied preconditions for actions and plans _____________ detects violations of the preconditions for successful completion of the plan. a) Conditional Plan b) Conformant Planning c) Execution monitoring d) Both Conditional Plan & Execution monitoring

Description : Incorrect information results in unsatisfied preconditions for actions and plans _____________ detects violations of the preconditions for successful completion of the plan. a) Conditional Plan b) Conformant Planning c) Execution monitoring d) Both Conditional Plan & Execution monitoring

Last Answer : c) Execution monitoring

Conditional Plans allows the agent to sense the world during execution to decide what branch of plan to follow. a) True b) False

Description : Conditional Plans allows the agent to sense the world during execution to decide what branch of plan to follow. a) True b) False

Last Answer : a) True

What are the different approaches used by Optimizer in choosing an execution plan?

Description : What are the different approaches used by Optimizer in choosing an execution plan?

Last Answer : Rule-based and Cost-based.

What is Execution Plan?

Description : What is Execution Plan?

Last Answer : The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.

How can one see who is using a temporary segment? (for DBA

Description : How can one see who is using a temporary segment? (for DBA

Last Answer : For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'. All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is ... For usage stats, see SYS.V_$SORT_SEGMENT From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these

Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE? (for DBA

Description : Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE? (for DBA

Last Answer : Tempfiles, unlike normal datafiles, are not listed in v$datafile or dba_data_files. Instead query v$tempfile or dba_temp_files: SELECT * FROM v$tempfile; SELECT * FROM dba_temp_files;

How does one see the uptime for a database? (for DBA

Description : How does one see the uptime for a database? (for DBA

Last Answer : Look at the following SQL query: SELECT to_char (startup_time,'DD-MON-YYYY HH24: MI: SS') "DB Startup Time" FROM sys.v_$instance; Marco Bergman provided the following alternative solution: SELECT ... from V$INSTANCE JUL, V$INSTANCE SEC where JUL.KEY like '%JULIAN%' and SEC.KEY like '%SECOND%';

How to add conditions in select statement in oracle?

Description : How to add conditions in select statement in oracle?

Last Answer : Just add the decode

What are the types of SQL Statement ?

Description : What are the types of SQL Statement ?

Last Answer : Data Definition Language : CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.

How do u implement the If statement in the Select Statement

Description : How do u implement the If statement in the Select Statement

Last Answer : We can implement the if statement in the select statement by using the Decode statement. e.g select DECODE (EMP_CAT,'1','First','2','Second'Null); Here the Null is the else statement where null is done .

Can a formula column be obtained through a select statement?

Description : Can a formula column be obtained through a select statement?

Last Answer : Yes

Can you use a commit statement within a database trigger?

Description : Can you use a commit statement within a database trigger?

Last Answer : No

What will happen after commit statement ?

Description : What will happen after commit statement ?

Last Answer : Cursor C1 is Select empno, ename from emp; Begin open C1; loop Fetch C1 into eno.ename; Exit When C1 %notfound;----- commit; end loop; end; The cursor having query as SELECT .... FOR ... after COMMIT/ROLLBACK. The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

What is Statement Auditing ?

Description : What is Statement Auditing ?

Last Answer : Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects.

Find the output of the following Java code snippet after execution of each java statement labelled -Technology

Description : Find the output of the following Java code snippet after execution of each java statement labelled -Technology

Last Answer : The output is:IN5inin5

Complete the following statement. The method of incorporating change into project planning and execution processes is called the: Select one: a. project logic diagram. b. change management process. c. milestone schedule d. critical path e. rough order of magnitude

Description : Complete the following statement. The method of incorporating change into  project planning and execution processes is called the: Select one: a. project logic diagram. b. change management process. c. milestone schedule d. critical path e. rough order of magnitude

Last Answer : b. change management process.

Which of the following statement is correct? (a) If an attesting witness denies or does not recollect the execution of the document, its execution may be proved by primary evidence (b) If an attesting witness denies or does not recollect the execution of the document, its execution may be proved by other evidence (c) Its execution may be proved by secondary evidence (d) All the above

Description : Which of the following statement is correct? (a) If an attesting witness denies or does not recollect the execution of the  document, its execution may be proved by primary evidence (b) If an ... by other evidence (c) Its execution may be proved by secondary evidence (d) All the above

Last Answer : (b) If an attesting witness denies or does not recollect the execution of the document,  its execution may be proved by other evidence

State weather the following statement is true or false for EJB. 1. EJB exists in the middle-tier 2. EJB specifies an execution environment 3. EJB supports transaction processing A) 1-true, 2. true, 3. true B) 1- true, 2. false, 3. true C) 1- false, 2- false, 3- false D) 1-true, 2-true, 3-false

Description : State weather the following statement is true or false for EJB. 1. EJB exists in the middle-tier 2. EJB specifies an execution environment 3. EJB supports transaction processing A) 1-true, 2. true, 3. true B) 1- ... false, 3. true C) 1- false, 2- false, 3- false D) 1-true, 2-true, 3-false

Last Answer : A) 1-true, 2. true, 3. true

State which statement is true for Suspended process? i) The process is not immediately available for execution. ii) The process may be removed from suspended state automatically without removal order. A) i only B) ii only C) i and ii only D) None

Description : State which statement is true for Suspended process? i) The process is not immediately available for execution. ii) The process may be removed from suspended state automatically without removal order. A) i only B) ii only C) i and ii only D) None

Last Answer : A) i only

What does "jumps off the screen" mean?

Description : What does "jumps off the screen" mean?

Last Answer : answer:AMD is a relatively small manufacturer but well established in the industry. In this context, AMD jumps off the screen at the guy doing the research into acquiring companies for ... chip manufacturing division, but AMD is the one that really stands out from an acquisition perspective.

How can I dump an oracle schema to a LOCAL file over a network connection from sqlplus?

Description : How can I dump an oracle schema to a LOCAL file over a network connection from sqlplus?

Last Answer : By connecting and spooling are you excluding the exp/imp commands (or expdb/impdb in 10g)?

What are the two ways to incorporate images into a oracle forms application?

Description : What are the two ways to incorporate images into a oracle forms application?

Last Answer : Boilerplate Images Image_items

Can one export a subset of a table? (for DBA

Description : Can one export a subset of a table? (for DBA

Last Answer : From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example: exp scott/tiger tables=emp query=\"where deptno=10\"

What are the types of visual attribute settings?

Description : What are the types of visual attribute settings?

Last Answer : Custom Visual attributes Default visual attributes Named Visual attributes. Window

How does one use the import/export utilities? (for DBA

Description : How does one use the import/export utilities? (for DBA

Last Answer : Look for the "imp" and "exp" executables in your $ORACLE_HOME/bin directory. One can run them interactively, using command line parameters, or using parameter files. Look at the imp/exp parameters ... and export data with the "Schema Manager" GUI that ships with Oracle Enterprise Manager (OEM).

what is a display item?

Description : what is a display item?

Last Answer : Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.

What is import/export and why does one need it? (for DBA

Description : What is import/export and why does one need it? (for DBA

Last Answer : The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. They are also used to move Oracle data from one machine, database or schema to ... corruption. Ensure that all the data can be read. . Transporting tablespaces between databases . Etc.

Are there any troubleshooting tips for OEM? (for DBA

Description : Are there any troubleshooting tips for OEM? (for DBA

Last Answer : . Create the OEM repository with a user (which will manage the OEM) and store it in a tablespace that does not share any data with other database users. It is a bad practice to create ... successfully. Check the log to see the results of the execution rather than relying on this status.

Where can one get more information about TCL? (for DBA

Description : Where can one get more information about TCL? (for DBA

Last Answer : One can write custom event checking routines for OEM using the TCL (Tool Command Language) language. Check the following sites for more information about TCL: . The Tcl Developer Xchange - ... the OraTCL package . Tom Poindexter's Tcl Page - Oratcl was originally written by Tom Poindexter

How does one start the Oracle Intelligent Agent? (for DBA

Description : How does one start the Oracle Intelligent Agent? (for DBA

Last Answer : One needs to start an OIA (Oracle Intelligent Agent) process on all machines that will to be managed via OEM. For OEM 9i and above:

What is the Oracle Intelligent Agent? (for DBA

Description : What is the Oracle Intelligent Agent? (for DBA

Last Answer : The Oracle Intelligent Agent (OIA) is an autonomous process that needs to run on a remote node in the network to make the node OEM manageable. The Oracle Intelligent Agent is ... events registered in Enterprise Manager; and . Executing tasks associated with jobs submitted to Enterprise Manager.

How does one backout events and jobs during maintenance slots? (for DBA

Description : How does one backout events and jobs during maintenance slots? (for DBA

Last Answer : Managemnet and data collection activity can be suspended by imposing a blackout. Look at these examples: agentctl start blackout # Blackout the entrire agent agentctl stop blackout # Resume normal monitoring ... management agentctl start blackout -s jobs -d 00:20 # Blackout jobs for 20 minutes

What are the pre requisites ?

Description : What are the pre requisites ?

Last Answer : I. to modify data type of a column ? ii. to add a column with NOT NULL constraint ? To Modify the datatype of a column the column must be empty. to add a column with NOT NULL constrain, the table must be empty.

What kind of jobs can one schedule with OEM? (for DBA

Description : What kind of jobs can one schedule with OEM? (for DBA

Last Answer : OEM comes with pre-defined jobs like Export, Import, run OS commands, run sql scripts, SQL*Plus commands etc. It also gives you the flexibility of scheduling custom jobs written with the TCL language.

Difference between SUBSTR and INSTR ?

Description : Difference between SUBSTR and INSTR ?

Last Answer : INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of string1.SUBSTR (String1 n,m)SUBSTR returns a character string of size m in string1, starting from nth position of string1.

Should the OEM Console be displayed at all times (when there are scheduled jobs)? (for DBA

Description : Should the OEM Console be displayed at all times (when there are scheduled jobs)? (for DBA

Last Answer : When a job is submitted the agent will confirm the status of the job. When the status shows up as scheduled, you can close down the OEM console. The processing of the job is managed by the OIA ... that OEM will not be able to send e-mail and paging notifications when the Console is not started.

Explain UNION,MINUS,UNION ALL, INTERSECT ?

Description : Explain UNION,MINUS,UNION ALL, INTERSECT ?

Last Answer : INTERSECT returns all distinct rows selected by both queries.MINUS - returns all distinct rows selected by the first query but not by the second.UNION - returns all distinct rows selected by either queryUNION ALL - returns all rows selected by either query, including all duplicates.

WHY USE RMAN ? (for DBA

Description : WHY USE RMAN ? (for DBA

Last Answer : No extra costs Its available free ?RMAN introduced in Oracle 8 it has become simpler with newer versions and easier than user managed backups ?Proper security ?You are 100% sure your ... what need to backed up Knows what is required for recovery Knows what backups are redundant

WHAT IS RMAN ? (for DBA

Description : WHAT IS RMAN ? (for DBA

Last Answer : Recovery Manager is a tool that: manages the process of creating backups and also manages the process of restoring and recovering from them.