Recompiling Invalid Schema Objects

Objects in a database tend to get invalid due to operation such as patches, DDL changes and upgrades etc. The invalid objects are to compiled to ensure proper health of the database and user's access to them. I will provide here the ways you can compile objects in Oracle.

Identifying Invalid Objects:

col format OBJECT_NAME a40

SQL> SELECT OBJECT_NAME,
                            OBJECT_TYPE,
                            OWNER,
                            STATUS
           FROM DBA_OBJECTS
           WHERE STATUS='INVALID'
           ORDER BY OBJECT_NAME, OBJECT_TYPE, OWNER;

to get invalid objects list for a particular schema below is the SQL:

col format OBJECT_NAME a40

SQL> SELECT OBJECT_NAME,
                          OBJECT_TYPE,
                          OWNER,
                          STATUS
           FROM DBA_OBJECTS
          WHERE OWNER='DEPT' AND STATUS='INVALID'
          ORDER BY OBJECT_NAME, OBJECT_TYPE, OWNER;

DBMS_UTILITY.COMPILE_SCHEMA:
DBMS_UTILITY package proides COMPILE_SCHEMA procedure to compile all the objects in a schema:

SQL> EXECUTE DBMS_UTILITY.COMPILE_SCHEMA ('SCHEMA_NAME');

UTLRP and UTLPRP:
UTLRP and UTLPRP are the oracle provided scripts to compile all invalid objects in oracle database.

Ex: SQL> ?/rdbms/admin/utlrp

Manual Approach:
Invalid Objects can be compiled individually after you have the list of invalid objects in your oracle database. Below are some of the examples:

SQL> ALTER PACKAGE package_name COMPILE;

SQL> ALTER PACKAGE package_name COMPILE BODY;

SQL> ALTER PROCEDURE procedure_name COMPILE;

SQL> ALTER FUNCTION function_name COMPILE;

SQL> ALTER TRIGGER trigger_name COMPILE;

SQL> ALTER VIEW view_name COMPILE;


An alternative approach is to use the DBMS_DDL package to perform the recompilations:

SQL> EXEC DBMS_DDL.alter_compile('PACKAGE', 'SCHEMA_NAME', 'PACKAGE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'SCHEMA_NAME', 'PACKAGE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('PROCEDURE', 'SCHEMA_NAME', 'PROCEDURE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('FUNCTION', 'SCHEMA_NAME', 'FUNCTION_NAME');

SQL> EXEC DBMS_DDL.alter_compile('TRIGGER', 'SCHEMA_NAME', 'TRIGGER_NAME');
 
In addition to the above approaches you can write your own script to get the invalid objects and recompile them using the manual approach to recompile them all included in the script.

How to get DDL for an object: TABLE, INDEX, PACKAGE....

DBMS_METADATA is a package that can be used to get DDL for TABLE, INDEXES etc. Below is a quick view of how it works:


SQL> select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

 The output would be:

CREATE TABLE "SCOTT"."IDX3_TAB"
( "NAME" VARCHAR2(30),
"ID" NUMBER,
"ADDR" VARCHAR2(100),
"PHONE" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"


To get the create table definition without the storage clause you could do as follows:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

The output should be PL/SQL procedure successfully completed.


And then if you run

set long 100000
select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

would return

CREATE TABLE "SCOTT"."IDX3_TAB"
( "NAME" VARCHAR2(30),
"ID" NUMBER,
"ADDR" VARCHAR2(100),
"PHONE" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"

But the above package wasn't useful when I tried to get DDL information for a package body based on my experience and DBA_SOURCE from into help for that. Below is an example:
 
SQL> SELECT TEXT FROM DBA_SOURCE WHERE NAME='PKG_DEPT_PROCESS';

CURSOR_SHARING Parameter in Oracle

CURSOR_SHARING parameter is the one that oracle uses to control whether it will auto-bind a SQL statement or not.

For example:
SQL> select * from dept where dept_id = 'literal_value';

Oracle takes the above statement and replaces the predicate with something as below:

SQL> select * from dept where dept_id=:"SYS_B_0";

And now the oracle compares the above sql statement to the plans that it already has in the memory to see if the plan can be reused or not, perhaps leading to a better utilization of shared_pool and reduction in number hard parses performed by the system.

The cursor_sharing parameter can be set to three values:

• EXACT: This is the default setting. With this value in place, the query is not rewritten to use bind variables.
• FORCE: This setting rewrites the query, replacing all literals with bind values and setting up a one-size-fits-all plan—a single plan for the rewritten query. I'll demonstrate what that implies in a moment.
• SIMILAR: This setting also rewrites the query, replacing the literals with bind variables, but can set up different plans for different bind variable combinations. This last point is why CURSOR_SHARING=SIMILAR might reduce the number of plans generated. Because multiple plans may be generated, the setting of SIMILAR may or may not reduce the number of actual plans you observe in the shared pool.

An example to show how CURSOR_SHARING works with setting equal to EXACT, FORCE and SIMILAR:

SQL> alter session set cursor_sharing=EXACT;

Session altered.

SQL> select * from dual CS_EXACT where dummy='A';

no rows selected

SQL> select * from dual CS_EXACT where dummy='B';

no rows selected

SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> select * from dual CS_FORCE where dummy='A';

no rows selected

SQL> select * from dual CS_FORCE where dummy='B';

no rows selected

SQL> alter session set cursor_sharing=SIMILAR;

Session altered.

SQL> select * from dual CS_SIMILAR where dummy='A';

no rows selected

SQL> select * from dual CS_SIMILAR where dummy='B';

no rows selected

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from dual CS% where dummy=%'
4 order by sql_text;

SQL_TEXT
--------------------------------------------------------------------------------
select * from dual CS_EXACT where dummy='A'
select * from dual CS_EXACT where dummy='B'
select * from dual CS_FORCE where dummy=:"SYS_B_0"
select * from dual CS_SIMILAR where dummy=:"SYS_B_0"

CURSOR_SHARING=EXACT: From the above example as we can see oracle uses different plans for each of the select statements which include the word "CS_EXACT". With this setting every SQL statement you excute will be new and a new plan is generated for every query that we execute and the plans are not shared. And a new entry is created in V$SQL as you can see from above for every SQL Statement we execute.

CURSOR_SHARING=FORCE: When this is the setting for the parameter then for each statement that I have executed above which include the word "CS_FORCE" the literal values 'A' and 'B' are replaced by "SYS_B_0". The oracle uses the same plan for each of the SQL whether the predicate is either 'A' or 'B' since rest of the statement is similar and thus we see just one entry in V$SQL for the two SQL that we have executed.

CURSOR_SHARING=SIMILAR: When this is the setting for the parameter then for each statement that I have executed above which include the word "CS_FORCE" the literal values 'A' and 'B' are replaced by "SYS_B_0". The oracle uses the same plan for each of the SQL whether the predicate is either 'A' or 'B' since rest of the statement is similar and thus we see just one entry in V$SQL for the two SQL that we have executed. So, the settings FORCE and SIMILAR looks similar huh? But there is one difference between both i.e., when the setting is SIMILAR oracle not only checks for similar looking statements but also compares the plans. For example when I executed the SELECT statement with the literal value 'A' it generates a plan which will say a FULL TABLE SCAN for the statement. Now when I executed the same SELECT statement with the literal value 'B' the it also generates a plan for the statement and compares to the plan that is already stored i.e., when literal value is 'A'. If the explain plan for the SELECT statement when literal is 'A' a FULL TABLE SCAN and also a FULL TABLE SCAN (lets assume for now) when the literal value is 'B' then we see only one entry in the dynamic view V$SQL. If both the plans are different say a FULL TABLE SCAN when literal value is 'A' and an INDEX SCAN when literal value is 'B' which is not in this case as per our assumption then we will see two different entries in V$SQL for the SQL statements which contain the word "CS_SIMILAR" and you would see something as below (which is not with our example above): The results displayed shown below are with respect to an imaginary table "t" which is a big table of about atleast 100 rows and has an index on it and the data is skewed

SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> select * from t CS_FORCE where t_id=1;

1 row selected.

SQL> select * from t CS_FORCE where t_id='99';

1 row selected.

SQL> alter session set cursor_sharing=SIMILAR;

Session altered.

SQL> select * from t CS_SIMILAR where t_id='1';

1 row selected.

SQL> select * from t CS_SIMILAR where t_id='99';

1 row selected.

SQL> select sql_text

2 from v$sql
3 where sql_text like 'select * from t CS% where t_id=%'
4 order by sql_text;

SQL_TEXT
--------------------------------------------------------------------------------
select * from t CS_FORCE where t_id=:"SYS_B_0"
select * from t CS_SIMILAR where t_id=:"SYS_B_0"
select * from t CS_SIMILAR where t_id=:"SYS_B_0"

From the above example we can see that the plans are different say a FULL TABLE SCAN when literal value is '1' and an INDEX SCAN when literal value is '99' we see two different entries in V$SQL for the SQL statements which contain the word "CS_SIMILAR".

Database Structures: Logical Structures

Oracle database logical structures mainly comprise of Tablespaces, Segements, Extents and Oracle Datablocks.

I will present the information starting with the finest logical structure Oracle Datablocks.

Oracle Datablocks: Oracle datablocks are at the finest level granularity, all of the oracle datatbase data is stored in oracle datablocks. One oracle datablock corresponds to specific number of bytes which occupy the same number of bytes on the physical disk space. The size of a datablock is determined by the initialization parameter DB_BLOCK_SIZE. In addition to the one specified already you can specify upto 5 additional datablock sizes

Extent: Extent is the next level of oracle logical database space. A extent is comprised specific number of contiguous datablocks, obtained in a single allocation, used to store a specific type of information.

Segment: A segment is a set of extents allocated for a certain logical structure. The segments can be of one of following type data segment,index segment,temporary segment,rollback segment.

Tablespace: Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Database Structures: Physical Structures

A database consists of Physical Structures and Logical Structures in this post I will post information regarding the physical structures in simple terms:

Datafiles: Contain all of the database data; logical structures , such as tables, indexes, packages, procedures, functions, triggers and etc.

Redo Log Files: Hold records of all changes made to the database for recovery purposes.

Control Files: Record the physical structure and status of the database

Parameter File: Contain startup values for database parameters (often referred as the init.ora file )


Note: The explanation provided here is for understanding only.