How would you go about generating an EXPLAIN plan?

How would you go about generating an EXPLAIN plan?
by

1 Answer

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
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 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.

Can you see Execution Plan of a statement?

Description : Can you see Execution Plan of a statement?

Last 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 ... - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

What are the two main ways of generating electricity? How are they different from each other ? Explain. -SST 10th

Description : What are the two main ways of generating electricity? How are they different from each other ? Explain. -SST 10th

Last Answer : Electricity is generated by hydropower and thermal power: 1. Hydro electricity is generated by running water while thermal power is generated by burning coal, petroleum and natural gas. ... of hydro electricity production: Bhakra Nangal dam Example of thermal power production: Neyveli power plant

Explain Min-Max method of generating the game tree.

Description : Explain Min-Max method of generating the game tree.

Last Answer : Ans. The Minmax search procedure is a depth-first, depth-limited procedure. The ideas is to start at the current position & use the plausible-move generator to generate the set of possible successor ... its child values - 4. When value reaches the root: choose max value and the corresponding move.

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.

What are the return values of functions SQLCODE and SQLERRM ? What is Pragma EXECPTION_INIT ? Explain the usage ?

Description : What are the return values of functions SQLCODE and SQLERRM ? What is Pragma EXECPTION_INIT ? Explain the usage ?

Last Answer : SQLCODE returns the latest code of the error that has occurred. SQLERRM returns the relevant error message of the SQLCODE.

Explain what partitioning is and what its benefit is.

Description : Explain what partitioning is and what its benefit is.

Last Answer : Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

Explain materialized views and how they are used.

Description : Explain materialized views and how they are used.

Last Answer : Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

Explain the use of table functions.

Description : Explain the use of table functions.

Last Answer : Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Description : Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Last Answer : A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any ... functions and procedures that are grouped together based on their commonality to a business function or application.

Explain the use of setting GLOBAL_NAMES equal to TRUE.

Description : Explain the use of setting GLOBAL_NAMES equal to TRUE.

Last Answer : Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

Description : Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

Last Answer : ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Explain an ORA-01555

Description : Explain an ORA-01555

Last Answer : You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

Description : Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

Last Answer : ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. ... having to write transactions to an archive log and thus increases the performance of the database slightly.

Explain the difference between a data block, an extent and a segment.

Description : Explain the difference between a data block, an extent and a segment.

Last Answer : A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings ... that an object takes when grouped together are considered the segment of the database object.

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

Description : Explain the difference between a hot backup and a cold backup and the benefits associated with each.

Last Answer : A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is ... and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

What is a join ? Explain the different types of joins ?

Description : What is a join ? Explain the different types of joins ?

Last Answer : Join is a query which retrieves related columns or rows from multiple tables.Self Join - Joining the table with itself.Equi Join - Joining two tables by equating two common columns.Non-Equi Join - ... that query can also retrieve rows that do not have corresponding join value in the other table.

Explain Connect by Prior ?in a session before accessing next value ?

Description : Explain Connect by Prior ?in a session before accessing next value ?

Last Answer : Retrieves rows in hierarchical order.e.g. select empno, ename from emp where.

Explain how procedures and functions are called in a PL/SQL block ?

Description : Explain how procedures and functions are called in a PL/SQL block ?

Last Answer : Function is called as part of an expression. sal := calculate_sal ('a822'); procedure is called as a PL/SQL statement calculate_bonus ('A822');

What is a package ? What are the advantages of packages ? What is Pragma EXECPTION_INIT ? Explain the usage ?

Description : What is a package ? What are the advantages of packages ? What is Pragma EXECPTION_INIT ? Explain the usage ?

Last Answer : The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Explain the two type of Cursors ?

Description : Explain the two type of Cursors ?

Last Answer : There are two types of cursors, Implicit Cursor and Explicit Cursor. PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

Explain the usage of WHERE CURRENT OF clause in cursors ?

Description : Explain the usage of WHERE CURRENT OF clause in cursors ?

Last Answer : WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

Explain the following file extension related to library?

Description : Explain the following file extension related to library?

Last Answer : .pll,.lib,.pld The library pll files is a portable design file comparable to an fmb form file The library lib file is a plat form specific, generated library file comparable to a fmx form file The pld file is Txt format file and can be used for source controlling your library files Parameter

Explain types of Block in forms4.0?

Description : Explain types of Block in forms4.0?

Last Answer : Base table Blocks. Control Blocks. 1. A base table block is one that is associated with a specific database table or view. 2. A control block is a block that is not associated with a database table. ITEMS

Explain about stacked canvas views?

Description : Explain about stacked canvas views?

Last Answer : Stacked canvas view is displayed in a window on top of, or "stacked" on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.

Explain about horizontal, Vertical tool bar canvas views?

Description : Explain about horizontal, Vertical tool bar canvas views?

Last Answer : Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its menu bar. Vertical Tool bars are displayed along the left side of a window

What is a Database instance ? Explain

Description : What is a Database instance ? Explain

Last Answer : A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are ... helps up to improve database performance by decreasing the amount of I/O performed against data file.

What is a deadlock ? Explain .

Description : What is a deadlock ? Explain .

Last Answer : Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing ... when a commit/rollback operation performed or any one of this processes being killed externally.

Explain the relationship among Database, Tablespace and Data file.?

Description : Explain the relationship among Database, Tablespace and Data file.?

Last Answer : Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

Explain the relationship among database, tablespace and data file.

Description : Explain the relationship among database, tablespace and data file.

Last Answer : Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

Does PL/SQL support “overloading”? Explain

Description : Does PL/SQL support “overloading”? Explain

Last Answer : The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, ... Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.

Explain Oracle memory structure

Description : Explain Oracle memory structure

Last Answer : The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures ... the user has retrieved from the database or data that the user wants to place into the database.

Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT.

Description : Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT.

Last Answer : SHUTOWN NORMAL = SHUTDOWN : It waits for all sessions to end, without allowing new connections. SHUTDOWN IMMEDIATE : Rollback current transactions and terminates every session. SHUTDOWN ABORT : Aborts all ... in an inconsistent state. It's the fastest method, but can lead to database corruption.

Explain the differences between PFILE and SPFILE

Description : Explain the differences between PFILE and SPFILE

Last Answer : A PFILE is a Static, text file that initializes the database parameter in the moment that it's started. If you want to modify parameters in PFILE, you have to restart the database. ... binary file that allows you to overwrite parameters while the database is already started (with some exceptions)

How would you go about increasing the buffer cache hit ratio?

Description : How would you go about increasing the buffer cache hit ratio?

Last Answer : Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

Description : You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

Last Answer : I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

Can we use GO-BLOCK package in a pre-field trigger ?

Description : Can we use GO-BLOCK package in a pre-field trigger ?

Last Answer : No

What is "dirty power" and is my space heater generating it?

Description : What is "dirty power" and is my space heater generating it?

Last Answer : Harmonics, they are the source of this dirty power you speak of. DC switching power supplies are some of the worst offenders. These are transients and non 60hz frequencies that are generated by said appliance. ... your heater works but I'm guessing it uses some sort of switching to run on low power.

Creativity workbook for generating new product ideas?

Description : Creativity workbook for generating new product ideas?

Last Answer : answer:Not sure what you are looking for. Some people really like mind mapping, though I have not tried it. I find it helps to organize information in a tree structure. This is a freeware tool that I have ... it for awhile I might write, That won't work because , but it might work if instead

Assuming the US reduces coal use due to negative externalities, should the US restrict coal exports to China and India who seek to use and build more coal-fired electrical generating plants?

Description : Assuming the US reduces coal use due to negative externalities, should the US restrict coal exports to China and India who seek to use and build more coal-fired electrical generating plants?

Last Answer : Coal jobs are already being lost at an alarming rate. If we don’t mine it, it will come from other coal exporting countries. I don’t see any benefit to hurting ourselves in a futile attempt to manage other countries.

Has anyone seen this article on generating energy from seawater?

Description : Has anyone seen this article on generating energy from seawater?

Last Answer : Bill1939 The link isn’t working.

What are good ways of generating nonsense, absurdity and silliness?

Description : What are good ways of generating nonsense, absurdity and silliness?

Last Answer : Of course. It balances the serious side out. Most times the uber silly stuff starts when someone makes a gaff during a serious statement. As long as the majority follow the path to silliness, it rolls ... always seems to be one person that is naive enough to not get it and it perpetuates from there.

Can someone help me generating a specific logrythmic scale?

Description : Can someone help me generating a specific logrythmic scale?

Last Answer : If the ratings are r1, r2,...,rn then convert each ri to ri*100/(r1+r2+...+rn)

What is the best way to create an online interface for generating PDF files based on a predefined template?

Description : What is the best way to create an online interface for generating PDF files based on a predefined template?

Last Answer : I do this. I accept the form fields over html. Then I use OpenOffice Uno to do a search and replace of the form fields. Then I use OpenOffice integration to print the .odt as a .pdf which is returned to the user.

Gray matter generating conscious experiences: When will scientists solve the mind-body problem?

Description : Gray matter generating conscious experiences: When will scientists solve the mind-body problem?

Last Answer : Scientists will never be able to solve this enigma, as it lies outside the range of observable phenomena.

Which of the following uses a number of two-dimensional profiles for generating athree-dimensional object? a.Tweaking b.Lofting c.Filleting d.none of the above

Description : Which of the following uses a number of two-dimensional profiles for generating athree-dimensional object? a.Tweaking b.Lofting c.Filleting d.none of the above

Last Answer : b.Lofting

QFor generating Coons patch we require a.A set of grid points on surface b.A set of control points c.Four bounding curves defining surface d.Two bounding curves and a set of grid control points

Description : QFor generating Coons patch we require a.A set of grid points on surface b.A set of control points c.Four bounding curves defining surface d.Two bounding curves and a set of grid control points

Last Answer : c.Four bounding curves defining surface

What are the two main ways of generating electricity? -SST 10th

Description : What are the two main ways of generating electricity? -SST 10th

Last Answer : The two main ways of generating electricity are:-