Category: Performance

The performance issue with an SQL query was resolved by using the /*+ PARALLEL(2) */ hint, reducing execution time from 180-200 seconds to milliseconds. The decision to perform a direct read from disk to memory instead of reading blocks from disk was made by the database engine and is controlled by the hidden “_serial_direct_read” parameter. To force direct path read for SQL statements, the running statement can be used with a parallel hint or by setting table statistics. This solution allows for applying a SQL patch and significantly improves query performance.

Cursordump is essential for detecting failed SQL parses in Oracle, as these failures are not stored in the data dictionary. Starting from Oracle 10g, event 10035 reports failed parses. With the 19.16 Release Update, SQL statements failing at parsing are logged with error numbers and OSPIDs but not in the alert.log. Using tools like the x$kglob table can help, but long statements may be undetectable. Implementing triggers for error logging can capture failures, but caution is needed to handle sensitive data and manage log sizes effectively.

The execution time for the “Select * from dba_mviews” query in a specific database is 45 seconds, which is unacceptable and requires improvement. The complexity of the dba_mviews view, particularly the inclusion of specific columns and full table scans of internal tables like SYS.SUMDELTA$ and SYS.SMON_SCN_TIME, contributes to this delay. Solutions suggested include excluding certain columns, creating a new view, or even indexing. Ultimately, optimizing refresh periods and managing data efficiently significantly reduced execution time, demonstrating the importance of these adjustments for performance enhancement.

SQL syntax errors and parse errors can impact database performance. The 12.2 release records failing SQLs in alert.log if called excessively, as seen with the “select dual” statement failing 100 times in 4 minutes. The default setting for recording parse errors in alert log is every 100 errors within a 60-minute period, but this can be adjusted.