Re: SQL help
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: JSweetser@xxxxxxxx
- Date: Tue, 13 May 2014 17:02:20 +0200
Joe,
I also have very recently trouble shooted a query performance problem wich
contains a disjunctive subquery (OR + EXISTS) by re-writing the query so
that unnesting the subquery will become an option for the CBO
http://hourim.wordpress.com/2014/05/12/disjunctive-subquery/
This re-write allows the query to go from a massif 3,771,234 of consistent
gets to just 8 buffer visits.
Best regards
Mohamed Houri
www.hourim.wordpress.com
2014-05-13 16:41 GMT+02:00 Sweetser, Joe <JSweetser@xxxxxxxx>:
> Wow, wow, wow. I am old enough nowadays to have the belief I have seen
> it all (at least, in theory) but this suggestion/answer by Mr. Lewis was
> truly eye-popping. The developer I have been working with had the same
> reaction (but he’s much younger so that’s to be expected J ). Taking
> Jonathan’s suggestion practically verbatim and making a few other minor
> tweaks like breaking one delete statement into 2 separate ones, the run
> time for this little program went from over 12 hours (and not even
> finishing) to about 2.5 minutes. The buffer gets that were over a billion
> for this particular delete dropped to 2.8 million.
>
>
>
> SSS3@idm3> @AdditionalInterestMigration.pls
>
>
>
> PL/SQL procedure successfully completed.
>
>
>
> Elapsed: 00:02:27.58
>
> SSS3@idm3>
>
>
>
> It’s been said many times over the years but I will say it again: This
> list is quite possibly the single best place on the internet to receive
> timely, accurate, and valuable information about this little beast we call
> Oracle.
>
>
>
> I have posted the explain plan for the revised statement below. Note the
> overall cost dropped from 1096 to 13 (!) with Jonathan’s insight. I just
> may have to take my “Oracle Core – Essential Internals for DBAs and
> Developers” book next time JL visits Colorado in order to have it signed.
> I thought my groupie days were over, but perhaps not. J
>
>
>
> Thanks again for the advice.
>
> -joe
>
>
>
> SQL stmt
>
> DELETE
>
> FROM BLDGASSIGNEDADDINT
>
> WHERE ID_ADDITIONALINTEREST in
>
> (
>
> SELECT pk
>
> FROM ADDINTEREST
>
> WHERE ID_INSURED = :1
>
> UNION ALL
>
> SELECT pk
>
> FROM ADDINTEREST
>
> WHERE lnnvl(ID_INSURED = :2)
>
> AND ID_POLICY in
>
> (
>
> SELECT q.pk
>
> FROM quote q
>
> WHERE q.COMMONINSURED_ID = :3
>
> AND q.ENDINFO_ID is null
>
> )
>
> AND id_policy != :4
>
> )
>
>
>
> Explain plan
>
> DELETE STATEMENT Optimizer=ALL_ROWS (Cost=13 Cardinality=6 Bytes=144)
>
> DELETE OF BLDGASSIGNEDADDINT
>
> NESTED LOOPS (Cost=13 Cardinality=6 Bytes=144)
>
> VIEW OF VW_NSO_1 (VIEW) (Cost=10 Cardinality=3 Bytes=39)
>
> SORT (UNIQUE) (Cost=10 Cardinality=3 Bytes=62)
>
> UNION-ALL
>
> TABLE ACCESS (BY INDEX ROWID) OF ADDINTEREST (TABLE) (Cost=2
> Cardinality=1 Bytes=8)
>
> INDEX (RANGE SCAN) OF ADDINTERESTINSURED (INDEX) (Cost=1
> Cardinality=1)
>
> NESTED LOOPS
>
> NESTED LOOPS (Cost=8 Cardinality=2 Bytes=54)
>
> TABLE ACCESS (BY INDEX ROWID) OF QUOTE (TABLE) (Cost=5
> Cardinality=2 Bytes=28)
>
> INDEX (RANGE SCAN) OF QUOTE_COMN_INSRD_ID_IDX (INDEX) (Cost=3
> Cardinality=2)
>
> INDEX (RANGE SCAN) OF ADDINTERESTPOLICY (INDEX) (Cost=1
> Cardinality=2)
>
> TABLE ACCESS (BY INDEX ROWID) OF ADDINTEREST (TABLE) (Cost=2
> Cardinality=1 Bytes=13)
>
> INDEX (RANGE SCAN) OF BLDGASSIGNEDADDINT_BYADDINT (INDEX) (Cost=1
> Cardinality=2 Bytes=22)
>
>
>
> *From:* Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
> *Sent:* Monday, May 12, 2014 2:00 PM
> *To:* Sweetser, Joe; Jeffrey Beckstrom
>
> *Cc:* ORACLE-L
> *Subject:* RE: SQL help
>
>
>
>
>
>
>
> Looks like an example of "subquery with OR" that forces a filter operation
> and stops the optimiser from doing an unnest. See:
> http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or
>
>
> I think you'll probably have to rewrite the query to look something like:
>
>
> delete from BLDGASSIGNEDADDINT where ID_ADDITIONALINTEREST in
> (select pk from ADDINTEREST where ID_INSURED = :3
> union all
> select pk from ADDINTEREST where lnnvl(ID_INSURED = :3)
> and ID_POLICY in
> (select q.pk from quote q where q.COMMONINSURED_ID = :1 and
> q.ENDINFO_ID is null)
> and id_policy != :2
> );
>
>
>
>
>
> I think you'll also need an index on BLDGASSIGNEDADDINT(ID_ADDITIONALINTEREST)
> since that's how you're trying to identify the rows for deletion.
>
>
>
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> @jloracle
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
> behalf of Sweetser, Joe [JSweetser@xxxxxxxx]
> *Sent:* 12 May 2014 20:39
> *To:* Jeffrey Beckstrom
> *Cc:* ORACLE-L
> *Subject:* RE: SQL help
>
> Forgot the index definitions….
>
> There are indexes on:
>
> addinterest.pk, addinterest.id_insured, addinterest.id_policy
>
> and
>
> bldgassignedaddint.id_additionalinterest
>
>
>
> -joe
>
> *From:* Jeffrey Beckstrom [mailto:JBECKSTROM@xxxxxxxxx<JBECKSTROM@xxxxxxxxx>]
>
> *Sent:* Monday, May 12, 2014 1:07 PM
> *To:* Sweetser, Joe
> *Subject:* Re: SQL help
>
>
>
> Where's the index on id_policy for addinterest?
>
> >>> "Sweetser, Joe" <JSweetser@xxxxxxxx> 5/12/14 3:01 PM >>>
> All,
>
> Without going into too much detail as to why (at this point), I have the
> following statement:
>
> delete from BLDGASSIGNEDADDINT where ID_ADDITIONALINTEREST in
> (select pk from ADDINTEREST where (ID_POLICY in
> (select q.pk from quote q where q.COMMONINSURED_ID = :1 and q.ENDINFO_ID
> is null) and id_policy != :2)
> or ID_INSURED = :3);
>
> That yields an explain plan of this:
>
> DELETE STATEMENT Optimizer=ALL_ROWS (Cost=1096 Cardinality=2316
> Bytes=150540)
> DELETE OF BLDGASSIGNEDADDINT
> FILTER
> HASH JOIN (Cost=1096 Cardinality=185659 Bytes=12067835)
> TABLE ACCESS (FULL) OF ADDINTEREST (TABLE) (Cost=435 Cardinality=88384
> Bytes=3446976)
> TABLE ACCESS (FULL) OF BLDGASSIGNEDADDINT (TABLE) (Cost=110
> Cardinality=185657 Bytes=4827082)
> TABLE ACCESS (BY INDEX ROWID) OF QUOTE (TABLE) (Cost=3 Cardinality=1
> Bytes=39)
> INDEX (UNIQUE SCAN) OF SYS_C005688632 (INDEX (UNIQUE)) (Cost=2
> Cardinality=1)
>
> That gets executed many times in a loop and is doing a boatload of logical
> IO's and no physical IO's. By boatload I mean over 100 million buffer gets
> each hour.
>
> I am trying to figure out a way to avoid the full table scans. Below are
> the table creation statements for bldgassignaddint and addinterest tables
> minus extraneous columns
>
> CREATE TABLE "BLDGASSIGNEDADDINT"
> ( "ID_BUILDING" NUMBER(10),
> "ID_ADDITIONALINTEREST" NUMBER(10),
> CONSTRAINT "FK17A3EAC98DDD8F2C" FOREIGN KEY ("ID_ADDITIONALINTEREST")
> REFERENCES "ADDINTEREST" ("PK") VALIDATE ,
> CONSTRAINT "FK17A3EAC9C50C5C52" FOREIGN KEY ("ID_BUILDING") REFERENCES
> "BUILDING" ("PK") VALIDATE ,
> PRIMARY KEY ("ID_BUILDING", "ID_ADDITIONALINTEREST") VALIDATE;
>
> CREATE TABLE "ADDINTEREST"
> ( "PK" NUMBER(10),
> "ID_INSURED" NUMBER(10),
> "ID_POLICY" NUMBER(10),
> CONSTRAINT "FK4B56FACB277D71C9" FOREIGN KEY ("ID_INSURED") REFERENCES
> "INSURED" ("PK") VALIDATE ,
> CONSTRAINT "FK4B56FACBE57638AB" FOREIGN KEY ("ID_POLICY") REFERENCES
> "QUOTE" ("PK") VALIDATE ,
> PRIMARY KEY ("PK") VALIDATE );
>
> Any suggestions? I am having no luck at all trying to incorporate EXISTS
> instead of IN.
>
> Thanks,
> -joe
> Confidentiality Note: This message contains information that may be
> confidential and/or privileged. If you are not the intended recipient, you
> should not use, copy, disclose, distribute or take any action based on this
> message. If you have received this message in error, please advise the
> sender immediately by reply email and delete this message. Although ICAT
> Managers, LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and
> attachments for viruses, it does not guarantee that either are virus-free
> and accepts no liability for any damage sustained as a result of viruses.
> Thank you.
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
> .
>
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
