Oracle-sql Program Optimization 3

ETL in the last one weeks whether in the early morning or at noon job execution process often stuck, resulting in the failure to complete the system engine running, the business impact.

By generating the AWR report, it was found that three SQL consumed a large amount of CPU, and that no execution was completed and terminated.

The second article is full of CPU resources, which is undoubtedly the cause of the jammed, view the execution plan, because the SAP_MAPL table scans the 20多万条 data but walk full table scan

After checking that the table does not have a Jianjian index for the MATNR character, create a normal index for the Matnr field of the table

CREATE INDEX Idx_matnr on SAP_MAPL (MATNR);

Re-observation of the execution plan

The contrast is obvious because the SQL has been consuming CPU resources continuously because no index was built.

Oracle-sql Program Optimization 3