7 Replies Latest reply on Apr 25, 2007 12:59 PM by 29997

    Hints in Mapping?

    527542
      Hi,

      I am using OWB 10G R1.

      While creating a mapping oracle is setting hints such as append parallel.
      Some people suggested that, by removing the hints, it improves the perfrormance.
      Then why oracle creating the hints automatically?

      Could anyone please explain under what circumstances it will improve the performance?

      In my real environment, I am having multiple CPUs. So that, would these hints helpful to me?

      Please suggest me which is the best practice.
      Any suggestions would be appreciated greatly.

      Thank you,
      Regards,

      Gowtham Sen.
        • 1. Re: Hints in Mapping?
          29997
          Gowthan,

          these hints are put on automatically since they do, in general increase performance. The APPEND hint should be used on all target tables, that are indeed a large FACT table. Regular smaller transactions (e.g. on staging tables and dimensions) do not benefit as much from this one. If you have a multi-CPU machine then I would suggest that you left the PARALLEL hint on. This should be done in accordance with your DBA, who should be able to tell you what you gain here. One gain here is whilst extracting data from multiple partitions you will be able to access each partition in parallel (i.e. at the same time), increasing the throughput of your ETL quite a lot. The OWB user's guide does cover this quite well.

          To remove these hints, should you want to see the difference, you do so in the Configuration for the mapping (right-click->Configure).

          Borkur
          • 2. Re: Hints in Mapping?
            527542
            Thanks Borkur for clear explanation.

            I have one more doubt.
            I am having multiple CPUs. And If I execute mutliple process flows/mappings parallely through OEM sheduler, would all these cpus will be utilized? or using some mechanism only one cpu will be shared for all the mappings/process flows?

            How can we give assurance that, all the cpus utilized successfully ?

            Thanks in advance.

            Thank you,
            Regards,

            Gowtham Sen.
            • 3. Re: Hints in Mapping?
              29997
              When your mappings are running, you can see how many parallel processes are being run. Take a look at V$SESSION. The Program field in the line for your ETL session will contain something like "oracle@host (P001)" meaning that this is parallel process number 1. This means then that for each parallel process you will have 1 line in V$SESSION (for the given session_id)
              • 4. Re: Hints in Mapping?
                527542
                Yes Borkur you are right.

                But one thing I would like to know, will it uses the different cpus for multiple processes.?

                Thank you,
                Regards,

                Gowtham Sen.
                • 5. Re: Hints in Mapping?
                  558993
                  Hi Gowthem,

                  The parallel processing happens internally. It will show u how many threads into which it is going to process but i donot think u will be able to see how the CPU takes it and executes.

                  In case u get any info please put it in the thread.

                  Regards
                  Bharath
                  • 6. Re: Hints in Mapping?
                    527542
                    ya sure Bharat.

                    Regards,
                    Gowtham Sen.
                    • 7. Re: Hints in Mapping?
                      29997
                      well,. that does depend on the operating system and the tools you use to monitor the CPU usage. Using 'glance' on the HPUX box we have here, I can see which process is running on which CPU. Like this I can see the distribution.
                      The operating system is responsible for allocating CPUs for each (parallel) process and will do so to maximize the total CPU throughput. I am sure there are some 3rd party tools available for Windows platforms to give you a better detailed view of what is going on. Using the windows task manager can give you some ideas. If you are running a query using parallel processing, you should see some (close to even) activities on all CPUs ..