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

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

1 Answer

Answer :

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

Related questions

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

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

Last Answer : Due to insufficient Shared SQL 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. LOGICAL & PHYSICAL ARCHITECTURE OF DATABASE.

What is SGA?

Description : What is SGA?

Last Answer : The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. ... . The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

Is it possible to insert comments into sql statements return in the data model editor?

Description : Is it possible to insert comments into sql statements return in the data model editor?

Last Answer : Yes

How many types of Sql Statements are there in Oracle

Description : How many types of Sql Statements are there in Oracle

Last Answer : There are basically 6 types of sql statments.They are a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and drop objects. b) Data Manipulation Language(DML) :: The DML ... e.g:: Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

What are the PL/SQL Statements used in cursor processing ?

Description : What are the PL/SQL Statements used in cursor processing ?

Last Answer : DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

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.

What is the basic structure of PL/SQL ?

Description : What is the basic structure of PL/SQL ?

Last Answer : PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

What are the datatypes a available in PL/SQL ?

Description : What are the datatypes a available in PL/SQL ?

Last Answer : Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.

How does one use ORADEBUG from Server Manager/ SQL*Plus? (for DBA

Description : How does one use ORADEBUG from Server Manager/ SQL*Plus? (for DBA

Last Answer : Execute the "ORADEBUG HELP" command from svrmgrl or sqlplus to obtain a list of valid ORADEBUG commands. Look at these examples: SQLPLUS> REM Trace SQL statements with bind variables SQLPLUS> oradebug ... oradebug lkdebug -a convres SQLPLUS> oradebug lkdebug -r (i.e 0x8066d338 from convres dump)

What is the difference between the SQL*Loader and IMPORT utilities?

Description : What is the difference between the SQL*Loader and IMPORT utilities?

Last Answer : These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL* ... from different data sources just so long as it conforms to ASCII formatted or delimited files.

You have just compiled a PL/SQL package but got errors, how would you view the errors?

Description : You have just compiled a PL/SQL package but got errors, how would you view the errors?

Last Answer : SHOW ERRORS

What command would you use to encrypt a PL/SQL application?

Description : What command would you use to encrypt a PL/SQL application?

Last Answer : WRAP

Can you use select in FROM clause of SQL select ?

Description : Can you use select in FROM clause of SQL select ?

Last Answer : Yes.

maxvalue.sql Select the Nth Highest value from a table?

Description : maxvalue.sql Select the Nth Highest value from a table?

Last Answer : select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name') group by level; Example: Given a table called emp with the following columns: -- id number -- name ... , max(sal) from emp -- where level=2 -- connect by prior sal > sal -- group by level

How you were passing cursor variables in PL/SQL 2.2?

Description : How you were passing cursor variables in PL/SQL 2.2?

Last Answer : In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2, the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.

Can cursor variables be stored in PL/SQL tables.If yes how. If not why?

Description : Can cursor variables be stored in PL/SQL tables.If yes how. If not why?

Last Answer : No, a cursor variable points a row which cannot be stored in a two- dimensional PL/SQL table.

What are various constraints used in SQL?

Description : What are various constraints used in SQL?

Last Answer : NULL -NOT NULL -CHECK -DEFAULT

minvalue.sql Select the Nth lowest value from a table?

Description : minvalue.sql Select the Nth lowest value from a table?

Last Answer : select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name') group by level; Example: Given a table called emp with the following columns: -- id number -- name ... , min(sal) from emp -- where level=2 -- connect by prior sal < sal -- group by level

What is difference between SQL and SQL*PLUS?

Description : What is difference between SQL and SQL*PLUS?

Last Answer : SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle ... SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Description : What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Last Answer : Procedures and Functions,Packages and Database Triggers.

What are the cursor attributes used in PL/SQL ?

Description : What are the cursor attributes used in PL/SQL ?

Last Answer : %ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows fetched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are ... featched. These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

What are the components of a PL/SQL block ?

Description : What are the components of a PL/SQL block ?

Last Answer : A set of related declarations and procedural statements is called block.

What are the components of a PL/SQL Block ?

Description : What are the components of a PL/SQL Block ?

Last Answer : Declarative part, Executable part and Exception part.Datatypes PL/SQL

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');

Question What is PL/SQL ?

Description : Question What is PL/SQL ?

Last Answer : PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

What package procedure used for invoke sql *plus from sql *forms ?

Description : What package procedure used for invoke sql *plus from sql *forms ?

Last Answer : Host (E.g. Host (sqlplus))

What is an SQL *FORMS ?

Description : What is an SQL *FORMS ?

Last Answer : SQL *forms is 4GL tool for developing and executing; Oracle based interactive application.

What is a Shared SQL pool?

Description : What is a Shared SQL pool?

Last Answer : The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

What are the two panes that Appear in the design time pl/sql interpreter?

Description : What are the two panes that Appear in the design time pl/sql interpreter?

Last Answer : 1. Source pane. 2. Interpreter pane

What are three panes that appear in the run time pl/sql interpreter?

Description : What are three panes that appear in the run time pl/sql interpreter?

Last Answer : 1. Source pane. 2. interpreter pane. 3. Navigator pane.

Where is a procedure return in an external pl/sql library executed at the client or at the server?

Description : Where is a procedure return in an external pl/sql library executed at the client or at the server?

Last Answer : At the client.

What are the sql clauses supported in the link property sheet?

Description : What are the sql clauses supported in the link property sheet?

Last Answer : Where start with having.

What is a SQL * NET?

Description : What is a SQL * NET?

Last Answer : SQL *NET is ORACLE's mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint- Server and Server-Server communications.

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.

Define SQL and state the differences between SQL and other conventional programming Languages

Description : Define SQL and state the differences between SQL and other conventional programming Languages

Last Answer : SQL is a nonprocedural language that is designed specifically for data access operations on normalized relational database structures. The primary difference between SQL and other conventional programming ... specify what data operations should be performed rather than how to perform them.

SQL> SELECT * FROM MY_SCHEMA.MY_TABLE; SP2-0678: Column or attribute type cannot be displayed by SQL*Plus Why I’m getting this error?

Description : SQL> SELECT * FROM MY_SCHEMA.MY_TABLE; SP2-0678: Column or attribute type cannot be displayed by SQL*Plus Why I’m getting this error?

Last Answer : The table has a BLOB column.

Name three clients to connect with Oracle, for example, SQL Developer:

Description : Name three clients to connect with Oracle, for example, SQL Developer:

Last Answer : SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an experienced dba should know at least three clients.

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 ?

Description : 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 ?

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

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

Description : Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

Last Answer : It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

What is the difference between DELETE and TRUNCATE statements?

Description : What is the difference between DELETE and TRUNCATE statements?

Last Answer : The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation ... be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

Can you audit SELECT statements?

Description : Can you audit SELECT statements?

Last Answer : YES. But beware; you will need a storage mechanism to hold your SQL SELECT audits, a high data volume that can exceed the size of your whole database, every day. SQL SELECT auditing can be ... data in the audit trail must also be audited to see who has selected data from the audit trail.

Consider the following table Furniture. Write SQL commands for the statements (i) to (viii) and write output for SQL queries (ix) and (x). -Technology

Description : Consider the following table Furniture. Write SQL commands for the statements (i) to (viii) and write output for SQL queries (ix) and (x). -Technology

Last Answer : SQL commands for the given statements (i) to (viii) ;1. SELECT FCODE, NAME. PRICE FROM Furniture WHERE PRICE < 5000;2. SELECT NAME. PRICE FROM Furniture3.4. WHERE NAME LIKE %table% ;5. SELECT DISTINCT WCODE ... ;15.16. Output for SQL queries (ix) and (x)(ix)SUM(PRICE)6500(x)COUNT(DISTINCT PRICE)5

In SQL, which command(s) is(are) used to issue multiple CREATE TABLE, CREATE VIEW, and GRANT statements in a single transaction? A. CREATE PACKAGE B. CREATE SCHEMAC.CREATE CLUSTERD.All of theaboveE.None of the above;

Description : In SQL, which command(s) is(are) used to issue multiple CREATE TABLE, CREATE VIEW, and GRANT statements in a single transaction? A. CREATE PACKAGE B. CREATE SCHEMA C. CREATE CLUSTER D. All of theabove E. None of the above;

Last Answer : CREATE SCHEMA

Consider the following two commands C1 and C2 on the relation R from an SQL database: C1: drop table R; C2: delete from R; Which of the following statements is TRUE? I. Both C1 and C2 delete the schema for R. II. C2 retains relation R, but deletes all tuples in R. III. C1 deletes not only all tuples of R, but also the schema for R. (A) I only (B) I and II only (C) II and III only (D) I, II and III

Description : Consider the following two commands C1 and C2 on the relation R from an SQL database:  C1: drop table R; C2: delete from R; Which of the following statements is TRUE? I. Both C1 and C2 delete the schema for R. II. C2 ... R. (A) I only (B) I and II only (C) II and III only (D) I, II and III

Last Answer : (C) II and III only

Analyse the factors causing a problems associated with water resources in India. -Geography

Description : Analyse the factors causing a problems associated with water resources in India. -Geography

Last Answer : Post-independence, the factors that caused problems with water resources in India are as follows: 1. Increasing industrialisation 2. Urbanization and their demands for water 3. Industrial wastes released into ... some of the major factors due to which India is facing a problem of water scarcity.

In context to the various factors responsible for causing the great upsurge of 1857, discuss about the introduction of Railways and Telegraphs. -SST 10th

Description : In context to the various factors responsible for causing the great upsurge of 1857, discuss about the introduction of Railways and Telegraphs. -SST 10th

Last Answer : Introduction of Railways and Telegraphs: (i) Telegraph poles were thought to be erected for the purpose of hanging people on them, who opposed the British rule in India. (ii) In ... Modern innovations threatened the old social rules and produced unrest among many common orthodox people in India.

Inflation is caused by certain factors 1. Those causing an increase in demand 2. Those a short, fall in supply 3. Structural rigidities in the economy 4. All of these 5. None of these

Description : Inflation is caused by certain factors 1. Those causing an increase in demand 2. Those a short, fall in supply 3. Structural rigidities in the economy 4. All of these 5. None of these

Last Answer : Those causing an increase in demand

Under Herzberg‟s theory, factors causing dissatisfaction is called (a) Demotivators ; (b) Negative stimuli ; (c) Hygiene factors ; (d) Defectors

Description : Under Herzberg‟s theory, factors causing dissatisfaction is called (a) Demotivators ; (b) Negative stimuli ; (c) Hygiene factors ; (d) Defectors

Last Answer :  (c) Hygiene factors ;

Which of the following statements is not true about triacylglycerols? (a) When solids and semisolids at room temperature, they are called fats. (b) When liquids at room temperature, they are called oils. (c) When hydrolyzed, they produce glycerol and carboxylate salts. (d) Triacylglycerols with low melting points are composed of saturated fatty acids, causing them to be liquids at room temperature

Description : Which of the following statements is not true about triacylglycerols? (a) When solids and semisolids at room temperature, they are called fats. (b) When liquids at room temperature, they are ... low melting points are composed of saturated fatty acids, causing them to be liquids at room temperature

Last Answer : Triacylglycerols with low melting points are composed of saturated fatty acids, causing them to be liquids at room temperature