What are the factors causing the reparsing of SQL statements in SGA?

ORACLE Interview Questions and Answers (Part 1)

(Continued from previous question...)

120. What are the factors causing the reparsing of SQL statements in SGA?

Due to insufficient shared pool size.
Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.

(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?