Finding Stale Statistics in Oracle Database

Statistics are very important to generate efficient execution plans and thus directly related to database performance. We have nightly maintenance window, which runs for an hour, each alternate day.

As we are growing very fast, the 1 hour window sometimes can't cover all the tables and indexes.

Here is how we could find the stale/obsolete statistics - data for those objects have been modified more than 10%.

How to find?

SQL> SET SERVEROUT ON

SQL> DECLARE
ObjList DBMS_STATS.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'PROD', objlist=>ObjList, options=>'LIST STALE');
FOR i IN ObjList.FIRST..ObjList.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/

In my case, it were showing a couple of tables -

CRITICAL_INCIDENT - TABLE
EXTRA_TEMPLATE - TABLE
MODULE_ASSIGNMENT - TABLE


How to fix?

Simple - by collecting statistics for those objects -

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'PROD', tabname=>'EXTRA_TEMPLATE', estimate_percent=>50, cascade=>true);


More:

* The important view from where we could find the stale information and number of modifications done on a table is USER_TAB_MODIFICATIONS

* Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

Database Link and Public Database Link

Database link is a useful thing that I use time to time mostly to compare small tables among databases. We have both way (master-master configuration) replication in place, so sometimes, I need to check few things after small release to make sure some tables are in sync.

I thought, it is worth writing few words about this.

What is database link?

Officially, a database link is a schema object in one database that enables access to other objects on another database. I would like to say - it is kind of 'a connection cable' that establish connections between databases.

Side rule: If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.

In SQL statements in source database, we can refer to a table or view on the other destination database by appending @dblink_name (at sign then db link name) to the table or view name.

How to create database link?

For example, we want to access a destination database which is known as DX01.

CREATE DATABASE LINK DX01.DOMAIN.NET CONNECT TO prod IDENTIFIED BY xyz USING 'DX01';

Here,
* DX01.DOMAIN.NET is the db link name
* prod is the destination schema and xyz is the pass to access that. So we would give access through 'prod' schema on destination server.
* DX01 is the service name for the destination database, which should be found in tnsnames.ora file on the source database.

Now, I can compare tables and also issue other commands to access tables on DX01 remote database using the database link.

SQL> SELECT COUNT(*) FROM LOGIN@DX01.DOMAIN.NET;

Also, to compare tables if those are in sync or not -

SQL> SELECT * FROM LOGIN
MINUS
SELECT * FROM LOGIN@DX01.DOMAIN.NET;



Who owns the database link?

By default, with the create syntax shown above, the database user who is creating database link will own this link - no other user will be able to use this.

To get information of the existing database links in a database, need to query the following view -

SQL> SELECT * FROM DBA_DB_LINKS;



Public database link

There may be cases where I want to create only one database link and would like to use the link for all the schema in source database. Public database link is the answer. The PUBLIC key word is specified to create a public database link available to all users.

CREATE PUBLIC DATABASE LINK DX01.DOMAIN.NET USING 'DX01';

Now, for example, HR schema can use this link, PROD schema can use this link and others too in source database.

Side note: To create a public database link, we will need the CREATE PUBLIC DATABASE LINK system privilege.

jdbc driver bug in 10g for batch updates

Recently we have discovered that, our batch updates not working for larger batch size. Then came to know that jdbc does not support batch size more than 65535 in 10g for the following versions-

• 10.1.0.4
• 10.2.0.2

The operations simply ignores rows after 65535. There was no errors for this types of serious problem!

The problem has been fixed in the versions -

10.2.0.2 Patch 3 on Windows Platforms

10.2.0.3 (Server Patch Set)