ICCG.MD.090a - Application Code SQL

	OMG = Oracle Method Guideline
	OMS = Oracle Method Standard

Code Layout

OMS-51001:

Write SQL statements in lowercase characters. Use uppercase characters when attention should be drawn to a specific part of the SQL statement. Note that comparisons to literal constants might require uppercase characters.

Rationale	Writing all SQL statements in lowercase makes the code more 'quiet ' and easier to read.

OMS-51002:

Start every clause that contains a column name, a table name, or a SQL reserved word on a new line.

Rationale	Experience has shown that SQL statements written using this method are less prone to editing errors. Therefore, this notation decreases development and maintenance time.

OMS-51003:

Place commas that separate column or table names on the first position of a new line. This rule also applies to data definition statements.

Rationale	Experience has shown that SQL statements written using this method are less prone to editing errors. Adding or removing lines does not result in missing commas. Therefore, this notation decreases development and maintenance time.

Analyzing SQL Statements

OMS-52844:

Ensure that your SQL statements do not use more than 1MB of sharable memory. Check the usage by using V$SQL.

Rationale

You should be careful with large and complex SQL statements. These may use a considerable amount of sharable memory which places a large burden on the shared pool. The larger the SQL statement, the more memory allocations is required to build a sharable cursor in the cursor cache. Therefore SQL statements which require a large amount of memory eventually evolve into a scalability problem as it limits the amount of sharable cursors that can be active in the shared pool. Large SQL statements may consume several megabytes of sharable memory.

Example	In the example below, you can see how much sharable memory has been used for the simple SQL Statement: SQL> select ename 2 from emp; SQL> select sql_text 2 , sharable_mem 3 from v$sql 4 where sql_text like '%select ename from% '; SQL_TEXT.................................SHARABLE_MEM ------------------------------------.....---------- select ename from emp....................6139 In this example a bit more than 6K of shared memory has been used.