Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 012EA100337A0F for ; Thu, 26 Aug 2021 14:54:54 +0200 (CEST) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id D7856424D2; Thu, 26 Aug 2021 12:54:52 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id CAA7F41481; Thu, 26 Aug 2021 12:54:52 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1629982492; bh=Kj0mDYMxOUzbNGYF+CGRPtt4tg6DdbIIQqWKSziuHRk=; h=From:Sender:Sender:From; b=g/5JCQIjOPU7ObbQJ5iSqCbDCzMgqAN/C5xMc+TX1amfOan3clkFNa8i2j6sny9qX tf+5ewo0O2tJ8Qw+Qhk7TleYLTUxnMeaKK5Et/0kKrvgCUpoEp4TLU/sIrPoAw9CvA WibWs1KQvubldrDRGu7PUlLiqv1WH3gB+uFXhWLg= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id t2SfJKF0qOfi; Thu, 26 Aug 2021 12:54:52 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 0B89441718; Thu, 26 Aug 2021 12:54:50 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1629982491; bh=Kj0mDYMxOUzbNGYF+CGRPtt4tg6DdbIIQqWKSziuHRk=; h=From:Sender:Sender:From; b=LM9gS4YB7/28O96WKwNjR0dG2AgydbwDOH8NzGHnAmdsjtqhbhvsRs5DazL5Usj+F q3SnPO8uuKEEpl5nKuFHZvnsT8Rl/wbMcXPPEolcVTtZkuIeIxhRuvLkKrYhrBJplV ccu48ncte6TPx5NDc/eWzgNwa+r5n/R3dWGqjBuI= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 26 Aug 2021 12:54:48 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 64B6E41481 for ; Thu, 26 Aug 2021 12:54:48 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=OfbePozk; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id s4f31ROd9Mab for ; Thu, 26 Aug 2021 12:54:48 +0000 (UTC) Received: from mail-yb1-f176.google.com (mail-yb1-f176.google.com [209.85.219.176]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 4E18F4147F for ; Thu, 26 Aug 2021 12:54:48 +0000 (UTC) Received: by mail-yb1-f176.google.com with SMTP id n126so5682925ybf.6 for ; Thu, 26 Aug 2021 05:54:48 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=e1VAEzyfbKGLMaJGilUjDsdXRlnCrigLaZNkYlfZFp8=; b=uirAkKDf6IX8D6gYylpPjZVE60mwfc8BvgUK8sTdaxCAT9mXcG+9polga/kzWDI5ga ffSZwL6hiBECEwZBidYDKhOvR0/McRVqlyBa7BYEsCq9oAhZgrkjxk8julqpTtByWCp5 Q5cysiuSqJNH6oh86gaUAlEsTSjpLCMosXVZtfi/y+ZrdUyFcgNiW10rkYLk673ETOQ8 XdkzzkLQuhl2yLsd1f+cCx6+OSO31vMF7III5qL2Y6YyybwC9BRXAFyVfSVIYWNsLV55 5Z6YMHPWONGuXqT7PoMGGpjce8RHm27TE+a3IE9nBTPSO8vvMX+Y/Go/BUeN76+bF9Mr B/Lw== X-Gm-Message-State: AOAM5323JZc9eqpUwcYKkPA9P7NP0pdIDIcDEeTA18MVf2sEJjCXLyD+ 8/VWH7bg4/sgcgIzytItQBhCWLzE0a/5wyvg+mnIzDwxLAs= X-Google-Smtp-Source: ABdhPJwUQC3qi5AXB/FowxVqh64pKEhf12jyv6TkB1T3VYMNURb6sYaBjyS3gXxqbqFtBWjFer9DoyP9zgd3k4MIQfs= X-Received: by 2002:a5b:2cf:: with SMTP id h15mr5058557ybp.426.1629982487758; Thu, 26 Aug 2021 05:54:47 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Lok P Date: Thu, 26 Aug 2021 18:24:35 +0530 Message-ID: Subject: Re: Fixing Performance issue with less selective columns To: Laurentiu Oprea Cc: Oracle L Content-Type: multipart/alternative; boundary="0000000000002c498c05ca75df36" X-archive-position: 80820 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: loknath.73@gmail.com Precedence: normal Reply-To: loknath.73@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --0000000000002c498c05ca75df36 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Yes Laurentiu, As i posted just now it does show ~99.99% cell offload in the sql monitoring report. But I am not able to interpret other v$sessstat stats if they are pointing to something like putting this base table fully in flash cache i.e. making cell_flash_cache as KEEP will help us or not? Partitioning on wof_date is something we need to look into the usage in other queries, as we will also have to go for making indexes local for smooth maintenance and in that case , the all index segment will also be divided into multiple pieces. So not sure how they will impact other queries where we scan across multiple wof_date data. On Thu, Aug 26, 2021 at 1:13 PM Laurentiu Oprea wrote: > Can you check in an active sql monitor report the details of the > offloading? Are there any storage indexes used? > > Maybe if you give more priority to flash cache to this table might improv= e > the response time: > ALTER TABLE PP_IN_TAB STORAGE (CELL_FLASH_CACHE KEEP); > > Or maybe you can partition the table after: WOF_DATE and have a separate > partition for null values, this will partition prune and will be faster > than an index I think (and you avoid any negative impact of > additional index) > > =C3=8En mie., 25 aug. 2021 la 16:15, Lok P a scris= : > >> Hello , This database has version 11.2.0.4 of Oracle. We have the below >> query which is executed thousands of times. It's used in a plsql functio= n >> which in turn gets called from a procedure. And this procedure gets call= ed >> from java thousands of times. And I see from dba_hist_sqlstat , for mos= t >> of the runs this below query results in zero rows. We see from the activ= e >> session history for the overall process this query is consuming most >> time/resources and making the process run longer. So wanted to understan= d >> if we can make this individual query execution faster which would >> eventually make the process faster? >> >> The base table- PP_IN_TAB is holding ~111million rows and is ~43GB in >> size. Column PP_ID is the primary key here. The filter predicates used i= n >> this query are below. Many of them were not very selective in nature. So= I >> am not able to conclude if any composite index is going to help us here. >> Can you please guide me , what is the correct approach to tune this proc= ess >> in such a scenario? >> >> Below is the column data pattern used as filter predicate in this query. >> Most of these are less selective in nature. >> >> TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS >> PP_IN_TAB EF_ID 39515 6151686 >> PP_IN_TAB PE 103074806 647050 >> PP_IN_TAB PT_Code 24 0 >> PP_IN_TAB PT_MCODE 20 0 >> PP_IN_TAB D_CUR_CODE 13 592784 >> PP_IN_TAB ED_AMT 320892 6 >> PP_IN_TAB WOF_DATE 2572 83154 >> PP_IN_TAB PR_CTGRY 2 86 >> PP_IN_TAB PDE_RSN_CAT 6 0 >> PP_IN_TAB MA_FLG 2 648172 >> PP_IN_TAB M_TXT 29460248 9118572 >> PP_IN_TAB D_UNMTCH 1 111766716 >> >> >> SELECT NVL (I.PP_ID, 0) >> FROM PP_IN_TAB I >> WHERE TRIM(I.M_TXT) =3D TRIM (SUBSTR ( :B8, 0.50)) AND I.PT_C= ode >> =3D :B7 >> AND NVL ( :B6, I.PT_MCODE) =3D NVL ( :B6, :B5) AND I.DC_COD= E =3D >> :B4 >> AND I.D_CUR_CODE =3D :B3 AND I.ED_AMT =3D :B2 >> AND I.PR_CTGRY =3D :B1 AND I.PE IS NOT NULL >> AND I.EF_ID IS NULL AND I.WOF_DATE IS NULL >> AND NVL (I.MA_FLG, 'N') <> 'Y' AND NVL (I.D_UNMTCH, 'N') <> >> 'Y' >> AND ROWNUM =3D 1; >> >> >> Global Information >> ------------------------------ >> Status : DONE (ALL ROWS) >> Instance ID : 1 >> SQL Execution ID : 16777216 >> Execution Started : 08/25/2021 03:53:25 >> First Refresh Time : 08/25/2021 03:53:25 >> Last Refresh Time : 08/25/2021 03:53:28 >> Duration : 3s >> Module/Action : SQL*Plus/- >> Program : sqlplus.exe >> Fetch Calls : 1 >> >> Global Stats >> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >> | Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | >> Read | Cell | >> | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | >> Bytes | Offload | >> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >> | 3.30 | 1.15 | 2.15 | 0.00 | 1 | 6M | 44379 | >> 43GB | 99.99% | >> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >> >> SQL Plan Monitoring Details (Plan Hash Value=3D1096440065) >> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >> | Id | Operation | Name | Rows | Cost = | >> Time | Start | Execs | Rows | Read | Read | Cell | Mem = | >> Activity | Activity Detail | >> | | | | (Estim) | = | >> Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) = | >> (%) | (# samples) | >> >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >> | 0 | SELECT STATEMENT | | | = | >> | | 1 | | | | | = | >> | | >> | 1 | COUNT STOPKEY | | | = | >> | | 1 | | | | | = | >> | | >> | 2 | TABLE ACCESS STORAGE FULL | PP_IN_TAB | 1 | 128K = | >> 3 | +2 | 1 | 0 | 44379 | 43GB | 99.99% | 6M = | >> 100.00 | cell smart table scan (3) | >> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >> >> >> Predicate Information (identified by operation id): >> --------------------------------------------------- >> >> 1 - filter(ROWNUM=3D1) >> 2 - storage("I"."WOF_DATE" IS NULL AND "I"."EF_ID" IS NULL AND >> "I"."PT_Code"=3D:B7 AND "I"."D_CUR_CODE"=3D:B3 AND "I"."PR_CTGRY"=3D:B1 = AND >> "I"."DC_CODE"=3D:B4 AND "I"."ED_AMT"=3DTO_NUMBER(:B2) AND >> NVL(:B6,"I"."PT_MCODE")=3DNVL(:B6,:B5) AND >> TRIM("I"."M_TXT")=3DTRIM(SUBSTR(:B8,0.50)) >> AND "I"."PE" IS NOT NULL AND NVL("I"."MA_FLG",'N')<>'Y' A= ND >> NVL("I"."D_UNMTCH",'N')<>'Y') >> filter("I"."WOF_DATE" IS NULL AND "I"."EF_ID" IS NULL AND >> "I"."PT_Code"=3D:B7 AND "I"."D_CUR_CODE"=3D:B3 AND "I"."PR_CTGRY"=3D:B1 = AND >> "I"."DC_CODE"=3D:B4 AND "I"."ED_AMT"=3DTO_NUMBER(:B2) AND >> NVL(:B6,"I"."PT_MCODE")=3DNVL(:B6,:B5) AND >> TRIM("I"."M_TXT")=3DTRIM(SUBSTR(:B8,0.50)) >> AND "I"."PE" IS NOT NULL AND NVL("I"."MA_FLG",'N')<>'Y' >> AND NVL("I"."D_UNMTCH",'N')<>'Y') >> > --0000000000002c498c05ca75df36 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes Laurentiu, As i posted just now it does show ~99.99% c= ell offload in the sql monitoring report. But I am not able to interpret ot= her=C2=A0v$sessstat stats if they are pointing to something like putting th= is base table fully in flash cache i.e. making cell_flash_cache as KEEP wil= l help us or not?

Partitioning on wof_date is something = we need to look into the usage in other queries, as we will also have to go= for making indexes local for smooth maintenance=C2=A0and in that case , th= e all index segment will also be divided=C2=A0into multiple=C2=A0pieces. So= not sure how they will impact other queries where we scan across multiple = wof_date data.

On Thu, Aug 26, 2021 at 1:13 PM Laurentiu Oprea <laurentiu.oprea06@gmail.com= > wrote:
Can you check in an active sql monitor report the details of t= he offloading? Are there any storage indexes used?

Maybe= if you give more priority to flash cache to this table might improve the r= esponse time:=C2=A0
ALTER TABLE=C2=A0 PP_IN_TAB=C2=A0=C2=A0 STORAGE (CELL_FLASH_CACHE KEEP);

=
Or maybe you can partition=C2=A0the table after:=C2=A0WOF_DATE and have a separate partition for null values, this will= partition=C2=A0prune and will be faster than an index I think (and you avo= id any negative impact of additional=C2=A0index)=C2=A0
=C3=8En = mie., 25 aug. 2021 la 16:15, Lok P <loknath.73@gmail.com> a scris:
Hello , This d= atabase has version 11.2.0.4 of Oracle. We have the below query which is ex= ecuted thousands of times. It's used in a plsql function which in turn = gets called from a procedure. And this procedure gets called from java thou= sands of times.=C2=A0 And I see=C2=A0from dba_hist_sqlstat , for most of th= e runs this below query results in zero rows. We see from the active sessio= n history for the overall process this query is consuming most time/resourc= es and making the process run longer. So wanted to understand if we can mak= e this individual query execution faster which would=C2=A0 eventually make = the process faster?

The base table- PP_IN_TAB is holding ~111million= rows and is ~43GB in size. Column PP_ID is the primary key here. The filte= r predicates used in this query are below. Many of them were not very selec= tive in nature. So I am not able to conclude if any composite index is goin= g to help us here. Can you please guide me , what is the correct approach t= o tune this process in such a scenario?

Below is the column data pat= tern used as filter predicate in this query. Most of these are less selecti= ve in nature.

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS
PP_IN_TAB EF_ID 39515 6151686
PP_IN_TAB PE 103074806 647050
PP_IN_TAB PT_Code 24 0
PP_IN_TAB PT_MCODE 20 0
PP_IN_TAB D_CUR_CODE 13 592784
PP_IN_TAB ED_AMT 320892 6
PP_IN_TAB WOF_DATE 2572 83154
PP_IN_TAB PR_CTGRY 2 86
PP_IN_TAB PDE_RSN_CAT 6 0
PP_IN_TAB MA_FLG 2 648172
PP_IN_TAB M_TXT 29460248 9118572
PP_IN_TAB D_UNMTCH 1 111766716


SELECT =C2=A0 NVL (I.PP_= ID, 0)
=C2=A0 FROM PP_IN_TAB I
=C2=A0WHERE =C2=A0 =C2=A0 TRIM(I.M_TXT= ) =3D TRIM (SUBSTR ( :B8, 0.50)) =C2=A0 =C2=A0 =C2=A0 AND I.PT_Code =3D :B7=
=C2=A0 =C2=A0 =C2=A0 =C2=A0AND NVL ( :B6, I.PT_MCODE) =3D NVL ( :B6, :B= 5) =C2=A0 =C2=A0 =C2=A0 AND I.DC_CODE =3D :B4
=C2=A0 =C2=A0 =C2=A0 =C2= =A0AND I.D_CUR_CODE =3D :B3 =C2=A0 =C2=A0 =C2=A0 AND I.ED_AMT =3D :B2
= =C2=A0 =C2=A0 =C2=A0 =C2=A0AND I.PR_CTGRY =3D :B1 =C2=A0 =C2=A0 =C2=A0 AND = I.PE IS NOT NULL
=C2=A0 = =C2=A0 =C2=A0 =C2=A0AND I.EF_ID IS NULL =C2=A0 =C2=A0 =C2=A0 AND I.WOF_DATE= IS NULL
=C2=A0 =C2=A0 =C2=A0 =C2=A0AND NVL (I.MA_FLG, 'N') <= > 'Y' =C2=A0 =C2=A0 =C2=A0 AND NVL (I.D_UNMTCH, 'N') <= ;> 'Y'
=C2=A0 =C2=A0 =C2=A0 =C2=A0AND ROWNUM =3D 1;
=C2= =A0

Global Information
----------------= --------------
=C2=A0Status =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0: =C2=A0DONE (ALL ROWS) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0= Instance ID =C2=A0 =C2=A0 =C2=A0 =C2=A0 : =C2=A01 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0SQL E= xecution ID =C2=A0 =C2=A0: =C2=A016777216 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0Execution Started =C2=A0 : =C2=A00= 8/25/2021 03:53:25 =C2=A0 =C2=A0 =C2=A0
=C2=A0First Refresh Time =C2=A0= : =C2=A008/25/2021 03:53:25 =C2=A0 =C2=A0 =C2=A0
=C2=A0Last Refresh Tim= e =C2=A0 : =C2=A008/25/2021 03:53:28 =C2=A0 =C2=A0 =C2=A0
=C2=A0Duratio= n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0: =C2=A03s =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0Mod= ule/Action =C2=A0 =C2=A0 =C2=A0 : =C2=A0SQL*Plus/- =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0Program =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 : =C2=A0sqlplus.exe =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0
=C2=A0Fetch Calls =C2=A0 =C2=A0 =C2=A0 =C2=A0 : =C2=A01 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0

Global Stats
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D
| Elapsed | =C2=A0 Cpu =C2=A0 | =C2=A0 =C2=A0IO =C2=A0 =C2=A0| Appli= cation | Fetch | Buffer | Read =C2=A0| Read =C2=A0| =C2=A0Cell =C2=A0 |
= | Time(s) | Time(s) | Waits(s) | =C2=A0Waits(s) =C2=A0 | Calls | =C2=A0Gets= =C2=A0| Reqs =C2=A0| Bytes | Offload |
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D
| =C2=A0 =C2=A03.30 | =C2=A0 =C2=A01.15 | =C2=A0 =C2=A0 2.1= 5 | =C2=A0 =C2=A0 =C2=A0 =C2=A00.00 | =C2=A0 =C2=A0 1 | =C2=A0 =C2=A0 6M | = 44379 | =C2=A043GB | =C2=A099.99% |
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D

SQL Plan Monitoring Details (Plan Hash Value=3D1096440065)=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
| Id | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Operation =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 N= ame =C2=A0 =C2=A0 =C2=A0 | =C2=A0Rows =C2=A0 | Cost | =C2=A0 Time =C2=A0 = =C2=A0| Start =C2=A0| Execs | =C2=A0 Rows =C2=A0 | Read =C2=A0| Read =C2=A0= | =C2=A0Cell =C2=A0 | =C2=A0Mem =C2=A0| Activity | =C2=A0 =C2=A0 =C2=A0Acti= vity Detail =C2=A0 =C2=A0 =C2=A0|
| =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|= (Estim) | =C2=A0 =C2=A0 =C2=A0| Active(s) | Active | =C2=A0 =C2=A0 =C2=A0 = | (Actual) | Reqs =C2=A0| Bytes | Offload | (Max) | =C2=A0 (%) =C2=A0 =C2= =A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0(# samples) =C2=A0 =C2=A0 =C2=A0 =C2=A0|=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
| =C2=A00 | SELECT STATEMENT =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 = =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0 1 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 = | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0= | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
| =C2=A01 | = =C2=A0 COUNT STOPKEY =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 1 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 |
| =C2=A02 | =C2=A0 =C2=A0TABLE ACCESS STORAGE FULL | PP_IN_= TAB =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 1 | 128K | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 3 | =C2=A0 =C2=A0 +2 | =C2=A0 =C2=A0 1 | =C2=A0 =C2=A0 = =C2=A0 =C2=A00 | 44379 | =C2=A043GB | =C2=A099.99% | =C2=A0 =C2=A06M | =C2= =A0 100.00 | cell smart table scan (3) |
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D =C2=A0

Predicate Information (identified by operation id):
= ---------------------------------------------------

=C2=A0 =C2=A01 -= filter(ROWNUM=3D1)
=C2=A0 =C2=A02 - storage("I"."WOF_DAT= E" IS NULL AND "I"."EF_ID" IS NULL AND "I&quo= t;."PT_Code"=3D:B7 AND "I"."D_CUR_CODE"=3D:B3= AND "I"."PR_CTGRY"=3D:B1 AND
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 "I"."DC_CODE"=3D:B4 AND &qu= ot;I"."ED_AMT"=3DTO_NUMBER(:B2) AND
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 NVL(:B6,"I"."PT_MCODE")=3DN= VL(:B6,:B5) AND TRIM("I"."M_TXT")=3DTRIM(SUBSTR(:B8,0.5= 0))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND "I&q= uot;."PE" IS NOT NULL AND NVL("I"."MA_FLG",&#= 39;N')<>'Y' AND
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 NVL("I"."D_UNMTCH",'N')<>'= Y')
=C2=A0 =C2=A0 =C2=A0 =C2=A0filter("I"."WOF_DATE&q= uot; IS NULL AND "I"."EF_ID" IS NULL AND "I".= "PT_Code"=3D:B7 AND "I"."D_CUR_CODE"=3D:B3 AN= D "I"."PR_CTGRY"=3D:B1 AND
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 "I"."DC_CODE"=3D:B4 AND "= I"."ED_AMT"=3DTO_NUMBER(:B2) AND NVL(:B6,"I"."= ;PT_MCODE")=3DNVL(:B6,:B5) AND TRIM("I"."M_TXT")= =3DTRIM(SUBSTR(:B8,0.50))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0AND "I"."PE" IS NOT NULL AND NVL("I"= ;."MA_FLG",'N')<>'Y' AND =C2=A0NVL("I&= quot;."D_UNMTCH",'N')<>'Y')
--0000000000002c498c05ca75df36-- -- http://www.freelists.org/webpage/oracle-l