What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ? What are the factors that affect OPTIMIZER in choosing an Optimization approach ?

ORACLE Interview Questions and Answers (Part 1)

(Continued from previous question...)

49. What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ? What are the factors that affect OPTIMIZER in choosing an Optimization approach ?

Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

(Continued on next question...)

Other Interview Questions

• What are the components of physical database structure of Oracle database?
• What are the components of logical database structure of Oracle database?
• What is a tablespace?
• What is SYSTEM tablespace and when is it created?
• Explain the relationship among database, tablespace and data file.
• What is schema?
• What are Schema Objects?
• Can objects of the same schema reside in different tablespaces?
• Can a tablespace hold objects from different schemes?
• What is Oracle table?
• What is an Oracle view?
• What is Partial Backup ?
• What is Mirrored on-line Redo Log ?
• What is Full Backup ?
• Can a View based on another View ?
• Can a Tablespace hold objects from different Schemes ?
• Can objects of the same Schema reside in different tablespaces.?
• What is the use of Control File ?
• Do View contain Data ?
• What are the Referential actions supported by FOREIGN KEY integrity constraint ?
• What are the type of Synonyms?
• What is a Redo Log ?
• What is an Index Segment ?
• Explain the relationship among Database, Tablespace and Data file.?
• What are the different type of Segments ?
• What are Clusters ?
• What is an Integrity Constrains ?
• What is an Index ?
• What is an Extent ?
• What is a View ?
• What is Table ?
• Can a view based on another view?
• What are the advantages of views?
• What is an Oracle sequence?
• What is a synonym?
• What are the types of synonyms?
• What is a private synonym?
• What is a public synonym?
• What are synonyms used for?
• What is an Oracle index?
• How are the index updates?
• What is a Tablespace?
• What is Rollback Segment ?
• What are the Characteristics of Data Files ?
• How to define Data Block size ?
• What does a Control file Contain ?
• What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
• What is Index Cluster ?
• What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ? What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
• What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
• How does one create a new database? (for DBA)
• What database block size should I use? (for DBA)
• What are the different approaches used by Optimizer in choosing an execution plan ?
• What does ROLLBACK do ?
• How does one coalesce free space?(for DBA)
• How does one prevent tablespace fragmentation? (for DBA)
• Where can one find the high water mark for a table? (for DBA)
• What is COST-based approach to optimization ?
• What does COMMIT do ?
• How are extents allocated to a segment? (for DBA)
• Can one rename a database user (schema)? (for DBA)
• Define Transaction ?
• What is Read-Only Transaction ?
• What is a deadlock ? Explain .
• What is a Schema ?
• What is a cluster Key ?
• What is Parallel Server ?
• What are the basic element of Base configuration of an oracle Database ?
• What is clusters ?
• What is an Index ? How it is implemented in Oracle Database ?
• What is a Database instance ? Explain
• WWhat is the use of ANALYZE command ?
• What is default tablespace ?
• What are the system resources that can be controlled through Profile ?
• What is Tablespace Quota ?
• What are the different Levels of Auditing ?
• What is Statement Auditing ?
• What are the database administrators utilities avaliable ?
• How can you enable automatic archiving ?
• What are roles? How can we implement roles ?
• What are Roles ?
• What are the use of Roles ?
• What is Privilege Auditing ?
• What is Object Auditing ?
• What is Auditing ?
• How does one see the uptime for a database? (for DBA
• Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE? (for DBA
• How do I find used/free space in a TEMPORARY tablespace? (for DBA
• What is a profile ?
• How will you enforce security using stored procedures?
• How can one see who is using a temporary segment? (for DBA
• How does one get the view definition of fixed views/tables?
• What are the dictionary tables used to monitor a database spaces ?
• How can we specify the Archived log file name format and destination?
• What is user Account in Oracle database?
• When will the data in the snapshot log be used?
• What dynamic data replication?
• What is Two-Phase Commit ?
• How can you Enforce Referential Integrity in snapshots ?
• What is a SQL * NET?
• What is a SNAPSHOT ?
• What is the mechanism provided by ORACLE for table replication ?
• What is snapshots?
• What are the various type of snapshots?
• Describe two phases of Two-phase commit ?
• What is snapshot log ?
• What are the benefits of distributed options in databases?
• What are the options available to refresh snapshots ?
• What is a SNAPSHOT LOG ?
• What is Distributed database ?
• How can we reduce the network traffic?
• Differentiate simple and complex, snapshots ?
• What are the Built-ins used for sending Parameters to forms?
• Can you have more than one content canvas view attached with a window?
• Is the After report trigger fired if the report execution fails?
• Does a Before form trigger fire when the parameter form is suppressed?
• What is SGA?
• What is a shared pool?
• What is mean by Program Global Area (PGA)?
• What is a data segment?
• What are the factors causing the reparsing of SQL statements in SGA?
• What are clusters?
• What is cluster key?
• Do a view contain data?
• What is user Account in Oracle database?
• How will you enforce security using stored procedures?
• What are the dictionary tables used to monitor a database space?
• Can a property clause itself be based on a property clause?
• If a parameter is used in a query without being previously defined, what diff. exist betw. report 2.0 and 2.5 when the query is applied?
• What are the sql clauses supported in the link property sheet?
• What is trigger associated with the timer?
• What are the trigger associated with image items?
• What are the different windows events activated at runtimes?
• When do you use data parameter type?
• What is difference between open_form and call_form?
• What is new_form built-in?
• What is the "LOV of Validation" Property of an item? What is the use of it?
• What is the diff. when Flex mode is mode on and when it is off?
• What is the diff. when confine mode is on and when it is off?
• What are visual attributes?
• Which of the two views should objects according to possession?
• What are the two types of views available in the object navigator(specific to report 2.5)?
• What are the vbx controls?
• What is the use of transactional triggers?
• How do you create a new session while open a new form?
• What are the ways to monitor the performance of the report?
• If two groups are not linked in the data model editor, What is the hierarchy between them?
• An open form can not be execute the call_form procedure if you chain of called forms has been initiated by another open form?
• Explain about horizontal, Vertical tool bar canvas views?
• What is the purpose of the product order option in the column property sheet?
• What is the use of image_zoom built-in?
• How do you reference a parameter indirectly?
• What is a timer?
• What are the two phases of block coordination?
• What are Most Common types of Complex master-detail relationships?
• What is a text list?
• What is term?
• What is use of term?
• What is pop list?
• What is the maximum no of chars the parameter can store?
• What are the default extensions of the files created by library module?
• What are the Coordination Properties in a Master-Detail relationship?
• How do you display console on a window ?
• What are the different Parameter types?
• State any three mouse events system variables?
• What are the types of calculated columns available?
• Explain about stacked canvas views?
• How does one do off-line database backups? (for DBA
• What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?
• What are the built-ins that are used to Attach an LOV programmatically to an item?
• How does one do on-line database backups? (for DBA
• How does one backup a database using RMAN? (for DBA
• What are the different file extensions that are created by oracle reports?
• What is strip sources generate options?
• How does one put a database into ARCHIVELOG mode? (for DBA
• What is the basic data structure that is required for creating an LOV?
• How does one backup archived log files? (for DBA
• Does Oracle write to data files in begin/hot backup mode? (for DBA
• What is the Maximum allowed length of Record group Column?
• Which parameter can be used to set read level consistency across multiple queries?
• What are the different types of Record Groups?