Databases
StackOverflow2013
Postsdbo
POOracle Sql Query taking a day long to return results using dblink
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle Sql Query taking a day long to return results using dblink
    primarykey
    data
    text
    <p>Guys i have the following oracle sql query that gives me the monthwise report between the dates.Basically for nov month i want sum of values between the dates 01nov to 30 nov. The table that is being queried is residing in another database and accesssed using dblink. The DT columns is of NUMBER type (for ex 20101201).</p> <pre><code>SELECT /*+ PARALLEL (A 8) */ /*+ DRIVING_STATE(A) */ TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')- 1,'MM'),'MONYYYY') "MONTH", TYPE AS "TYPE", COLUMN, COUNT (DISTINCT A) AS "A_COUNT", COUNT (COLUMN) AS NO_OF_COLS, SUM (DURATION) AS "SUM_DURATION", SUM (COST) AS "COST" FROM **A@LN_PROD A** WHERE DT &gt;= TO_NUMBER(TO_CHAR(add_months(SYSDATE,-1),'YYYYMM"01"')) AND DT &lt; TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMM"01"')) GROUP BY TYPE, COLUMN </code></pre> <p>The execution of the query is taking a day long and not completed. kindly suggest me , if their is any optimisation that can be suggested to my DBA on the dblink, or any tuning that can be done on the query , or rewriting the same.</p> <p><strong>UPDATES ON THE TABLE</strong></p> <p>The table is partiontioned on the date column and almost 1 billion records.</p> <p>Below i have given the <strong>EXPLAIN PLAN</strong> from <strong>TOAD</strong></p> <pre><code>**Plan** SELECT STATEMENT REMOTE ALL_ROWSCost: 1,208,299 Bytes: 34,760 Cardinality: 790 12 PX COORDINATOR 11 PX SEND QC (RANDOM) SYS.:TQ10002 Cost: 1,208,299 Bytes: 34,760 Cardinality: 790 10 SORT GROUP BY Cost: 1,208,299 Bytes: 34,760 Cardinality: 790 9 PX RECEIVE Cost: 1,208,299 Bytes: 34,760 Cardinality: 790 8 PX SEND HASH SYS.:TQ10001 Cost: 1,208,299 Bytes: 34,760 Cardinality: 790 7 SORT GROUP BY Cost: 1,208,299 Bytes: 34,760 Cardinality: 790 6 PX RECEIVE Cost: 1,208,299 Bytes: 34,760 Cardinality: 790 5 PX SEND HASH SYS.:TQ10000 Cost: 1,208,299 Bytes: 34,760 Cardinality: 790 4 SORT GROUP BY Cost: 1,208,299 Bytes: 34,760 Cardinality: 790 3 FILTER 2 PX BLOCK ITERATOR Cost: 1,203,067 Bytes: 15,066,833,144 Cardinality: 342,428,026 Partition #: 11 Partitions accessed #1 - #5 1 TABLE ACCESS FULL TABLE CDRR.FRD_CDF_DATA_INTL_IN_P Cost: 1,203,067 Bytes: 15,066,833,144 Cardinality: 342,428,026 Partition #: 11 </code></pre> <p><strong>The following things i am going to do today ,any additional tips would be helpful.</strong></p> <ol> <li>I am going to gather the tablewise statistics for this table, which may give optimal execution plan.</li> <li>Check whether an local index is created for the partition .</li> <li>using BETWEEN instead of >= and &lt;.</li> </ol>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDead Programmer
    1. USDead Programmer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle Sql Query taking a day long to return results using dblink
      1. USDead Programmer
      1. VTFavorite
    1. COJust curious, how long does it take to run on the remote database directly? The DB Link may be a red herring if the query itself is simply taking a long time.
      singulars
      1. POOracle Sql Query taking a day long to return results using dblink
      1. USAdam Hawkes
    2. COA billion rows! No wonder it takes a long time!
      singulars
      1. POOracle Sql Query taking a day long to return results using dblink
      1. USMike Meyers
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload