DBA Management

Table of Contents

• Introduction to DB Administration
• Index Management
  • Oracle Options
  • PostgreSQL Options
  • MySQL Options
• Database Statistics
  • Oracle Options
  • PostgreSQL Options
  • MySQL Options
• Space Management
  • Oracle Options
  • PostgreSQL Options
  • MySQL Options

Introduction to DB Administration

This part of SqlUtil contain some additional tools to handle subset of database administration. Common application developer usually does not need to handle anything described in this section on his own.

Warning

Database administration is complex task and it requires knowledge in various areas of IT. Always ask your DBA if and when you can use these tools.

DBA tools are part of the AbstractDatabase (or Database) classes. Methods described below are wrappers around database specific commands.

Index Management

• SqlUtil::AbstractDatabase::rebuildIndex

Index rebuilding is quite controversial task and it is highly variable for each database server. And even worse - it's varying between database server versions too. Modern database servers usually do not need to rebuild indexes in regular operations, but there can be situations when it's required still. Like: testing DB features, index corruption as a result of HW failure, storage parameters changing etc.

The easiest criteria which can be quickly and easily given to you is it not to rebuild or coalesce any index unless you have a specific, proven reason for doing so.

rebuildIndex() methods always takes a index name or an SqlUtil::AbstractIndex instance as first argument. The second argument is an optional hash with rebuild index options. These options are specific for each database server. See driver-specific modules for more info.

new Database(ds).rebuildIndex("ix_foo_bar");

Oracle Options

Oracle backend implementation uses ALTER INDEX REBUILD statement internally.

Option	Type	Description
parallel	Boolean	Use parallel keyword in the statement if it's set to True
logging	Boolean	Use logging keyword in the statement if it's set to True. By default nologging keyword is used.
statistics	Boolean	Use compute statistics keyword in the stetement if it's set to True
tablespace	String	Rebuild index in given tablespace
cond_rebuild	Boolean	Rebuild index only when it meets INDEX_STATS criteria
cond_maxheight	Integer	maximum index height before rebuilding index
cond_maxleafpct	Integer	maximum percentage of deleted leaf nodes before rebuilding index

PostgreSQL Options

PostgreSQL uses REINDEX INDEX statement internally.

There are no special options for PostgreSQL.

MySQL Options

MySQL does not offer any command to rebuild an index. Exception MYSQL-REBUILD-INDEX-ERROR is thrown if is the rebuildIndex method called for MySQL.

Use reclaimSpace method for MySQL.

Database Statistics

• SqlUtil::AbstractDatabase::computeStatistics

Database statistics are critical metadata used by SQL optimizers to build as good execution plans as possible. Some DB environments refresh statistics on regular basis, some not. All is usually highly configured by DBA.

The optional argument holds options for statistics. These options are specific for each database server. See driver-specific modules for more info.

Oracle Options

Oracle uses DBMS_STATS package internally. With GATHER_SCHEMA_STATS or GATHER_TABLE_STATS depending on options set below.

Statistics are calculated in currently logged user's schema.

Please consult Oracle documentation for exact option meanings.

Option	Type	Description
tables	list of strings	If it's set, the table by table statistics are computed with GATHER_TABLE_STATS. Otherwise the full schema with GATHER_SCHEMA_STATS is used.
estimate_percent	Integer	A percent as integer given. DBMS_STATS.AUTO_SAMPLE_SIZE is used as a default
block_sample	Boolean	Whether use random block sampling
method_opt	String	an optional size clause. By default "for all columns size auto" is used.
degree	Integer	a degree of parallelism. Default is NULL.
granularity	String	granularity of statistics to collect. Default is "ALL"
cascade	Boolean	True = gather statistics on indexes as well, False = do not make statistics for indexes. By default "dbms_stats.auto_cascade" is used.
stattab	String	user statistics table identifier describing where to save the current statistics
statid	String	identifier to associate with these statistics within stattab
options	String	further specification of which objects to gather statistics. By default "gather auto"
statown	String	schema containing stattab (if different than ownname)

Example: Compute statistics for enumerated tables only.

hash<auto> opts = (
    "tables": ("workflows", "workflow_instance"),
);
db.computeStatistics(opts);

PostgreSQL Options

PostgreSQL uses ANALYZE TABLE internally.

Option	Type	Description
tables	list of strings	If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used.

MySQL Options

MySQL uses ANALYZE TABLE internally.

Please consult MySQL documentation for exact option meanings.

Option	Type	Description
tables	list of strings	If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used.
no-binlog	Boolean	If True, the "no_write_to_binlog" is used. If False (default), the "local" keyword is used.

Space Management

• SqlUtil::AbstractDatabase::reclaimSpace

Space reclaiming can be done if there was a huge deletion from some tables. As usual causes are DB-dependent.

The optional argument holds options for space management. These options are specific for each database server. See driver-specific modules for more info.

Oracle Options

Oracle uses ALTER TABLE SHRINK SPACE CASCADE statement internally. So it requires enabled row movement.

Please consult Oracle documentation for more information.

Option	Type	Description
tables	list of strings	If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used.

PostgreSQL Options

PostgreSQL uses VACUUM internally.

Please consult PostgreSQL documentation for exact option meanings.

Option	Type	Description
tables	list of strings	If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used.
full	Boolean	True if full keyword should be used
analyze	Boolean	True if analyze keyword should be used

MySQL Options

MySQL uses OPTIMIZE TABLE internally.

Please consult MySQL documentation for exact option meanings.

Option	Type	Description
tables	list of strings	If it's set, the table by table statistics are computed. Otherwise all tables in the user DB are used.
no-binlog	Boolean	If True, the "no_write_to_binlog" is used. If False (default), the "local" keyword is used.