Skip to main content

oracle with clause and materialize hint

oracle guru Burleson's note  helped me to improve performance on a query cross database link
http://www.dba-oracle.com/t_with_clause.htm

 we have a database link from oracle to MS SQL server using oracle DRDA, the following query take long to complete, I cannot wait for it to complete and have to kill the query.

SELECT count(*) FROM "ContractItem"@QSENCT002 A, "contractheader_cr"@QSENCT002 B
WHERE A. "ContractHeaderID" = B. "contractheaderid"
AND "ContractItemDayID" BETWEEN 37345 AND 37346
and not exists
(SELECT REVENUESTREAMEXPORTID FROM radiostage.REVENUESTREAMEXPORT_XREF WHERE DIVESTED = 'Y' and A."RevenueStreamExportID" =REVENUESTREAMEXPORTID )
/

After re-write the query using with clause and materialize hint, the query complete in 1 second.
However, if I give bigger range of date in the query, the performance is not improved much, so it's depends on case by case.

with ctview as
(select /*+ materialize */ * FROM "ContractItem"@QSENCT002 A, "contractheader_cr"@QSENCT002 B
WHERE A. "ContractHeaderID" = B. "contractheaderid"
AND "ContractItemDayID" BETWEEN 37345 AND 37346)
select count(*) from ctview
where ctview."RevenueStreamExportID" NOT IN (SELECT REVENUESTREAMEXPORTID FROM radiostage.REVENUESTREAMEXPORT_XREF WHERE DIVESTED = 'Y')

/

  COUNT(*)
----------
         0

Elapsed: 00:00:00.88

Execution Plan
----------------------------------------------------------
Plan hash value: 1488614543

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time  | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     1 |    18 |   406   (1)| 00:00:05 |        |      |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |       |   |      |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6603_13638A70 |       |       |            |       |   |      |
|   3 |    REMOTE                  |                             |     5 |  1300 |   200   (0)| 00:00:03 | QSENC~ | R->S |
|   4 |   SORT AGGREGATE           |                             |     1 |    18 |            |       |   |      |
|*  5 |    HASH JOIN ANTI NA       |                             |     1 |    18 |     6  (17)| 00:00:01 |        |      |
|   6 |     VIEW                   |                             |     5 |    65 |     2   (0)| 00:00:01 |        |      |
|   7 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6603_13638A70 |     5 |  1365 |     2   (0)| 00:00:01 |        |      |
|*  8 |     TABLE ACCESS FULL      | REVENUESTREAMEXPORT_XREF    |     4 |    20 |     3   (0)| 00:00:01 |        |      |
--------------------------------------------------------------------------------------------------------------------------

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

   5 - access("CTVIEW"."RevenueStreamExportID"="REVENUESTREAMEXPORTID")
   8 - filter("DIVESTED"='Y')

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT A1."ContractItemID",A1."ContractHeaderID",A1."RevenueStreamExportID",A1."ContractItemDayID",A1."DayP
       artHalfHourID",A1."TransactionDayID",A1."ContractItemStatusID",A1."ContractItemTypeID",A1."SaleTypeID",A1."Industr
       yProductID",A1."TransactionCodeID",A1."AnnouncementTypeID",A1."NumberOfItems",A1."TotalCostGross",A1."TotalCostNet
       ",A1."TotalValueGross",A1."TotalValueNet",A1."IsBroadcast",A1."SpotLengthID",A1."CustomerID",A2."contractheaderid"
        FROM "ContractItem" A1,"contractheader_cr" A2 WHERE A1."ContractHeaderID"=A2."contractheaderid" AND
       A1."ContractItemDayID">=37345 AND A1."ContractItemDayID"<=37346 (accessing 'QSENCT002' )



Statistics
----------------------------------------------------------
        205  recursive calls
          7  db block gets
        217  consistent gets
          9  physical reads
       1392  redo size
        209  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed


11:26:12 RMS8DEV>

Comments

Post a Comment

Popular posts from this blog

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory for the given Oracle Home. LsInventorySession failed: Unable to create patchObject Possible ca
Post a Comment
Read more

non-existent process lock port on windows server

I have a database link created between oracle and sqlserver using oracle tg4odbc, the product is installed on windows server and run as service "OracleOraGtw11g_home1TNSListener", but sometime the service cannot started, the root cause of this problem is that the port number 1521 is used by an non-existent process. The first step is to use netstat -bano|find "1521" to get the process id, in my case it's 5844, which shows the connection is from my oracle server 10.8.0.169 H:\>netstat -bano|find "1521"   TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       5844   TCP    10.14.45.33:1521       10.8.0.169:42987       ESTABLISHED     5844 however the process id does not show in either task manager or process explorer. The next step is to run tcpview, which shows non-existent under process column, there are three rows, two show status as "listening", the other one shows status "established", right click and k
3 comments
Read more

shell script to clean up oracle dumpfile

https://github.com/iacosta/personal/blob/master/shells/cleanhouse.sh #!/bin/ksh # # Script used to cleanup any Oracle environment. # # Cleans:      audit_file_dest #              background_dump_dest #              core_dump_dest #              user_dump_dest #              Clusterware logs # # Rotates:     Alert Logs #              Listener Logs # # Scheduling:  00 00 * * * /networkdrive/dba/scripts/purge/cleanup.sh -d 7 > /u01/dba/bin/cleanup.log 2>&1 # # Created By:  Lei Dao # # # RM="rm -f" RMDIR="rm -rf" LS="ls -l" MV="mv" TOUCH="touch" TESTTOUCH="echo touch" TESTMV="echo mv" TESTRM=$LS TESTRMDIR=$LS SUCCESS=0 FAILURE=1 TEST=0 HOSTNAME=`hostname` ORAENV="oraenv" TODAY=`date +%Y%m%d` ORIGPATH=/usr/local/bin:$PATH ORIGLD=$LD_LIBRARY_PATH export PATH=$ORIGPATH # Usage function. f_usage(){   echo "Usage: `basename $0` -d DAYS [-a DAYS] [-b DAYS] [
Post a Comment
Read more