ORACLE Optimize SQL

Example 1

original one:

update acc_bill b

set brand = (

    select brand

    from user

    where serv_id = b.serv_id

    )

, plan_id = (

    select plan_id 

    from user

    where serv_id = b.serv_id

    )

where b.serv_id in (

                    select serv_id from user

                    )

optimized one:

update acc_bill b

set brand, plan_id = (select brand, plan_id from user where serv_id = b.serv_id)

where b.serv_id in (select serv_id from user)

Further optimized one:

update ( select a.brand brand_a, a.plan_id plan_id_a, b.brand, b.plan_id from acc_bill b, user a

where b.serv_id = a.serv_id)

set brand=brand_a, plan_id=plan_id_a;

Example 2

TOP 5

1. latch: cache buffers chains

sql execute elapsed time: 191,405.24

SQL order by Elapsed Time

75% Total DB Time --> one sql

SQL order by Gets

95% Total --> one sql

This SQL is the problem

TF_R_UNICARD is partition table divided by card no

SQL script

SELECT UNICARD_NO FROM TF_R_UNICARD WHERE PRESENT_TAG ='0' 

AND LIMIT_DATE +0 > SYSDATA +90

AND UNICARD_STATE||NULL = '0'

AND UNICARD_VALCODE||NULL = :B3

AND ROWNUM <= :B2

AND RESERVED1 = :B1

AND (RESERVED2 <> '99' OR RESERVED2 IS NULL)

FOR UPDATE

In order to prevent use index

AND LIMIT_DATE +0 > SYSDATA +90

AND UNICARD_STATE||NULL = '0'

AND UNICARD_VALCODE||NULL = :B3

Index

INDEX_NAME                COLUMN

PK_TF_R_UNICARD        UNICARD_NO

IDX_TF_R_UNICARD_4    RESERVED1, UNICARD_BATCHNO, UNICARD_VALCODE

Optimized script (can not change application)

SQL>ALTER SESSION FORCE PRARLLEL DDL;

SQL>CREATE INDEX IND_UNICARD_RES_VALCODE_DATE ON TF_R_UNICARD

(RESERVED1, UNICARD_VALCODE||NULL, UNICARD_STATE||NULL, LIMIT_DATE+0) PARALLEL 8 ONLINE;

SQL>ALTER INDEX IND_UNICARD_RES_VALCODE_DATE NOPARALLEL;