Deneme

July 13, 2011

Deneme


Oracle Cursor / Sql Processing

April 1, 2011

Cursor/SQL Processing

Here are some scripts related to Cursor/SQL Processing .

Disk Intensive SQL

SQL WITH MOST DISK READ NOTES:

  • Username – Name of the user
  • Disk Reads – Total number of disk reads for this statement
  • Executions – Total number of times this statement has been executed
  • Reads/Execs – Number of reads per execution
  • SQL Text – Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
    select 	a.USERNAME,
    	DISK_READS,
    	EXECUTIONS,
    	round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS)) "Reads/Execs",
    	SQL_TEXT
    from 	dba_users a, v$session, v$sqlarea
    where 	PARSING_USER_ID=USER_ID
    and 	ADDRESS=SQL_ADDRESS(+)
    and 	DISK_READS > 10000
    order  	by DISK_READS desc, EXECUTIONS desc

    Buffer Intensive SQL

    SQL WITH MOST BUFFER SCAN NOTES:

  • Username – Name of the user
  • Buffer Gets – Total number of buffer gets for this statement
  • Executions – Total number of times this statment has been executed
  • Gets/Execs – Number of buffer gets per execution
  • SQL Text – Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
    select	EXECUTIONS,
    	BUFFER_GETS,
    	round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS) / 400,2) "Gets/Execs",
    	SQL_TEXT
    from 	v$sqlarea
    where  	BUFFER_GETS / decode(EXECUTIONS,0,1, EXECUTIONS) / 400 > 10
    order  	by EXECUTIONS desc

    Buffer SQL w/ Most Loads

    SQL WITH MOST LOAD NOTES:

  • Loads – Number of times the cursor has been loaded after the body of the cursor has been aged out of the cache while the text of the SQL statement remained in it, or after the cursor is invalidated
  • First Load Time – Time at which the cursor was first loaded into the SGA
  • Sorts – Number of sorts performed by the SQL statement
  • SQL Text – Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
    select 	LOADS,
    	FIRST_LOAD_TIME,
    	SORTS,
    	SQL_TEXT
    from 	v$sqlarea
    where  	LOADS > 50
    order  	by EXECUTIONS desc

    Open Cursors By User

    OPEN CURSORS BY USER NOTES:

  • Username – Name of user
  • SQL Text – Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
    select	nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')' username,
    	SQL_TEXT
    from 	v$open_cursor oc,
    	v$session s
    where 	s.SADDR = oc.SADDR
    order 	by 1

    Running Cursors By User

    RUNNING CURSORS BY USER NOTES:

  • Username – Name of user
  • SQL Text – Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
    select	nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')' username,
    	SQL_TEXT
    from 	v$open_cursor oc, v$session s
    where 	s.SQL_ADDRESS = oc.ADDRESS
    and 	s.SQL_HASH_VALUE = oc.HASH_VALUE
    order 	by 1

    LR Open Cursors

    OPEN CURSORS WITH LOW HIT RATIO NOTES:

  • Username – Name of user
  • SQL Text – Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
    select	nvl(se0.USERNAME,'ORACLE PROC')||'('||se0.SID||')' username,
    	SQL_TEXT
    from	v$open_cursor oc0, v$session se0
    where 	se0.SADDR = oc0.SADDR
    and	se0.USERNAME != 'SYS'
    and    	60 < (
    		select 	"Hit Ratio"
    		from 	(
    select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",
    	sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",
    	sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",
    	sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads",
    	(
    	(sum(decode(NAME, 'consistent gets',value, 0)) +
    	 sum(decode(NAME, 'db block gets',value, 0)) -
    	 sum(decode(NAME, 'physical reads',value, 0)))
    	/
    	(sum(decode(NAME, 'consistent gets',value, 0)) +
    	 sum(decode(NAME, 'db block gets',value, 0))) * 100)
    	 "Hit Ratio"
    from 	v$sesstat ss, v$statname sn, v$session se
    where 	ss.SID = se.SID
    and	sn.STATISTIC# = ss.STATISTIC#
    and 	VALUE != 0
    and 	sn.NAME in ('db block gets', 'consistent gets', 'physical reads')
    group 	by se.USERNAME, se.SID
    ) XX
    		where 	nvl(se0.USERNAME,'ORACLE PROC')||'('||se0.SID||')' = "User Session")
    order 	by nvl(se0.USERNAME,'ORACLE'), se0.SID

    LR Running Cursors

    RUNNING CURSORS WITH LOW HIT RATIO NOTES:

  • Username – Name of user
  • SQL Text – Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
    select	nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||'),
    	SQL_TEXT
    from 	v$open_cursor oc0, v$session se0
    where  	se0.SQL_ADDRESS = oc0.ADDRESS
    and 	se0.SQL_HASH_VALUE = oc0.HASH_VALUE
    and	se0.username != 'SYS'
    and    	60 > (
    	select 	"Hit Ratio"
    	from (
    select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",
    	sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",
    	sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",
    	sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads",
    		(
    		(sum(decode(NAME, 'consistent gets',value, 0)) +
    		 sum(decode(NAME, 'db block gets',value, 0)) -
    		 sum(decode(NAME, 'physical reads',value, 0)))
    		/
    		(sum(decode(NAME, 'consistent gets',value, 0)) +
    		 sum(decode(NAME, 'db block gets',value, 0))) * 100) "Hit Ratio"
    from 	v$sesstat ss, v$statname sn, v$session se
    where 	ss.SID = se.SID
    and	sn.STATISTIC# = ss.STATISTIC#
    and 	VALUE != 0
    and 	sn.NAME in ('db block gets', 'consistent gets', 'physical reads')
    group 	by se.USERNAME, se.SID
    )
            where 	nvl(se0.username,'ORACLE PROC')||'('||se0.sid||')' = "User Session")
    order 	by nvl(se0.username,'ORACLE'), se0.sid

    LR Objects Access

    OBJECTS BEING USED BY USERS WITH LOW HIT RATIO NOTES:

  • Username – Name of the user
  • Object Owner – Owner of the object
  • Object – Name of the object
    select	nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||')' username,
    	OWNER,
    	OBJECT
    from 	v$access ac, v$session se0
    where 	ac.SID    = se0.SID
    and  	ac.TYPE   = 'TABLE'
    and 	60 < (
    	select 	"Hit Ratio"
    	from
    (
    select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",
    	sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",
    	sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",
    	sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads",
    	(
    		(sum(decode(NAME, 'consistent gets',value, 0)) +
    		 sum(decode(NAME, 'db block gets',value, 0)) -
    		 sum(decode(NAME, 'physical reads',value, 0)))
    		/
    		(sum(decode(NAME, 'consistent gets',value, 0)) +
    		 sum(decode(NAME, 'db block gets',value, 0))) * 100) "Hit Ratio"
    from 	v$sesstat ss,
    	v$statname sn,
    	v$session se
    where 	ss.SID = se.SID
    and	sn.STATISTIC# = ss.STATISTIC#
    and 	VALUE != 0
    and 	sn.NAME in ('db block gets', 'consistent gets', 'physical reads')
    group 	by se.USERNAME, se.SID
    )
    	where 	nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||')' = "User Session")
    order 	by USERNAME,se0.SID,OWNER

  • QP Price List Import

    January 11, 2011

    Here is an example code of QP price list import.

    procedure price_list_import(errbuf out varchar2,
    retcode out varchar2,
    p_price_list_name in varchar2,
    p_price_list_desc in varchar2,
    p_line_type_code in varchar2 default ‘PRL’,
    p_currency_code in varchar2 default ‘USD’,
    p_aritmetic_operator in varchar2 default ‘UNIT_PRICE’,
    p_qualifier_context in varchar2 default ‘PO_SUPPLIER’,
    p_qualifier_attribute in varchar2 default ‘QUALIFIER_ATTRIBUTE1′,
    p_comparison_operator_code in varchar2 default ‘=’,
    p_qualifier_precedence in number default 3
    )
    is
    gpr_return_status             varchar2 (1) := null;
    gpr_msg_count                 number := 0;
    gpr_msg_data                  varchar2 (2000);
    gpr_price_list_rec            qp_price_list_pub.price_list_rec_type;
    gpr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
    gpr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
    gpr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
    gpr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
    gpr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
    gpr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
    gpr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
    ppr_price_list_rec            qp_price_list_pub.price_list_rec_type;
    ppr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
    ppr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
    ppr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
    ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
    ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
    ppr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
    ppr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
    k number := 1;
    j number := 1;
    l_current_inventory_item_id number;
    l_current_uom_code           mtl_system_items_b.primary_uom_code%type;
    l_current_vendor_id            number;
    l_qual_group_num              number := 1;
    l_qp_line_id                       number;
    l_line_index                       number := 1;

    cursor c_lines
    is

    select xeql.*
    from xxtg_eam_qp_lines_tmp xeql
    where list_line_id = -1
    –and inventory_item_id is null
    and nvl(success_flag,’N') = ‘N’
    and segment1 in (select segment1
    from mtl_system_items_b);

    cursor c_qualifier
    is

    select xeql.*
    from xxtg_eam_qp_qualifier_vals_tmp xeql
    where list_line_id is null
    and nvl(success_flag,’N') = ‘N’
    –and qualifier_value is null
    and qualifier in (select vendor_name
    from ap_suppliers);

    begin
    –dbms_output.put_line(‘after get price list ‘);
    /* set the list_header_id to g_miss_num */
    gpr_price_list_rec.operation := qp_globals.g_opr_create;
    gpr_price_list_rec.list_header_id := fnd_api.g_miss_num;
    gpr_price_list_rec.name := p_price_list_name;
    gpr_price_list_rec.list_type_code := p_line_type_code;
    gpr_price_list_rec.description := p_price_list_desc;
    gpr_price_list_rec.currency_code := p_currency_code;

    –looping at price list lines. for each line an attribute is created.
    for r_lines in c_lines
    loop

    begin
    select inventory_item_id,
    primary_uom_code
    into l_current_inventory_item_id,
    l_current_uom_code
    from mtl_system_items_b msib
    where msib.segment1 = r_lines.segment1
    and rownum = 1;
    exception
    when no_data_found then
    l_current_inventory_item_id := null;
    continue;
    end;
    –QP_LIST_LINES_s

    select qp_list_lines_s.nextval
    into l_qp_line_id
    from dual;

    gpr_price_list_line_tbl (k).list_line_id := l_qp_line_id;
    gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_create;
    gpr_price_list_line_tbl(k).list_line_id := fnd_api.g_miss_num;
    gpr_price_list_line_tbl (k).list_line_type_code := ‘PLL’;
    gpr_price_list_line_tbl(k).inventory_item_id := l_current_inventory_item_id;
    gpr_price_list_line_tbl (k).operand := r_lines.price;
    gpr_price_list_line_tbl (k).arithmetic_operator := p_aritmetic_operator;

    dbms_output.put_line(‘attribute ‘);
    gpr_pricing_attr_tbl (k).list_line_id := l_qp_line_id;
    gpr_pricing_attr_tbl (k).operation := qp_globals.g_opr_create;
    gpr_pricing_attr_tbl (k).pricing_attribute_id := fnd_api.g_miss_num;
    gpr_pricing_attr_tbl (k).list_line_id := fnd_api.g_miss_num;
    gpr_pricing_attr_tbl (k).product_attribute_context := ‘ITEM’;
    gpr_pricing_attr_tbl (k).product_attribute := ‘PRICING_ATTRIBUTE1′;
    gpr_pricing_attr_tbl (k).excluder_flag := ‘N’;
    gpr_pricing_attr_tbl (k).price_list_line_index := k;
    gpr_pricing_attr_tbl (k).product_attr_value := l_current_inventory_item_id;
    gpr_pricing_attr_tbl (k).product_uom_code := l_current_uom_code;
    dbms_output.put_line(‘attribute done’);

    l_line_index := l_line_index + 1;

    update xxtg.xxtg_eam_qp_lines_tmp tmp
    set tmp.inventory_item_id = l_current_inventory_item_id
    where tmp.segment1 = r_lines.segment1;

    dbms_output.put_line(‘l_current_inventory_item_id: ‘ || l_current_inventory_item_id || ‘ r_lines.segment1: ‘ || r_lines.segment1);
    fnd_file.put_line(FND_FILE.LOG,’l_current_inventory_item_id: ‘ || l_current_inventory_item_id || ‘ r_lines.segment1: ‘ || r_lines.segment1);

    k := k + 1;
    end loop;

    /*
    product_attr_value stores inventory item id – product_attribute for Item Number
    is Pricing_Attribute1 product_attribute_context is ITEM. Each line can have one
    or more pricing attributes. PRICE_LIST_LINE_INDEX is used to link the child
    (pricing attributes) to the parent(line).
    When you have pricing attributes like color, length, width etc, populate the
    fields pricing_attribute_context, pricing_attribute, pricing_attr_value_from,
    pricing_attr_value_to and comparison_operator_code ( ‘=’ or ‘between’) and
    repeat the product_attr_value and its attribute and context for each record.
    */

    –looping at qualifier. Here we define vendors that can use this price list.
    for r_qualifier in c_qualifier
    loop
    begin
    select vendor_id
    into l_current_vendor_id
    from ap_suppliers aps
    where aps.vendor_name = r_qualifier.qualifier;
    exception
    when no_data_found then
    l_current_vendor_id := null;
    continue;
    end;

    –gpr_qualifiers_tbl(j).list_line_id := fnd_api.g_miss_num;
    gpr_qualifiers_tbl (j).operation := qp_globals.g_opr_create;
    gpr_qualifiers_tbl (j).qualifier_attr_value := l_current_vendor_id;
    gpr_qualifiers_tbl (j).qualifier_context := p_qualifier_context;
    gpr_qualifiers_tbl (j).qualifier_attribute := p_qualifier_attribute;
    gpr_qualifiers_tbl (j).comparison_operator_code := p_comparison_operator_code;
    gpr_qualifiers_tbl (j).qualifier_precedence := p_qualifier_precedence;
    gpr_qualifiers_tbl (j).qualifier_grouping_no := l_qual_group_num;

    l_qual_group_num := l_qual_group_num + 1;

    update xxtg.xxtg_eam_qp_qualifier_vals_tmp
    set QUALIFIER_VALUE = l_current_vendor_id
    where qualifier = r_qualifier.qualifier;

    dbms_output.put_line(‘l_current_vendor_id: ‘ || l_current_vendor_id || ‘ r_lines.segment1: ‘ || r_qualifier.qualifier);
    fnd_file.put_line(FND_FILE.LOG,’l_current_vendor_id: ‘ || l_current_vendor_id || ‘ r_lines.segment1: ‘ || r_qualifier.qualifier);

    j := j + 1;
    end loop;

    –dbms_output.put_line(‘before process price list ‘);
    qp_price_list_pub.process_price_list (
    p_api_version_number        => 1,
    p_init_msg_list             => fnd_api.g_true,
    p_return_values             => fnd_api.g_true,
    p_commit                    => fnd_api.g_false,
    x_return_status             => gpr_return_status,
    x_msg_count                 => gpr_msg_count,
    x_msg_data                  => gpr_msg_data,
    p_price_list_rec            => gpr_price_list_rec,
    p_price_list_line_tbl       => gpr_price_list_line_tbl,
    p_pricing_attr_tbl          => gpr_pricing_attr_tbl,
    p_qualifiers_tbl            =>  gpr_qualifiers_tbl,
    p_qualifiers_val_tbl        => gpr_qualifiers_val_tbl,
    x_price_list_rec            => ppr_price_list_rec,
    x_price_list_val_rec        => ppr_price_list_val_rec,
    x_price_list_line_tbl       => ppr_price_list_line_tbl,
    x_price_list_line_val_tbl   => ppr_price_list_line_val_tbl,
    x_qualifiers_tbl            => ppr_qualifiers_tbl,
    x_qualifiers_val_tbl        => ppr_qualifiers_val_tbl,
    x_pricing_attr_tbl          => ppr_pricing_attr_tbl,
    x_pricing_attr_val_tbl      => ppr_pricing_attr_val_tbl);

    if gpr_return_status <> fnd_api.g_ret_sts_success
    then
    dbms_output.put_line(‘unsuccessfull:’ || gpr_msg_count || ‘-’ || gpr_msg_data);

    fnd_file.put_line(FND_FILE.LOG,’unsuccessfull:’ || gpr_msg_data);
    rollback;

    for m in 1 .. gpr_msg_count
    loop
    gpr_msg_data := oe_msg_pub.get (p_msg_index => m, p_encoded => ‘F’);

    fnd_file.put_line(FND_FILE.LOG,’exception – ‘ || gpr_msg_data);
    dbms_output.put_line(‘exception -  ‘ || gpr_msg_data);
    end loop;

    raise fnd_api.g_exc_unexpected_error;
    end if;

    if gpr_return_status = fnd_api.g_ret_sts_success
    then
    dbms_output.put_line(‘success’);
    fnd_file.put_line(FND_FILE.LOG,’success’);

    update xxtg_eam_qp_lines_tmp
    set success_flag = ‘Y’;

    update xxtg_eam_qp_qualifier_vals_tmp
    set success_flag = ‘Y’;
    end if;
    exception
    when fnd_api.g_exc_error
    then
    gpr_return_status := fnd_api.g_ret_sts_error;
    fnd_file.put_line(FND_FILE.LOG,’exception:’ || gpr_return_status);

    rollback;
    when fnd_api.g_exc_unexpected_error
    then
    gpr_return_status := fnd_api.g_ret_sts_unexp_error;
    fnd_file.put_line(FND_FILE.LOG,’exception:’ || gpr_return_status);

    for k in 1 .. gpr_msg_count
    loop
    gpr_msg_data := oe_msg_pub.get (p_msg_index => k, p_encoded => ‘F’);

    fnd_file.put_line(FND_FILE.LOG,’exception – ‘ || gpr_msg_data);
    end loop;

    rollback;
    when others
    then
    gpr_return_status := fnd_api.g_ret_sts_unexp_error;
    fnd_file.put_line(FND_FILE.LOG,’exception – others:’ || gpr_return_status);
    rollback;
    end price_list_import;


    Creating Invoice By AP_INVOICES_INTERFACE with match option

    December 16, 2010

    At this article i am giving an example of creating invoice with AP_INVOICES_INTERFACE. You only need to be careful about your purchase order’s match option must be ‘R’ (match option at po_line_locations_all at R12).

    This script inserts into ap invoice interfaces so that you can execute your Open Interfaces Import to create an invoice. You can also call this concurrent from this code sample.

    procedure insert_into_interface (errbuf    out varchar2,
    retcode   out varchar2,
    p_allowance_id in number,
    p_invoice_num in varchar2,
    p_group_id      in varchar2)
    is
    cursor c_allowance_lines(cp_allowance_id number)
    is

    select xeaa.vendor_id,
    xeaa.vendor_site_id,
    plla.line_location_id,
    plla.match_option,
    pha.segment1,
    pha.po_header_id,
    pla.po_line_id,
    pda.po_distribution_id,
    plla.org_id,
    rsl.item_id,
    plla.shipment_num,
    apss.vendor_site_code,
    apss.payment_method_lookup_code,
    aps.accts_pay_code_combination_id,
    rsh.receipt_num,
    rsl.line_num,
    xeal.*
    from xxxt_allowances_all        xeaa,
    xxxt_allowance_lines_all xeal,
    ap_suppliers                         aps,
    ap_supplier_sites_all             apss,
    rcv_transactions                    rt,
    rcv_shipment_headers        rsh,
    rcv_shipment_lines rsl,
    po_line_locations_all           plla,
    po_lines_all                        pla,
    po_headers_all                   pha,
    po_distributions_all             pda
    where xeal.allowance_id = xeaa.allowance_id
    and pla.po_line_id = plla.po_line_id
    and pha.po_header_id = pla.po_header_id
    and pda.line_location_id = rt.po_line_location_id
    and aps.vendor_id = xeaa.vendor_id
    and apss.vendor_id = xeaa.vendor_id
    and apss.vendor_site_id = xeaa.vendor_site_id
    and xeaa.allowance_id = cp_allowance_id
    and rt.transaction_id = xeal.rcv_transaction_id
    and rsl.shipment_line_id = rt.shipment_line_id
    and rsh.shipment_header_id = rsl.shipment_header_id
    and plla.line_location_id = rt.po_line_location_id;

    l_line_number number;
    l_line_id         number;
    l_total_invoice_amount number;
    l_invoice_id    number;
    l_invoice_count number;
    l_allowance_status xxxt_allowances_all.status%type;
    l_invoice_num ap_invoices_all.invoice_num%type;

    begin
    select count(1)
    into l_invoice_count
    from ap_invoices_all
    where invoice_num = p_invoice_num;

    begin
    select status
    into l_allowance_status
    from xxxt_allowances_all
    where allowance_id = p_allowance_id;

    if(l_allowance_status <> ‘ONAYLANDI’) then
    fnd_file.put_line (fnd_file.OUTPUT, ‘ ALLOWANCE_ID: ‘ || p_allowance_id || ‘ hakedişinin statüsü ONAYLANDI değildir.’);
    –return;
    end if;
    exception
    when no_data_found then
    fnd_file.put_line (fnd_file.OUTPUT, ‘ ALLOWANCE_ID: ‘ || p_allowance_id || ‘ böyle bir hakediş bulunmamaktadır.’);
    return;
    end;

    if(l_invoice_count > 0) then
    fnd_file.put_line (fnd_file.OUTPUT, ‘ INVOICE_NUM: ‘ || p_invoice_num || ‘ fatura numarası kullanımdadır. Lütfen kullanılmayan bir fatura numarası giriniz.’);
    return;
    end if;

    select ap_invoices_s.nextval
    into l_invoice_id
    from dual;

    fnd_file.put_line (fnd_file.OUTPUT, ‘INVOICE_ID: ‘ || l_invoice_id ||’ INVOICE_NUM: ‘ || p_invoice_num || ‘ ALLOWANCE_ID:’ || p_allowance_id || ‘ Faturası girilecek.’);

    –inserting into ap_invoices_interface
    insert into ap_invoices_interface
    (
    invoice_id,
    invoice_num,
    invoice_type_lookup_code,
    invoice_date,
    exchange_date,
    exchange_rate_type,
    vendor_id,
    vendor_site_id,
    payment_method_lookup_code,
    invoice_amount,
    invoice_currency_code,
    terms_name,
    last_update_date,
    last_updated_by,
    last_update_login,
    creation_date,
    created_by,
    source,
    group_id,
    accts_pay_code_combination_id,
    org_id,
    attribute15
    )
    –values
    (
    select l_invoice_id,
    p_invoice_num,
    ‘STANDARD’,
    sysdate,
    null,–exchange_date
    null,
    xeaa.vendor_id,
    xeaa.vendor_site_id,
    nvl(apss.payment_method_lookup_code,’EFT’),
    (select nvl(sum(round(price_override * quantity_override,2)),0)
    from xxxt_allowance_lines_all
    where allowance_id = xeaa.allowance_id),
    ‘TRY’,
    ’30 GÜN’,
    sysdate,
    fnd_profile.value(‘USER_ID’),
    fnd_profile.value(‘LOGIN_ID’),
    sysdate,
    fnd_profile.value(‘USER_ID’),
    ‘HAKEDIS’,
    p_group_id,
    aps.accts_pay_code_combination_id,
    fnd_profile.value(‘ORG_ID’),
    apss.vendor_site_code
    from xxxt_allowances_all xeaa,
    ap_suppliers aps,
    ap_supplier_sites_all apss
    where aps.vendor_id = xeaa.vendor_id
    and apss.vendor_id = xeaa.vendor_id
    and apss.vendor_site_id = xeaa.vendor_site_id
    and xeaa.allowance_id = p_allowance_id);

    –looping at allowance lines
    for r_allowance_lines in c_allowance_lines(p_allowance_id)
    loop
    begin
    select ap_invoice_lines_interface_s.nextval
    into l_line_id
    from dual;

    select nvl (max (line_number),0) + 1
    into l_line_number
    from ap_invoice_lines_interface
    where invoice_id = l_invoice_id;

    fnd_file.put_line (fnd_file.OUTPUT, ”);
    fnd_file.put_line (fnd_file.OUTPUT, ‘——————————————————————————————————————————————’);
    fnd_file.put_line (fnd_file.OUTPUT, ”);
    fnd_file.put_line (fnd_file.OUTPUT, ‘INVOICE_LINE_ID: ‘ || l_line_id ||’ LINE_NUMBER: ‘ || l_line_number || ‘ AMOUNT: ‘ || r_allowance_lines.price_override);
    fnd_file.put_line (fnd_file.OUTPUT, ‘RECEIPT_NUM: ‘ || r_allowance_lines.receipt_num ||’ RCV_TRANSACTION_ID: ‘ || r_allowance_lines.rcv_transaction_id || ‘ QUANTITY: ‘ || r_allowance_lines.quantity_override || ‘ ORG_ID: ‘ || r_allowance_lines.org_id);

    –inserting into ap_invoice_lines_interface
    insert into ap_invoice_lines_interface
    (invoice_id,
    invoice_line_id,
    line_number,
    line_type_lookup_code,
    match_option,
    amount,
    inventory_item_id,
    last_update_date,
    last_updated_by,
    last_update_login,
    creation_date,
    created_by,
    rcv_transaction_id,
    quantity_invoiced,
    po_distribution_id,
    org_id
    )
    values
    (
    l_invoice_id,–p_invoice_id,
    l_line_id,
    l_line_number,
    ‘ITEM’,
    ‘R’,–match option
    round(r_allowance_lines.price_override * r_allowance_lines.quantity_override,2),–amount,
    r_allowance_lines.item_id,
    sysdate,
    fnd_profile.value(‘USER_ID’),
    fnd_profile.value(‘LOGIN_ID’),
    sysdate,
    fnd_profile.value(‘USER_ID’),
    r_allowance_lines.rcv_transaction_id,
    r_allowance_lines.quantity_override,
    r_allowance_lines.po_distribution_id,
    r_allowance_lines.org_id
    );

    commit;
    exception
    when no_data_found
    then
    xxxt_error_package.raise_error(sqlerrm);
    end;
    end loop;
    end insert_into_interface;


    Custom Workflow Using Oracle Approvals Manager(AME)

    December 4, 2010

    In most of Oracle standard workflows Oracle Approvals Manager(AME) is used to define approvers. At one of my projects, there was so many workflows that approvers of workflow could be changed. So, my functional consultant advised me to use AME for defining approval groups and rules on which user to send the notification.

    I developed a custom method for my workflows that look fine at last.

    Here we can see firstly we set the startup values and the first step approvals at set_startup_values.  I got a variable as current_notif_done which i keep for mentioning whether next approver exists or not.

    If you are careful enough you can see i make a loop at approval_ntf. Here workflow loops at approval_ntf each time next approval exists. When there is no next approver i set current_notif_done attribute1 to break the loop.

    I also make two more loops which does not need approval(FYI notifications at approval and reject).

    I settled this structure in two main steps.

    1- Getting approvers for current step

    Here i use this code for asking AME which approver is the next

    ame_api2.getNextApprovers4(applicationIdIn=>your_ame_setups_application_id,
    transactionTypeIn=>your_ame_setups_transaction_type,
    transactionIdIn=>your_ame_setups_transaction_id,–generally itemkey
    flagApproversAsNotifiedIn => ame_util.booleanFalse,
    approvalProcessCompleteYNOut => l_complete,–mentions if approval is complete
    nextApproversOut=>l_next_approvers);–next approval table for current step

    2- Telling approval status to AME

    ame_api2.updateApprovalStatus2(applicationIdIn => your_ame_setups_application_id,
    transactionIdIn => your_ame_setups_transaction_id,–generally itemkey
    approvalStatusIn => AME_UTIL.approvedStatus,–approved_status_or_rejected_status
    approverNameIn => approver_or_rejecters_user_name,
    transactionTypeIn => your_ame_setups_transaction_type);

     

    There are some more keypoints about this. First of all, this generic workflow can be implemented for any needs. So, creating a new workflow time would decrease so much by the help of this generic workflow.

    Two AME transaction type is needed to implement both approval and FYI at AME. Because, if it is not done like this FYI notifications could not be sent.


    Gathering Scripts For Migration

    April 8, 2010

     

    At this article i will try to show you an easier way of migrating scripts (including table, view, procedure, function, package creation scripts) to another system. Here are the steps for doing this;

    1 - Create a table for keeping data temptorarily.

    2- create a table. I will name my table as xxtg_kts_engin_deneme

       create table xxtg.xxtg_kts_engin_deneme
       (
           text clob;
       );

    3 - Choose the prefixes of the tables(or whatever object you want) you desire.

    4 - Choose the prefixes of tables you want to exclude.

    5 - modify the cursor c_table below upon your needs

    cursor c_table is

            SELECT *
              FROM dba_objects
             WHERE object_type = ‘TABLE’
               AND object_name LIKE ‘XX%’ 
               AND object_name NOT LIKE ‘XXTG%’

    6 - Run the script below to insert your table creation scripts into xxtg_kts_engin_deneme table. After that you can copy your scripts by just opening the table data.

    declare
        cursor c_table is
            SELECT *
              FROM dba_objects
             WHERE object_type = ‘TABLE’
               AND object_name LIKE ‘XX%’
               and object_name not like ‘XXXT_PO%’
               and object_name not like ‘XXXT_INV%’
               and object_name not like ‘XXXT_AP%’
               AND owner NOT IN (‘XXTEPE’, ‘XXBR’, ‘XXPYXIS’);
        r_table c_table%rowtype;
        
        one_table clob;
        tmp_table clob;
        tmp_table2 clob;
        pos number;
    begin
        open c_table;
        loop
            fetch c_table into r_table;
            exit when c_table%notfound;
            
            tmp_table2 := ‘drop table ‘ || r_table.table_name;
            
            select DBMS_METADATA.GET_DDL (
                                    ’TABLE’,
                                    r_table.table_name,
                                    ’XXTG’
                                    ) X
            into tmp_table
            from dual;
            
            tmp_table := ‘drop table xxtg.’ || r_table.table_name || chr(10) || tmp_table;
            
            
            begin
            select ‘drop synonym ‘ || r_table.table_name || chr(10) || DBMS_METADATA.GET_DDL (
                            ’SYNONYM’,
                            r_table.table_name,
                            ’APPS’
                            ) X
            into tmp_table2
            from dual;
            exception when others then
                tmp_table2 := ”;
            end;
            
            tmp_table := tmp_table || tmp_table2;
            
            one_table := one_table || tmp_table;
            dbms_output.put_line(r_table.table_name);
            one_table := one_table || chr(10) || ‘/’ || chr(10);
            
            
            –one_table := one_table || tmp_table;
        end loop;
        close c_table;

        insert into xxtg.xxtg_kts_engin_deneme
        (text)
        values(one_table);
    end;


    Developing On E-Business Suite

    March 1, 2010

    fnd_date.canonical_to_date(p_date_from);


    Oracle Forms Triggers

    November 13, 2009

    At this article i will try to mention about Forms’ special triggers on various types. We will examine them in three sections; Form level triggers, Data block level triggers and Item level triggers.

    Form Level Triggers;

    When-New-Form-Instance: This trigger is called just after form is called. So you can do everything you need to do before the form starts. Be careful that this trigger is called only when the form is opened.

    When-Form-Navigate: This trigger is helpful when you need to open a new form(as new program) from a from. This trigger is called when navigating between those forms.

    When-New-Block-Instance: This trigger is called when any of the blocks the form contains is entered.

    When-New-Record-Instance: This trigger is called when any record changes in any blocks.

    When-New-Item-Instance: This trigger is called when any of the items in the forms is entered.

    These are most commonly used triggers at form level. There are some other triggers like WHEN-MOUSE-CLICK, WHEN-MOUSE-DOUBLECLICK, Key-Commit, Key-Listval … But there these kind of triggers are more commonly used at lower levels that will be explained later.

    Block Level Triggers;

    When-New-Block-Instance: This trigger is called when one specific block is entered.

    When-New-Record-Instance: This trigger is called when cursor enters to a new record in specified block. This trigger may be useful when it is needed to set calculated value to an item at every record.

    When-New-Item-Instance: This trigger is called when any of the items in the forms is entered. This trigger can be used at block level upon needs. But, it is better idea to use this trigger at item level.

    WHEN-VALIDATE-RECORD: This trigger is called when cursor is relocated out of current record of specified block to another record. This trigger can be very useful while trying to insert or update a record under some costraints. If the conditions are checked at this trigger and form_trigger_failure will be raised under the situation of failing validation, record will not be saved. So, everything works just fine.

    POST-QUERY: This trigger is called when the form user searches a record or searches all records at form. POST-QUERY trigger is triggered for each record the search fetches. So, it is very useful when it is needed to set some item’s values upon other database items’ values.

    PRE-INSERT: This trigger is called when the form user tries to insert a new record to table. This trigger is generally used to give sequence number to table’s “id” column. You can also make some validation of record that is specific for only insert statements.

    PRE-UPDATE: This trigger is called when the form user tries to update one record.  You can make some validation of record that is specific for only update statements.

    ON-POPULATE-DETAILS: This trigger populates the detail blocks that has relation with the record we select. Well, before telling about this trigger i should tell about relations of blocks. Blocks can be joined on one or more item’s values and there must be a master-detail relationship between that blocks. For example, let’s assume that customers is our master table, customer_accounts is our detail table and these tables can be joines on customer_id. These table data on forms can be showed with two data blocks. One of them is Customers and other block is Customer_Accounts. At this structure, customers block will be given a relation with customer_accounts on customer_id. Then after, this trigger is created automatically.

    It is not a good idea to edit automatically created code on the concern of maintenance.

    ON-CHECK-DELETE-MASTER: This trigger is also created automatically upon relation. This trigger is created upon the Delete Record Behaviour choice at relation. Again it is not a good idea to edit this trigger.

    These are most commonly used triggers at block level. There is much more. Others’ usage is on your need.

    Item Level Triggers: At this level most of the common triggers exits but the ones following are the most commonly used ones.

    WHEN-VALIDATE-ITEM: This trigger is called when the cursor exits from specified item. This trigger is used for validation for only one field.

    POST-CHANGE: This trigger is called when any change of an item occurs. You can use this trigger for calculations. For example, you calculate %20 tax upon cost of service. If cost of service changes then the tax changes. If you write this trigger on cost of service item for recalculation of tax each time cost changes, it will work just fine.

    KEY-LISTVAL: This trigger is called when the form user tries to open LOV from an item. This trigger is used to calculate some other values at the execution of LOV. And also, this trigger is called to show calendar on LOV at text items with date data type.


    Oracle Explain Plan – 2

    September 25, 2009

    Zurück

      Interpreting Explain Plan 

    What’s an explain plan?

    An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

    Query processing can be divided into 7 phases:

    [1] Syntactic Checks the syntax of the query
    [2] Semantic Checks that all objects exist and are accessible
    [3] View Merging Rewrites query as join on base tables as opposed to using views
    [4] Statement
         Transformation
    Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
    [5] Optimization Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
    [6] QEP Generation QEP = Query Evaluation Plan
    [7] QEP Execution QEP = Query Evaluation Plan

    Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement.

    The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation  of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is reparsed.

    Terminology

    Row Source A set of rows used in a query may be a select from a base object or the result set returned by joining 2 earlier row sources
    Predicate where clause of a query
    Tuples rows
    Driving Table This is the row source that we use to seed the query. If this returns a lot of rows then this can have a negative affect on all subsequent operations
    Probed Table This is the object we lookup data in after we have retrieved relevant key data from the driving table.

    How does Oracle access data?

    At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:

    • Full Table Scan (FTS)
    • Index Lookup (unique & non-unique)
    • Rowid

    Explain plan Hierarchy

    Simple explain plan:

    Query Plan
    —————————————–
    SELECT STATEMENT     [CHOOSE] Cost=1234
      TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]

    The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means we are doing a full table scan of table LARGE. When this operation completes then the resultant row source is passed up to the
    next level of the query for processing. In this case it is the SELECT STATEMENT which is the top of the query.

    [CHOOSE] is an indication of the optimizer_goal for the query. This DOES NOT necessarily indicate that plan has actually used this goal. The only way to confirm this is to check the
    cost= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field:

    SELECT STATEMENT     [CHOOSE] Cost=1234

    However the explain plan below indicates the use of the RBO because the cost field is blank:

    SELECT STATEMENT     [CHOOSE] Cost=

    The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.

    [:Q65001] indicates that this particular part of the query is being executed in parallel. This number indicates that the operation will be processed by a parallel query slave as opposed to being executed serially.

    [ANALYZED] indicates that the object in question has been analyzed and there are currently statistics available for the CBO to use. There is no indication of the ‘level’ of analysis done.

    Access Methods in detail

    Full Table Scan (FTS)

    In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. Multiblock i/o is controlled by the parameter <PARAM:db_block_multi_block_read_count>.

    This defaults to:

    db_block_buffers / ( (PROCESSES+3) / 4 )

    Maximum values are OS dependant

    Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache so will be quickly aged out. FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel.

    Example FTS explain plan:

    SQL> explain plan for select * from dual;

    Query Plan
    —————————————–
    SELECT STATEMENT     [CHOOSE] Cost=
      TABLE ACCESS FULL DUAL

    Index lookup

    Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.

    In this example an index is used to find the relevant row(s) and then the table is accessed to lookup the ename column (which is not included in the index):

    SQL> explain plan for
    select empno,ename from emp where empno=10;
    Query Plan

    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
        INDEX UNIQUE SCAN EMP_I1

    Notice the ‘TABLE ACCESS BY ROWID’ section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. In this case the rowid has been produced by looking up values in the index first. The index is being accessed by an ‘INDEX UNIQUE SCAN’ operation. This is explained below. The index name in this case is EMP_I1. If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access.

    In the following example all the columns (empno) are in the index. Notice that no table access takes place:

    SQL> explain plan for
    select empno from emp where empno=10;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
      INDEX UNIQUE SCAN EMP_I1

    Indexes are presorted so sorting may be unecessary if the sort order required is the same as the index.

    SQL> explain plan for select empno,ename from emp
    where empno > 7876 order by empno;

    Query Plan
    ————————————————————-
    SELECT STATEMENT   [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
      INDEX RANGE SCAN EMP_I1 [ANALYZED]

    In this case the index is sorted so ther rows will be returned in the order of the index hence a sort is unecessary.

    SQL> explain plan for
    select /*+ Full(emp) */ empno,ename from emp
    where empno> 7876 order by empno;

    Query Plan
    ————————————————————-
    SELECT STATEMENT   [CHOOSE] Cost=9
      SORT ORDER BY
        TABLE ACCESS FULL EMP [ANALYZED]  Cost=1 Card=2 Bytes=66

    Because we have forced a FTS the data is unsorted and so we must sort the data
    after it has been retrieved.

    There are 4 methods of index lookup:

    • index unique scan
    • index range scan
    • index full scan
    • index fast full scan

    Index unique scan

    Method for looking up a single key value via a unique index. Always returns a single value You must supply AT LEAST the leading column of the index to access data via the index, However this may return > 1 row as the uniqueness will not be guaranteed.

    SQL> explain plan for
    select empno,ename from emp where empno=10;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
        INDEX UNIQUE SCAN EMP_I1

    Index range scan

    Method for accessing multiple column values You must supply AT LEAST the leading column of the index to access data via the index Can be used for range operations (e.g. > < <> >= <= between)

    SQL> explain plan for select empno,ename from emp
    where empno > 7876 order by empno;

    Query Plan
    ——————————————————-
    SELECT STATEMENT   [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
      INDEX RANGE SCAN EMP_I1 [ANALYZED]

    A non-unique index may return multiple values for the predicate col1 = 5 and will use an index range scan

    SQL> explain plan for select mgr from emp where mgr = 5

    Query plan
    ——————–
    SELECT STATEMENT [CHOOSE] Cost=1
      INDEX RANGE SCAN EMP_I2 [ANALYZED]

    Index Full Scan

    In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided for a table). Full index scans are  only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.

    For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.

    An Index full scan will perform single block i/o’s and so it may prove to be inefficient. Index BE_IX is a concatenated index on big_emp (empno,ename)

    SQL> explain plan for select empno,ename
         from big_emp order by empno,ename;

    Query Plan
    ————————————————————
    SELECT STATEMENT   [CHOOSE] Cost=26
      INDEX FULL SCAN BE_IX [ANALYZED]

    Index Fast Full Scan

    Scans all the block in the index Rows are not returned in sorted order Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO may be hinted using INDEX_FFS hint uses multiblock i/o can be executed in parallel can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

    Note that INDEX FAST FULL SCAN is the mechinism behind fast index create and recreate. Index BE_IX is a concatenated index on big_emp (empno,ename)

    SQL> explain plan for select empno,ename from big_emp;

    Query Plan
    ——————————————
    SELECT STATEMENT   [CHOOSE] Cost=1
      INDEX FAST FULL SCAN BE_IX [ANALYZED]

    Selecting the 2nd column of concatenated index:

    SQL> explain plan for select ename from big_emp;

    Query Plan
    ——————————————
    SELECT STATEMENT   [CHOOSE] Cost=1
      INDEX FAST FULL SCAN BE_IX [ANALYZED]

    Rowid

    This is the quickest access method available Oracle simply retrieves the block specified and extracts the rows it is interested in. Most frequently seen in explain plans as Table access by Rowid

    SQL> explain plan for select * from dept where rowid = ‘:x’;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID DEPT [ANALYZED]

    Table is accessed by rowid following index lookup:

    SQL> explain plan for
    select empno,ename from emp where empno=10;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
        INDEX UNIQUE SCAN EMP_I1

    Joins

    A Join is a predicate that attempts to combine 2 row sources We only ever join 2 row sources together Join steps are always performed serially even though underlying row sources may have been accessed in parallel. Join order – order in which joins are performed

    The join order makes a significant difference to the way in which the query is executed. By accessing particular row sources first, certain predicates may be satisfied that are not satisfied by with other join orders. This may prevent certain access paths from being taken.

    Suppose there is a concatenated index on A(a.col1,a.col2). Note that a.col1 is the leading column. Consider the following query:

    select A.col4
    from   A,B,C
    where  B.col3 = 10
    and    A.col1 = B.col1
    and    A.col2 = C.col2
    and    C.col3 = 5

    We could represent the joins present in the query using the following schematic:

      B     <—> A <—>    C
    col3=10                col3=5

    There are really only 2 ways we can drive the query: via B.col3 or C.col3. We would have to do a Full scan of A to be able to drive off it. This is unlikely to be efficient with large tables;

    If we drive off table B, using predicate B.col3=10 (as a filter or lookup key) then we will retrieve the value for B.col1 and join to A.col1. Because we have now filled the leading column of the concatenated index on table A we can use this index to give us values for A.col2 and join to A.

    However if we drive of table c, then we only get a value for a.col2 and since this is a trailing column of a concatenated index and the leading column has not been supplied at this point, we cannot use the index on a to lookup the data.

    So it is likely that the best join order will be B A C. The CBO will obviously use costs to establish whether the individual access paths are a good idea or not.

    If the CBO does not choose this join order then we can hint it by changing the from
    clause to read:

    from B,A,C

    and using the /*+ ordered */ hint. The resultant query would be:

    select /*+ ordered */ A.col4
    from   B,A,C
    where  B.col3 = 10
    and    A.col1 = B.col1
    and    A.col2 = C.col2
    and    C.col3 = 5

    Join Types

    • Sort Merge Join (SMJ)
    • Nested Loops (NL)
    • Hash Join

    Sort Merge Join

    Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently Sorted rows from both sides are then merged together (joined)

                       MERGE
                     /      \
                SORT        SORT
                 |             |
            Row Source 1  Row Source 2

    If the row sources are already (known to be) sorted then the sort operation is unecessary as long as both ‘sides’ are sorted using the same key. Presorted row sources include indexed columns and row sources that have already been sorted in earlier steps. Although the merge of the 2 row sources is handled serially, the row sources could be accessed in parallel.

    SQL> explain plan for
    select /*+ ordered */ e.deptno,d.deptno
    from emp e,dept d
    where e.deptno = d.deptno
    order by e.deptno,d.deptno;

    Query Plan
    ————————————-
    SELECT STATEMENT [CHOOSE] Cost=17
      MERGE JOIN
        SORT JOIN
          TABLE ACCESS FULL EMP [ANALYZED]
        SORT JOIN
          TABLE ACCESS FULL DEPT [ANALYZED]

    Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

    Nested Loops

    First we return all the rows from row source 1 Then we probe row source 2 once for each row returned from row source 1

    Row source 1
    ~~~~~~~~~~~~
    Row 1 ————–       — Probe ->       Row source 2
    Row 2 ————–       — Probe ->       Row source 2
    Row 3 ————–       — Probe ->       Row source 2

    Row source 1 is known as the outer table
    Row source 2 is known as the inner table

    Accessing row source 2 is known a probing the inner table For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

    SQL> explain plan for
    select a.dname,b.sql
    from dept a,emp b
    where a.deptno = b.deptno;

    Query Plan
    ————————-
    SELECT STATEMENT [CHOOSE] Cost=5
      NESTED LOOPS
        TABLE ACCESS FULL DEPT [ANALYZED]
        TABLE ACCESS FULL EMP [ANALYZED]

    Hash Join

    New join type introduced in 7.3 More efficient in theory than NL & SMJ Only accessible via the CBO Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.

    SQL> explain plan for
    select /*+ use_hash(emp) */ empno
    from emp,dept
    where emp.deptno = dept.deptno;

    Query Plan
    —————————-
    SELECT STATEMENT  [CHOOSE] Cost=3
      HASH JOIN
        TABLE ACCESS FULL DEPT
        TABLE ACCESS FULL EMP

    Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3

    Cartesian Product

    A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances – Star joins uses cartesian products.

    Notice that there is no join between the 2 tables:

    SQL> explain plan for
    select emp.deptno,dept,deptno
    from emp,dept

    Query Plan
    ——————————
    SLECT STATEMENT [CHOOSE] Cost=5
      MERGE JOIN CARTESIAN
        TABLE ACCESS FULL DEPT
        SORT JOIN
          TABLE ACCESS FULL EMP

    The CARTESIAN keyword indicate that we are doing a cartesian product.

    Operations

    Operations that show up in explain plans

    • sort
    • filter
    • view

    Sorts

    There are a number of different operations that promote sorts

    • order by clauses
    • group by
    • sort merge join

    Note that if the row source is already appropriately sorted then no sorting is required. This is now indicated in 7.3:

    SORT GROUP BY NOSORT
         INDEX FULL SCAN …..

    In this case the group by operation simply groups the rows it does not do the sort operation as this has already been completed.

    Sorts are expensive operations especially on large tables where the rows do not fit in memory and spill to disk. By default sort blocks are placed into the buffer cache. This may result in aging out of other blocks that may be reread by other processes. To avoid this you can use the parameter <Parameter:SORT_DIRECT_WRITES> which does not place sort blocks into the buffer cache.

    Filter

    Has a number of different meanings used to indicate partition elimination may also indicate an actual filter step where one row source is filtering another functions such as min may introduce filter steps into query plans

    In this example there are 2 filter steps. The first is effectively like a NL except that it stops when it gets something that it doesn’t like (i.e. a bounded NL). This is there because of the not in. The second is filtering out the min value:

    SQL> explain plan for select * from emp
         where empno not in (select min(empno)
         from big_emp group by empno);

    Query Plan
    ——————
    SELECT STATEMENT [CHOOSE]  Cost=1
      FILTER     **** This is like a bounded nested loops
        TABLE ACCESS FULL EMP [ANALYZED]
         FILTER   **** This filter is introduced by the min
            SORT GROUP BY NOSORT
              INDEX FULL SCAN BE_IX

    This example is also interesting in that it has a NOSORT function. The group by does not need to sort because the index row source is already pre sorted.

    Views

    When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the ‘view’ will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.

    In the following example the select contains an inline view which cannot be merged:

    SQL> explain plan for
    select ename,tot
    from emp,
        (select empno,sum(empno) tot from big_emp group by empno) tmp
    where emp.empno = tmp.empno;

    Query Plan
    ————————
    SELECT STATEMENT [CHOOSE]
      HASH JOIN
        TABLE ACCESS FULL EMP [ANALYZED]
        VIEW
          SORT GROUP BY
            INDEX FULL SCAN BE_IX

    In this case the inline view tmp which contains an aggregate function cannot be merged into the main query. The explain plan shows this as a view step.

    Partition Views

    Allows a large table to be broken up into a number of smaller partitions which can be queried much more quickly than the table as a whole a union all view is built over the top to provide the original functionality Check constraints or where clauses provide partition elimination capabilities

    SQL> explain plan for
    select /*+ use_nl(p1,kbwyv1) ordered */  sum(prc_pd)
    from parent1 p1,  kbwyv1
    where p1.class = 22
    and   kbwyv1.bitm_numb = p1.bitm_numb
    and   kbwyv1.year = 1997
    and   kbwyv1.week between 32 and 33 ;

    Query Plan
    —————————————–
    SELECT STATEMENT   [FIRST_ROWS] Cost=1780
      SORT AGGREGATE
        NESTED LOOPS   [:Q65001] Ct=1780 Cd=40 Bt=3120
          TABLE ACCESS FULL PARENT1 [:Q65000] [AN] Ct=20 Cd=40 Bt=1040
          VIEW  KBWYV1 [:Q65001]
            UNION-ALL PARTITION  [:Q65001]
              FILTER   [:Q64000]
                TABLE ACCESS FULL KBWYT1 [AN] Ct=11 Cd=2000 Bt=104000
              TABLE ACCESS FULL KBWYT2 [AN] Ct=11 Cd=2000 Bt=104000
              TABLE ACCESS FULL KBWYT3 [AN] Ct=11 Cd=2000 Bt=104000
              FILTER   [:Q61000]
                TABLE ACCESS FULL KBWYT4 [AN] Ct=11 Cd=2000 Bt=104000

    KBWYV1 is a view on 4 tables KBWYT1-4. KBWYT1-4 contain rows for week 31-34 respectively and are maintained by check constraints. This query should only return rows from partions 2 & 3. The filter operation indicates this. Partitions 1 & 4 are eliminated at execution time. The view line indicates that the view is not merged. The union-all partion information indicates that we have recognised this as a partition view. Note that the tables can be accessed in parallel.

    Remote Queries

    Only shows remote in the OPERATION column OTHER column shows query executed on remote node OTHER_NODE shows where it is executed Different operational characteristics for RBO & CBO

    RBO – Drags everything across the link and joins locally
    CBO – Uses cost estimates to determine whether to execute remotely or locally

    SQL>  explain plan for
    select *
    from dept@loop_link;

    Query Plan
    ——————————————————-
    SELECT STATEMENT REMOTE  [CHOOSE] Cost=1
      TABLE ACCESS FULL DEPT [SJD.WORLD] [ANALYZED]

    In this case the whole query has been sent to the remote site. The other column shows nothing.

    SQL> explain plan for
    select a.dname,avg(b.sal),max(b.sal)
    from dept@loop_link a, emp b
    where a.deptno=b.deptno
    group by a.dname
    order by max(b.sal),avg(b.sal) desc;

    Query Plan
    —————————————————–
    SELECT STATEMENT   [CHOOSE] Cost=20
      SORT ORDER BY  [:Q137003] [PARALLEL_TO_SERIAL]
        SORT GROUP BY  [:Q137002] [PARALLEL_TO_PARALLEL]
          NESTED LOOPS   [:Q137001] [PARALLEL_TO_PARALLEL]
            REMOTE   [:Q137000] [PARALLEL_FROM_SERIAL]
            TABLE ACCESS FULL EMP [:Q137001] [ANALYZED]
            [PARALLEL_COMBINED_WITH_PARENT]

    Bind Variables

    Bind variables are recommended in most cases because they promote sharing of sql code
    At parse time the parser has NO IDEA what the bind variable contains. With RBO this makes no difference but with CBO, which relies on accurate statistics to produce plans, this can be a problem.

    Defining bind variables in sqlplus:

    variable x varchar2(18);
    assigning values:
    begin
    :x := ‘hello’;
    end;
    /

    SQL> explain plan for
    select *
    from dept
    where rowid = ‘:x’;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
      TABLE ACCESS BY ROWID DEPT [ANALYZED]

    Parallel Query

    Main indicators that a query is using PQO:

    • [:Q1000004] entries in the explain plan
    • Checkout the other column for details of what the slaves are executing
    • v$pq_slave will show any parallel activity

    Columns to look in for information

    • other – contains the query passed to the slaves
    • other_tag – describes the contents of other
    • object_node – indicates order of pqo slaves

    Parallel Query operates on a producer/consumer basis. When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers. If there are only 2 slaves available then we use these. If there is only 1 slave available then we go serial If there are none available then we use serial. If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial

    Consumer processes typically perform a sorting function. If there is no requirement for the data to be sorted then the consumer slaves are not produced and we end up with the number of slaves used matching the degree of parallelism as opposed to being 2x the degree.

    Parallel Terms

    PARALLEL_FROM_SERIAL This means that source of the data is serial but it is passed to a parallel consumer
    PARALLEL_TO_PARALLEL Both the consumer and the producer are  parallel
    PARALLEL_COMBINED_WITH_PARENT This operation has been combined with the parent operator. For example in a sort merge join the sort operations would be shown as PARALLEL_COMBINED_WITH_PARENT because the sort and the merge are handled as 1 operation.
    PARALELL_TO_SERIAL The source of the data is parallel but it is passed to a serial consumer. This typically will happen at the top of the explain plan but could occur anywhere

    Examples of parallel queries

    Assumptions

    OPTIMIZER_MODE = CHOOSE
    DEPT is small compared to EMP
    DEPT has an index (DEPT_INDX) on deptno column

    Three examples are presented

    Query #1:  Serial
    Query #2:  Parallel
    Query #3:  Parallel, with forced optimization to RULE and forced usage of DEPT_INDX

    Sample Query #1 (Serial)

    select A.dname, avg(B.sal), max(B.sal)
    from  dept A, emp B
    where A.deptno = B.deptno
    group by A.dname
    order by max(B.sal), avg(B.sal) desc;

    Execution Plan #1 (Serial)

    OBJECT_NAME                      OBJECT_NODE OTHER
    ——————————-  ———– ——-
    SELECT STATEMENT
     SORT ORDER BY
       SORT GROUP BY
         MERGE JOIN
           SORT JOIN
             TABLE ACCESS FULL emp
           SORT JOIN
             TABLE ACCESS FULL dept

    Notice that the object_node and other columns are empty

    Sample Query #2 (Query #1 with parallel hints)

    select /*+ parallel(B,4) parallel(A,4) */
    A.dname, avg(B.sal), max(B.sal)
    from  dept A, emp B
    where A.deptno = B.deptno
    group by A.dname
    order by max(B.sal), avg(B.sal) desc;

    Execution Plan #2  (Parallel)

    OBJECT_NAME                      OBJECT_NODE OTHER
    ——————————-  ———– ——-
    SELECT STATEMENT      Cost = ??
     SORT ORDER BY                   :Q55004     **[7]**
       SORT GROUP BY                 :Q55003     **[6]**
         MERGE JOIN                  :Q55002     **[5]**
           SORT JOIN                 :Q55002     **[4]**
             TABLE ACCESS FULL emp   :Q55001     **[2]**
           SORT JOIN                 :Q55002     **[3]**
             TABLE ACCESS FULL dept  :Q55000     **[1]**

    Execution Plan #2  — OTHER column

    **[1]**  (:Q55000) “PARALLEL_FROM_SERIAL”

    Serial execution of SELECT DEPTNO, DNAME FROM DEPT

    **[2]**  (:Q55001) “PARALLEL_TO_PARALLEL”

            SELECT /*+ ROWID(A1)*/
            A1.”DEPTNO” C0, A1.”SAL” C1
            FROM “EMP” A1
            WHERE ROWID BETWEEN :1 AND :2

    **[3]**  (:Q55002) “PARALLEL_COMBINED_WITH_PARENT”
    **[4]**  (:Q55002) “PARALLEL_COMBINED_WITH_PARENT”
    **[5]**  (:Q55002) “PARALLEL_TO_PARALLEL”

            SELECT /*+ ORDERED USE_MERGE(A2)*/
            A2.C1 C0, A1.C1 C1
            FROM :Q55001 A1,:Q55000 A2
            WHERE A1.C0=A2.C0

    **[6]**  (:Q55003) “PARALLEL_TO_PARALLEL”

            SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2
            FROM :Q55002 A1
            GROUP BY A1.C0

    **[7]**  (:Q55004) “PARALLEL_FROM_SERIAL”

            SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2
            FROM :Q55003 A1
            ORDER BY A1.CO, A1.C1 DESC

    Sample Query #3 (Query #2 with fudged hints)

    select /*+ index(A dept_indx) parallel(B,4) parallel(A,4) */
          A.dname, avg(B.sal), max(B.sal)
    from  dept A, emp B
    where A.deptno = B.deptno
    group by A.dname
    order by max(B.sal), avg(B.sal) desc;

    Execution Plan #3  (Parallel)

    OBJECT_NAME                         OBJECT_NODE OTHER
    ———————————– ———– ——-
    SELECT STATEMENT          Cost = ??
     SORT ORDER BY                      :Q58002     **[6]**
       SORT GROUP BY                    :Q58001     **[5]**
         NESTED LOOPS JOIN              :Q58000     **[4]**
           TABLE ACCESS FULL emp        :Q58000     **[3]**
           TABLE ACCESS BY ROWID dept   :Q58000     **[2]**
             INDEX RANGE SCAN dept_indx :Q58000     **[1]**

    Execution Plan #3  — OTHER column

    **[1]**  (:Q58000) “PARALLEL_COMBINED_WITH_PARENT”
    **[2]**  (:Q58000) “PARALLEL_COMBINED_WITH_PARENT”
    **[3]**  (:Q58000) “PARALLEL_COMBINED_WITH_PARENT”
    **[4]**  (:Q58000) “PARALLEL_TO_PARALLEL”

            SELECT /*+ ORDERED USE_NL(A2) INDEX(A2) */
            A2.”DNAME” C0, A1.C0 C1
            FROM
              (SELECT /*+ ROWID(A3) */
               A3.”SAL” CO, A3.”DEPTNO” C1
               FROM “EMP” A3
               WHERE ROWID BETWEEN :1 AND :2) A1,
              “DEPT” A2
            WHERE A2.”DEPTNO” = A1.C1

    **[5]**  (:Q58001) “PARALLEL_TO_PARALLEL”

            SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2
            FROM :Q58000 A1
            GROUP BY A1.C0

    **[6]**  (:Q58002) “PARALLEL_TO_SERIAL”

            SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2
            FROM :Q58001 A1
            ORDER BY A1.C0, A1.C1 DESC

    How to obtain explain plans

    Explain plan for

    Main advantage is that it does not actually run the query – just parses the sql. This means that it executes quickly. In the early stages of tuning explain plan gives you an idea of the potential performance of your query without actually running it. You can then make a judgement as to any modifications you may choose to make.

    Autotrace

    Autotrace can be configured to run the sql & gives a plan  and statistics afterwards or just give you an explain plan without execu

    Zurück

      Interpreting Explain Plan 

    What’s an explain plan?

    An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

    Query processing can be divided into 7 phases:

    [1] Syntactic Checks the syntax of the query
    [2] Semantic Checks that all objects exist and are accessible
    [3] View Merging Rewrites query as join on base tables as opposed to using views
    [4] Statement
         Transformation
    Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
    [5] Optimization Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
    [6] QEP Generation QEP = Query Evaluation Plan
    [7] QEP Execution QEP = Query Evaluation Plan

    Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement.

    The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation  of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is reparsed.

    Terminology

    Row Source A set of rows used in a query may be a select from a base object or the result set returned by joining 2 earlier row sources
    Predicate where clause of a query
    Tuples rows
    Driving Table This is the row source that we use to seed the query. If this returns a lot of rows then this can have a negative affect on all subsequent operations
    Probed Table This is the object we lookup data in after we have retrieved relevant key data from the driving table.

    How does Oracle access data?

    At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:

    • Full Table Scan (FTS)
    • Index Lookup (unique & non-unique)
    • Rowid

    Explain plan Hierarchy

    Simple explain plan:

    Query Plan
    —————————————–
    SELECT STATEMENT     [CHOOSE] Cost=1234
      TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]

    The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means we are doing a full table scan of table LARGE. When this operation completes then the resultant row source is passed up to the
    next level of the query for processing. In this case it is the SELECT STATEMENT which is the top of the query.

    [CHOOSE] is an indication of the optimizer_goal for the query. This DOES NOT necessarily indicate that plan has actually used this goal. The only way to confirm this is to check the
    cost= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field:

    SELECT STATEMENT     [CHOOSE] Cost=1234

    However the explain plan below indicates the use of the RBO because the cost field is blank:

    SELECT STATEMENT     [CHOOSE] Cost=

    The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.

    [:Q65001] indicates that this particular part of the query is being executed in parallel. This number indicates that the operation will be processed by a parallel query slave as opposed to being executed serially.

    [ANALYZED] indicates that the object in question has been analyzed and there are currently statistics available for the CBO to use. There is no indication of the ‘level’ of analysis done.

    Access Methods in detail

    Full Table Scan (FTS)

    In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. Multiblock i/o is controlled by the parameter <PARAM:db_block_multi_block_read_count>.

    This defaults to:

    db_block_buffers / ( (PROCESSES+3) / 4 )

    Maximum values are OS dependant

    Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache so will be quickly aged out. FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel.

    Example FTS explain plan:

    SQL> explain plan for select * from dual;

    Query Plan
    —————————————–
    SELECT STATEMENT     [CHOOSE] Cost=
      TABLE ACCESS FULL DUAL

    Index lookup

    Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o.

    In this example an index is used to find the relevant row(s) and then the table is accessed to lookup the ename column (which is not included in the index):

    SQL> explain plan for
    select empno,ename from emp where empno=10;
    Query Plan

    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
        INDEX UNIQUE SCAN EMP_I1

    Notice the ‘TABLE ACCESS BY ROWID’ section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. In this case the rowid has been produced by looking up values in the index first. The index is being accessed by an ‘INDEX UNIQUE SCAN’ operation. This is explained below. The index name in this case is EMP_I1. If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access.

    In the following example all the columns (empno) are in the index. Notice that no table access takes place:

    SQL> explain plan for
    select empno from emp where empno=10;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
      INDEX UNIQUE SCAN EMP_I1

    Indexes are presorted so sorting may be unecessary if the sort order required is the same as the index.

    SQL> explain plan for select empno,ename from emp
    where empno > 7876 order by empno;

    Query Plan
    ————————————————————-
    SELECT STATEMENT   [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
      INDEX RANGE SCAN EMP_I1 [ANALYZED]

    In this case the index is sorted so ther rows will be returned in the order of the index hence a sort is unecessary.

    SQL> explain plan for
    select /*+ Full(emp) */ empno,ename from emp
    where empno> 7876 order by empno;

    Query Plan
    ————————————————————-
    SELECT STATEMENT   [CHOOSE] Cost=9
      SORT ORDER BY
        TABLE ACCESS FULL EMP [ANALYZED]  Cost=1 Card=2 Bytes=66

    Because we have forced a FTS the data is unsorted and so we must sort the data
    after it has been retrieved.

    There are 4 methods of index lookup:

    • index unique scan
    • index range scan
    • index full scan
    • index fast full scan

    Index unique scan

    Method for looking up a single key value via a unique index. Always returns a single value You must supply AT LEAST the leading column of the index to access data via the index, However this may return > 1 row as the uniqueness will not be guaranteed.

    SQL> explain plan for
    select empno,ename from emp where empno=10;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
        INDEX UNIQUE SCAN EMP_I1

    Index range scan

    Method for accessing multiple column values You must supply AT LEAST the leading column of the index to access data via the index Can be used for range operations (e.g. > < <> >= <= between)

    SQL> explain plan for select empno,ename from emp
    where empno > 7876 order by empno;

    Query Plan
    ——————————————————-
    SELECT STATEMENT   [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
      INDEX RANGE SCAN EMP_I1 [ANALYZED]

    A non-unique index may return multiple values for the predicate col1 = 5 and will use an index range scan

    SQL> explain plan for select mgr from emp where mgr = 5

    Query plan
    ——————–
    SELECT STATEMENT [CHOOSE] Cost=1
      INDEX RANGE SCAN EMP_I2 [ANALYZED]

    Index Full Scan

    In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided for a table). Full index scans are  only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.

    For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.

    An Index full scan will perform single block i/o’s and so it may prove to be inefficient. Index BE_IX is a concatenated index on big_emp (empno,ename)

    SQL> explain plan for select empno,ename
         from big_emp order by empno,ename;

    Query Plan
    ————————————————————
    SELECT STATEMENT   [CHOOSE] Cost=26
      INDEX FULL SCAN BE_IX [ANALYZED]

    Index Fast Full Scan

    Scans all the block in the index Rows are not returned in sorted order Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO may be hinted using INDEX_FFS hint uses multiblock i/o can be executed in parallel can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

    Note that INDEX FAST FULL SCAN is the mechinism behind fast index create and recreate. Index BE_IX is a concatenated index on big_emp (empno,ename)

    SQL> explain plan for select empno,ename from big_emp;

    Query Plan
    ——————————————
    SELECT STATEMENT   [CHOOSE] Cost=1
      INDEX FAST FULL SCAN BE_IX [ANALYZED]

    Selecting the 2nd column of concatenated index:

    SQL> explain plan for select ename from big_emp;

    Query Plan
    ——————————————
    SELECT STATEMENT   [CHOOSE] Cost=1
      INDEX FAST FULL SCAN BE_IX [ANALYZED]

    Rowid

    This is the quickest access method available Oracle simply retrieves the block specified and extracts the rows it is interested in. Most frequently seen in explain plans as Table access by Rowid

    SQL> explain plan for select * from dept where rowid = ‘:x’;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID DEPT [ANALYZED]

    Table is accessed by rowid following index lookup:

    SQL> explain plan for
    select empno,ename from emp where empno=10;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
    TABLE ACCESS BY ROWID EMP [ANALYZED]
        INDEX UNIQUE SCAN EMP_I1

    Joins

    A Join is a predicate that attempts to combine 2 row sources We only ever join 2 row sources together Join steps are always performed serially even though underlying row sources may have been accessed in parallel. Join order – order in which joins are performed

    The join order makes a significant difference to the way in which the query is executed. By accessing particular row sources first, certain predicates may be satisfied that are not satisfied by with other join orders. This may prevent certain access paths from being taken.

    Suppose there is a concatenated index on A(a.col1,a.col2). Note that a.col1 is the leading column. Consider the following query:

    select A.col4
    from   A,B,C
    where  B.col3 = 10
    and    A.col1 = B.col1
    and    A.col2 = C.col2
    and    C.col3 = 5

    We could represent the joins present in the query using the following schematic:

      B     <—> A <—>    C
    col3=10                col3=5

    There are really only 2 ways we can drive the query: via B.col3 or C.col3. We would have to do a Full scan of A to be able to drive off it. This is unlikely to be efficient with large tables;

    If we drive off table B, using predicate B.col3=10 (as a filter or lookup key) then we will retrieve the value for B.col1 and join to A.col1. Because we have now filled the leading column of the concatenated index on table A we can use this index to give us values for A.col2 and join to A.

    However if we drive of table c, then we only get a value for a.col2 and since this is a trailing column of a concatenated index and the leading column has not been supplied at this point, we cannot use the index on a to lookup the data.

    So it is likely that the best join order will be B A C. The CBO will obviously use costs to establish whether the individual access paths are a good idea or not.

    If the CBO does not choose this join order then we can hint it by changing the from
    clause to read:

    from B,A,C

    and using the /*+ ordered */ hint. The resultant query would be:

    select /*+ ordered */ A.col4
    from   B,A,C
    where  B.col3 = 10
    and    A.col1 = B.col1
    and    A.col2 = C.col2
    and    C.col3 = 5

    Join Types

    • Sort Merge Join (SMJ)
    • Nested Loops (NL)
    • Hash Join

    Sort Merge Join

    Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently Sorted rows from both sides are then merged together (joined)

                       MERGE
                     /      \
                SORT        SORT
                 |             |
            Row Source 1  Row Source 2

    If the row sources are already (known to be) sorted then the sort operation is unecessary as long as both ‘sides’ are sorted using the same key. Presorted row sources include indexed columns and row sources that have already been sorted in earlier steps. Although the merge of the 2 row sources is handled serially, the row sources could be accessed in parallel.

    SQL> explain plan for
    select /*+ ordered */ e.deptno,d.deptno
    from emp e,dept d
    where e.deptno = d.deptno
    order by e.deptno,d.deptno;

    Query Plan
    ————————————-
    SELECT STATEMENT [CHOOSE] Cost=17
      MERGE JOIN
        SORT JOIN
          TABLE ACCESS FULL EMP [ANALYZED]
        SORT JOIN
          TABLE ACCESS FULL DEPT [ANALYZED]

    Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

    Nested Loops

    First we return all the rows from row source 1 Then we probe row source 2 once for each row returned from row source 1

    Row source 1
    ~~~~~~~~~~~~
    Row 1 ————–       — Probe ->       Row source 2
    Row 2 ————–       — Probe ->       Row source 2
    Row 3 ————–       — Probe ->       Row source 2

    Row source 1 is known as the outer table
    Row source 2 is known as the inner table

    Accessing row source 2 is known a probing the inner table For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

    SQL> explain plan for
    select a.dname,b.sql
    from dept a,emp b
    where a.deptno = b.deptno;

    Query Plan
    ————————-
    SELECT STATEMENT [CHOOSE] Cost=5
      NESTED LOOPS
        TABLE ACCESS FULL DEPT [ANALYZED]
        TABLE ACCESS FULL EMP [ANALYZED]

    Hash Join

    New join type introduced in 7.3 More efficient in theory than NL & SMJ Only accessible via the CBO Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.

    SQL> explain plan for
    select /*+ use_hash(emp) */ empno
    from emp,dept
    where emp.deptno = dept.deptno;

    Query Plan
    —————————-
    SELECT STATEMENT  [CHOOSE] Cost=3
      HASH JOIN
        TABLE ACCESS FULL DEPT
        TABLE ACCESS FULL EMP

    Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3

    Cartesian Product

    A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances – Star joins uses cartesian products.

    Notice that there is no join between the 2 tables:

    SQL> explain plan for
    select emp.deptno,dept,deptno
    from emp,dept

    Query Plan
    ——————————
    SLECT STATEMENT [CHOOSE] Cost=5
      MERGE JOIN CARTESIAN
        TABLE ACCESS FULL DEPT
        SORT JOIN
          TABLE ACCESS FULL EMP

    The CARTESIAN keyword indicate that we are doing a cartesian product.

    Operations

    Operations that show up in explain plans

    • sort
    • filter
    • view

    Sorts

    There are a number of different operations that promote sorts

    • order by clauses
    • group by
    • sort merge join

    Note that if the row source is already appropriately sorted then no sorting is required. This is now indicated in 7.3:

    SORT GROUP BY NOSORT
         INDEX FULL SCAN …..

    In this case the group by operation simply groups the rows it does not do the sort operation as this has already been completed.

    Sorts are expensive operations especially on large tables where the rows do not fit in memory and spill to disk. By default sort blocks are placed into the buffer cache. This may result in aging out of other blocks that may be reread by other processes. To avoid this you can use the parameter <Parameter:SORT_DIRECT_WRITES> which does not place sort blocks into the buffer cache.

    Filter

    Has a number of different meanings used to indicate partition elimination may also indicate an actual filter step where one row source is filtering another functions such as min may introduce filter steps into query plans

    In this example there are 2 filter steps. The first is effectively like a NL except that it stops when it gets something that it doesn’t like (i.e. a bounded NL). This is there because of the not in. The second is filtering out the min value:

    SQL> explain plan for select * from emp
         where empno not in (select min(empno)
         from big_emp group by empno);

    Query Plan
    ——————
    SELECT STATEMENT [CHOOSE]  Cost=1
      FILTER     **** This is like a bounded nested loops
        TABLE ACCESS FULL EMP [ANALYZED]
         FILTER   **** This filter is introduced by the min
            SORT GROUP BY NOSORT
              INDEX FULL SCAN BE_IX

    This example is also interesting in that it has a NOSORT function. The group by does not need to sort because the index row source is already pre sorted.

    Views

    When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the ‘view’ will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.

    In the following example the select contains an inline view which cannot be merged:

    SQL> explain plan for
    select ename,tot
    from emp,
        (select empno,sum(empno) tot from big_emp group by empno) tmp
    where emp.empno = tmp.empno;

    Query Plan
    ————————
    SELECT STATEMENT [CHOOSE]
      HASH JOIN
        TABLE ACCESS FULL EMP [ANALYZED]
        VIEW
          SORT GROUP BY
            INDEX FULL SCAN BE_IX

    In this case the inline view tmp which contains an aggregate function cannot be merged into the main query. The explain plan shows this as a view step.

    Partition Views

    Allows a large table to be broken up into a number of smaller partitions which can be queried much more quickly than the table as a whole a union all view is built over the top to provide the original functionality Check constraints or where clauses provide partition elimination capabilities

    SQL> explain plan for
    select /*+ use_nl(p1,kbwyv1) ordered */  sum(prc_pd)
    from parent1 p1,  kbwyv1
    where p1.class = 22
    and   kbwyv1.bitm_numb = p1.bitm_numb
    and   kbwyv1.year = 1997
    and   kbwyv1.week between 32 and 33 ;

    Query Plan
    —————————————–
    SELECT STATEMENT   [FIRST_ROWS] Cost=1780
      SORT AGGREGATE
        NESTED LOOPS   [:Q65001] Ct=1780 Cd=40 Bt=3120
          TABLE ACCESS FULL PARENT1 [:Q65000] [AN] Ct=20 Cd=40 Bt=1040
          VIEW  KBWYV1 [:Q65001]
            UNION-ALL PARTITION  [:Q65001]
              FILTER   [:Q64000]
                TABLE ACCESS FULL KBWYT1 [AN] Ct=11 Cd=2000 Bt=104000
              TABLE ACCESS FULL KBWYT2 [AN] Ct=11 Cd=2000 Bt=104000
              TABLE ACCESS FULL KBWYT3 [AN] Ct=11 Cd=2000 Bt=104000
              FILTER   [:Q61000]
                TABLE ACCESS FULL KBWYT4 [AN] Ct=11 Cd=2000 Bt=104000

    KBWYV1 is a view on 4 tables KBWYT1-4. KBWYT1-4 contain rows for week 31-34 respectively and are maintained by check constraints. This query should only return rows from partions 2 & 3. The filter operation indicates this. Partitions 1 & 4 are eliminated at execution time. The view line indicates that the view is not merged. The union-all partion information indicates that we have recognised this as a partition view. Note that the tables can be accessed in parallel.

    Remote Queries

    Only shows remote in the OPERATION column OTHER column shows query executed on remote node OTHER_NODE shows where it is executed Different operational characteristics for RBO & CBO

    RBO – Drags everything across the link and joins locally
    CBO – Uses cost estimates to determine whether to execute remotely or locally

    SQL>  explain plan for
    select *
    from dept@loop_link;

    Query Plan
    ——————————————————-
    SELECT STATEMENT REMOTE  [CHOOSE] Cost=1
      TABLE ACCESS FULL DEPT [SJD.WORLD] [ANALYZED]

    In this case the whole query has been sent to the remote site. The other column shows nothing.

    SQL> explain plan for
    select a.dname,avg(b.sal),max(b.sal)
    from dept@loop_link a, emp b
    where a.deptno=b.deptno
    group by a.dname
    order by max(b.sal),avg(b.sal) desc;

    Query Plan
    —————————————————–
    SELECT STATEMENT   [CHOOSE] Cost=20
      SORT ORDER BY  [:Q137003] [PARALLEL_TO_SERIAL]
        SORT GROUP BY  [:Q137002] [PARALLEL_TO_PARALLEL]
          NESTED LOOPS   [:Q137001] [PARALLEL_TO_PARALLEL]
            REMOTE   [:Q137000] [PARALLEL_FROM_SERIAL]
            TABLE ACCESS FULL EMP [:Q137001] [ANALYZED]
            [PARALLEL_COMBINED_WITH_PARENT]

    Bind Variables

    Bind variables are recommended in most cases because they promote sharing of sql code
    At parse time the parser has NO IDEA what the bind variable contains. With RBO this makes no difference but with CBO, which relies on accurate statistics to produce plans, this can be a problem.

    Defining bind variables in sqlplus:

    variable x varchar2(18);
    assigning values:
    begin
    :x := ‘hello’;
    end;
    /

    SQL> explain plan for
    select *
    from dept
    where rowid = ‘:x’;

    Query Plan
    ————————————
    SELECT STATEMENT [CHOOSE] Cost=1
      TABLE ACCESS BY ROWID DEPT [ANALYZED]

    Parallel Query

    Main indicators that a query is using PQO:

    • [:Q1000004] entries in the explain plan
    • Checkout the other column for details of what the slaves are executing
    • v$pq_slave will show any parallel activity

    Columns to look in for information

    • other – contains the query passed to the slaves
    • other_tag – describes the contents of other
    • object_node – indicates order of pqo slaves

    Parallel Query operates on a producer/consumer basis. When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers. If there are only 2 slaves available then we use these. If there is only 1 slave available then we go serial If there are none available then we use serial. If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial

    Consumer processes typically perform a sorting function. If there is no requirement for the data to be sorted then the consumer slaves are not produced and we end up with the number of slaves used matching the degree of parallelism as opposed to being 2x the degree.

    Parallel Terms

    PARALLEL_FROM_SERIAL This means that source of the data is serial but it is passed to a parallel consumer
    PARALLEL_TO_PARALLEL Both the consumer and the producer are  parallel
    PARALLEL_COMBINED_WITH_PARENT This operation has been combined with the parent operator. For example in a sort merge join the sort operations would be shown as PARALLEL_COMBINED_WITH_PARENT because the sort and the merge are handled as 1 operation.
    PARALELL_TO_SERIAL The source of the data is parallel but it is passed to a serial consumer. This typically will happen at the top of the explain plan but could occur anywhere

    Examples of parallel queries

    Assumptions

    OPTIMIZER_MODE = CHOOSE
    DEPT is small compared to EMP
    DEPT has an index (DEPT_INDX) on deptno column

    Three examples are presented

    Query #1:  Serial
    Query #2:  Parallel
    Query #3:  Parallel, with forced optimization to RULE and forced usage of DEPT_INDX

    Sample Query #1 (Serial)

    select A.dname, avg(B.sal), max(B.sal)
    from  dept A, emp B
    where A.deptno = B.deptno
    group by A.dname
    order by max(B.sal), avg(B.sal) desc;

    Execution Plan #1 (Serial)

    OBJECT_NAME                      OBJECT_NODE OTHER
    ——————————-  ———– ——-
    SELECT STATEMENT
     SORT ORDER BY
       SORT GROUP BY
         MERGE JOIN
           SORT JOIN
             TABLE ACCESS FULL emp
           SORT JOIN
             TABLE ACCESS FULL dept

    Notice that the object_node and other columns are empty

    Sample Query #2 (Query #1 with parallel hints)

    select /*+ parallel(B,4) parallel(A,4) */
    A.dname, avg(B.sal), max(B.sal)
    from  dept A, emp B
    where A.deptno = B.deptno
    group by A.dname
    order by max(B.sal), avg(B.sal) desc;

    Execution Plan #2  (Parallel)

    OBJECT_NAME                      OBJECT_NODE OTHER
    ——————————-  ———– ——-
    SELECT STATEMENT      Cost = ??
     SORT ORDER BY                   :Q55004     **[7]**
       SORT GROUP BY                 :Q55003     **[6]**
         MERGE JOIN                  :Q55002     **[5]**
           SORT JOIN                 :Q55002     **[4]**
             TABLE ACCESS FULL emp   :Q55001     **[2]**
           SORT JOIN                 :Q55002     **[3]**
             TABLE ACCESS FULL dept  :Q55000     **[1]**

    Execution Plan #2  — OTHER column

    **[1]**  (:Q55000) “PARALLEL_FROM_SERIAL”

    Serial execution of SELECT DEPTNO, DNAME FROM DEPT

    **[2]**  (:Q55001) “PARALLEL_TO_PARALLEL”

            SELECT /*+ ROWID(A1)*/
            A1.”DEPTNO” C0, A1.”SAL” C1
            FROM “EMP” A1
            WHERE ROWID BETWEEN :1 AND :2

    **[3]**  (:Q55002) “PARALLEL_COMBINED_WITH_PARENT”
    **[4]**  (:Q55002) “PARALLEL_COMBINED_WITH_PARENT”
    **[5]**  (:Q55002) “PARALLEL_TO_PARALLEL”

            SELECT /*+ ORDERED USE_MERGE(A2)*/
            A2.C1 C0, A1.C1 C1
            FROM :Q55001 A1,:Q55000 A2
            WHERE A1.C0=A2.C0

    **[6]**  (:Q55003) “PARALLEL_TO_PARALLEL”

            SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2
            FROM :Q55002 A1
            GROUP BY A1.C0

    **[7]**  (:Q55004) “PARALLEL_FROM_SERIAL”

            SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2
            FROM :Q55003 A1
            ORDER BY A1.CO, A1.C1 DESC

    Sample Query #3 (Query #2 with fudged hints)

    select /*+ index(A dept_indx) parallel(B,4) parallel(A,4) */
          A.dname, avg(B.sal), max(B.sal)
    from  dept A, emp B
    where A.deptno = B.deptno
    group by A.dname
    order by max(B.sal), avg(B.sal) desc;

    Execution Plan #3  (Parallel)

    OBJECT_NAME                         OBJECT_NODE OTHER
    ———————————– ———– ——-
    SELECT STATEMENT          Cost = ??
     SORT ORDER BY                      :Q58002     **[6]**
       SORT GROUP BY                    :Q58001     **[5]**
         NESTED LOOPS JOIN              :Q58000     **[4]**
           TABLE ACCESS FULL emp        :Q58000     **[3]**
           TABLE ACCESS BY ROWID dept   :Q58000     **[2]**
             INDEX RANGE SCAN dept_indx :Q58000     **[1]**

    Execution Plan #3  — OTHER column

    **[1]**  (:Q58000) “PARALLEL_COMBINED_WITH_PARENT”
    **[2]**  (:Q58000) “PARALLEL_COMBINED_WITH_PARENT”
    **[3]**  (:Q58000) “PARALLEL_COMBINED_WITH_PARENT”
    **[4]**  (:Q58000) “PARALLEL_TO_PARALLEL”

            SELECT /*+ ORDERED USE_NL(A2) INDEX(A2) */
            A2.”DNAME” C0, A1.C0 C1
            FROM
              (SELECT /*+ ROWID(A3) */
               A3.”SAL” CO, A3.”DEPTNO” C1
               FROM “EMP” A3
               WHERE ROWID BETWEEN :1 AND :2) A1,
              “DEPT” A2
            WHERE A2.”DEPTNO” = A1.C1

    **[5]**  (:Q58001) “PARALLEL_TO_PARALLEL”

            SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2
            FROM :Q58000 A1
            GROUP BY A1.C0

    **[6]**  (:Q58002) “PARALLEL_TO_SERIAL”

            SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2
            FROM :Q58001 A1
            ORDER BY A1.C0, A1.C1 DESC

    How to obtain explain plans

    Explain plan for

    Main advantage is that it does not actually run the query – just parses the sql. This means that it executes quickly. In the early stages of tuning explain plan gives you an idea of the potential performance of your query without actually running it. You can then make a judgement as to any modifications you may choose to make.

    Autotrace

    Autotrace can be configured to run the sql & gives a plan  and statistics afterwards or just give you an explain plan without executing the query.

    Tkprof

    Analyzes trace file

    ting the query.

    Tkprof

     

    This article is from ; http://www.akadia.com/services/ora_interpreting_explain_plan.html

    Analyzes trace file


    Oracle Explain Plan

    September 25, 2009

     

    Oracle Explain Plan Terms:

     

    Table Access Full: Oracle reads every row in selected table. In terms of that there is no primary key or index defined with the columns that you use at your where clause Oracle makes full table access.

    OPERATION                      OPTIONS         OBJECT_NAME

    —————————— ————— ——————–

    SELECT STATEMENT

    TABLE ACCESS                   FULL            Students

    Selecting With Primary Keys:  Primary key defines a row uniquely. By using table’s primary key at where clause Oracle makes Index RowID Unique Scan   in order to select unique row which reduces our total cost so much.

    OPERATION                      OPTIONS         OBJECT_NAME
    ------------------------------ --------------- --------------------
    SELECT STATEMENT
    TABLE ACCESS                   BY INDEX ROWID  Students
    INDEX                          UNIQUE SCAN     Students _PK

     

    Selecting With Ununique Indexes: Ununique indexes reduces the cost of full table scan. But, of course if we have change to use primary keys we should use them. If we can’t we can use other non-unique indexes. The reducement of cost varies due to index’s definition. If the uniqueness of our index columns raises retrieval time will go down. Because of this reason, use the criteria that will catch the fastest index for that query.

    OPERATION                      OPTIONS         OBJECT_NAME

    —————————— ————— ——————–

    SELECT STATEMENT

    TABLE ACCESS                   BY INDEX ROWID  Students

    INDEX                          RANGE SCAN      Students_IDX1

     

    Efficiency of Join Statements:  We should be very careful at writing join statements. The column you choose the join plays very important role here. The most important thing we have to consider at creating joins is using indexed columns at where clause. Let’s assume that there is a table “StudentMarks” keeping marks of students for some particular lesson.For example phisics. The statement following retrievs  all the marks of the students having the name “ENGİN”. StudentMarks table does not have any foreign key at student column. İts only index column is mark.

    select s.name,s.surname,m.mark
      from StudentMarks m, Students s
     where s.name = ‘ENGİN’

       and s.id = m.studentid

       and m.mark between 10000 and 30000

     

    OPERATION                      OPTIONS         OBJECT_NAME

    —————————— ————— ——————–

    SELECT STATEMENT

    NESTED LOOPS

    TABLE ACCESS                   BY INDEX ROWID  Students

    INDEX                          RANGE SCAN      Student_IDX2

    TABLE ACCESS                   BY INDEX ROWID  StudentMarks

    INDEX                          RANGE SCAN      StudentMarks_IDX3

     

    All the columns are catched by range scan.

     

    Choosing The Fastest Index: Indexes varies by the mean of cost. To query with minimum cost, we have to keep in mind the table’s count of rows. We should always give our priority to most sized table by the mean of choosing where clauses. Let’s take an example to examine this furter; 

     

    select s.name,s.surname,m.mark
      from StudentMarks m, Students s
     where s.name = ‘ENGİN’

       and s.id = m.studentid

       and m.mark between 10000 and 30000

     

     

     

     

    Will produce this plan:

    OPERATION                      OPTIONS         OBJECT_NAME

    —————————— ————— ——————–

    SELECT STATEMENT

    NESTED LOOPS

    TABLE ACCESS                   BY INDEX ROWID  Students

    INDEX                          RANGE SCAN      Students_IDX2

    TABLE ACCESS                   BY INDEX ROWID  StudentMarks

    INDEX                          RANGE SCAN      StudentMarks _IDX3

    Oracle, tries to determine whether students or studentsmarks table is the best to index first. At the term oracle can not decide, Oracle starts indexing at the order of right to left at from clause. Be careful about this query. It is the same with the one above. But, at this query Oracle starts indexing with StudentMarks table.

     select s.name,s.surname,m.mark
      from Students s, StudentMarks m
     where s.name = ‘ENGİN’

       and s.id = m.studentid

       and m.mark between 10000 and 30000

     

    This query produces this plan:

    OPERATION                      OPTIONS         OBJECT_NAME

    —————————— ————— ——————–

    SELECT STATEMENT

    NESTED LOOPS

    TABLE ACCESS                   BY INDEX ROWID  StudentMarks

    INDEX                          RANGE SCAN      StudentMarks _IDX3

    TABLE ACCESS                   BY INDEX ROWID  Students

    INDEX                          RANGE SCAN      Students_IDX2

     

    StudentMarks.mark is queried first because we told Oracle to do so.

     

     

     

    Utilizing Multiple Column Indexes

    Utilizing Multiple Column Indexes: Multiple Column indexes uses more than one column for indexing. In order to use that index we have to search for the first column before we search second column in the index. To get more efficient queries from that indexes we have to choose index criteria more uniquely. Unique columns are also indexes. It is the best way to use unique indexes to get one specific row.

    Oracle Explan Plan Terms

    Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle’s task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.

    If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.

    The general syntax of EXPLAIN PLAN is:

    explain plan for your-precious-sql-statement;

     

    If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:

    explain plan into table_name for your-precious-sql-statement;

    If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

     

    Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle’s task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.

    If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.

    The general syntax of EXPLAIN PLAN is:

    explain plan for your-precious-sql-statement;

    If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:

    explain plan into table_name for your-precious-sql-statement;

    If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

    The Plan Table

    The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN). If you like, however, you can choose any other name for the plan table, as long as you have been granted insert on it and it has all the fields as here.

    The fields (attributes) within the plan table

    Arguably, the most important fields within the plan table are operation, option, object_name, id, and parent_id. The pair operation and object_name define what operation would be done on (or with) object_name. If an operation has an id which other operations have as parent_id, it means the other operations feed their result to the parent.

     

    Possible values for operation are:

    • DELETE STATEMENT
    • INSERT STATEMENT
    • SELECT STATEMENT
    • UPDATE STATEMENT
    • AND-EQUAL
    • CONNECT BY
    • CONCATENATION
    • COUNT
    • DOMAIN INDEX
    • FILTER
    • FIRST ROW
    • FOR UPDATE
    • HASH JOIN
    • INDEX
    • INLIST ITERATOR
    • INTERSECTION
    • MERGE JOIN
    • MINUS
    • NESTED LOOPS
    • PARTITION,
    • REMOTE
    • SEQUENCE
    • SORT
    • TABLE ACCESS
    • UNION
    • VIEW

     

     

    Option tells more about how an operation would be done. For example, the operation TABLE ACCESS can have the options: FULL or BY ROWID or many others. Full in this case means, that the entire table is accessed (takes a long time if table is huge) whereas BY ROWID means, Oracle knows where (from which block) the rows are to be retrieved, which makes the time to access the table shorter.

    dbms_xplan

    As of 9i, dbms_xplan can be used to format the plan table.

    Operations

    The following table is used to demonstrate EXPLAIN PLAN:

    create table test_for_ep (a number, b varchar2(100));

    Now, let’s explain the plan for selecting everything on that table:

    delete plan_table;

    explain plan for select /*+ rule */ * from test_for_ep where a = 5;

     

    Displaying the execution plan

    In order to view the explained plan, we have to query the plan table:

    select
      substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
      object_name                                                              "Object"
    from
      plan_table
    start with id = 0
    connect by prior;

    This statement is a simplified version of utlxpls.sql. utlxpls.sql is a script that Oracle ships.

    Here’s the output of the explain plan:

    SELECT STATEMENT ()
     TABLE ACCESS (FULL)           TEST_FOR_EP

    First, take a look at the indention: TABLE ACCESS is indented right. In an explain plan output, the more indented an operation is, the earlier it is executed. And the result of this operation (or operations, if more than one have are equally indented AND have the same parent) is then feeded to the parent operation. In this case, TABLE ACCESS is made first, and its result feeded to SELECT STATEMENT (which is not an actual operation). Note the FULL in paranthesis in TABLE ACCESS: this means that the entire table is accessed.

    Btw, sql*plus automatically explains the plan for you if autotrace is enabled.

    Now, let’s create an index on that table:

    create index test_for_ep_ix on test_for_ep (a);

    And do the same select statement again:

    delete plan_table;
    explain plan for select /*+ rule */ * from test_for_ep where a = 5;

    The plan is now:

    SELECT STATEMENT ()
     TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
      INDEX (RANGE SCAN)           TEST_FOR_EP_IX

    Obviously, the index (TEST_FOR_EP_IX) is used first (most indented) then used for a TABLE ACCESS, second most indented, then the result is returned. The table access is not done by a full table scan but rather by using the data’s rowid.

    INDEX

    In the last example, Oracle employed an INDEX (RANGE SCAN). The RANGE SCAN basically means, that the index was used, but that it can return more than one row. Now, we create a unique index to see how this alters the explain plan:

    create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));
     
    delete plan_table;
    explain plan for select /*+ rule */ * from test_for_ep where a = 5;

    The explained plan is:

    SELECT STATEMENT ()
     TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
      INDEX (UNIQUE SCAN)          UQ_TP

    INDEX (UNIQUE SCAN) means, that this index is used, and it sort of guarantees that this index returnes exactly one rowid. What happens, if we query the field not for equality but for greater than (a>5)?

    explain plan for select /*+ rule */ * from test_for_ep where a > 5;

    Here, we see that the index is used, but for a RANGE SCAN:

    SELECT STATEMENT ()
     TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
      INDEX (RANGE SCAN)           UQ_TP

    If we only query fields of a table that are already in an index, Oracle doesn’t have to read the data blocks because it can get the relevant data from the index:

    create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));
     
    delete plan_table;
    explain plan for select /*+ rule */ a from test_for_ep where a > 5 and a < 50;

    Here’s the query execution planexecution plan. No table access anymore!

    SELECT STATEMENT ()
     INDEX (RANGE SCAN)            UQ_TP

    MERGE JOIN

    See here. The first table’s join key is ba while the second table’s join key is aa.

    create table test_for_ep_a (aa number, ab varchar2(100));
    create table test_for_ep_b (ba number, bb varchar2(100));

    Note, there are no indexes on both of the tables. Now, we join the tables on aa and ba:

    explain plan for
      select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where
      a.aa=b.ba and a.aa > 5;

    As there are no indexes, both tables must be TABLE ACCESSed (FULL). After these accesses, their results are sorted.

    SELECT STATEMENT ()
     MERGE JOIN ()
      SORT (JOIN)
       TABLE ACCESS (FULL)         TEST_FOR_EP_B
      SORT (JOIN)
       TABLE ACCESS (FULL)         TEST_FOR_EP_A

    Note MERGE JOINs can only be used for equi joins, as is demonstrated in NESTED LOOPS

     

    NESTED LOOPS

    For each relevant row in the first table (driving table), find all matching rows in the other table (probed table).

    See also here.

    create table test_for_ep_a (aa number, ab varchar2(100));
    create table test_for_ep_b (ba number, bb varchar2(100));
    explain plan for
      select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where
      a.aa > b.ba and a.aa > 5;

    Note, there is no equi join to join test_for_ep_a and test_for_ep_b, (a.aa > b.ba)

    SELECT STATEMENT ()
     NESTED LOOPS ()
      TABLE ACCESS (FULL)          TEST_FOR_EP_B
      TABLE ACCESS (FULL)          TEST_FOR_EP_A

    Now, we put an index on TEST_FOR_EP_B and see how that influences our nested loop:

    create table test_for_ep_a (aa number, ab varchar2(100));
    create table test_for_ep_b (ba number, bb varchar2(100), constraint uq_ba unique(ba));
     
    delete plan_table;
    explain plan for
      select /*+ rule */ a.aa from test_for_ep_a a, test_for_ep_b b where
      a.aa > b.ba;

    The plan is:

    SELECT STATEMENT ()
     NESTED LOOPS ()
      TABLE ACCESS (FULL)          TEST_FOR_EP_A
      INDEX (RANGE SCAN)           UQ_BA

    Interpreted, this means: TEST_FOR_EP_A is fully accessed and for each row, TEST_FOR_EP_B (or more accurately, its index UQ_BA) is probed. Thinking about it, this makes sense, doing the costly TABLE ACCESS once and use the index for each row. Then again, thinking about it, if TEST_FOR_EP_A is very small nad TEST_FOR_EP_B is large, this doesn’t make sense anymore. This is when the Cost Based Optimizer comes into play.

    Sorts

    Aggregate Sorts

    Whenever a result set must be sorted, the operation is sort. If this sort is used to return a single row (for example max or min) the options is AGGREGATE. Consider the following example:

    create table t_ep (
      w date,
      v number,
      x varchar2(40)
    );
     
     
    delete plan_table;
    explain plan for select /*+ rule */ max(w) from t_ep where v=4;
    SELECT STATEMENT ()
     SORT (AGGREGATE)
      TABLE ACCESS (FULL)          T_EP

    Now: creating an index:

    alter table t_ep add constraint uq_t_ep unique(v);
    delete plan_table;
    explain plan for select /*+ rule */ max(w) from t_ep where v=4;
    SELECT STATEMENT ()
     SORT (AGGREGATE)
      TABLE ACCESS (BY INDEX ROWID) T_EP
       INDEX (UNIQUE SCAN)          UQ_T_EP

    TKPROF

    If you want to know, how much time an SQL statement acutally used, use TKPROF

     

     

    ReferencesTop of Form

     

    -          http://www.evolt.org/node/2986

    Adam Patrick, Use Oracle’s Explain Plan to Tune Your Queries, August 24, 2000

    -          http://www.adp-gmbh.ch/ora/explainplan.html

     


    Follow

    Get every new post delivered to your Inbox.