1 2 3 4 Previous Next 45 Replies Latest reply: Nov 24, 2009 10:25 AM by 724046 Go to original post RSS
      • 30. Re: Help with Parallelization on our SPARC server...we are drowning!
        614235
        Maurice,

        It looks like you are right to question exactly what a T2 processor is internally. I believed that it was 8 cores and 8 threads each for 64 virtual CPUs for all T2's, but the Sun web site says something different. Gunners said that he has a T6320 with a 1.2 GHz CPU. From the [Sun Blade T6320 Specs|http://www.sun.com/servers/blades/t6320/specs.xml] web page it says:
        Processor - Four-core 1.2-GHz, eight-core 1.4-GHz, or eight-core 1.6-GHz UltraSPARC T2 processor; one processor per system, maximum 64 threads
        Threads - Up to 64 simultaneous execution threads
        On the one hand I was right that the T2 can have 8 cores and 8 threads each for 64 threads total. On the other hand, the 1.2 GHz T2 only has 4 cores in it, for 32 threads total. Which will explain why Oracle only has the CPU_COUNT set to 32, and not 64 on Gunner's system.

        Unfortunately, this actually makes things much worse for Gunners, as he only has 4 CPU execution cores that can actually execute instructions, not 8 as I assumed. And at 1.2 GHz this will be at least half the net performance of a quad core Intel CPU running at over 2 GHz. Gunners, I am sorry, but to me it looks like you have been sold a puppy. That 1.2 GHz 4 core T2 CPU is next to useless - the cores are too slow at 1.2 GHz, and there are only 4 of them. Any current Intel or AMD quad core CPU runs at over 2 GHz and would wipe the floor with the T2, if you see what I mean.
        As far as I understood each core of the T2 is subdivided into 4 virtual CPUs.
        Maurice, I think you may have got some of the technical details of how the T2 (Niagara) CPU cores actually work wrong. Each core is a relatively simple UltraSPARC based core, and still only executes one instruction at a time. It is no different to other CPUs in this respect. The key feature is that it keeps within the CPU the state of 4 jobs or threads - which are the virtual CPUs Solaris sees. But internally there is still a normal UltraSPARC-II CPU core executing at 1.2 GHz, or more.

        The virtualization works this way. When the currently executing process, lets call it thread 1, causes the CPU core to do a memory access, the CPU would otherwise have to wait (stall) for many CPU cycles until the data came back from memory. And this is quite a long period relative to the CPU cycle time at 1.2 GHz. In the Niagara based processors, when thread 1 does a memory access, the CPU core immediately switches its registers and internal stuff to that of thread 2, and carries on by executing instructions for thread 2. That way there are no wasted, unused CPU cycles. Eventually thread 2 will do a memory access, and the CPU core will switch to thread 3; and when that accesses memory it will switch to thread 4.

        These thread switches within the CPU core are very fast. The principle of the Niagara CPU design is to use what would otherwise be idle CPU cycles during relatively long memory accesses, to instead execute instructions from another thread (process). This increases the overall efficiency of the CPU, because less execution cycles are wasted while it waits for a memory access to complete.

        The switching between threads in the CPU core only takes place when the currently executing thread performs a memory access. If a thread existed that never did a memory access - all of its data was in the CPU cache for instance - then the CPU core would never switch to another thread. Of course, such a scenario would never occur in reality, but the point is that the CPU core is not shared equally between threads. A thread executes until it does a memory access, and it only is executed again after all the other threads have also done memory accesses and the CPU core has switched between them.

        It also means that if only one thread is running, and there are no other runnable threads, then it gets full use of the 1.2 GHz CPU core. And when it does a memory access, the CPU core will wait because there are no other threads to run, and carry on with that same thread when the memory access completes.

        I know this is a bit of a detailed explanation, but it is my understanding of how the T2 and T1 Niagara based processors actually work, and how one CPU core is "shared" between multiple execution threads. The design principle was to make use of wasted CPU cycles during memory access to execute instructions from another thread. It was not designed to ensure "equal" usage of the CPU core by all threads. The goal was to increase CPU efficiency internally, so that no CPU cycles were wasted. Which is why I keep saying that as more threads do run on each CPU core, and the sharing increases, so each thread gets less and less of the 1.2 GHz of that CPU core. And at 4 concurrent threads per core, they would each achieve the equivalent of a 300 MHz CPU in terms of instructions executed per unit of time. Likewise, 8 threads per core reduces the throughput per thread to that of a 150 MHz CPU. Clearly a case of more threads equals less throughput per thread.

        John
        • 31. Re: Help with Parallelization on our SPARC server...we are drowning!
          137669
          Hi John,

          As far as I understood that's not the way the T2 works. As far as I undersood it each of it's cores are divided into 4 virtual CPUs threads being each one limited to a 1/4 of the frequency of one core.

          That's also why, even if there is no load at all on the sever, that one single serial process is much slower on a T2 as if the same process would be exeucted on a sever with ultraspac III (also @1.2Ghz).

          https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=781763.1
          So a single process may report it is nearly 100% CPU bound (as measured via getrusage() calls) from its perspective, but it can only keep one virtual CPU busy at most. So at the system level, the computer may only appear to be (100/N)% busy (where N is # virtual CPUs) due to the activity of that single process
          If the following formula is correct it would confirm my assumption:
          (100/N)% busy (where N is # virtual CPUs)

          Regards
          Maurice
          • 32. Re: Help with Parallelization on our SPARC server...we are drowning!
            614235
            The way I describe how the Niagara CPU core works in the T2 processor is based on the stuff I have read from Sun - now over 4 years ago. However, which of the two of us is more correct about what really goes on inside a T2 is another thing ;-)

            For instance, the information on [Improving Application Efficiency Through Chip Multi-Threading|http://developers.sun.com/solaris/articles/chip_multi_thread.html] describes how the multi-threading uses what would otherwise be idle cycles during memory access. There are diagrams that clearly show the timeslicing within a CPU core between each thread, and how different threads overlap in time, but only one is running on a CPU core at any moment in time. Within this it says:
            *1.2 Chip Multi-Processing With Hardware Threads*
            The OS schedules software threads on the cores in a US-IV processor. In the case of a memory access, a core needs to wait for the access to complete while the other core continues processing instructions. This is not very efficient as a core wastes clock cycles. The core using more registers and logic in hardware can context-switch the software thread to another software thread. This logic in hardware holding the thread state is called a hardware thread. The hardware thread appears as a logical processor to the OS. A processor with n hardware threads would appear as n logical processors.
            ...
            The OS schedules software threads on these logical processors. On a memory access, the core switches to a new hardware thread so it does not have to wait for the access to be complete. The context switch is in hardware so no clock cycles are wasted. Hardware threads improve the efficiency of a processor by processing instructions every clock cycle.
            So it is pretty clear that the switch between threads happens when the currently executing thread does a memory access.

            The trouble is probably that the diagrams Sun uses all show perfect uniformity between the threads and their CPU usage. Which matches up with your description of each thread getting 1/n'th of the time on a n-thread CPU core. Unfortunately I do not thing that real world applications would have such a perfectly uniform memory access pattern.

            Other things mentioned in this article include:
            Each core can process one instruction every cycle
            The Niagara processor can process eight instructions simultaneously every clock cycle or one instruction per core.
            Simultaneous multi-threading uses hardware threads layered on top of a core to execute instructions from multiple threads. The hardware threads consist of all the different registers to keep track of a thread execution state. These hardware threads are also called logical processors.
            The efficiency of a CMT-enabled system can be identified by the memory stalls, cache references and misses, and number of instructions processed per cycle. Cache misses, memory stalls, and instructions processed can be obtained using the cputrack, cpustat, and har utilities on the Solaris OS. An ideal system with no cache misses or stalls should run at 100% efficiency.
            There is also a [White Paper on Throughput Computing|http://www.sun.com/processors/whitepapers/throughput_whitepaper.pdf] (PDF) which describes the way that the Niagara processors work in the same way:
            Unlike traditional single-threaded processors and even most current multi-core processors, hardware multithreaded processors allow rapid switching between active threads as other threads stall for memory.
            The negative effects of memory latency are masked since the processor and the memory subsystems remain active in parallel to the processor execution pipeline.
            Overall, all of the documents I have read on the Niagara processor describe it the same as these two - thread switching takes place on a memory access by the currently executing thread. There are references at the end of the first article to other documents, for those that want more detail on the Niagara based processors.

            John
            • 33. Re: Help with Parallelization on our SPARC server...we are drowning!
              724046
              John,

              Thanks for the reply. I should be sending all my results to Oracle Support today after all the testing they wanted me to do. Hopefully they have some answers for us.

              As for the Server, seems like I was right from the start. Then vendor sold this company a crap server for the type of work there doing and sold it off as a 8 core machine when I kept pointing it out that it was a 4 core machine based on the Sun Server specs. Does not seem like they upgraded the CPU either.
              • 34. Re: Help with Parallelization on our SPARC server...we are drowning!
                724046
                this is what I am seeing
                () VADP> psrinfo -pv
                The physical processor has 64 virtual processors (0-63)
                  UltraSPARC-T2 (chipid 0, clock 1165 MHz)
                So the vendor poeple are telling me this:
                Gunners wrote:
                Hello,

                            The server has an 8 cores single 1.2 GHz processor with the possibility of 8 threads per core.

                # psrinfo -pv
                The physical processor has 64 virtual processors (0-63)
                  UltraSPARC-T2 (chipid 0, clock 1165 MHz)
                Regards,
                • 35. Re: Help with Parallelization on our SPARC server...we are drowning!
                  Taral
                  What is the query that was running.

                  there are two things here

                  1. parallel_execution_message_size integer 32768
                  Why this much big ? Did you benchmarked with different message size. This might cause you problem with high value

                  2. PX Deq Credit: send blkd
                  This is major event which is there which says producer is producing more then consumer. So may be due to some parallel operation are converted into serial.

                  Now, the only way to look this is to find problem SQL which is running while this is happening. Also, try to reduce DOP this might help you.
                  • 36. Re: Help with Parallelization on our SPARC server...we are drowning!
                    724046
                    Taral Desai wrote:
                    What is the query that was running.

                    there are two things here

                    1. parallel_execution_message_size integer 32768
                    Why this much big ? Did you benchmarked with different message size. This might cause you problem with high value

                    2. PX Deq Credit: send blkd
                    This is major event which is there which says producer is producing more then consumer. So may be due to some parallel operation are converted into serial.

                    Now, the only way to look this is to find problem SQL which is running while this is happening. Also, try to reduce DOP this might help you.
                    We set it 32K from 8k as Oracle advised us to due to high waits.

                    PX Deq Credit: send blkd waits does occur but not as much when we increase PARALLEL_MAX_SERVER to a value above 300 as our queries never get downgraded to serial. Any value below it does.
                    Now, the only way to look this is to find problem SQL which is running while this is happening. Also, try to reduce DOP this might help you.
                    Well we currently have not DOP set (if not set then I assume its defaulted to 1???).

                    I ran the following test scenarios for Oracle:

                    Scenario 1 - Baseline
                    -As is, no DOP
                    -Forced Parallel, no DOP
                    -Objects with Defaulted degree’s of parallelism

                    Scenario 2 – Setting Parallel Max Servers to 300
                    -As is, no DOP
                    -Forced Parallel, no DOPl
                    -Objects with Defaulted degree’s of parallelism

                    Scenario 3 – Setting Parallel Max Servers to 640
                    -As is, no DOP
                    -Forced Parallel, no DOP
                    -Objects with Defaulted degree’s of parallelism

                    The average timings were about 2 hours each...but for Scenario 2, Objects with Defaulted degree’s of parallelism, it completed this in 83 minutes. Best timing of them all. Some of the stats I collected over this time perdiod were as follows:
                    • 37. Re: Help with Parallelization on our SPARC server...we are drowning!
                      724046
                      SQL> @"H:\My Documents\Oracle\Databases\VAD\VADP\TestRuns\px_stats.sql";
                      SELECT * FROM GV$SYSSTAT WHERE name LIKE 'Parallel operation%';
                      
                         INST_ID STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
                      ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------
                               1        318 Parallel operations not downgraded                                       32         95 2410168744
                               1        319 Parallel operations downgraded to serial                                 32          0 4286011915
                               1        320 Parallel operations downgraded 75 to 99 pct                              32          0  322858058
                               1        321 Parallel operations downgraded 50 to 75 pct                              32         34 3281923117
                               1        322 Parallel operations downgraded 25 to 50 pct                              32          7 3330456527
                               1        323 Parallel operations downgraded 1 to 25 pct                               32          0 3721245209
                       
                      6 rows selected
                      
                      SELECT * FROM V$PX_PROCESS order by 1;
                       
                      SERVER_NAME STATUS           PID SPID            SID    SERIAL#
                      ----------- --------- ---------- ------------ ------ ----------
                      P000        IN USE            18 12041          1077         16
                      P001        IN USE            19 12043           957         15
                      P002        IN USE            20 12045          1052         15
                      P003        IN USE            21 12047          1059         15
                      P004        IN USE            22 12049          1076         16
                      P005        IN USE            23 12051           992         15
                      P006        IN USE            24 12053          1058         15
                      P007        IN USE            25 12055          1043         15
                      P008        IN USE            26 12057           973         15
                      P009        IN USE            27 12059          1020         15
                      P010        IN USE            28 12061           964         15
                      P011        IN USE            29 12063          1016         15
                      P012        IN USE            30 12065          1072         15
                      P013        IN USE            31 12067          1009         16
                      P014        IN USE            32 12069          1012         15
                      P015        IN USE            33 12071          1054         15
                      P016        IN USE            38 12100          1044         15
                      P017        IN USE            39 12102           972         15
                      P018        IN USE            40 12104           956         15
                      P019        IN USE            41 12106           993         15
                      P020        IN USE            42 12108           962         15
                      P021        IN USE            43 12110          1078         15
                      P022        IN USE            44 12112          1017         15
                      P023        IN USE            45 12114          1022         15
                      P024        IN USE            46 12116           958         15
                      P025        IN USE            47 12118          1081         15
                      P026        IN USE            48 12120          1024         15
                      P027        IN USE            49 12122          1023         15
                      P028        IN USE            50 12124          1056         15
                      P029        IN USE            51 12126          1075         15
                      P030        IN USE            52 12128           969         15
                      P031        IN USE            53 12130           967         15
                      P032        IN USE            54 12132          1021         15
                      P033        IN USE            55 12134          1033         15
                      P034        IN USE            56 12136           980         15
                      P035        IN USE            57 12138          1050         16
                      P036        IN USE            58 12140          1061         15
                      P037        IN USE            59 12142           989         15
                      P038        IN USE            60 12144          1060         15
                      P039        IN USE            61 12146           963         15
                      P040        IN USE            62 12148          1055         15
                      P041        IN USE            63 12150          1035         15
                      P042        IN USE            64 12152          1014         15
                      P043        IN USE            65 12154          1082         31
                      P044        IN USE            66 12156           954         15
                      P045        IN USE            67 12158          1019         15
                      P046        IN USE            68 12160           968         15
                      P047        IN USE            69 12162          1064         15
                      P048        IN USE            70 12164          1057         15
                      P049        IN USE            71 12166          1070         15
                      P050        IN USE            72 12168           997         15
                      P051        IN USE            73 12170          1007         15
                      P052        IN USE            74 12172          1039         15
                      P053        IN USE            75 12174           975         15
                      P054        IN USE            76 12176           966         16
                      P055        IN USE            77 12178           999         15
                      P056        IN USE            78 12180          1068         15
                      P057        IN USE            79 12182          1002         15
                      P058        IN USE            80 12184          1006         15
                      P059        IN USE            81 12186          1032         15
                      P060        IN USE            82 12188           959         15
                      P061        IN USE            83 12190          1008         15
                      P062        IN USE            84 12192          1051         15
                      P063        IN USE            85 12194          1027         15
                      P064        IN USE            86 12196           986         15
                      P065        IN USE            87 12198          1026         15
                      P066        IN USE            88 12200           994         15
                      P067        IN USE            89 12202          1066         15
                      P068        IN USE            90 12204           960         15
                      P069        IN USE            91 12206          1065         15
                      P070        IN USE            92 12208          1011         15
                      P071        IN USE            93 12210          1030         15
                      P072        IN USE            94 12212          1029         15
                      P073        IN USE            95 12214           996         15
                      P074        IN USE            96 12216           961         15
                      P075        IN USE            97 12218          1079         15
                      P076        IN USE            98 12220          1047         15
                      P077        IN USE            99 12222          1074         16
                      P078        IN USE           100 12224           970         15
                      P079        IN USE           101 12226          1041         15
                      P080        IN USE           102 12228          1046         15
                      P081        IN USE           103 12230          1053         15
                      P082        IN USE           104 12232          1001         15
                      P083        IN USE           105 12234          1063         15
                      P084        IN USE           106 12236           979         15
                      P085        IN USE           107 12238          1031         15
                      P086        IN USE           108 12240          1003         15
                      P087        IN USE           109 12242          1025         15
                      P088        IN USE           110 12244          1045         15
                      P089        IN USE           111 12246           983         15
                      P090        IN USE           112 12248           987         15
                      P091        IN USE           113 12250          1010         15
                      P092        IN USE           114 12252          1013         15
                      P093        IN USE           115 12254           971         15
                      P094        IN USE           116 12256          1080         15
                      P095        IN USE           117 12258          1000         15
                      P096        IN USE           118 12260           974         15
                      P097        IN USE           119 12262          1073         15
                      P098        IN USE           120 12264           984         16
                      P099        IN USE           121 12266           976         15
                      P100        IN USE           122 12268           981         15
                      P101        IN USE           123 12270          1069         15
                      P102        IN USE           124 12272           955         15
                      P103        IN USE           125 12274           998         15
                      P104        IN USE           126 12276           977         15
                      P105        IN USE           127 12278           988         15
                      P106        IN USE           128 12280          1037         15
                      P107        IN USE           129 12282          1034         15
                      P108        IN USE           130 12284          1004         15
                      P109        IN USE           131 12286           990         15
                      P110        IN USE           132 12288           995         15
                      P111        IN USE           133 12290          1071         15
                      P112        IN USE           134 12292          1040         15
                      P113        IN USE           135 12294          1036         15
                      P114        IN USE           136 12296           982         15
                      P115        IN USE           137 12298          1038         15
                      P116        IN USE           138 12300           965         15
                      P117        IN USE           139 12302          1015         15
                      P118        IN USE           140 12304          1005         15
                      P119        IN USE           141 12306          1049         15
                      P120        IN USE           142 12308          1062         15
                      P121        IN USE           143 12310           991         15
                      P122        IN USE           144 12312           978         15
                      P123        IN USE           145 12314          1067         15
                      P124        IN USE           146 12316          1048         15
                      P125        IN USE           147 12318          1028         15
                      P126        IN USE           148 12320           985         15
                      P127        IN USE           149 12322          1042         15
                      P128        IN USE           150 12328           937         15
                      P129        IN USE           151 12330           912         15
                      P130        IN USE           152 12332           924         15
                      P131        IN USE           153 12334           864         13
                      P132        IN USE           154 12336           921         15
                      P133        IN USE           155 12338           900         15
                      P134        IN USE           156 12340           929         15
                      P135        IN USE           157 12342           827         13
                      P136        IN USE           158 12344           952         15
                      P137        IN USE           159 12346           836         13
                      P138        IN USE           160 12348           890         15
                      P139        IN USE           161 12350           886         13
                      P140        IN USE           162 12352           946         15
                      P141        IN USE           163 12354           876         13
                      P142        IN USE           164 12356           841         13
                      P143        IN USE           165 12358           926         15
                      P144        IN USE           166 12360           826         13
                      P145        IN USE           167 12362           839         13
                      P146        IN USE           168 12364           871         13
                      P147        IN USE           169 12366           916         15
                      P148        IN USE           170 12368           858         13
                      P149        IN USE           171 12370           868         13
                      P150        IN USE           172 12372           914         15
                      P151        IN USE           173 12374           932         15
                      P152        IN USE           174 12376           852         13
                      P153        IN USE           175 12378           869         13
                      P154        IN USE           176 12380           861         13
                      P155        IN USE           177 12382           898         15
                      P156        IN USE           178 12384           850         13
                      P157        IN USE           179 12386           835         13
                      P158        IN USE           180 12388           953         15
                      P159        IN USE           181 12390           855         13
                      P160        IN USE           182 12392           854         13
                      P161        IN USE           183 12394           828         13
                      P162        IN USE           184 12396           847         13
                      P163        IN USE           185 12398           832         13
                      P164        IN USE           186 12400           942         15
                      P165        IN USE           187 12402           849         13
                      P166        IN USE           188 12404           907         15
                      P167        IN USE           189 12406           913         15
                      P168        IN USE           190 12408           923         15
                      P169        IN USE           191 12410           948         15
                      P170        IN USE           192 12412           844         13
                      P171        IN USE           193 12414           933         16
                      P172        IN USE           194 12416           883         13
                      P173        IN USE           195 12418           877         13
                      P174        IN USE           196 12420           910         15
                      P175        IN USE           197 12422           939         15
                      P176        IN USE           198 12424           831         13
                      P177        IN USE           199 12426           931         15
                      P178        IN USE           200 12428           885         13
                      P179        IN USE           201 12430           856         13
                      P180        IN USE           202 12432           888         13
                      P181        IN USE           203 12434           911         15
                      P182        IN USE           204 12436           833         13
                      P183        IN USE           205 12438           882         13
                      P184        IN USE           206 12440           938         15
                      P185        IN USE           207 12442           878         13
                      P186        IN USE           208 12444           875         13
                      P187        IN USE           209 12446           896         15
                      P188        IN USE           210 12448           829         13
                      P189        IN USE           211 12450           917         15
                      P190        IN USE           212 12452           901         15
                      P191        IN USE           213 12454           920         15
                      P192        IN USE           214 12456           919         15
                      P193        IN USE           215 12458           863         13
                      P194        IN USE           216 12460           834         13
                      P195        IN USE           217 12462           915         15
                      P196        IN USE           218 12464           893         15
                      P197        IN USE           219 12466           880         13
                      P198        IN USE           220 12468           922         15
                      P199        IN USE           221 12470           905         15
                      P200        IN USE           222 12472           936         15
                      P201        IN USE           223 12474           838         13
                      P202        IN USE           224 12476           884         13
                      P203        IN USE           225 12478           918         15
                      P204        IN USE           226 12480           842         13
                      P205        IN USE           227 12482           941         15
                      P206        IN USE           228 12484           851         13
                      P207        IN USE           229 12486           870         13
                      P208        IN USE           230 12488           950         15
                      P209        IN USE           231 12490           927         15
                      P210        IN USE           232 12492           943         15
                      P211        IN USE           233 12494           830         13
                      P212        IN USE           234 12496           853         13
                      P213        IN USE           235 12498           940         15
                      P214        IN USE           236 12500           894         15
                      P215        IN USE           237 12502           867         13
                      P216        IN USE           238 12504           949         15
                      P217        IN USE           239 12506           848         13
                      P218        IN USE           240 12508           951         15
                      P219        IN USE           241 12510           904         15
                      P220        IN USE           242 12512           906         15
                      P221        IN USE           243 12514           897         15
                      P222        IN USE           244 12516           944         15
                      P223        IN USE           245 12518           845         13
                      P224        IN USE           246 12520           860         13
                      P225        IN USE           247 12522           873         13
                      P226        IN USE           248 12524           874         13
                      P227        IN USE           249 12526           866         13
                      P228        IN USE           250 12528           843         13
                      P229        IN USE           251 12530           892         15
                      P230        IN USE           252 12532           925         15
                      P231        IN USE           253 12534           902         15
                      P232        IN USE           254 12536           934         15
                      P233        IN USE           255 12538           908         15
                      P234        IN USE           256 12540           891         15
                      P235        IN USE           257 12542           899         15
                      P236        IN USE           258 12544           862         13
                      P237        IN USE           259 12546           859         13
                      P238        IN USE           260 12548           857         13
                      P239        IN USE           261 12550           947         15
                      P240        IN USE           262 12552           881         13
                      P241        IN USE           263 12554           872         13
                      P242        IN USE           264 12556           837         13
                      P243        IN USE           265 12558           928         15
                      P244        IN USE           266 12560           840         13
                      P245        IN USE           267 12562           865         13
                      P246        IN USE           268 12564           903         15
                      P247        IN USE           269 12566           945         15
                      P248        IN USE           270 12568           879         13
                      P249        IN USE           271 12570           895         15
                      P250        IN USE           272 12572           887         13
                      P251        IN USE           273 12574           935         15
                      P252        IN USE           274 12576           930         15
                      P253        IN USE           275 12578           909         15
                      P254        IN USE           276 12580           889         13
                      P255        IN USE           277 12582           846         13
                      P256        AVAILABLE        278 12584               
                      P257        AVAILABLE        279 12586               
                      P258        AVAILABLE        280 12588               
                      P259        AVAILABLE        281 12590               
                      P260        AVAILABLE        282 12592               
                      P261        AVAILABLE        283 12594               
                      P262        AVAILABLE        284 12596               
                      P263        AVAILABLE        285 12598               
                      P264        AVAILABLE        286 12600               
                      P265        AVAILABLE        287 12602               
                      P266        AVAILABLE        288 12604               
                      P267        AVAILABLE        289 12606               
                      P268        AVAILABLE        290 12608               
                      P269        AVAILABLE        291 12610               
                      P270        AVAILABLE        292 12612               
                      P271        AVAILABLE        293 12614               
                      P272        AVAILABLE        294 12616               
                      P273        AVAILABLE        295 12618               
                      P274        AVAILABLE        296 12620               
                      P275        AVAILABLE        297 12622               
                      P276        AVAILABLE        298 12624               
                      P277        AVAILABLE        299 12626               
                      P278        AVAILABLE        300 12628               
                      P279        AVAILABLE        301 12630               
                      P280        AVAILABLE        302 12632               
                      P281        AVAILABLE        303 12634               
                      P282        AVAILABLE        304 12636               
                      P283        AVAILABLE        305 12638               
                      P284        AVAILABLE        306 12640               
                      P285        AVAILABLE        307 12642               
                      P286        AVAILABLE        308 12645               
                      P287        AVAILABLE        309 12647               
                      P288        AVAILABLE        310 12649               
                      P289        AVAILABLE        311 12652               
                      P290        AVAILABLE        312 12657               
                      P291        AVAILABLE        313 12663               
                      P292        AVAILABLE        314 12665               
                      P293        AVAILABLE        315 12667               
                      P294        AVAILABLE        316 12669               
                      P295        AVAILABLE        317 12671               
                      P296        AVAILABLE        318 12673               
                      P297        AVAILABLE        319 12675               
                      P298        AVAILABLE        320 12677               
                      P299        AVAILABLE        321 12679               
                      • 38. Re: Help with Parallelization on our SPARC server...we are drowning!
                        724046
                        SELECT decode(px.qcinst_id,
                                      null,
                                      username,
                                      ' - ' || lower(substr(s.program, length(s.program) - 4, 4))) "Username",
                               decode(px.qcinst_id, null, 'QC', '(Slave)') "QC/Slave",
                               to_char(px.server_set) "Slave Set",
                               to_char(s.sid) "SID",
                               decode(px.qcinst_id, null, to_char(s.sid), px.qcsid) "QC SID",
                               px.req_degree "Req DOP",
                               px.degree "Actual DOP"
                          FROM v$px_session px, v$session s
                         WHERE px.sid = s.sid(+)
                           and px.serial# = s.serial#
                         order by 5, 1 desc;
                         
                        Username   QC/Slave Slave Set                                SID    QC SID Req DOP Actual
                        ---------- -------- ---------------------------------------- ------ ------ ------- -------
                        GILLRAY    QC                                                1091   1091           
                         - p125    (Slave)  2                                        1028   1091        64      64
                         - p122    (Slave)  2                                        978    1091        64      64
                         - p119    (Slave)  2                                        1049   1091        64      64
                         - p118    (Slave)  2                                        1005   1091        64      64
                         - p116    (Slave)  2                                        965    1091        64      64
                         - p115    (Slave)  2                                        1038   1091        64      64
                         - p113    (Slave)  2                                        1036   1091        64      64
                         - p112    (Slave)  2                                        1040   1091        64      64
                         - p111    (Slave)  2                                        1071   1091        64      64
                         - p110    (Slave)  2                                        995    1091        64      64
                         - p109    (Slave)  2                                        990    1091        64      64
                         - p107    (Slave)  2                                        1034   1091        64      64
                         - p105    (Slave)  2                                        988    1091        64      64
                         - p100    (Slave)  2                                        981    1091        64      64
                         - p099    (Slave)  2                                        976    1091        64      64
                         - p098    (Slave)  2                                        984    1091        64      64
                         - p097    (Slave)  2                                        1073   1091        64      64
                         - p093    (Slave)  2                                        971    1091        64      64
                         - p089    (Slave)  2                                        983    1091        64      64
                         - p085    (Slave)  2                                        1031   1091        64      64
                         - p084    (Slave)  2                                        979    1091        64      64
                         - p082    (Slave)  2                                        1001   1091        64      64
                         - p081    (Slave)  2                                        1053   1091        64      64
                         - p080    (Slave)  2                                        1046   1091        64      64
                         - p078    (Slave)  2                                        970    1091        64      64
                         - p076    (Slave)  2                                        1047   1091        64      64
                         - p073    (Slave)  2                                        996    1091        64      64
                         - p070    (Slave)  2                                        1011   1091        64      64
                         - p069    (Slave)  2                                        1065   1091        64      64
                         - p068    (Slave)  2                                        960    1091        64      64
                         - p067    (Slave)  2                                        1066   1091        64      64
                         - p066    (Slave)  2                                        994    1091        64      64
                         - p065    (Slave)  2                                        1026   1091        64      64
                         - p064    (Slave)  2                                        986    1091        64      64
                         - p063    (Slave)  1                                        1027   1091        64      64
                         - p061    (Slave)  1                                        1008   1091        64      64
                         - p060    (Slave)  1                                        959    1091        64      64
                         - p059    (Slave)  1                                        1032   1091        64      64
                         - p057    (Slave)  1                                        1002   1091        64      64
                         - p055    (Slave)  1                                        999    1091        64      64
                         - p054    (Slave)  1                                        966    1091        64      64
                         - p053    (Slave)  1                                        975    1091        64      64
                         - p052    (Slave)  1                                        1039   1091        64      64
                         - p051    (Slave)  1                                        1007   1091        64      64
                         - p050    (Slave)  1                                        997    1091        64      64
                         - p049    (Slave)  1                                        1070   1091        64      64
                         - p048    (Slave)  1                                        1057   1091        64      64
                         - p047    (Slave)  1                                        1064   1091        64      64
                         - p046    (Slave)  1                                        968    1091        64      64
                         - p045    (Slave)  1                                        1019   1091        64      64
                         - p044    (Slave)  1                                        954    1091        64      64
                         - p041    (Slave)  1                                        1035   1091        64      64
                         - p040    (Slave)  1                                        1055   1091        64      64
                         - p039    (Slave)  1                                        963    1091        64      64
                         - p038    (Slave)  1                                        1060   1091        64      64
                         - p037    (Slave)  1                                        989    1091        64      64
                         - p036    (Slave)  1                                        1061   1091        64      64
                         - p034    (Slave)  1                                        980    1091        64      64
                         - p033    (Slave)  1                                        1033   1091        64      64
                         - p032    (Slave)  1                                        1021   1091        64      64
                         - p031    (Slave)  1                                        967    1091        64      64
                         - p030    (Slave)  1                                        969    1091        64      64
                         - p029    (Slave)  1                                        1075   1091        64      64
                         - p028    (Slave)  1                                        1056   1091        64      64
                         - p027    (Slave)  1                                        1023   1091        64      64
                         - p026    (Slave)  1                                        1024   1091        64      64
                         - p024    (Slave)  1                                        958    1091        64      64
                         - p023    (Slave)  1                                        1022   1091        64      64
                         - p022    (Slave)  1                                        1017   1091        64      64
                         - p021    (Slave)  1                                        1078   1091        64      64
                         - p019    (Slave)  1                                        993    1091        64      64
                         - p018    (Slave)  1                                        956    1091        64      64
                         - p017    (Slave)  1                                        972    1091        64      64
                         - p016    (Slave)  1                                        1044   1091        64      64
                         - p015    (Slave)  1                                        1054   1091        64      64
                         - p014    (Slave)  1                                        1012   1091        64      64
                         - p013    (Slave)  1                                        1009   1091        64      64
                         - p012    (Slave)  1                                        1072   1091        64      64
                         - p011    (Slave)  1                                        1016   1091        64      64
                         - p010    (Slave)  1                                        964    1091        64      64
                         - p009    (Slave)  1                                        1020   1091        64      64
                         - p008    (Slave)  1                                        973    1091        64      64
                         - p007    (Slave)  1                                        1043   1091        64      64
                         - p006    (Slave)  1                                        1058   1091        64      64
                         - p005    (Slave)  1                                        992    1091        64      64
                         - p004    (Slave)  1                                        1076   1091        64      64
                         - p003    (Slave)  1                                        1059   1091        64      64
                         - p001    (Slave)  1                                        957    1091        64      64
                         - p000    (Slave)  1                                        1077   1091        64      64
                        
                        select
                          sw.SID as RCVSID,
                          decode(pp.server_name,
                                 NULL, 'A QC',
                                 pp.server_name) as RCVR,
                          sw.inst_id as RCVRINST,
                        case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
                          decode(bitand(p1, 65535),
                                 65535, 'QC',
                                 'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,
                          bitand(p1, 16711680) - 65535 as SNDRINST,
                          decode(bitand(p1, 65535),
                                 65535, ps.qcsid,
                                 (select
                                    sid
                                  from
                                    gv$px_process
                                  where
                                    server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and
                                    inst_id = bitand(sw.p1, 16711680) - 65535)
                                ) as SNDRSID,
                           decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE
                        from
                          gv$session_wait sw,
                          gv$px_process pp,
                          gv$px_session ps
                        where
                          sw.sid = pp.sid (+) and
                          sw.inst_id = pp.inst_id (+) and
                          sw.sid = ps.sid (+) and
                          sw.inst_id = ps.inst_id (+) and
                          p1text  = 'sleeptime/senderid' and
                          bitand(p1, 268435456) = 268435456
                        order by
                          decode(ps.QCINST_ID,  NULL, ps.INST_ID,  ps.QCINST_ID),
                          ps.QCSID,
                          decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),
                          ps.SERVER_SET,
                          ps.INST_ID;
                         
                            RCVSID RCVR   RCVRINST WAIT_EVENT                     SNDR    SNDRINST    SNDRSID STATE
                        ---------- ---- ---------- ------------------------------ ----- ---------- ---------- --------
                               959 P000          1                                QC             1       1091 NOT WAIT
                              1036 P063          1                                QC             1       1091 NOT WAIT
                               963 P002          1                                QC             1       1091 NOT WAIT
                              1023 P003          1                                QC             1       1091 NOT WAIT
                               999 P004          1                                QC             1       1091 NOT WAIT
                               966 P005          1                                QC             1       1091 NOT WAIT
                              1017 P006          1                                QC             1       1091 NOT WAIT
                              1055 P007          1                                QC             1       1091 NOT WAIT
                               958 P008          1                                QC             1       1091 NOT WAIT
                              1077 P009          1                                QC             1       1091 NOT WAIT
                              1064 P010          1                                QC             1       1091 NOT WAIT
                               954 P011          1                                QC             1       1091 NOT WAIT
                              1008 P012          1                                QC             1       1091 NOT WAIT
                              1032 P013          1                                QC             1       1091 NOT WAIT
                              1058 P014          1                                QC             1       1091 NOT WAIT
                              1033 P015          1                                QC             1       1091 NOT WAIT
                              1019 P016          1                                QC             1       1091 NOT WAIT
                              1022 P017          1                                QC             1       1091 NOT WAIT
                               993 P018          1                                QC             1       1091 NOT WAIT
                              1059 P019          1                                QC             1       1091 NOT WAIT
                              1035 P020          1                                QC             1       1091 NOT WAIT
                              1072 P021          1                                QC             1       1091 NOT WAIT
                               975 P022          1                                QC             1       1091 NOT WAIT
                               967 P023          1                                QC             1       1091 NOT WAIT
                               989 P024          1                                QC             1       1091 NOT WAIT
                               956 P025          1                                QC             1       1091 NOT WAIT
                               957 P026          1                                QC             1       1091 NOT WAIT
                              1012 P027          1                                QC             1       1091 NOT WAIT
                               997 P028          1                                QC             1       1091 NOT WAIT
                              1044 P029          1                                QC             1       1091 NOT WAIT
                              1021 P030          1                                QC             1       1091 NOT WAIT
                              1027 P031          1                                QC             1       1091 NOT WAIT
                              1007 P032          1                                QC             1       1091 NOT WAIT
                               969 P033          1                                QC             1       1091 NOT WAIT
                              1061 P034          1                                QC             1       1091 NOT WAIT
                              1028 P035          1                                QC             1       1091 NOT WAIT
                              1002 P036          1                                QC             1       1091 NOT WAIT
                              1060 P037          1                                QC             1       1091 NOT WAIT
                              1011 P038          1                                QC             1       1091 NOT WAIT
                              1020 P039          1                                QC             1       1091 NOT WAIT
                               960 P040          1                                QC             1       1091 NOT WAIT
                              1065 P041          1                                QC             1       1091 NOT WAIT
                               964 P042          1                                QC             1       1091 NOT WAIT
                              1057 P043          1                                QC             1       1091 NOT WAIT
                              1024 P044          1                                QC             1       1091 NOT WAIT
                              1076 P045          1                                QC             1       1091 NOT WAIT
                              1054 P046          1                                QC             1       1091 NOT WAIT
                              1075 P047          1                                QC             1       1091 NOT WAIT
                              1009 P048          1                                QC             1       1091 NOT WAIT
                              1078 P049          1                                QC             1       1091 NOT WAIT
                               965 P050          1                                QC             1       1091 NOT WAIT
                              1049 P051          1                                QC             1       1091 NOT WAIT
                              1047 P052          1                                QC             1       1091 NOT WAIT
                              1056 P053          1                                QC             1       1091 NOT WAIT
                              1016 P054          1                                QC             1       1091 NOT WAIT
                              1071 P055          1                                QC             1       1091 NOT WAIT
                               984 P056          1                                QC             1       1091 NOT WAIT
                              1070 P057          1                                QC             1       1091 NOT WAIT
                              1005 P058          1                                QC             1       1091 NOT WAIT
                               980 P059          1                                QC             1       1091 NOT WAIT
                              1039 P060          1                                QC             1       1091 NOT WAIT
                              1038 P061          1                                QC             1       1091 NOT WAIT
                              1034 P062          1                                QC             1       1091 NOT WAIT
                              1043 P001          1                                QC             1       1091 NOT WAIT
                               968 P064          1                                QC             1       1091 NOT WAIT
                              1066 P065          1                                QC             1       1091 NOT WAIT
                              1040 P066          1                                QC             1       1091 NOT WAIT
                               990 P067          1                                QC             1       1091 NOT WAIT
                              1046 P068          1                                QC             1       1091 NOT WAIT
                              1031 P069          1                                QC             1       1091 NOT WAIT
                              1053 P070          1                                QC             1       1091 NOT WAIT
                              1073 P071          1                                QC             1       1091 NOT WAIT
                               988 P072          1                                QC             1       1091 NOT WAIT
                              1001 P073          1                                QC             1       1091 NOT WAIT
                               995 P074          1                                QC             1       1091 NOT WAIT
                               992 P075          1                                QC             1       1091 NOT WAIT
                              1062 P076          1                                QC             1       1091 NOT WAIT
                               986 P077          1                                QC             1       1091 NOT WAIT
                               981 P078          1                                QC             1       1091 NOT WAIT
                               979 P079          1                                QC             1       1091 NOT WAIT
                              1051 P080          1                                QC             1       1091 NOT WAIT
                               983 P081          1                                QC             1       1091 NOT WAIT
                              1068 P082          1                                QC             1       1091 NOT WAIT
                              1041 P083          1                                QC             1       1091 NOT WAIT
                              1042 P084          1                                QC             1       1091 NOT WAIT
                              1074 P085          1                                QC             1       1091 NOT WAIT
                              1063 P086          1                                QC             1       1091 NOT WAIT
                              1026 P087          1                                QC             1       1091 NOT WAIT
                               994 P088          1                                QC             1       1091 NOT WAIT
                               996 P089          1                                QC             1       1091 NOT WAIT
                               976 P090          1                                QC             1       1091 NOT WAIT
                               973 P091          1                                QC             1       1091 NOT WAIT
                              1006 P092          1                                QC             1       1091 NOT WAIT
                               970 P093          1                                QC             1       1091 NOT WAIT
                               971 P094          1                                QC             1       1091 NOT WAIT
                              1045 P095          1                                QC             1       1091 NOT WAIT
                               978 P096          1                                QC             1       1091 NOT WAIT
                               972 P097          1                                QC             1       1091 NOT WAIT
                              1029 P098          1                                QC             1       1091 NOT WAIT
                              1081 P099          1                                QC             1       1091 NOT WAIT
                               987 P100          1                                QC             1       1091 NOT WAIT
                              1000 P101          1                                QC             1       1091 NOT WAIT
                              1052 P102          1                                QC             1       1091 NOT WAIT
                               982 P103          1                                QC             1       1091 NOT WAIT
                              1030 P104          1                                QC             1       1091 NOT WAIT
                              1069 P105          1                                QC             1       1091 NOT WAIT
                               998 P106          1                                QC             1       1091 NOT WAIT
                              1079 P107          1                                QC             1       1091 NOT WAIT
                              1025 P108          1                                QC             1       1091 NOT WAIT
                               961 P109          1                                QC             1       1091 NOT WAIT
                              1082 P110          1                                QC             1       1091 NOT WAIT
                               974 P111          1                                QC             1       1091 NOT WAIT
                              1050 P112          1                                QC             1       1091 NOT WAIT
                              1003 P113          1                                QC             1       1091 NOT WAIT
                               977 P114          1                                QC             1       1091 NOT WAIT
                               985 P115          1                                QC             1       1091 NOT WAIT
                              1015 P116          1                                QC             1       1091 NOT WAIT
                               955 P117          1                                QC             1       1091 NOT WAIT
                              1013 P118          1                                QC             1       1091 NOT WAIT
                              1048 P119          1                                QC             1       1091 NOT WAIT
                              1037 P120          1                                QC             1       1091 NOT WAIT
                              1004 P121          1                                QC             1       1091 NOT WAIT
                              1080 P122          1                                QC             1       1091 NOT WAIT
                              1014 P123          1                                QC             1       1091 NOT WAIT
                               962 P124          1 PX Deq: Execution Msg          QC             1       1091 WAIT
                              1010 P125          1                                QC             1       1091 NOT WAIT
                               991 P126          1                                QC             1       1091 NOT WAIT
                              1067 P127          1                                QC             1       1091 NOT WAIT
                         
                        • 39. Re: Help with Parallelization on our SPARC server...we are drowning!
                          Taral
                          I can't understand what are you trying to say here. But 300 parallel you might nedd 64 or more CPU box to scale with that.

                          Anyways, your test doesn't tell anything about performance. If your real issue is that you are not getting DOP and downgraded to serial then you should consider lowering your parallel queries.

                          Tell us the SQL which you are using not to see slaves the actual SQL which is causing problem along with plan
                          • 40. Re: Help with Parallelization on our SPARC server...we are drowning!
                            724046
                            OK, let me try again.

                            Here is an example of one of the queries. I am running an explain plan as well under the PARALLEL_MAX_SERVERS=300 and then 64 (which is the current setting). Currently there is no DOP set on any objects. I will post the SQL and explain plan for that. In my next post I will do it with DOP as DEFAULT on the objects that are most commonly used and largest in size(not that many).
                            select 'OP_TERM_RES' METRIC_TYPE
                                  ,sum(STG_CNT) as STG_CNT_LOAD_&stg_load_id
                                  ,sum(DLI_CNT) as DLI_CNT_LOAD_&dli_load_id
                                  ,sum(ADW_CNT) as ADW_CNT_LOAD_&adw_load_id
                                  ,sum(STG_SUM) as STG_SUM_LOAD_&stg_load_id
                                  ,sum(DLI_SUM) as DLI_SUM_LOAD_&dli_load_id
                                  ,sum(ADW_SUM) as ADW_SUM_LOAD_&adw_load_id
                            from (       
                            select 0        as STG_CNT
                                  ,count(*) as DLI_CNT
                                  ,0        as ADW_CNT
                                  ,0        as STG_SUM
                                  ,sum(tx.JH_ILI_RES_OPENING_METRIC_AMT) as DLI_SUM
                                  ,0        as ADW_SUM
                              from (select JH_ILI_METRIC_TYPE_OBJ_ID
                                      from fdss_dli.JH_EDW_DLI_ILI_METRIC_TYPE 
                                      where JH_ILI_METRIC_TYPE_CODE = 'TERM_RES') m
                                   ,(select ILI_POL_EDW_ID from fdss_dli.EDW_DLI_ILI_POL p 
                                       where ILI_POL_FOREX_CURCY_CODE = 'USD') p
                                  ,fdss_dli.EDW_DLI_ILI_COV cv
                                  ,fdss_dli.EDW_DLI_ILI_CESN cs 
                                  ,fdss_dli.JH_EDW_DLI_ILI_RES r 
                                  ,fdss_dli.JH_EDW_DLI_ILI_RES_TX tx
                              where m.JH_ILI_METRIC_TYPE_OBJ_ID=tx.JH_ILI_METRIC_TYPE_OBJ_ID 
                                and tx.JH_ILI_RES_EDW_ID=r.JH_ILI_RES_EDW_ID 
                                and r.ILI_CESN_EDW_ID=cs.ILI_CESN_EDW_ID 
                                and cs.ILI_COV_EDW_ID=cv.ILI_COV_EDW_ID 
                                and cv.ILI_POL_EDW_ID=p.ILI_POL_EDW_ID 
                                and tx.load_id=&dli_load_id 
                            union all
                            select 0        as STG_CNT
                                  ,0        as DLI_CNT
                                  ,count(*) as ADW_CNT
                                  ,0        as STG_SUM
                                  ,0        as DLI_SUM
                                  ,SUM(rf.JH_ILI_RES_OPENING_METRIC_AMT) as ADW_SUM
                              from fdss_adw_vad.JH_LKP_ILI_METRIC_TYPE m
                                  ,fdss_adw_vad.JH_FACT_ILI_RES rf
                                   ,fdss_adw_vad.JH_LKP_ILI_RES r
                                   ,fdss_adw_vad.LKP_ILI_CESN cs
                                   ,fdss_adw_vad.LKP_ILI_COV cv
                                   ,(select ILI_POL_ADW_ID 
                                       from fdss_adw_vad.LKP_ILI_POL p, fdss_adw_vad.LKP_ILI_FOREX_CURCY f
                                       where p.ILI_FOREX_CURCY_ADW_ID=f.ILI_FOREX_CURCY_ADW_ID
                                            and f.ILI_FOREX_CURCY_CODE='USD') p
                              where m.JH_ILI_METRIC_TYPE_ADW_ID=rf.JH_ILI_METRIC_TYPE_ADW_ID 
                                and p.ILI_POL_ADW_ID=cv.ILI_POL_ADW_ID
                                 and cv.ILI_COV_ADW_ID=cs.ILI_COV_ADW_ID
                                 and cs.ILI_CESN_ADW_ID=r.ILI_CESN_ADW_ID
                                 and r.JH_ILI_RES_ADW_ID=rf.JH_ILI_RES_ADW_ID 
                                and rf.load_id=&adw_load_id 
                                 and m.JH_ILI_METRIC_TYPE_CODE = 'TERM_RES'
                            union all
                            select count(*)      as STG_CNT
                                  ,0             as DLI_CNT
                                  ,0             as ADW_CNT
                                  ,SUM(OP_TERM_RES) as STG_SUM
                                  ,0             as DLI_SUM
                                  ,0             as ADW_SUM
                              from fdss_stg.STG_VN0003_POL p
                                  ,fdss_stg.STG_VN0004_RES v
                              where p.polno=v.polno
                                and v.load_id=p.load_id
                                and v.load_id=&stg_load_id 
                                and p.GL_CURR='USD')
                            group by 'OP_TERM_RES'               
                             order by 1 ;
                            • 41. Re: Help with Parallelization on our SPARC server...we are drowning!
                              724046
                              PARALLEL_MAX_SERVERS=64, NO DOP
                              SQL> @BAL_ITEM.sql;
                              METRIC_TYPE STG_CNT_LOAD_47 DLI_CNT_LOAD_48 ADW_CNT_LOAD_49 STG_SUM_LOAD_47     
                              ----------- --------------- --------------- --------------- ---------------     
                              DLI_SUM_LOAD_48 ADW_SUM_LOAD_49                                                 
                              --------------- ---------------                                                 
                              OP_TERM_RES          923701          923701          923701      7665157850     
                                   7665157850      7665157850                                                 
                              
                              Elapsed: 00:02:32.99
                              
                              Execution Plan
                              ----------------------------------------------------------                      
                              Plan hash value: 473001301                                                      
                                                                                                              
                              --------------------------------------------------------------------------------
                              -------------------------------------------------                               
                                                                                                              
                              | Id  | Operation                                  | Name                       
                              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                               
                                                                                                              
                              --------------------------------------------------------------------------------
                              -------------------------------------------------                               
                                                                                                              
                              |   0 | SELECT STATEMENT                           |                            
                              |     3 |   234 |       |   109K (31)| 00:01:48 |                               
                                                                                                              
                              |   1 |  SORT GROUP BY NOSORT                      |                            
                              |     3 |   234 |       |   109K (31)| 00:01:48 |                               
                                                                                                              
                              |   2 |   VIEW                                     |                            
                              |     3 |   234 |       |   109K (31)| 00:01:48 |                               
                                                                                                              
                              |   3 |    UNION-ALL                               |                            
                              |       |       |       |            |          |                               
                                                                                                              
                              |   4 |     SORT AGGREGATE                         |                            
                              |     1 |    81 |       |            |          |                               
                                                                                                              
                              |*  5 |      HASH JOIN                             |                            
                              | 15308 |  1210K|       | 36788  (34)| 00:00:37 |                               
                                                                                                              
                              |*  6 |       TABLE ACCESS FULL                    | EDW_DLI_ILI_POL            
                              | 16117 |   157K|       |  5972  (20)| 00:00:06 |                               
                                                                                                              
                              |*  7 |       HASH JOIN                            |                            
                              |   260K|    17M|    13M| 30732  (36)| 00:00:31 |                               
                                                                                                              
                              |   8 |        INDEX FULL SCAN                     | X_EDW_DLI_ILI_COV_2        
                              |   588K|  6896K|       |    81  (45)| 00:00:01 |                               
                                                                                                              
                              |*  9 |        HASH JOIN                           |                            
                              |   260K|    14M|    14M| 29114  (36)| 00:00:29 |                               
                                                                                                              
                              |* 10 |         HASH JOIN                          |                            
                              |   260K|    11M|    11M| 16927  (45)| 00:00:17 |                               
                                                                                                              
                              |  11 |          TABLE ACCESS BY INDEX ROWID       | JH_EDW_DLI_ILI_RES_TX      
                              |   260K|  4320K|       |  6856  (47)| 00:00:07 |                               
                                                                                                              
                              |  12 |           NESTED LOOPS                     |                            
                              |   260K|  8894K|       |  6856  (47)| 00:00:07 |                               
                                                                                                              
                              |* 13 |            TABLE ACCESS FULL               | JH_EDW_DLI_ILI_METRIC_TYPE 
                              |     1 |    18 |       |     5   (0)| 00:00:01 |                               
                                                                                                              
                              |  14 |            BITMAP CONVERSION TO ROWIDS     |                            
                              |       |       |       |            |          |                               
                                                                                                              
                              |  15 |             BITMAP AND                     |                            
                              |       |       |       |            |          |                               
                                                                                                              
                              |* 16 |              BITMAP INDEX SINGLE VALUE     | X_JH_EDW_DLI_ILI_RES_TX_1  
                              |       |       |       |            |          |                               
                                                                                                              
                              |  17 |              BITMAP CONVERSION FROM ROWIDS |                            
                              |       |       |       |            |          |                               
                                                                                                              
                              |* 18 |               INDEX RANGE SCAN             | X_JH_EDW_DLI_ILI_RES_TX_2  
                              |  2862K|       |       |   333  (54)| 00:00:01 |                               
                                                                                                              
                              |  19 |          INDEX FULL SCAN                   | X_JH_EDW_DLI_ILI_RES_2     
                              |  6139K|    70M|       |   845  (46)| 00:00:01 |                               
                                                                                                              
                              |  20 |         TABLE ACCESS FULL                  | EDW_DLI_ILI_CESN           
                              |  1616K|    18M|       |  9255  (19)| 00:00:10 |                               
                                                                                                              
                              |  21 |     SORT AGGREGATE                         |                            
                              |     1 |    91 |       |            |          |                               
                                                                                                              
                              |* 22 |      HASH JOIN                             |                            
                              | 14458 |  1284K|       | 69715  (29)| 00:01:09 |                               
                                                                                                              
                              |* 23 |       TABLE ACCESS FULL                    | LKP_ILI_FOREX_CURCY        
                              |     1 |     9 |       |     4   (0)| 00:00:01 |                               
                                                                                                              
                              |* 24 |       HASH JOIN                            |                            
                              |   260K|    20M|  6176K| 69633  (29)| 00:01:09 |                               
                                                                                                              
                              |  25 |        TABLE ACCESS FULL                   | LKP_ILI_POL                
                              |   273K|  2943K|       |  2986  (23)| 00:00:03 |                               
                                                                                                              
                              |* 26 |        HASH JOIN                           |                            
                              |   260K|    17M|    13M| 65412  (30)| 00:01:05 |                               
                                                                                                              
                              |  27 |         TABLE ACCESS FULL                  | LKP_ILI_COV                
                              |   588K|  6896K|       |  6295  (46)| 00:00:07 |                               
                                                                                                              
                              |* 28 |         HASH JOIN                          |                            
                              |   260K|    14M|    14M| 57580  (28)| 00:00:57 |                               
                                                                                                              
                              |* 29 |          HASH JOIN                         |                            
                              |   260K|    11M|    11M| 47363  (27)| 00:00:47 |                               
                                                                                                              
                              |  30 |           TABLE ACCESS BY INDEX ROWID      | JH_FACT_ILI_RES            
                              |   260K|  4320K|       |  6974  (47)| 00:00:07 |                               
                                                                                                              
                              |  31 |            NESTED LOOPS                    |                            
                              |   260K|  8894K|       |  6974  (47)| 00:00:07 |                               
                                                                                                              
                              |* 32 |             TABLE ACCESS FULL              | JH_LKP_ILI_METRIC_TYPE     
                              |     1 |    18 |       |     4   (0)| 00:00:01 |                               
                                                                                                              
                              |  33 |             BITMAP CONVERSION TO ROWIDS    |                            
                              |       |       |       |            |          |                               
                                                                                                              
                              |  34 |              BITMAP AND                    |                            
                              |       |       |       |            |          |                               
                                                                                                              
                              |* 35 |               BITMAP INDEX SINGLE VALUE    | X_JH_FACT_ILI_RES_1        
                              |       |       |       |            |          |                               
                                                                                                              
                              |  36 |               BITMAP CONVERSION FROM ROWIDS|                            
                              |       |       |       |            |          |                               
                                                                                                              
                              |* 37 |                INDEX RANGE SCAN            | X_JH_FACT_ILI_RES_2        
                              |  2862K|       |       |   333  (54)| 00:00:01 |                               
                                                                                                              
                              |  38 |           TABLE ACCESS FULL                | JH_LKP_ILI_RES             
                              |  6139K|    70M|       | 31163  (18)| 00:00:31 |                               
                                                                                                              
                              |  39 |          TABLE ACCESS FULL                 | LKP_ILI_CESN               
                              |  1616K|    18M|       |  7284  (26)| 00:00:08 |                               
                                                                                                              
                              |  40 |     SORT AGGREGATE                         |                            
                              |     1 |    32 |       |            |          |                               
                                                                                                              
                              |* 41 |      HASH JOIN                             |                            
                              |   294K|  9192K|  3328K|  2899  (43)| 00:00:03 |                               
                                                                                                              
                              |* 42 |       TABLE ACCESS BY INDEX ROWID          | STG_VN0003_POL             
                              |   120K|  1884K|       |   453  (45)| 00:00:01 |                               
                                                                                                              
                              |* 43 |        INDEX RANGE SCAN                    | X_STG_VN0003_POL_1         
                              |   132K|       |       |    14  (58)| 00:00:01 |                               
                                                                                                              
                              |  44 |       TABLE ACCESS BY INDEX ROWID          | STG_VN0004_RES             
                              |   602K|  9411K|       |  1338  (45)| 00:00:02 |                               
                                                                                                              
                              |* 45 |        INDEX RANGE SCAN                    | X_STG_VN0004_RES_1         
                              |   602K|       |       |    63  (59)| 00:00:01 |                               
                                                                                                              
                              --------------------------------------------------------------------------------
                              -------------------------------------------------                                                                                                            
                                                                                                              
                              Predicate Information (identified by operation id):                             
                              ---------------------------------------------------                             
                                                                                                              
                                 5 - access("CV"."ILI_POL_EDW_ID"="ILI_POL_EDW_ID")                           
                                 6 - filter("ILI_POL_FOREX_CURCY_CODE"='USD')                                 
                                 7 - access("CS"."ILI_COV_EDW_ID"="CV"."ILI_COV_EDW_ID")                      
                                 9 - access("R"."ILI_CESN_EDW_ID"="CS"."ILI_CESN_EDW_ID")                     
                                10 - access("TX"."JH_ILI_RES_EDW_ID"="R"."JH_ILI_RES_EDW_ID")                 
                                13 - filter("JH_ILI_METRIC_TYPE_CODE"='TERM_RES')                             
                                16 - access("TX"."LOAD_ID"=48)                                                
                                18 - access("JH_ILI_METRIC_TYPE_OBJ_ID"="TX"."JH_ILI_METRIC_TYPE_OBJ_ID")     
                                22 - access("P"."ILI_FOREX_CURCY_ADW_ID"="F"."ILI_FOREX_CURCY_ADW_ID")        
                                23 - filter("F"."ILI_FOREX_CURCY_CODE"='USD')                                 
                                24 - access("ILI_POL_ADW_ID"="CV"."ILI_POL_ADW_ID")                           
                                26 - access("CV"."ILI_COV_ADW_ID"="CS"."ILI_COV_ADW_ID")                      
                                28 - access("CS"."ILI_CESN_ADW_ID"="R"."ILI_CESN_ADW_ID")                     
                                29 - access("R"."JH_ILI_RES_ADW_ID"="RF"."JH_ILI_RES_ADW_ID")                 
                                32 - filter("M"."JH_ILI_METRIC_TYPE_CODE"='TERM_RES')                         
                                35 - access("RF"."LOAD_ID"=49)                                                
                                37 - access("M"."JH_ILI_METRIC_TYPE_ADW_ID"="RF"."JH_ILI_METRIC_TYPE_ADW_ID") 
                                41 - access("P"."POLNO"="V"."POLNO" AND "V"."LOAD_ID"="P"."LOAD_ID")          
                                42 - filter("P"."GL_CURR"='USD')                                              
                                43 - access("P"."LOAD_ID"=47)                                                 
                                45 - access("V"."LOAD_ID"=47)                                                 
                                                                                                              
                              Note                                                                            
                              -----                                                                           
                                 - dynamic sampling used for this statement                                   
                              
                              Statistics
                              ----------------------------------------------------------                      
                                     3456  recursive calls                                                    
                                        0  db block gets                                                      
                                   208182  consistent gets                                                    
                                   207795  physical reads                                                     
                                     1188  redo size                                                          
                                     1001  bytes sent via SQL*Net to client                                   
                                     1191  bytes received via SQL*Net from client                             
                                        2  SQL*Net roundtrips to/from client                                  
                                       30  sorts (memory)                                                     
                                        0  sorts (disk)                                                       
                                        1  rows processed                                                     
                              PARALLEL_MAX_SERVERS=300, NO DOP
                              SQL> @BAL_ITEM.sql;
                              METRIC_TYPE STG_CNT_LOAD_47 DLI_CNT_LOAD_48 ADW_CNT_LOAD_49 STG_SUM_LOAD_47
                              ----------- --------------- --------------- --------------- ---------------
                              DLI_SUM_LOAD_48 ADW_SUM_LOAD_49
                              --------------- ---------------
                              OP_TERM_RES          923701          923701          923701      7665157850
                                   7665157850      7665157850
                              
                              Elapsed: 00:02:22.26
                              
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 473001301
                              
                              --------------------------------------------------------------------------------
                              -------------------------------------------------
                              
                              | Id  | Operation                                  | Name
                              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                              
                              --------------------------------------------------------------------------------
                              -------------------------------------------------
                              
                              |   0 | SELECT STATEMENT                           |
                              |     3 |   234 |       |   109K (31)| 00:01:48 |
                              
                              |   1 |  SORT GROUP BY NOSORT                      |
                              |     3 |   234 |       |   109K (31)| 00:01:48 |
                              
                              |   2 |   VIEW                                     |
                              |     3 |   234 |       |   109K (31)| 00:01:48 |
                              
                              |   3 |    UNION-ALL                               |
                              |       |       |       |            |          |
                              
                              |   4 |     SORT AGGREGATE                         |
                              |     1 |    81 |       |            |          |
                              
                              |*  5 |      HASH JOIN                             |
                              | 15308 |  1210K|       | 36788  (34)| 00:00:37 |
                              
                              |*  6 |       TABLE ACCESS FULL                    | EDW_DLI_ILI_POL
                              | 16117 |   157K|       |  5972  (20)| 00:00:06 |
                              
                              |*  7 |       HASH JOIN                            |
                              |   260K|    17M|    13M| 30732  (36)| 00:00:31 |
                              
                              |   8 |        INDEX FULL SCAN                     | X_EDW_DLI_ILI_COV_2
                              |   588K|  6896K|       |    81  (45)| 00:00:01 |
                              
                              |*  9 |        HASH JOIN                           |
                              |   260K|    14M|    14M| 29114  (36)| 00:00:29 |
                              
                              |* 10 |         HASH JOIN                          |
                              |   260K|    11M|    11M| 16927  (45)| 00:00:17 |
                              
                              |  11 |          TABLE ACCESS BY INDEX ROWID       | JH_EDW_DLI_ILI_RES_TX
                              |   260K|  4320K|       |  6856  (47)| 00:00:07 |
                              
                              |  12 |           NESTED LOOPS                     |
                              |   260K|  8894K|       |  6856  (47)| 00:00:07 |
                              
                              |* 13 |            TABLE ACCESS FULL               | JH_EDW_DLI_ILI_METRIC_TYPE
                              |     1 |    18 |       |     5   (0)| 00:00:01 |
                              
                              |  14 |            BITMAP CONVERSION TO ROWIDS     |
                              |       |       |       |            |          |
                              
                              |  15 |             BITMAP AND                     |
                              |       |       |       |            |          |
                              
                              |* 16 |              BITMAP INDEX SINGLE VALUE     | X_JH_EDW_DLI_ILI_RES_TX_1
                              |       |       |       |            |          |
                              
                              |  17 |              BITMAP CONVERSION FROM ROWIDS |
                              |       |       |       |            |          |
                              
                              |* 18 |               INDEX RANGE SCAN             | X_JH_EDW_DLI_ILI_RES_TX_2
                              |  2862K|       |       |   333  (54)| 00:00:01 |
                              
                              |  19 |          INDEX FULL SCAN                   | X_JH_EDW_DLI_ILI_RES_2
                              |  6139K|    70M|       |   845  (46)| 00:00:01 |
                              
                              |  20 |         TABLE ACCESS FULL                  | EDW_DLI_ILI_CESN
                              |  1616K|    18M|       |  9255  (19)| 00:00:10 |
                              
                              |  21 |     SORT AGGREGATE                         |
                              |     1 |    91 |       |            |          |
                              
                              |* 22 |      HASH JOIN                             |
                              | 14458 |  1284K|       | 69715  (29)| 00:01:09 |
                              
                              |* 23 |       TABLE ACCESS FULL                    | LKP_ILI_FOREX_CURCY
                              |     1 |     9 |       |     4   (0)| 00:00:01 |
                              
                              |* 24 |       HASH JOIN                            |
                              |   260K|    20M|  6176K| 69633  (29)| 00:01:09 |
                              
                              |  25 |        TABLE ACCESS FULL                   | LKP_ILI_POL
                              |   273K|  2943K|       |  2986  (23)| 00:00:03 |
                              
                              |* 26 |        HASH JOIN                           |
                              |   260K|    17M|    13M| 65412  (30)| 00:01:05 |
                              
                              |  27 |         TABLE ACCESS FULL                  | LKP_ILI_COV
                              |   588K|  6896K|       |  6295  (46)| 00:00:07 |
                              
                              |* 28 |         HASH JOIN                          |
                              |   260K|    14M|    14M| 57580  (28)| 00:00:57 |
                              
                              |* 29 |          HASH JOIN                         |
                              |   260K|    11M|    11M| 47363  (27)| 00:00:47 |
                              
                              |  30 |           TABLE ACCESS BY INDEX ROWID      | JH_FACT_ILI_RES
                              |   260K|  4320K|       |  6974  (47)| 00:00:07 |
                              
                              |  31 |            NESTED LOOPS                    |
                              |   260K|  8894K|       |  6974  (47)| 00:00:07 |
                              
                              |* 32 |             TABLE ACCESS FULL              | JH_LKP_ILI_METRIC_TYPE
                              |     1 |    18 |       |     4   (0)| 00:00:01 |
                              
                              |  33 |             BITMAP CONVERSION TO ROWIDS    |
                              |       |       |       |            |          |
                              
                              |  34 |              BITMAP AND                    |
                              |       |       |       |            |          |
                              
                              |* 35 |               BITMAP INDEX SINGLE VALUE    | X_JH_FACT_ILI_RES_1
                              |       |       |       |            |          |
                              
                              |  36 |               BITMAP CONVERSION FROM ROWIDS|
                              |       |       |       |            |          |
                              
                              |* 37 |                INDEX RANGE SCAN            | X_JH_FACT_ILI_RES_2
                              |  2862K|       |       |   333  (54)| 00:00:01 |
                              
                              |  38 |           TABLE ACCESS FULL                | JH_LKP_ILI_RES
                              |  6139K|    70M|       | 31163  (18)| 00:00:31 |
                              
                              |  39 |          TABLE ACCESS FULL                 | LKP_ILI_CESN
                              |  1616K|    18M|       |  7284  (26)| 00:00:08 |
                              
                              |  40 |     SORT AGGREGATE                         |
                              |     1 |    32 |       |            |          |
                              
                              |* 41 |      HASH JOIN                             |
                              |   294K|  9192K|  3328K|  2899  (43)| 00:00:03 |
                              
                              |* 42 |       TABLE ACCESS BY INDEX ROWID          | STG_VN0003_POL
                              |   120K|  1884K|       |   453  (45)| 00:00:01 |
                              
                              |* 43 |        INDEX RANGE SCAN                    | X_STG_VN0003_POL_1
                              |   132K|       |       |    14  (58)| 00:00:01 |
                              
                              |  44 |       TABLE ACCESS BY INDEX ROWID          | STG_VN0004_RES
                              |   602K|  9411K|       |  1338  (45)| 00:00:02 |
                              
                              |* 45 |        INDEX RANGE SCAN                    | X_STG_VN0004_RES_1
                              |   602K|       |       |    63  (59)| 00:00:01 |
                              
                              --------------------------------------------------------------------------------
                              -------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 5 - access("CV"."ILI_POL_EDW_ID"="ILI_POL_EDW_ID")
                                 6 - filter("ILI_POL_FOREX_CURCY_CODE"='USD')
                                 7 - access("CS"."ILI_COV_EDW_ID"="CV"."ILI_COV_EDW_ID")
                                 9 - access("R"."ILI_CESN_EDW_ID"="CS"."ILI_CESN_EDW_ID")
                                10 - access("TX"."JH_ILI_RES_EDW_ID"="R"."JH_ILI_RES_EDW_ID")
                                13 - filter("JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
                                16 - access("TX"."LOAD_ID"=48)
                                18 - access("JH_ILI_METRIC_TYPE_OBJ_ID"="TX"."JH_ILI_METRIC_TYPE_OBJ_ID")
                                22 - access("P"."ILI_FOREX_CURCY_ADW_ID"="F"."ILI_FOREX_CURCY_ADW_ID")
                                23 - filter("F"."ILI_FOREX_CURCY_CODE"='USD')
                                24 - access("ILI_POL_ADW_ID"="CV"."ILI_POL_ADW_ID")
                                26 - access("CV"."ILI_COV_ADW_ID"="CS"."ILI_COV_ADW_ID")
                                28 - access("CS"."ILI_CESN_ADW_ID"="R"."ILI_CESN_ADW_ID")
                                29 - access("R"."JH_ILI_RES_ADW_ID"="RF"."JH_ILI_RES_ADW_ID")
                                32 - filter("M"."JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
                                35 - access("RF"."LOAD_ID"=49)
                                37 - access("M"."JH_ILI_METRIC_TYPE_ADW_ID"="RF"."JH_ILI_METRIC_TYPE_ADW_ID")
                                41 - access("P"."POLNO"="V"."POLNO" AND "V"."LOAD_ID"="P"."LOAD_ID")
                                42 - filter("P"."GL_CURR"='USD')
                                43 - access("P"."LOAD_ID"=47)
                                45 - access("V"."LOAD_ID"=47)
                              
                              Note
                              -----
                                 - dynamic sampling used for this statement
                              
                              Statistics
                              ----------------------------------------------------------
                                        0  recursive calls
                                        0  db block gets
                                        0  consistent gets
                                        0  physical reads
                                        0  redo size
                                        0  bytes sent via SQL*Net to client
                                        0  bytes received via SQL*Net from client
                                        0  SQL*Net roundtrips to/from client
                                        0  sorts (memory)
                                        0  sorts (disk)
                                        1  rows processed
                              • 42. Re: Help with Parallelization on our SPARC server...we are drowning!
                                724046
                                PARALLEL_MAX_SERVERS=64, DOP DEFAULT
                                SQL> @BAL_ITEM.sql;
                                METRIC_TYPE STG_CNT_LOAD_47 DLI_CNT_LOAD_48 ADW_CNT_LOAD_49 STG_SUM_LOAD_47
                                ----------- --------------- --------------- --------------- ---------------
                                DLI_SUM_LOAD_48 ADW_SUM_LOAD_49
                                --------------- ---------------
                                OP_TERM_RES          923701          923701          923701      7665157850
                                     7665157850      7665157850
                                
                                Elapsed: 00:06:03.24
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 1512977653
                                
                                --------------------------------------------------------------------------------
                                --------------------------------------------------------------------------------
                                
                                | Id  | Operation                                    | Name
                                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
                                
                                --------------------------------------------------------------------------------
                                --------------------------------------------------------------------------------
                                
                                |   0 | SELECT STATEMENT                             |
                                  |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
                                
                                |   1 |  SORT GROUP BY NOSORT                        |
                                  |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
                                
                                |   2 |   VIEW                                       |
                                  |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
                                
                                |   3 |    UNION-ALL                                 |
                                  |       |       |       |            |          |        |      |            |
                                
                                |   4 |     SORT AGGREGATE                           |
                                  |     1 |    81 |       |            |          |        |      |            |
                                
                                |   5 |      PX COORDINATOR                          |
                                  |       |       |       |            |          |        |      |            |
                                
                                |   6 |       PX SEND QC (RANDOM)                    | :TQ10004
                                  |     1 |    81 |       |            |          |  Q1,04 | P->S | QC (RAND)  |
                                
                                |   7 |        SORT AGGREGATE                        |
                                  |     1 |    81 |       |            |          |  Q1,04 | PCWP |            |
                                
                                |   8 |         NESTED LOOPS                         |
                                  | 15308 |  1210K|       |  1893   (8)| 00:00:02 |  Q1,04 | PCWP |            |
                                
                                |*  9 |          HASH JOIN                           |
                                  |   260K|    17M|       |  1418   (9)| 00:00:02 |  Q1,04 | PCWP |            |
                                
                                |  10 |           PX RECEIVE                         |
                                  |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,04 | PCWP |            |
                                
                                |  11 |            PX SEND HASH                      | :TQ10002
                                  |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | P->P | HASH       |
                                
                                |  12 |             PX BLOCK ITERATOR                |
                                  |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | PCWC |            |
                                
                                |  13 |              INDEX FAST FULL SCAN            | X_EDW_DLI_ILI_COV_2
                                  |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | PCWP |            |
                                
                                |  14 |           PX RECEIVE                         |
                                  |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,04 | PCWP |            |
                                
                                |  15 |            PX SEND HASH                      | :TQ10003
                                  |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,03 | P->P | HASH       |
                                
                                |  16 |             BUFFER SORT                      |
                                  |     3 |   234 |       |  7399  (21)| 00:00:08 |  Q1,03 | PCWP |            |
                                
                                |  17 |              NESTED LOOPS                    |
                                  |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,03 | PCWP |            |
                                
                                |* 18 |               HASH JOIN                      |
                                  |   260K|    11M|       |   877  (11)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |  19 |                BUFFER SORT                   |
                                  |       |       |       |            |          |  Q1,03 | PCWC |            |
                                
                                |  20 |                 PX RECEIVE                   |
                                  |   260K|  4320K|       |   214   (8)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |  21 |                  PX SEND HASH                | :TQ10000
                                  |   260K|  4320K|       |   214   (8)| 00:00:01 |        | S->P | HASH       |
                                
                                |* 22 |                   TABLE ACCESS BY INDEX ROWID| JH_EDW_DLI_ILI_RES_TX
                                  |   260K|  4320K|       |   214   (8)| 00:00:01 |        |      |            |
                                
                                |  23 |                    NESTED LOOPS              |
                                  |   260K|  8894K|       |   220   (8)| 00:00:01 |        |      |            |
                                
                                |* 24 |                     TABLE ACCESS FULL        | JH_EDW_DLI_ILI_METRIC_TYP
                                E |     1 |    18 |       |     5   (0)| 00:00:01 |        |      |            |
                                
                                |* 25 |                     INDEX RANGE SCAN         | X_JH_EDW_DLI_ILI_RES_TX_2
                                  |  2862K|       |       |     6  (50)| 00:00:01 |        |      |            |
                                
                                |  26 |                PX RECEIVE                    |
                                  |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |  27 |                 PX SEND HASH                 | :TQ10001
                                  |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | P->P | HASH       |
                                
                                |  28 |                  PX BLOCK ITERATOR           |
                                  |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | PCWC |            |
                                
                                |  29 |                   INDEX FAST FULL SCAN       | X_JH_EDW_DLI_ILI_RES_2
                                  |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | PCWP |            |
                                
                                |  30 |               TABLE ACCESS BY INDEX ROWID    | EDW_DLI_ILI_CESN
                                  |     1 |    12 |       |     0   (0)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |* 31 |                INDEX UNIQUE SCAN             | XPK_JH_EDW_DLI_ILI_CESN
                                  |     1 |       |       |     0   (0)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |* 32 |          TABLE ACCESS BY INDEX ROWID         | EDW_DLI_ILI_POL
                                  |     1 |    10 |       |     0   (0)| 00:00:01 |  Q1,04 | PCWP |            |
                                
                                |* 33 |           INDEX UNIQUE SCAN                  | XPK_EDW_DLI_ILI_POL
                                  |     1 |       |       |     0   (0)| 00:00:01 |  Q1,04 | PCWP |            |
                                
                                |  34 |     SORT AGGREGATE                           |
                                  |     1 |    91 |       |            |          |        |      |            |
                                
                                |* 35 |      HASH JOIN                               |
                                  | 14458 |  1284K|       |  2607   (6)| 00:00:03 |        |      |            |
                                
                                |* 36 |       TABLE ACCESS FULL                      | LKP_ILI_FOREX_CURCY
                                  |     1 |     9 |       |     4   (0)| 00:00:01 |        |      |            |
                                
                                |  37 |       NESTED LOOPS                           |
                                  |   260K|    20M|       |  2601   (6)| 00:00:03 |        |      |            |
                                
                                |  38 |        NESTED LOOPS                          |
                                  |   260K|    17M|       |  2126   (6)| 00:00:03 |        |      |            |
                                
                                |  39 |         NESTED LOOPS                         |
                                  |   260K|    14M|       |  1650   (6)| 00:00:02 |        |      |            |
                                
                                |  40 |          NESTED LOOPS                        |
                                  |   260K|    11M|       |  1175   (6)| 00:00:02 |        |      |            |
                                
                                |  41 |           NESTED LOOPS                       |
                                  |   260K|  8894K|       |   229   (8)| 00:00:01 |        |      |            |
                                
                                |* 42 |            TABLE ACCESS FULL                 | JH_LKP_ILI_METRIC_TYPE
                                  |     1 |    18 |       |     4   (0)| 00:00:01 |        |      |            |
                                
                                |* 43 |            TABLE ACCESS BY INDEX ROWID       | JH_FACT_ILI_RES
                                  |   260K|  4320K|       |   224   (8)| 00:00:01 |        |      |            |
                                
                                |* 44 |             INDEX RANGE SCAN                 | X_JH_FACT_ILI_RES_2
                                  |  2862K|       |       |     6  (50)| 00:00:01 |        |      |            |
                                
                                |  45 |           TABLE ACCESS BY INDEX ROWID        | JH_LKP_ILI_RES
                                  |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |* 46 |            INDEX UNIQUE SCAN                 | XPK_JH_LKP_ILI_RES
                                  |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |  47 |          TABLE ACCESS BY INDEX ROWID         | LKP_ILI_CESN
                                  |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |* 48 |           INDEX UNIQUE SCAN                  | XPK_LKP_ILI_CESN
                                  |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |  49 |         TABLE ACCESS BY INDEX ROWID          | LKP_ILI_COV
                                  |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |* 50 |          INDEX UNIQUE SCAN                   | XPK_LKP_ILI_COV
                                  |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |  51 |        TABLE ACCESS BY INDEX ROWID           | LKP_ILI_POL
                                  |     1 |    11 |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |* 52 |         INDEX UNIQUE SCAN                    | XPK_LKP_ILI_POL
                                  |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |  53 |     SORT AGGREGATE                           |
                                  |     1 |    32 |       |            |          |        |      |            |
                                
                                |* 54 |      HASH JOIN                               |
                                  |   294K|  9192K|  3328K|  2899  (43)| 00:00:03 |        |      |            |
                                
                                |* 55 |       TABLE ACCESS BY INDEX ROWID            | STG_VN0003_POL
                                  |   120K|  1884K|       |   453  (45)| 00:00:01 |        |      |            |
                                
                                |* 56 |        INDEX RANGE SCAN                      | X_STG_VN0003_POL_1
                                  |   132K|       |       |    14  (58)| 00:00:01 |        |      |            |
                                
                                |  57 |       TABLE ACCESS BY INDEX ROWID            | STG_VN0004_RES
                                  |   602K|  9411K|       |  1338  (45)| 00:00:02 |        |      |            |
                                
                                |* 58 |        INDEX RANGE SCAN                      | X_STG_VN0004_RES_1
                                  |   602K|       |       |    63  (59)| 00:00:01 |        |      |            |
                                
                                --------------------------------------------------------------------------------
                                --------------------------------------------------------------------------------
                                
                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                
                                   9 - access("CS"."ILI_COV_EDW_ID"="CV"."ILI_COV_EDW_ID")
                                  18 - access("TX"."JH_ILI_RES_EDW_ID"="R"."JH_ILI_RES_EDW_ID")
                                  22 - filter("TX"."LOAD_ID"=48)
                                  24 - filter("JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
                                  25 - access("JH_ILI_METRIC_TYPE_OBJ_ID"="TX"."JH_ILI_METRIC_TYPE_OBJ_ID")
                                  31 - access("R"."ILI_CESN_EDW_ID"="CS"."ILI_CESN_EDW_ID")
                                  32 - filter("ILI_POL_FOREX_CURCY_CODE"='USD')
                                  33 - access("CV"."ILI_POL_EDW_ID"="ILI_POL_EDW_ID")
                                  35 - access("P"."ILI_FOREX_CURCY_ADW_ID"="F"."ILI_FOREX_CURCY_ADW_ID")
                                  36 - filter("F"."ILI_FOREX_CURCY_CODE"='USD')
                                  42 - filter("M"."JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
                                  43 - filter("RF"."LOAD_ID"=49)
                                  44 - access("M"."JH_ILI_METRIC_TYPE_ADW_ID"="RF"."JH_ILI_METRIC_TYPE_ADW_ID")
                                  46 - access("R"."JH_ILI_RES_ADW_ID"="RF"."JH_ILI_RES_ADW_ID")
                                  48 - access("CS"."ILI_CESN_ADW_ID"="R"."ILI_CESN_ADW_ID")
                                  50 - access("CV"."ILI_COV_ADW_ID"="CS"."ILI_COV_ADW_ID")
                                  52 - access("ILI_POL_ADW_ID"="CV"."ILI_POL_ADW_ID")
                                  54 - access("P"."POLNO"="V"."POLNO" AND "V"."LOAD_ID"="P"."LOAD_ID")
                                  55 - filter("P"."GL_CURR"='USD')
                                  56 - access("P"."LOAD_ID"=47)
                                  58 - access("V"."LOAD_ID"=47)
                                
                                Note
                                -----
                                   - dynamic sampling used for this statement
                                
                                Statistics
                                ----------------------------------------------------------
                                          0  recursive calls
                                          0  db block gets
                                          0  consistent gets
                                          0  physical reads
                                          0  redo size
                                          0  bytes sent via SQL*Net to client
                                          0  bytes received via SQL*Net from client
                                          0  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                          1  rows processed
                                PARALLEL_MAX_SERVERS=300, DOP DEFAULT
                                SQL> @BAL_ITEM.sql
                                METRIC_TYPE STG_CNT_LOAD_47 DLI_CNT_LOAD_48 ADW_CNT_LOAD_49 STG_SUM_LOAD_47
                                ----------- --------------- --------------- --------------- ---------------
                                DLI_SUM_LOAD_48 ADW_SUM_LOAD_49
                                --------------- ---------------
                                OP_TERM_RES          923701          923701          923701      7665157850
                                     7665157850      7665157850
                                
                                Elapsed: 00:06:04.76
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 1512977653
                                
                                --------------------------------------------------------------------------------
                                --------------------------------------------------------------------------------
                                
                                | Id  | Operation                                    | Name
                                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
                                
                                --------------------------------------------------------------------------------
                                --------------------------------------------------------------------------------
                                
                                |   0 | SELECT STATEMENT                             |
                                  |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
                                
                                |   1 |  SORT GROUP BY NOSORT                        |
                                  |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
                                
                                |   2 |   VIEW                                       |
                                  |     3 |   234 |       |  7399  (21)| 00:00:08 |        |      |            |
                                
                                |   3 |    UNION-ALL                                 |
                                  |       |       |       |            |          |        |      |            |
                                
                                |   4 |     SORT AGGREGATE                           |
                                  |     1 |    81 |       |            |          |        |      |            |
                                
                                |   5 |      PX COORDINATOR                          |
                                  |       |       |       |            |          |        |      |            |
                                
                                |   6 |       PX SEND QC (RANDOM)                    | :TQ10004
                                  |     1 |    81 |       |            |          |  Q1,04 | P->S | QC (RAND)  |
                                
                                |   7 |        SORT AGGREGATE                        |
                                  |     1 |    81 |       |            |          |  Q1,04 | PCWP |            |
                                
                                |   8 |         NESTED LOOPS                         |
                                  | 15308 |  1210K|       |  1893   (8)| 00:00:02 |  Q1,04 | PCWP |            |
                                
                                |*  9 |          HASH JOIN                           |
                                  |   260K|    17M|       |  1418   (9)| 00:00:02 |  Q1,04 | PCWP |            |
                                
                                |  10 |           PX RECEIVE                         |
                                  |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,04 | PCWP |            |
                                
                                |  11 |            PX SEND HASH                      | :TQ10002
                                  |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | P->P | HASH       |
                                
                                |  12 |             PX BLOCK ITERATOR                |
                                  |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | PCWC |            |
                                
                                |  13 |              INDEX FAST FULL SCAN            | X_EDW_DLI_ILI_COV_2
                                  |   588K|  6896K|       |    60   (7)| 00:00:01 |  Q1,02 | PCWP |            |
                                
                                |  14 |           PX RECEIVE                         |
                                  |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,04 | PCWP |            |
                                
                                |  15 |            PX SEND HASH                      | :TQ10003
                                  |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,03 | P->P | HASH       |
                                
                                |  16 |             BUFFER SORT                      |
                                  |     3 |   234 |       |  7399  (21)| 00:00:08 |  Q1,03 | PCWP |            |
                                
                                |  17 |              NESTED LOOPS                    |
                                  |   260K|    14M|       |  1351   (9)| 00:00:02 |  Q1,03 | PCWP |            |
                                
                                |* 18 |               HASH JOIN                      |
                                  |   260K|    11M|       |   877  (11)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |  19 |                BUFFER SORT                   |
                                  |       |       |       |            |          |  Q1,03 | PCWC |            |
                                
                                |  20 |                 PX RECEIVE                   |
                                  |   260K|  4320K|       |   214   (8)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |  21 |                  PX SEND HASH                | :TQ10000
                                  |   260K|  4320K|       |   214   (8)| 00:00:01 |        | S->P | HASH       |
                                
                                |* 22 |                   TABLE ACCESS BY INDEX ROWID| JH_EDW_DLI_ILI_RES_TX
                                  |   260K|  4320K|       |   214   (8)| 00:00:01 |        |      |            |
                                
                                |  23 |                    NESTED LOOPS              |
                                  |   260K|  8894K|       |   220   (8)| 00:00:01 |        |      |            |
                                
                                |* 24 |                     TABLE ACCESS FULL        | JH_EDW_DLI_ILI_METRIC_TYP
                                E |     1 |    18 |       |     5   (0)| 00:00:01 |        |      |            |
                                
                                |* 25 |                     INDEX RANGE SCAN         | X_JH_EDW_DLI_ILI_RES_TX_2
                                  |  2862K|       |       |     6  (50)| 00:00:01 |        |      |            |
                                
                                |  26 |                PX RECEIVE                    |
                                  |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |  27 |                 PX SEND HASH                 | :TQ10001
                                  |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | P->P | HASH       |
                                
                                |  28 |                  PX BLOCK ITERATOR           |
                                  |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | PCWC |            |
                                
                                |  29 |                   INDEX FAST FULL SCAN       | X_JH_EDW_DLI_ILI_RES_2
                                  |  6139K|    70M|       |   626   (7)| 00:00:01 |  Q1,01 | PCWP |            |
                                
                                |  30 |               TABLE ACCESS BY INDEX ROWID    | EDW_DLI_ILI_CESN
                                  |     1 |    12 |       |     0   (0)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |* 31 |                INDEX UNIQUE SCAN             | XPK_JH_EDW_DLI_ILI_CESN
                                  |     1 |       |       |     0   (0)| 00:00:01 |  Q1,03 | PCWP |            |
                                
                                |* 32 |          TABLE ACCESS BY INDEX ROWID         | EDW_DLI_ILI_POL
                                  |     1 |    10 |       |     0   (0)| 00:00:01 |  Q1,04 | PCWP |            |
                                
                                |* 33 |           INDEX UNIQUE SCAN                  | XPK_EDW_DLI_ILI_POL
                                  |     1 |       |       |     0   (0)| 00:00:01 |  Q1,04 | PCWP |            |
                                
                                |  34 |     SORT AGGREGATE                           |
                                  |     1 |    91 |       |            |          |        |      |            |
                                
                                |* 35 |      HASH JOIN                               |
                                  | 14458 |  1284K|       |  2607   (6)| 00:00:03 |        |      |            |
                                
                                |* 36 |       TABLE ACCESS FULL                      | LKP_ILI_FOREX_CURCY
                                  |     1 |     9 |       |     4   (0)| 00:00:01 |        |      |            |
                                
                                |  37 |       NESTED LOOPS                           |
                                  |   260K|    20M|       |  2601   (6)| 00:00:03 |        |      |            |
                                
                                |  38 |        NESTED LOOPS                          |
                                  |   260K|    17M|       |  2126   (6)| 00:00:03 |        |      |            |
                                
                                |  39 |         NESTED LOOPS                         |
                                  |   260K|    14M|       |  1650   (6)| 00:00:02 |        |      |            |
                                
                                |  40 |          NESTED LOOPS                        |
                                  |   260K|    11M|       |  1175   (6)| 00:00:02 |        |      |            |
                                
                                |  41 |           NESTED LOOPS                       |
                                  |   260K|  8894K|       |   229   (8)| 00:00:01 |        |      |            |
                                
                                |* 42 |            TABLE ACCESS FULL                 | JH_LKP_ILI_METRIC_TYPE
                                  |     1 |    18 |       |     4   (0)| 00:00:01 |        |      |            |
                                
                                |* 43 |            TABLE ACCESS BY INDEX ROWID       | JH_FACT_ILI_RES
                                  |   260K|  4320K|       |   224   (8)| 00:00:01 |        |      |            |
                                
                                |* 44 |             INDEX RANGE SCAN                 | X_JH_FACT_ILI_RES_2
                                  |  2862K|       |       |     6  (50)| 00:00:01 |        |      |            |
                                
                                |  45 |           TABLE ACCESS BY INDEX ROWID        | JH_LKP_ILI_RES
                                  |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |* 46 |            INDEX UNIQUE SCAN                 | XPK_JH_LKP_ILI_RES
                                  |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |  47 |          TABLE ACCESS BY INDEX ROWID         | LKP_ILI_CESN
                                  |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |* 48 |           INDEX UNIQUE SCAN                  | XPK_LKP_ILI_CESN
                                  |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |  49 |         TABLE ACCESS BY INDEX ROWID          | LKP_ILI_COV
                                  |     1 |    12 |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |* 50 |          INDEX UNIQUE SCAN                   | XPK_LKP_ILI_COV
                                  |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |  51 |        TABLE ACCESS BY INDEX ROWID           | LKP_ILI_POL
                                  |     1 |    11 |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |* 52 |         INDEX UNIQUE SCAN                    | XPK_LKP_ILI_POL
                                  |     1 |       |       |     0   (0)| 00:00:01 |        |      |            |
                                
                                |  53 |     SORT AGGREGATE                           |
                                  |     1 |    32 |       |            |          |        |      |            |
                                
                                |* 54 |      HASH JOIN                               |
                                  |   294K|  9192K|  3328K|  2899  (43)| 00:00:03 |        |      |            |
                                
                                |* 55 |       TABLE ACCESS BY INDEX ROWID            | STG_VN0003_POL
                                  |   120K|  1884K|       |   453  (45)| 00:00:01 |        |      |            |
                                
                                |* 56 |        INDEX RANGE SCAN                      | X_STG_VN0003_POL_1
                                  |   132K|       |       |    14  (58)| 00:00:01 |        |      |            |
                                
                                |  57 |       TABLE ACCESS BY INDEX ROWID            | STG_VN0004_RES
                                  |   602K|  9411K|       |  1338  (45)| 00:00:02 |        |      |            |
                                
                                |* 58 |        INDEX RANGE SCAN                      | X_STG_VN0004_RES_1
                                  |   602K|       |       |    63  (59)| 00:00:01 |        |      |            |
                                
                                --------------------------------------------------------------------------------
                                --------------------------------------------------------------------------------
                                
                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                
                                   9 - access("CS"."ILI_COV_EDW_ID"="CV"."ILI_COV_EDW_ID")
                                  18 - access("TX"."JH_ILI_RES_EDW_ID"="R"."JH_ILI_RES_EDW_ID")
                                  22 - filter("TX"."LOAD_ID"=48)
                                  24 - filter("JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
                                  25 - access("JH_ILI_METRIC_TYPE_OBJ_ID"="TX"."JH_ILI_METRIC_TYPE_OBJ_ID")
                                  31 - access("R"."ILI_CESN_EDW_ID"="CS"."ILI_CESN_EDW_ID")
                                  32 - filter("ILI_POL_FOREX_CURCY_CODE"='USD')
                                  33 - access("CV"."ILI_POL_EDW_ID"="ILI_POL_EDW_ID")
                                  35 - access("P"."ILI_FOREX_CURCY_ADW_ID"="F"."ILI_FOREX_CURCY_ADW_ID")
                                  36 - filter("F"."ILI_FOREX_CURCY_CODE"='USD')
                                  42 - filter("M"."JH_ILI_METRIC_TYPE_CODE"='TERM_RES')
                                  43 - filter("RF"."LOAD_ID"=49)
                                  44 - access("M"."JH_ILI_METRIC_TYPE_ADW_ID"="RF"."JH_ILI_METRIC_TYPE_ADW_ID")
                                  46 - access("R"."JH_ILI_RES_ADW_ID"="RF"."JH_ILI_RES_ADW_ID")
                                  48 - access("CS"."ILI_CESN_ADW_ID"="R"."ILI_CESN_ADW_ID")
                                  50 - access("CV"."ILI_COV_ADW_ID"="CS"."ILI_COV_ADW_ID")
                                  52 - access("ILI_POL_ADW_ID"="CV"."ILI_POL_ADW_ID")
                                  54 - access("P"."POLNO"="V"."POLNO" AND "V"."LOAD_ID"="P"."LOAD_ID")
                                  55 - filter("P"."GL_CURR"='USD')
                                  56 - access("P"."LOAD_ID"=47)
                                  58 - access("V"."LOAD_ID"=47)
                                
                                Note
                                -----
                                   - dynamic sampling used for this statement
                                
                                Statistics
                                ----------------------------------------------------------
                                        748  recursive calls
                                          4  db block gets
                                   12541612  consistent gets
                                     533929  physical reads
                                       1048  redo size
                                       1001  bytes sent via SQL*Net to client
                                       1191  bytes received via SQL*Net from client
                                          2  SQL*Net roundtrips to/from client
                                        130  sorts (memory)
                                          0  sorts (disk)
                                          1  rows processed
                                • 43. Re: Help with Parallelization on our SPARC server...we are drowning!
                                  724046
                                  Also this might be worth while to mention that I am doing all these tests on different server. The server is the same as the original one. The DB however is just larger in size, all other settings are the same:
                                  NAME                                 TYPE        VALUE
                                  ------------------------------------ ----------- ------------------------------
                                  O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE
                                  _lgwr_io_slaves                      integer     0
                                  _log_parallelism                     integer     8
                                  _log_parallelism_max                 integer     12
                                  _optimizer_cost_based_transformation string      off
                                  _optimizer_cost_model                string      CPU
                                  _realfree_heap_pagesize_hint         big integer 4M
                                  active_instance_count                integer     
                                  aq_tm_processes                      integer     1
                                  archive_lag_target                   integer     0
                                  asm_diskgroups                       string      
                                  asm_diskstring                       string      
                                  asm_power_limit                      integer     1
                                  audit_file_dest                      string      /JH0001/app/oracle/admin/VADP/adump
                                  audit_sys_operations                 boolean     FALSE
                                  audit_syslog_level                   string      
                                  audit_trail                          string      NONE
                                  background_core_dump                 string      partial
                                  background_dump_dest                 string      /JH0001/app/oracle/admin/VADP/bdump
                                  backup_tape_io_slaves                boolean     FALSE
                                  bitmap_merge_area_size               integer     0
                                  blank_trimming                       boolean     FALSE
                                  buffer_pool_keep                     string      
                                  buffer_pool_recycle                  string      
                                  circuits                             integer     
                                  cluster_database                     boolean     FALSE
                                  cluster_database_instances           integer     1
                                  cluster_interconnects                string      
                                  commit_point_strength                integer     1
                                  commit_write                         string      
                                  compatible                           string      10.2.0.1.0
                                  control_file_record_keep_time        integer     7
                                  control_files                        string      /JH4056/db/VADP/controlfile/control01.ctl, /JH4056/db/VADP/oradata/control02.ctl, /JH4056/db/VADP/redos/control03.ctl
                                  core_dump_dest                       string      /JH0001/app/oracle/admin/VADP/cdump
                                  cpu_count                            integer     32
                                  create_bitmap_area_size              integer     0
                                  create_stored_outlines               string      
                                  cursor_sharing                       string      EXACT
                                  cursor_space_for_time                boolean     FALSE
                                  db_16k_cache_size                    big integer 0
                                  db_2k_cache_size                     big integer 0
                                  db_32k_cache_size                    big integer 0
                                  db_4k_cache_size                     big integer 0
                                  db_8k_cache_size                     big integer 0
                                  db_block_buffers                     integer     0
                                  db_block_checking                    string      FALSE
                                  db_block_checksum                    string      false
                                  db_block_size                        integer     32768
                                  db_cache_advice                      string      ON
                                  db_cache_size                        big integer 0
                                  db_create_file_dest                  string      
                                  db_create_online_log_dest_1          string      
                                  db_create_online_log_dest_2          string      
                                  db_create_online_log_dest_3          string      
                                  db_create_online_log_dest_4          string      
                                  db_create_online_log_dest_5          string      
                                  db_domain                            string      
                                  db_file_multiblock_read_count        integer     32
                                  db_file_name_convert                 string      
                                  db_files                             integer     200
                                  db_flashback_retention_target        integer     1440
                                  db_keep_cache_size                   big integer 0
                                  db_name                              string      VADP
                                  db_recovery_file_dest                string      /JH4056/db/VADP/flash_recovery_area
                                  db_recovery_file_dest_size           big integer 2G
                                  db_recycle_cache_size                big integer 0
                                  db_unique_name                       string      VADP
                                  db_writer_processes                  integer     4
                                  dbwr_io_slaves                       integer     0
                                  ddl_wait_for_locks                   boolean     FALSE
                                  dg_broker_config_file1               string      /JH0001/app/oracle/product/10.2.0.4/dbs/dr1VADP.dat
                                  dg_broker_config_file2               string      /JH0001/app/oracle/product/10.2.0.4/dbs/dr2VADP.dat
                                  dg_broker_start                      boolean     FALSE
                                  disk_asynch_io                       boolean     FALSE
                                  dispatchers                          string      (PROTOCOL=TCP) (SERVICE=VADPXDB)
                                  distributed_lock_timeout             integer     60
                                  dml_locks                            integer     4860
                                  drs_start                            boolean     FALSE
                                  event                                string      
                                  fal_client                           string      
                                  fal_server                           string      
                                  fast_start_io_target                 integer     0
                                  fast_start_mttr_target               integer     300
                                  fast_start_parallel_rollback         string      HIGH
                                  file_mapping                         boolean     FALSE
                                  fileio_network_adapters              string      
                                  filesystemio_options                 string      SETALL
                                  fixed_date                           string      
                                  gc_files_to_locks                    string      
                                  gcs_server_processes                 integer     0
                                  global_context_pool_size             string      
                                  global_names                         boolean     FALSE
                                  hash_area_size                       integer     0
                                  hi_shared_memory_address             integer     0
                                  hs_autoregister                      boolean     TRUE
                                  ifile                                file        
                                  instance_groups                      string      
                                  instance_name                        string      VADP
                                  instance_number                      integer     0
                                  instance_type                        string      RDBMS
                                  java_max_sessionspace_size           integer     0
                                  java_pool_size                       big integer 0
                                  java_soft_sessionspace_limit         integer     0
                                  job_queue_processes                  integer     2
                                  large_pool_size                      big integer 0
                                  ldap_directory_access                string      NONE
                                  license_max_sessions                 integer     0
                                  license_max_users                    integer     0
                                  license_sessions_warning             integer     0
                                  local_listener                       string      
                                  lock_name_space                      string      
                                  lock_sga                             boolean     FALSE
                                  log_archive_config                   string      
                                  log_archive_dest                     string      /JH0001/app/oracle/admin/VADP/arch
                                  log_archive_dest_1                   string      
                                  log_archive_dest_10                  string      
                                  log_archive_dest_2                   string      
                                  log_archive_dest_3                   string      
                                  log_archive_dest_4                   string      
                                  log_archive_dest_5                   string      
                                  log_archive_dest_6                   string      
                                  log_archive_dest_7                   string      
                                  log_archive_dest_8                   string      
                                  log_archive_dest_9                   string      
                                  log_archive_dest_state_1             string      enable
                                  log_archive_dest_state_10            string      enable
                                  log_archive_dest_state_2             string      enable
                                  log_archive_dest_state_3             string      enable
                                  log_archive_dest_state_4             string      enable
                                  log_archive_dest_state_5             string      enable
                                  log_archive_dest_state_6             string      enable
                                  log_archive_dest_state_7             string      enable
                                  log_archive_dest_state_8             string      enable
                                  log_archive_dest_state_9             string      enable
                                  log_archive_duplex_dest              string      
                                  log_archive_format                   string      VADP_%t_%s_%r.arc
                                  log_archive_local_first              boolean     TRUE
                                  log_archive_max_processes            integer     2
                                  log_archive_min_succeed_dest         integer     1
                                  log_archive_start                    boolean     FALSE
                                  log_archive_trace                    integer     0
                                  log_buffer                           integer     14272512
                                  log_checkpoint_interval              integer     0
                                  log_checkpoint_timeout               integer     1800
                                  log_checkpoints_to_alert             boolean     FALSE
                                  log_file_name_convert                string      
                                  logmnr_max_persistent_sessions       integer     1
                                  max_commit_propagation_delay         integer     0
                                  max_dispatchers                      integer     
                                  max_dump_file_size                   string      UNLIMITED
                                  max_enabled_roles                    integer     150
                                  max_shared_servers                   integer     
                                  nls_calendar                         string      
                                  nls_comp                             string      
                                  nls_currency                         string      
                                  nls_date_format                      string      
                                  nls_date_language                    string      
                                  nls_dual_currency                    string      
                                  nls_iso_currency                     string      
                                  nls_language                         string      AMERICAN
                                  nls_length_semantics                 string      BYTE
                                  nls_nchar_conv_excp                  string      FALSE
                                  nls_numeric_characters               string      
                                  nls_sort                             string      
                                  nls_territory                        string      AMERICA
                                  nls_time_format                      string      
                                  nls_time_tz_format                   string      
                                  nls_timestamp_format                 string      
                                  nls_timestamp_tz_format              string      
                                  object_cache_max_size_percent        integer     10
                                  object_cache_optimal_size            integer     102400
                                  olap_page_pool_size                  big integer 0
                                  open_cursors                         integer     1000
                                  open_links                           integer     4
                                  open_links_per_instance              integer     4
                                  optimizer_dynamic_sampling           integer     4
                                  optimizer_features_enable            string      10.2.0.4
                                  optimizer_index_caching              integer     0
                                  optimizer_index_cost_adj             integer     10
                                  optimizer_mode                       string      ALL_ROWS
                                  optimizer_secure_view_merging        boolean     TRUE
                                  os_authent_prefix                    string      ops$
                                  os_roles                             boolean     FALSE
                                  parallel_adaptive_multi_user         boolean     TRUE
                                  parallel_automatic_tuning            boolean     FALSE
                                  parallel_execution_message_size      integer     32768
                                  parallel_instance_group              string      
                                  parallel_max_servers                 integer     64
                                  parallel_min_percent                 integer     0
                                  parallel_min_servers                 integer     16
                                  parallel_server                      boolean     FALSE
                                  parallel_server_instances            integer     1
                                  parallel_threads_per_cpu             integer     2
                                  pga_aggregate_target                 big integer 2560M
                                  plsql_ccflags                        string      
                                  plsql_code_type                      string      INTERPRETED
                                  plsql_compiler_flags                 string      INTERPRETED, NON_DEBUG
                                  plsql_debug                          boolean     FALSE
                                  plsql_native_library_dir             string      
                                  plsql_native_library_subdir_count    integer     0
                                  plsql_optimize_level                 integer     2
                                  plsql_v2_compatibility               boolean     FALSE
                                  plsql_warnings                       string      DISABLE:ALL
                                  pre_11g_enable_capture               boolean     FALSE
                                  pre_page_sga                         boolean     FALSE
                                  processes                            integer     300
                                  query_rewrite_enabled                string      FALSE
                                  query_rewrite_integrity              string      enforced
                                  rdbms_server_dn                      string      
                                  read_only_open_delayed               boolean     FALSE
                                  recovery_parallelism                 integer     0
                                  recyclebin                           string      OFF
                                  remote_archive_enable                string      true
                                  remote_dependencies_mode             string      TIMESTAMP
                                  remote_listener                      string      
                                  remote_login_passwordfile            string      EXCLUSIVE
                                  remote_os_authent                    boolean     FALSE
                                  remote_os_roles                      boolean     FALSE
                                  replication_dependency_tracking      boolean     TRUE
                                  resource_limit                       boolean     FALSE
                                  resource_manager_plan                string      
                                  resumable_timeout                    integer     0
                                  rollback_segments                    string      
                                  serial_reuse                         string      disable
                                  service_names                        string      VADP
                                  session_cached_cursors               integer     20
                                  session_max_open_files               integer     10
                                  sessions                             integer     1105
                                  sga_max_size                         big integer 5G
                                  sga_target                           big integer 5G
                                  shadow_core_dump                     string      partial
                                  shared_memory_address                integer     0
                                  shared_pool_reserved_size            big integer 200M
                                  shared_pool_size                     big integer 0
                                  shared_server_sessions               integer     
                                  shared_servers                       integer     1
                                  skip_unusable_indexes                boolean     TRUE
                                  smtp_out_server                      string      
                                  sort_area_retained_size              integer     0
                                  sort_area_size                       integer     0
                                  spfile                               string      /JH0001/app/oracle/product/10.2.0.4/dbs/spfileVADP.ora
                                  sql92_security                       boolean     FALSE
                                  sql_trace                            boolean     FALSE
                                  sql_version                          string      NATIVE
                                  sqltune_category                     string      DEFAULT
                                  standby_archive_dest                 string      ?/dbs/arch
                                  standby_file_management              string      MANUAL
                                  star_transformation_enabled          string      TRUE
                                  statistics_level                     string      TYPICAL
                                  streams_pool_size                    big integer 0
                                  tape_asynch_io                       boolean     TRUE
                                  thread                               integer     0
                                  timed_os_statistics                  integer     0
                                  timed_statistics                     boolean     TRUE
                                  trace_enabled                        boolean     TRUE
                                  tracefile_identifier                 string      
                                  transactions                         integer     1215
                                  transactions_per_rollback_segment    integer     5
                                  undo_management                      string      AUTO
                                  undo_retention                       integer     2400
                                  undo_tablespace                      string      VADP_UNDO
                                  use_indirect_data_buffers            boolean     FALSE
                                  user_dump_dest                       string      /JH0001/app/oracle/admin/VADP/udump
                                  utl_file_dir                         string      
                                  workarea_size_policy                 string      AUTO
                                  • 44. Re: Help with Parallelization on our SPARC server...we are drowning!
                                    Taral
                                    Well this is not in parallel. So, may be something else is doing some other run on server. Or may be this would run in parallel at runtime.
                                    1. Is there parallel set at table level ?
                                    2. Some of the table are not analyzed .
                                    3. some predicates are applied very late so may be you can try sub query unnesting