Top DBA Q&A: Solving Slow DB2 Queries, Oracle Backup Issues, and MySQL Performance Tips

1. DB2 Query Slowdown Investigation

A user reported a SQL statement that took about 9 seconds when executed from a WAS server or dbVisualizer, but only ~100 ms when run directly on the DB2 server. The statement was:

select * from Wf_Task where ActivityPOID='A86468F97F00000123781AR2UCUJ89PG' for update with RS

Observations showed the delay was caused by network transmission and fetch operations, not by execution or compilation. Removing for update with rs reduced execution time to 10 ms. The user captured execution plans and timing files (e.g., 1db2exfmt.txt, 1db2batch.txt) to confirm the analysis.

Sample db2batch timing output:

* Prepare Time is:    0.000338 seconds
* Execute Time is:    0.000058 seconds
* Fetch Time is:      0.060426 seconds
* Elapsed Time is:    0.060822 seconds (complete)

2. Oracle Import/Export and Session Management Issues

A DBA faced three problems: (1) imp running slowly due to a small redo log group, (2) recurring ORA‑01156 errors during recovery, and (3) an unkillable session that blocked shutdown immediate. Solutions included enlarging the redo log by dropping the old group and adding a larger one, using rowid to filter data during import, and finally using shutdown abort to resolve the hanging session.

3. Request for Database Proxy / Bastion Tool

One participant asked for a tool that forces IT staff to connect through an intermediate server before accessing the Oracle database, enabling IP logging and command auditing. The community suggested using a bastion host (e.g., Jumpserver, QiZhi, Paradi) to achieve this functionality.

4. MySQL Inspection Topics

Discussion about MySQL health checks highlighted common inspection items such as data volume, auto‑increment values, primary keys, slow‑query logs, and performance‑monitoring trends. Zabbix was mentioned as a monitoring solution, while other tools like spotlight on MySQL and MySQLmtop were referenced.

5. Oracle Export/Import Version Compatibility

When moving data from an 11g database to a 10g target, the recommended approach is to use expdp with version=10.*.* and then impdp. The expdp utility does not provide a parameter to ignore offline datafiles; instead, the import can be filtered by rowid to skip problematic files.

6. Partition Table Design and Maintenance

Best practices for large partitioned tables were shared: filter most rows using a partition key (e.g., business key sbrx), then create either a global range partition or a local non‑prefixed partition on sbrq. When dropping a partition, use the command: alter table xx drop partition xx update global indexes; This avoids invalidating global indexes. A rule of thumb suggested using partitions of about ten million rows.

8. DB2 LIKE vs = Performance

Another user asked why a LIKE predicate with a wildcard (%) was dramatically slower than an equality check, even when the same index was used. The explanation was that = performs a range‑bounded index lookup (few I/O operations), whereas LIKE often triggers a full leaf‑node scan, causing many random and sequential I/Os. Using full‑text search or placing the wildcard at the end of the pattern can improve performance.

9. SQL Server Large‑Data Deletion Performance

After deleting a large amount of old data in SQL Server 2008 R2, the database remained slow. The community pointed out that index high‑water marks need rebuilding, statistics should be refreshed, and SQL statements recompiled. Partitioning the table was also suggested as a longer‑term solution.

10. Sorting Chinese Numerals in Oracle

To order rows containing Chinese numerals (e.g., "一二三"), a complex ORDER BY using nested REPLACE and TRANSLATE functions was proposed:

SELECT MC FROM xxxx ORDER BY TRANSLATE(REPLACE(REPLACE(MC,'十一','n'),'十二','o'),'一二三四五六七八九十','cdefhijklm');

Further extensions for numbers up to hundreds were discussed, acknowledging the difficulty and suggesting a custom function for a more maintainable solution.