1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

A sql tuning

Discussion in 'SQL PL/SQL' started by 13478, Jun 13, 2016.

  1. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,

    We have a troubled sql (acutrally it is from a PL/SQL block) running about 18 hours to finish.

    so we run a ADDM report, find

    Recommendation 1: SQL Tuning
    Estimated benefit is 1.01 active sessions, 98.94% of total activity.
    --------------------------------------------------------------------
    Action
    Run SQL Tuning Advisor on the UPDATE statement with SQL_ID
    "888888".
    Related Object
    SQL statement with SQL_ID 888888.
    update af_ip_frm_actg_ln set orig_acpd = (select
    substr(af_ip_actg_ln.orig_acpd_id, :"SYS_B_0", :"SYS_B_1") ||
    :"SYS_B_2" || substr(af_ip_actg_ln.orig_acpd_id, :"SYS_B_3",
    :"SYS_B_4")
    from af_ip_actg_ln where substr(af_ip_frm_actg_ln.uidy,:"SYS_B_5") =
    substr(af_ip_actg_ln.uidy, :"SYS_B_6"))
    where orig_acpd is null and exists
    (select :"SYS_B_7" from af_ip_actg_ln where
    substr(af_ip_actg_ln.uidy, :"SYS_B_8") =
    substr(af_ip_frm_actg_ln.uidy, :"SYS_B_9") and orig_acpd_id is not
    null)
    Rationale
    The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
    This part of database time may be improved by the SQL Tuning Advisor.
    Rationale
    Database time for this SQL was divided as follows: 100% for SQL
    execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
    execution.
    Rationale
    Full scan of TABLE "af_ip_actg_ln" with object ID 199636
    consumed 99% of the database time spent on this SQL statement.


    We run a tuning advisor as instructed in ADDM, but no recommendation to improve this SQL.


    Ok, check execution plan, find

    table of af_ip_actg_ln is full table scan

    I think the full table scan is caued by:
    substr(af_ip_actg_ln.uidy, :"SYS_B_6"))
    (and by)
    orig_acpd_id is not null

    I found table AF_IP_ACTG_LN has NO function based index for SUBSTR, if I want to create a Function Based index on column UIDY, how?
    (same theory for af_ip_frm_actg_ln table)
    Thank you very much!!!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    if it doesn't cause a problem in operation of application, then
    can to create a virtualny column and on it to create a functional index(this decision is controled:gathered stats,change of method of calculation and etc.).
    else create simple functional index.

    Code (SQL):
    ALTER TABLE your_table ADD new_col AS( substr(your_column,1,.));
    CREATE INDEX your_index_name ON your_table(your_virtual_column);
     
    or
    Code (SQL):
    CREATE INDEX your_index_name  ON your_table (substr(your_filed,1,..));
     
    Last edited: Jun 14, 2016
  3. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana




    'UPDATE .F00000F. FRM_LN SET FRM_LN.STAE_OF_LINE = ''new''
    WHERE (SELECT COUNT(*) FROM .F11111F. DOC_LN
    WHERE SUBSTR(FRM_LN.UIDY, INSTR(FRM_LN.UIDY, ''&'', 1, 4)+1) = SUBSTR(DOC_LN.UIDY, INSTR(DOC_LN.UIDY, ''&'', 1, 4)+1)) = 0',

    'DROP INDEX .F00000F.FRM_INDX',
    'DROP INDEX .F11111F.DOC_INDX'

    ),
    t_ARR100x4000('index .F00000F.FRM_INDX created','index .F11111F.DOC_INDX created',
    ' records updated in .F00000F.',
    'index .F00000F.FRM_INDX dropped','index .F11111F.DOC_INDX dropped'));
    end;
    /

    We let client company run a ADDM, which picked out below sql is the slow one (cost 19 hours), based on above PL/SQL block.


    UPDATE AF_IP_FRM_ACTG_LN FRM_LN SET FRM_LN.STAE_OF_LINE = :"SYS_B_0"
    WHERE (SELECT COUNT(*) FROM AF_IP_ACTG_LN DOC_LN
    WHERE SUBSTR(FRM_LN.UIDY, INSTR(FRM_LN.UIDY, :"SYS_B_1", :"SYS_B_2",
    :"SYS_B_3")+:"SYS_B_4") = SUBSTR(DOC_LN.UIDY, INSTR(DOC_LN.UIDY,
    :"SYS_B_5", :"SYS_B_6", :"SYS_B_7")+:"SYS_B_8")) = :"SYS_B_9";



    Problem: SQL statement with SQL_ID "4ngp5gtdx9sxr" was executed 1 times and had
    an average elapsed time of 70816 seconds.(19 HOURS)


    The interesting part is: in Pl/SQL
    two function based indexes have been created.

    'CREATE INDEX .F00000F.FRM_INDX ON .F00000F.(SUBSTR(UIDY, INSTR(UIDY, ''&'', 1, 4)+1))',
    'CREATE INDEX .F11111F.DOC_INDX ON .F11111F.(SUBSTR(UIDY, INSTR(UIDY, ''&'', 1, 4)+1))',

    I let client side to run a tuning adviser report, while, because the SQL ID did not exist anymore, so they can't produce report.

    hmm...I am thinking what can cause it is slow...

    Thank you very much!
     
    Last edited: Jun 24, 2016
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    I suppose, your functional indexes aren't used.
    You can provide the report of AWR/ASH here?

    From of view v$active_sessoin_history by problem query it is possible to receive a necessary ifnormation.

    For search of query it is possible to use views: v$active_sessoin_history, dba_his_sql *
     
    Last edited: Jun 19, 2016
  5. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hi Krasnoslobodtsev_si:


    Hmm, I made a test in my performance test database (huge data).
    I saw : no, it won't use those two function based indexes, while it runs pretty fast in my database. (not in client side), I am thinking why? (in client side ADDM, it complains, hours time were consumed for full table scan of AF_IP_ACTG_LN; while in my test, AF_IP_ACTG_LN is accessed by its PK index)


    And,
    I am thinking of making a sql plan baseline, exp from my database, and let client to import to its database.

    here is what I do:

    SQL> Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE
    2 ;

    System altered.


    –run our slow PL/SQL


    set serveroutput on
    set timing on
    begin
    -- 'AF_TAX_FRM_SUBL' 'AF_TAX_SUBL'
    UPG_PKG.N_table_N_Query(t_ARR100x4000('.F00000F.,
    AF_IP_FRM_ACTG_LN',
    '.F11111F.,
    AF_IP_ACTG_LN'),
    t_ARR100x4000(

    'CREATE INDEX .F00000F.FRM_INDX ON .F00000F.(SUBSTR(UIDY, INSTR(UIDY, ''&'', 1, 4)+1))',
    'CREATE INDEX .F11111F.DOC_INDX ON .F11111F.(SUBSTR(UIDY, INSTR(UIDY, ''&'', 1, 4)+1))',

    'UPDATE .F00000F. FRM_LN SET FRM_LN.STAE_OF_LINE = ''new''
    WHERE (SELECT COUNT(*) FROM .F11111F. DOC_LN
    WHERE SUBSTR(FRM_LN.UIDY, INSTR(FRM_LN.UIDY, ''&'', 1, 4)+1) = SUBSTR(DOC_LN.UIDY, INSTR(DOC_LN.UIDY, ''&'', 1, 4)+1)) = 0',

    'DROP INDEX .F00000F.FRM_INDX',
    'DROP INDEX .F11111F.DOC_INDX'

    ),
    t_ARR100x4000('index .F00000F.FRM_INDX created','index .F11111F.DOC_INDX created',
    ' records updated in .F00000F.',
    'index .F00000F.FRM_INDX dropped','index .F11111F.DOC_INDX dropped'));
    end;
    /

    --its spool file
    Started:06/18/2016 16:12:43
    index
    AF_IP_FRM_ACTG_LNFRM_INDX created
    index
    AF_IP_ACTG_LNDOC_INDX created
    4428 records updated in
    AF_IP_FRM_ACTG_LN
    index
    AF_IP_FRM_ACTG_LNFRM_INDX dropped
    index
    AF_IP_ACTG_LNDOC_INDX dropped
    Ended:06/18/2016 16:13:14

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:30.72

    --check
    SQL> select sid,serial# from v$session where username='<my_core_schema_name>';

    SID SERIAL#
    ---------- ----------
    1422 9389


    SQL> select sql_id from v$session where sid=1422;

    SQL_ID
    -------------
    4tycwmytx19px

    SQL> DECLARE
    my_plans pls_integer;
    BEGIN
    my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '4tycwmytx19px');
    END;
    /


    --GET SQL_HANDLE

    select sql_handle,sql_text from DBA_SQL_PLAN_BASELINES;


    SQL_HANDLE
    ------------------------------
    SQL_TEXT
    --------------------------------------------------------------------------------
    SQL_e461fe4a2eb2e9d5
    UPDATE
    AF_IP_FRM_ACTG_LN FRM_LN SET FRM_LN.STAE_OF_LINE = 'new'
    WHERE (SEL


    –Displaying SQL Plan Baselines
    select * from table(
    dbms_xplan.display_sql_plan_baseline(
    sql_handle=>'SQL_e461fe4a2eb2e9d5',
    format=>'basic'));

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------
    SQL handle: SQL_e461fe4a2eb2e9d5
    SQL text: UPDATE AF_IP_FRM_ACTG_LN FRM_LN SET FRM_LN.STAE_OF_LINE = 'new'
    WHERE (SELECT COUNT(*) FROM AF_IP_ACTG_LN DOC_LN WHERE
    SUBSTR(FRM_LN.UIDY, INSTR(FRM_LN.UIDY, '&', 1, 4)+1) =
    SUBSTR(DOC_LN.UIDY, INSTR(DOC_LN.UIDY, '&', 1, 4)+1)) = 0
    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------
    Plan name: SQL_PLAN_f8sgy98rb5ufpf75f635e Plan id: 4150223710

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
    --------------------------------------------------------------------------------

    Plan hash value: 1523395462

    -----------------------------------------------------
    | Id | Operation | Name |
    -----------------------------------------------------
    | 0 | UPDATE STATEMENT | |
    | 1 | UPDATE | AF_IP_FRM_ACTG_LN |
    | 2 | FILTER | |

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | 3 | TABLE ACCESS FULL | AF_IP_FRM_ACTG_LN |
    | 4 | SORT AGGREGATE | |
    | 5 | INDEX FAST FULL SCAN| PK_IP_ACTG_LN | ====> AF_IP_ACTG_LN is accessed by its PK index
    -----------------------------------------------------

    26 rows selected.


    –load the sql plan from cursor cache again

    SQL>
    DECLARE
    my_plans pls_integer;
    BEGIN
    my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '4tycwmytx19px');
    END;
    /

    SQL> 2 3 4 5 6
    PL/SQL procedure successfully completed.

    –The new plan was accepted
    –check SQL Plan Baselines again
    SQL>select * from table(
    dbms_xplan.display_sql_plan_baseline(
    sql_handle=>'SQL_e461fe4a2eb2e9d5',
    format=>'basic'));


    SQL handle: SQL_e461fe4a2eb2e9d5
    SQL text: UPDATE AF_IP_FRM_ACTG_LN FRM_LN SET FRM_LN.STAE_OF_LINE = 'new'
    WHERE (SELECT COUNT(*) FROM AF_IP_ACTG_LN DOC_LN WHERE
    SUBSTR(FRM_LN.UIDY, INSTR(FRM_LN.UIDY, '&', 1, 4)+1) =
    SUBSTR(DOC_LN.UIDY, INSTR(DOC_LN.UIDY, '&', 1, 4)+1)) = 0
    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------
    Plan name: SQL_PLAN_f8sgy98rb5ufpf75f635e Plan id: 4150223710

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
    --------------------------------------------------------------------------------

    Plan hash value: 1523395462

    -----------------------------------------------------
    | Id | Operation | Name |
    -----------------------------------------------------
    | 0 | UPDATE STATEMENT | |
    | 1 | UPDATE | AF_IP_FRM_ACTG_LN |
    | 2 | FILTER | |

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | 3 | TABLE ACCESS FULL | AF_IP_FRM_ACTG_LN |
    | 4 | SORT AGGREGATE | |
    | 5 | INDEX FAST FULL SCAN| PK_IP_ACTG_LN |
    -----------------------------------------------------

    26 rows selected.


    select sql_handle,sql_text from DBA_SQL_PLAN_BASELINES where SQL_handle='SQL_e461fe4a2eb2e9d5';

    SQL_HANDLE
    ------------------------------
    SQL_TEXT
    --------------------------------------------------------------------------------
    SQL_e461fe4a2eb2e9d5
    UPDATE
    AF_IP_FRM_ACTG_LN FRM_LN SET FRM_LN.STAE_OF_LINE = 'new'
    WHERE (SEL



    BEGIN
    DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'exp_pb');
    END;
    /


    DECLARE
    my_plans number;
    BEGIN
    my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name => 'exp_pb',
    enabled => 'yes',
    creator => '<my_core_schmea_name>');
    END;
    /

    --CHECK, YES, IT IS THERE
    SQL> SELECT SQL_HANDLE, SQL_TEXT FROM exp_pb WHERE SQL_HANDLE='SQL_e461fe4a2eb2e9d5';

    SQL_HANDLE
    ------------------------------
    SQL_TEXT
    --------------------------------------------------------------------------------
    SQL_e461fe4a2eb2e9d5
    UPDATE
    AF_IP_FRM_ACTG_LN FRM_LN SET FRM_LN.STAE_OF_LINE = 'new'
    WHERE (SEL



    delete from exp_pb where SQL_HANDLE<>'SQL_e461fe4a2eb2e9d5';
    commit;

    3.Export the staging table stage1 into a flat file using the export command or Oracle Data Pump.


    --let client do below
    4.Transfer the flat file to the target system.

    5.Import the staging table stage1 from the flat file using the import command or Oracle Data Pump.

    6.Unpack the SQL plan baselines from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function:
    DECLARE
    my_plans number;
    BEGIN
    my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
    table_name => 'exp_pb',
    fixed => ‘yes’);
    END;
    /


    Thank you
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Can you provide the description of tables /indexes(DDL) here?

    Several additional questions:
    1) tables of AF_IP_FRM_ACTG_LN & AF_IP_FRM_ACTG_LN is partioned ?
    2) the update query will be executed permanently or it is one-time operation of synchronization
    3) how many rows in tables(AF_IP_FRM_ACTG_LN ,AF_IP_FRM_ACTG_LN)?
    p.s. if very large data volume, then processing by one transaction can be ineffective.

    for example :
    Code (SQL):
    SET verify off
    SET define off
    --1
    DROP TABLE test1;
    CREATE TABLE test1
    (
        id INT NOT NULL PRIMARY KEY,
        str varchar2(16 CHAR),
        mrk NUMBER(10)
    );  

    EXECUTE  dbms_random.seed('A');

    INSERT INTO  test1
    SELECT
        level id,
        'str&1&2&3&'||dbms_random.string('U',2) str,
        to_number(0) mrk
    FROM dual
    CONNECT BY level<= 1e5;

    -- this index is not used  ,because  you are not use predicate substr(...)  for geting  source rows
    --create index test1_i1  on  test1(substr(str, instr(str, '&', -1)+1));

    EXECUTE   dbms_stats.gather_table_stats(ownname => NULL,tabname => 'test1',cascade => TRUE,method_opt => 'for all columns size skewonly')

    --2
    DROP TABLE test2;
    CREATE TABLE test2
    (
        id INT NOT NULL PRIMARY KEY,
        str varchar2(16 CHAR)
    );  

    EXECUTE  dbms_random.seed('A');

    INSERT INTO test2
    SELECT
        level id,
        'str&1&2&3&'||dbms_random.string('U',2) str
    FROM dual
    CONNECT BY level<= 1e5;


    --this index is  used  ,because  you are  using  predicate substr(...)  for find rows by substr(..)
    CREATE INDEX test2_i1  ON  test2(substr(str, instr(str, '&', -1)+1));

    EXECUTE  dbms_stats.gather_table_stats(ownname => NULL,tabname => 'test2',cascade => TRUE,method_opt => 'for all columns size skewonly');

    -- diff str
    prompt make diff ON str
    UPDATE test1 SET str=str||'z' WHERE id IN (1,3,7);
    --
    prompt find BY str2
    --
    UPDATE  test1 n
    SET n.mrk = 1
        WHERE NOT EXISTS
                (SELECT /*+ index(o test2_i1) no_unnest */ 1 FROM test2 o WHERE substr(n.str, instr(n.str, '&', -1)+1) = substr(o.str, instr(o.str, '&', -1)+1)
                );


    commit;

    --
    SELECT COUNT(*) FROM test1 WHERE mrk = 1;
     
    Last edited: Jun 21, 2016
  7. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello Kras:

    the spool file from client side is as below:

    index AF_IP_FRM_ACTG_LNFRM_INDX created
    index AF_IP_ACTG_LNDOC_INDX created
    81000 records updated in AF_IP_FRM_ACTG_LN ===> this is much bigger than my database (4428 records)
    index AF_IP_FRM_ACTG_LNFRM_INDX dropped
    index AF_IP_ACTG_LNDOC_INDX dropped


    As to your questions;

    1) tables of AF_IP_FRM_ACTG_LN & AF_IP_FRM_ACTG_LN is partioned ? ==> not know yet, will ask
    2) the update query will be executed permanently or it is one-time operation of synchronization==> one time
    3) how many rows in tables(AF_IP_FRM_ACTG_LN ,AF_IP_FRM_ACTG_LN)? ==> not know yet, will ask.

    Thanks
     
  8. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello Kras,

    In my database (not client)
    hmm I run re-collect stats, and then re-run pl/sql block, tkprof its trace, find optimizer starts to use one newly created index.

    any thoughts?
    Thanks

    EXECUTE dbms_stats.gather_table_stats(ownname => NULL,tabname => 'AF_IP_FRM_ACTG_LN',cascade => TRUE,method_opt => 'for all columns size skewonly');
    EXECUTE dbms_stats.gather_table_stats(ownname => NULL,tabname => 'AF_IP_ACTG_LN',cascade => TRUE,method_opt => 'for all columns size skewonly');



    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ---------- ---------- ---------- ---------------------------------------------------
    0 0 0 UPDATE MF_IP_FRM_ACTG_LN (cr=8826 pr=1257 pw=0 time=975950 us)
    4428 4428 4428 FILTER (cr=8826 pr=1257 pw=0 time=170598 us)
    4497 4497 4497 TABLE ACCESS FULL MF_IP_FRM_ACTG_LN (cr=359 pr=0 pw=0 time=10086 us cost=66 size=197868 card=4497)
    4497 4497 4497 SORT AGGREGATE (cr=8467 pr=1257 pw=0 time=858322 us)
    69 69 69 INDEX RANGE SCAN AF_IP_ACTG_LNDOC_INDX (cr=8467 pr=1257 pw=0 time=830268 us cost=3 size=1787760 card=45840)(object id 761309)
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Generating current statistics allows the optimizer to work with accurate information so the new index this statement is using was found to be a favorable execution path step. Running the statement after a 10053 trace is started will reveal WHY that index is used. You will see what steps the optimizer is executing to arrive at the new path, and you'll also see why one index is selected over the other.
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Can you provide result of these querys
    Code (SQL):
    SELECT t.TABLE_NAME,t.partitioned,t.num_rows,t.avg_row_len,t.last_analyzed
    FROM user_tables t
    WHERE t.TABLE_NAME  IN ('AF_IP_FRM_ACTG_LN','AF_IP_ACTG_LN');

    SELECT t.TABLE_NAME,t.index_name,t.blevel,t.leaf_blocks,t.num_rows,t.partitioned,t.last_analyzed
    FROM user_indexes t
    WHERE t.TABLE_NAME  IN ('AF_IP_FRM_ACTG_LN','AF_IP_ACTG_LN');

     
    Code (SQL):
    EXPLAIN FOR
    SELECT
        COUNT(1) cnt4upd
    FROM af_ip_frm_actg_ln n
        WHERE NOT EXISTS
                (SELECT /*+ index(o) no_unnest */ 1 FROM  af_ip_actg_ln o WHERE substr(n.str, instr(n.str, '&', -1)+1) = substr(o.str, instr(o.str, '&', -1)+1)
                );
    SELECT * FROM TABLE(dbms_xplan.display(format => 'typical'));
    here ?

    p.s.
    If your request needs to be executed only once, then it is possible to use scenarios:
    parallel dml or
    dbms_parallel_execution.
    If it is planned to carry out up-dating to working hours, then it is better to use DBMS_PARALLEL_EXECUTION
     
    Last edited: Jun 23, 2016