Overview

In Tibero DBMS, performance degradation or failures can be prevented by limiting some supported features or certain functions internally. However, it is recommended that these matters be carried out under the review of the DB administrator.

Method

1. DBMS Function Restrictions

1.2. Major Restrictions

• HINT
• Use of Parallel, Append
• Dynamic SQL
• Database Link
• Trigger
• Package & Procedure & Function 
• Foreign Key
• Global Index
• Global Temporary Table
• Restriction on use of HINT

If HINT must be used, it should be reviewed and pre-agreed with the DB administrator regarding the impact.

Use of Parallel can cause excessive resource consumption during operation, which may affect the service.

For Append hint, rollback of work on nologging tables is not possible.

Restriction on Dynamic SQL Use

If the entire query is executed multiple times using '%s' without using variables, Hard Parsing occurs, which causes performance degradation.

Restriction on Database Link Use

If the source server fails, the failure can propagate to the target server.

In case of unexpected errors causing network failure, the session is not automatically cleaned up.

How to check the usage status of Database Link.

[Example] Query to Check DB Link Usage

SELECT OBJECT_TYPE, OWNER, OBJECT_NAME 
FROM DBA_OBJECTS 
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OWNER NOT IN ('SYS','SYSCAT','SYSGIS')

Restriction on Trigger Use

A maximum of 3 triggers can exist per table: one each for insert, update, and delete. Changes in business requirements or large batch operations may lead to system performance degradation.

Parallel Insert or Parallel Append restricts triggers from being executed.

How to check the usage status of Triggers.

[Example] Query to Check Trigger Usage

SELECT OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, TABLE_NAME
FROM DBA_TRIGGERS
WHERE OWNER NOT IN ('SYS','SYSCAT','SYSGIS');

Restriction on Package & Procedure & Function Use

Because dependencies are set, additional work on related objects may be required when working on specific objects.

Generally, recursive methods and multiple SQLs are used, making root cause tracing cumbersome, and errors occurring during business processing are difficult to handle.

Example of how to check the usage status of Package & Procedure & Function.

[Example] Query to Check PSM Usage

SELECT OWNER, OBJECT_TYPE, COUNT(1)
FROM DBA_OBJECTS
WHERE OBJECT_TYPE IN ('PACKAGE', 'FUNCTION','PROCEDURE') 
AND OWNER NOT IN ('SYS','SYSCAT','SYSGIS')
GROUP BY OWNER, OBJECT_TYPE;

Restriction on Foreign Key Use

When FK is applied, data insert and delete must be done in order.

Insert must be done in parent → child order, and delete in child → parent order. If the order is different, insert and delete will not execute, resulting in errors.

When multiple levels of inheritance are involved, the relationships become complex, causing management difficulties.

When inserting data into child tables, a Select For Update equivalent trigger is triggered to check whether the inherited column values exist in the parent, causing additional resource consumption.

Example of how to check Foreign Key usage.

[Example] Query to Check Foreign Key Usage

SELECT OWNER, CONSTRAINT_TYPE, CONSTRAINT_NAME, TABLE_NAME, DELETE_RULE 
FROM DBA_CONSTRAINTS
WHERE OWNER NOT IN ('SYS','SYSCAT','SYSGIS')
AND CONSTRAINT_TYPE = 'R'

Restriction on Global Index Use

When performing Drop, Split, Move, or Truncate on a specific partition, the entire index becomes unusable, affecting other partitions not targeted, which can lead to performance degradation and excessive resource consumption.

If partitioning techniques are used according to data optimization policies, indexes must be created as Local Indexes.

Example of how to check Global Index usage status.

[Example] Query to Check Global Index Usage

SELECT A.OWNER, A.INDEX_NAME, A.TABLE_NAME, A.INDEX_TYPE 
FROM DBA_INDEXES A, DBA_PART_TABLES B
WHERE A.OWNER = B.OWNER
AND A.OWNER NOT IN ('SYS','SYSCAT','SYSGIS')
AND A.TABLE_NAME = B.TABLE_NAME
AND A.PARTITIONED = 'NO'
UNION ALL
SELECT A.OWNER, A.INDEX_NAME, A.TABLE_NAME, LOCALITY
FROM DBA_INDEXES A, DBA_PART_TABLES B
WHERE A.OWNER NOT IN ('SYS','SYSCAT','SYSGIS')
AND LOCALITY = 'GLOBAL'
ORDER BY 1,2

Restriction on Global Temporary Table Use

Global Temporary Tables are volatile tables where data remains only while the session is maintained.

Since there is no data, statistics cannot be collected, and if a malicious execution plan is generated, performance may degrade.

How to check whether Global Temporary Tables are used.

[Example] Query to Check Global Temporary Table Usage

SELECT OBJECT_TYPE, OWNER, OBJECT_NAME, TEMPORARY
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = ‘TABLE’
AND TEMPORARY = 'Y'
AND OWNER NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
AND NOT REGEXP_LIKE(OBJECT_NAME, 'PLAN_TABLE|_TB_STAT_SNAP_', 'i')

2. DBMS OBJECT Checks

2.1. Check Enable Row Movement

Example of how to query tables with ROW_MOVEMENT enabled.

[Example] Check Enable Row Movement

SQL> 
SELECT OWNER, TABLE_NAME, ROW_MOVEMENT 
FROM DBA_TABLES
WHERE ROW_MOVEMENT = 'ENABLED'
/

2.2. Check Role

Example of how to query tables missing SELECT privileges.

[Example] Query to Check Missing Select Privileges

SELECT 'GRANT SELECT ON '||A.OWNER||'.'||A.TABLE_NAME||' TO '||'TARGET_ROLE;' 
FROM DBA_TABLES A
WHERE OWNER = 'TIBERO' -- set desired user
AND NOT EXISTS ( SELECT 1 FROM DBA_TAB_PRIVS B
WHERE A.OWNER=B.OWNER
AND A.TABLE_NAME=B.TABLE_NAME
AND B.GRANTEE='TARGET_ROLE'
AND B.PRIVILEGE='SELECT' )

Example of how to query tables missing DML privileges.

[Example] Query to Check Missing DML Privileges

SELECT 'GRANT INSERT, DELETE, UPDATE ON '||A.OWNER||'.'||A.TABLE_NAME||' TO '||'TARGET_ROLE;'
FROM DBA_TABLES A
WHERE OWNER = 'TIBERO' -- set desired user
AND NOT EXISTS ( SELECT 1 FROM DBA_TAB_PRIVS B
WHERE A.OWNER=B.OWNER
AND A.TABLE_NAME=B.TABLE_NAME
AND B.GRANTEE='TARGET_ROLE'
AND B.PRIVILEGE IN ('INSERT','UPDATE','DELETE') )

Example of how to query revocation of DML and Alter privileges granted to roles.

[Example] Privilege Revocation

SELECT 'REVOKE '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' FROM '||'TARGET_ROLE;'
FROM DBA_TAB_PRIVS
WHERE GRANTEE='TARGET_ROLE'
AND PRIVILEGE IN ('INSERT','UPDATE','DELETE', 'ALTER')

2.3. Synonym Creation and Deletion

Example of how to create Public Synonyms.

[Example] Create Public Synonym

SELECT 'CREATE PUBLIC SYNONYM '||'SYNONYM_NAME'||' FOR '||OWNER||'.'||OBJECT_NAME||';'
FROM DBA_OBJECTS A
WHERE OWNER = 'TIBERO' -- set desired user
AND OBJECT_TYPE IN ('TABLE', 'VIEW','SEQUENCE','PROCEDURE', 'FUNCTION', 'PACKAGE') 
AND NOT EXISTS (SELECT 1 FROM DBA_SYNONYMS B
WHERE A.OWNER = B.ORG_OBJECT_OWNER
AND A.OBJECT_NAME = B.ORG_OBJECT_NAME
AND B.OWNER = 'PUBLIC')

Example of how to delete Public Synonyms.

[Example] Drop Public Synonym

SELECT 'DROP PUBLIC SYNONYM '||SYNONYM_NAME||';'
FROM DBA_SYNONYMS
WHERE ORG_OBJECT_OWNER = 'TIBERO' -- set original user of the synonym
AND OWNER = 'PUBLIC'

2.4. Check Table PCTFREE Setting

Example of how to query table PCTFREE settings.

[Example] Check PCTFREE Setting

SELECT OWNER, TABLE_NAME,PCT_FREE 
FROM DBA_TABLES
WHERE PCT_FREE != 10
UNION
SELECT OWNER, TABLE_NAME,PCT_FREE 
FROM DBA_TAB_PARTITIONS 
WHERE PCT_FREE != 10
UNION
SELECT OWNER, TABLE_NAME,PCT_FREE 
FROM DBA_TAB_SUBPARTITIONS 
WHERE PCT_FREE != 10

2.5. Monthly Partition Check

If there is no partition for 2 months ahead from the current time, it will appear in the results.

[Example] Monthly Partition Check

SELECT OWNER, TABLE_NAME, MAXPART
FROM (
SELECT OWNER, TABLE_NAME, MAX(PARTITION_NAME) AS MAXPART
FROM DBA_TAB_PARTITIONS
WHERE OWNER = 'TIBERO' -- set desired user
AND PARTITION_NAME NOT LIKE '%MAX%'
GROUP BY OWNER, TABLE_NAME
) 
WHERE MAXPART <= 'PT_'|| TO_CHAR(ADD_MONTHS(SYSDATE,2),'YYYYMMDD');

2.6. Yearly Partition Check

If there is no partition for 24 months ahead from the current time, it will appear in the results.

[Example] Yearly Partition Check

/* Assuming partition names are PT_year (PT_2013)
Assuming MAXVALUE contains the word MAX in the name
Modify these two parts according to the situation */ -> Cannot query by partition create time

SQL> 
SELECT OWNER, TABLE_NAME, MAXPART
FROM (
SELECT OWNER, TABLE_NAME, MAX(PARTITION_NAME) AS MAXPART
FROM DBA_TAB_PARTITIONS
WHERE OWNER = 'TIBERO' -- set desired user
AND PARTITION_NAME NOT LIKE '%MAX%'
GROUP BY OWNER, TABLE_NAME
) 
WHERE MAXPART <= 'PT_'|| TO_CHAR(ADD_MONTHS(SYSDATE,12),'YYYY')
/

2.7. Check Index Unusable Status

Example of how to check indexes in unusable status.

[Example] Index Unusable Check

SELECT OWNER, INDEX_NAME, STATUS FROM DBA_INDEXES
WHERE STATUS != 'VALID'
UNION
SELECT OWNER, INDEX_NAME, STATUS
FROM DBA_IND_PARTITIONS
WHERE STATUS != 'USABLE'
UNION
SELECT OWNER, INDEX_NAME, STATUS
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS != 'USABLE'

2.8. Check Invalid Objects

Example of generating compile statements for invalid objects.

[Example] Check Invalid Objects

SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' AS "TEXT"
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER') 
UNION ALL
SELECT 'SELECT * FROM '||OWNER||'.'||OBJECT_NAME||' WHERE ROWNUM < 1;' AS "TEXT" 
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE = 'VIEW'

2.9. Generate Compile Statements for Invalid Objects

Example of generating compile statements for invalid objects.

[Example] Check Invalid Objects

SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' AS "TEXT"
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER') 
UNION ALL
SELECT 'SELECT * FROM '||OWNER||'.'||OBJECT_NAME||' WHERE ROWNUM < 1;' AS "TEXT" 
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE = 'VIEW'

2.10. PK Index that is Non-Unique

How to check normal PK indexes.

[Example] Normal PK Index Check

-- Query needs modification
SELECT OWNER, TABLE_NAME, INDEX_NAME 
FROM DBA_INDEXES
WHERE INDEX_NAME LIKE 'PK%'
AND UNIQUENESS != 'UNIQUE'

2.11. Change No Parallel Logging Settings

Example of generating query statements to change table NOPARALLEL, LOGGING settings.

[Example] Change NOPARALLEL, LOGGING Settings

S
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' NOPARALLEL LOGGING;' 
FROM DBA_TABLES
WHERE LOGGING != 'YES' 
OR DEGREE > 1

2.12. Delete Partition Statistics Information

Example of how to delete index partition statistics information.

[Example] Delete Index Partition Statistics

SELECT 'EXEC DBMS_STATS.DELETE_INDEX_STATS('''||OWNER||''', '''||INDEX_NAME||''', '''||PARTITION_NAME||''');' as "Text"
FROM DBA_IND_PARTITIONS
WHERE LAST_ANALYZED IS NOT NULL

Example of how to delete table partition statistics information.

[Example] Delete Table Partition Statistics

SELECT 'EXEC DBMS_STATS.DELETE_TABLE_STATS('''||OWNER||''', '''||TABLE_NAME||''', '''||PARTITION_NAME||''', CASCADE_INDEXES => TRUE);' 
FROM DBA_TAB_PARTITIONS
WHERE LAST_ANALYZED IS NOT NULL