My query was fine last week and now it is slow. Why?!
According to Application System Performance Problem with Glovia System.
And I also found so many customers have been suffered with similar problem.
I would like to share an experience to all of you to prevent same kind of the problem to be occurred to your customers.
1. Application Program come with RDBMS such as Oracle or MS/SQL
2. Program is run with very poor performance (several hours/days)
3. Sometime, Program has been changed or modified from original
1. Data was grown up a lot
2. DML SQL Command was not optimized (80% cause of the problem)
3. DATA was queried without INDEX (FULL TABLE SCAN)
4. Query Execution Plan has changed
How to prevent the problem:
1. In design Period:
a. Make Good Database Design (3NF Normalization/De-Normalization)
b. Verify all Queries and WHERE Conditions which are used in program
c. Use tool to check cost of DML SQL Command running
d. Avoid Inner Join without Condition (Cartesian Product or TABLE x TABLE)
e. Consider to use Partitioning Feature for Huge Data
f. Consider to use Clustering Index for Join Table (Master-Child)
2. In Test Period:
a. Perform Load Test or Stress Test (by using some Tools)
b. Simulate big of data for the performance Test
3. In Maintenance Period:
a. Usually, running Analyze Index to check Invalid Index and Execution Plan
b. Having Document/Script about Index (Sometime Index is lost)
How to Solve the Problem:
1. Monitor poor performance process by using Database tool or utility
a. Oracle Enterprise Manager
2. Application Tuning,
a. Re-write with optimal DML SQL Statement in the process
b. Avoid FULL TABLE SCAN Condition in DML SQL should meet with created INDEX
c. Create necessary INDEX (Create too many INDEX may effect to performance too)
3. Analyze Table/Index and Re-Create Invalid Index
a. Which tables are currently analyzed? Were they previously analyzed?
b. Has the DEGREE of parallelism been defined/changed on any table?
c. Have the tables been re-analyzed? Were the tables analyzed percentage using for estimate?
4. Check Database Parameters
a. Has OPTIMIZER_MODE been changed in INIT.ORA?
b. Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
c. Has the INIT.ORA parameter SORT_AREA_SIZE been changed?
d. Have any other INIT.ORA parameters been changed?
For Database improvement, we can request some help from DBA:
1. Eliminate Database Contention (e.g. Lock, Wait Event, Pin Code etc.)
2. Database or Disk De-fragmentation or Re-Build Database
3. Memory Tuning
4. Disk I/O Tuning
5. O/S Tuning