Recursive calls???

From: Terrian, Thomas <Thomas.Terrian_at_standardregister.com>
Date: Tue, 6 Jun 2000 10:34:06 -0400
Message-Id: <10520.107879@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01BFCFC4.45177C90

Content-Type: text/plain;

        charset="iso-8859-1"

Does anyone know what recursive calls are and how to reduce them?

I have the following statistics from two almost identical SQL statements. The only difference in the statements is that the first uses a partitioned view and the second uses a partitioned table. The view runs much quicker and uses no recursive calls. Anyone have an idea of how I can reduce the recursive calls for the partitioned table?   

Statistics - Partition View (runs in 1 minute)

---

	    0  recursive calls
      22678  db block gets
    5307218  consistent gets
      19459  physical reads
          0  redo size
    2814049  bytes sent via SQL*Net to client
     103987  bytes received via SQL*Net from client
        935  SQL*Net roundtrips to/from client
         26  sorts (memory)
          0  sorts (disk)
      14010  rows processed

Statistics - Partition Table (runs in 5 minutes)

---

     157732  recursive calls
      31107  db block gets
    2178802  consistent gets
      30559  physical reads

    3794572 redo size
    2745586 bytes sent via SQL*Net to client

     103987  bytes received via SQL*Net from client
        935  SQL*Net roundtrips to/from client
         11  sorts (memory)
          4  sorts (disk)
      14010  rows processed

Tom Terrian
Oracle DBA
(937) 221-1647
Thomas.Terrian_at_standardregister.com

------_=_NextPart_000_01BFCFC4.45177C90

Content-Type: application/ms-tnef
Content-Transfer-Encoding: base64

eJ8+IgoOAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQWAAwAOAAAA0AcGAAYACgAiAAYAAgAXAQEggAMADgAAANAHBgAG AAoAIgAIAAIAGQEBCYABACEAAAA2MzBCRjMwREE4M0JENDExOUFBRTAwQTBDOUQ1RDdGQQBYBwEE gAEAEwAAAFJlY3Vyc2l2ZSBjYWxscz8/PwCkBgENgAQAAgAAAAIAAgABA5AGAFQJAAAxAAAAAgFx AAEAAAAWAAAAAb/PxG/AGahdLzt6EdSd2QDAT/KF+gAAHgAKgAggBgAAAAAAwAAAAAAAAEYAAAAA N4UAAAEAAAABAAAAAAAAAB4AcAABAAAAEwAAAFJlY3Vyc2l2ZSBjYWxscz8/PwAAAwDeP69vAAAD AAGACCAGAAAAAADAAAAAAAAARgAAAABShQAA8BMAAB4AAoAIIAYAAAAAAMAAAAAAAABGAAAAAFSF AAABAAAABAAAADguNQALAA6ACCAGAAAAAADAAAAAAAAARgAAAAAGhQAAAAAAAAMAA4AIIAYAAAAA AMAAAAAAAABGAAAAAAGFAAAAAAAACwAEgAggBgAAAAAAwAAAAAAAAEYAAAAAA4UAAAAAAAALAAWA CCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMABoAIIAYAAAAAAMAAAAAAAABGAAAAABCFAAAA AAAAAwAHgAggBgAAAAAAwAAAAAAAAEYAAAAAEYUAAAAAAAADAAiACCAGAAAAAADAAAAAAAAARgAA AAAYhQAAAAAAAAsADIALIAYAAAAAAMAAAAAAAABGAAAAAACIAAAAAAAACwANgAsgBgAAAAAAwAAA AAAAAEYAAAAABYgAAAAAAAACAQkQAQAAAIsDAACHAwAAaQcAAExaRnWlurEyAwAKAHJjcGcxMjXi MgNDdGV4BUEBAwH3/wqAAqQD5AcTAoAP8wBQBFY/CFUHshElDlEDAQIAY2jhCsBzZXQyBgAGwxEl 9jMERhO3MBIsETMI7wn3tjsYHw4wNREiDGBjAFCzCwkBZDM2FlALp2MBMFggRG8HkQBweQIgZdAg a25vB+B3E+AFQK0YIGMIcACQdh3QYwdAjmwdYRggHXFkIGgeETR0bx6BZBrQHdB0aLhlbT8KogqE CoBJIAD2YR8BIPEgAhAfUB4QC4BoZyBzAZB0BAAjgGP3BCADUiBAdyBgB0AEYCNQfCBpAQACMA3g B0AGAFEOTCNDIRAlIXMuICCGVCKBAiBseSBkBpD+ZgSQCfAgwQuAImMl6CTwbwQgIPAeYSJzaRQA BUB1+xQQHWEgCrEjgCOAHbEf8P52CJAH4B/SKCMFkR/hKj8zK9EBoGxlJoUrY3J1hQYxbRrQaCBx dQ3gfmsSgR/SLKMeAB6OJoFBnx2UIiMDkSUBKoBvZiAD/yIAHzADoCCIHo8CEAXAInIvLR8hNSaQ IUpTI2gtINJQKrYgVityKC7jJ/FWMS8gC4B1DrApIUQt/zvPPN897z5UIUUBkSaQQAEPFlA0njdG QAIyMjY3WjgmkGQ/8AJgby+wIEZnFCBBaTUzMAHAMX9CcSxRAJAjUCUhQy0mkDEQOTQ1OSaQcGh5 Zw3RJXEYIGFkQWtAF2QTIGAAkHplQXgyODHINDA0RpFieQ6wBCDfFBBFIStgKoAloSoHwClxOSBg Y2wIkAIwQXkxMLAzOTg3SnYekWUe8R8rQktoJANMP0ABOTM1zyaQS4YDYC7wZHQFESkhvG8vT39I B0JAJpBzF8HNBCAoB4AEYHJ5O1VIC/NUlSdAc2tVa0ohTUBAYfseEAQgcANgIMAEEAmAN8+/ON8m oC3iOjhRIDr0cztfD14vXz8+j0zHNTc3M+cUQEB/QYkzMU1ATZFCr+VJpTFCYDgwYjFEr2N5zjAa YEafQXgzN0ZhAcDLSL9B8zdGcDU4VGFKn/9Lr0y/Tc9O31MvUP9SD3G/70YiOtBUj1WcNFaPV59Y r9chhnu/IZVUJCFUBJAHITMLkCFTT3IA0FvRREJKQSFEKHLgNylCITHgLTE2NDd9NSAQAMB7JnB9 1UAjUR/gCxEYIGepZsJyLgWgbSFEfYOAAAsAAgABAAAAAwD9P+QEAAAeAAmACCAGAAAAAADAAAAA AAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgALgAggBgAAAAAAwAAAAAAAAEYAAAAAOIUAAAEAAAAB AAAAAAAAAEAAOQCQfBdFxM+/AQMA8T8JBAAAHgAxQAEAAAAIAAAAVFRFUlJJQQADABpAAAAAAB4A MEABAAAACAAAAFRURVJSSUEAAwAZQAAAAAADACYAAAAAAAMANgAAAAAAAwCAEP////8CAUcAAQAA ADUAAABjPVVTO2E9IDtwPVN0ZFJlZztsPU9IX0RBWVROX1hDSC0wMDA2MDYxNDM0MDZaLTE0MjY3 AAAAAAIB+T8BAAAAYwAAAAAAAADcp0DIwEIQGrS5CAArL+GCAQAAAAAAAAAvTz1TVEFOREFSRCBS RUdJU1RFUiBDT01QQU5ZL09VPVNSQ19DT1JQT1JBVEUvQ049UkVDSVBJRU5UUy9DTj1UVEVSUklB AAAeAPg/AQAAABAAAABUZXJyaWFuLCBUaG9tYXMAHgA4QAEAAAAIAAAAVFRFUlJJQQACAfs/AQAA AGMAAAAAAAAA3KdAyMBCEBq0uQgAKy/hggEAAAAAAAAAL089U1RBTkRBUkQgUkVHSVNURVIgQ09N UEFOWS9PVT1TUkNfQ09SUE9SQVRFL0NOPVJFQ0lQSUVOVFMvQ049VFRFUlJJQQAAHgD6PwEAAAAQ AAAAVGVycmlhbiwgVGhvbWFzAB4AOUABAAAACAAAAFRURVJSSUEAQAAHMLAGDkXEz78BQAAIMMZq UUbEz78BHgA9AAEAAAABAAAAAAAAAB4AHQ4BAAAAEwAAAFJlY3Vyc2l2ZSBjYWxscz8/PwAAHgA1 EAEAAABFAAAAPDc3ODJCRTZDRkQ2NEQyMTE5QTdCMDBBMEM5RDVEN0ZBMDUwMUUxNzBAb2hfZGF5 dG5feGNoMDIuc3RkcmVnLmNvbT4AAAAACwApAAAAAAALACMAAAAAAAMABhCd06lXAwAHECcEAAAD ABAQAAAAAAMAERAAAAAAHgAIEAEAAABlAAAARE9FU0FOWU9ORUtOT1dXSEFUUkVDVVJTSVZFQ0FM TFNBUkVBTkRIT1dUT1JFRFVDRVRIRU0/SUhBVkVUSEVGT0xMT1dJTkdTVEFUSVNUSUNTRlJPTVRX T0FMTU9TVElERU5USQAAAAACAX8AAQAAAEUAAAA8Nzc4MkJFNkNGRDY0RDIxMTlBN0IwMEEwQzlE NUQ3RkEwNTAxRTE3MEBvaF9kYXl0bl94Y2gwMi5zdGRyZWcuY29tPgAAAACqHw== Received on Tue Jun 06 2000 - 09:34:06 CDT