Oracle-DB: Find SQLs where expected parallel DML or direct load does not work
If you try to use parallel DML or direct load in SQL statements by placing the appropiate optimizer hints (PARALLEL, APPEND etc.), there might be several reasons why the DB ignores them. For example, if you want to execute DML in parallel you need to enable the session to do this before by executing ALTER SESSION ENABLE PARALLEL DML; . There are lots of similar pitfalls that may prevent the DB from using the expected execution path. The free performance analysis tool Panorama offers a way to scan your entire database for SQLs where parallel DML or direct load should have been used but in reality is not. Use the shown selection from menu "Dragnet investigation" (point 2.2.12) to execute this check and find SQLs that are not executed as expected. The result is sorted by the time this SQLs took to execute. The link in column "sql id" allows you to dig deeper in the details of the found SQL statement. Precondition is licensing of Oracle Diagnostics Pack or the u...