9 Replies Latest reply: Mar 14, 2013 8:17 AM by Srini Chavali-Oracle RSS

    What are the best solutions for data warehouse configuration in 10gR2

    Tapan Kumar Saha
      I need help on solutions to be provided to my Client for upgrading the data warehouse.

      Current Configuration: Oracle database 9.2.0.8. This database contains the data warehouse and one more data mart on the same host.Sizes are respectively 6 Terabyte(retention policy of 3 years+current year) and 1 Terabyte. The ETL tool and BO Reporting tools are also hosted on the same host. This current configuration is really performing poor.

      Client cannot go for a major architectural or configuration changes to its existing environment now due to some constraints.

      However, they have agreed to separate out the databases on separate hosts from the ETL tools and BO objects. Also we are planning to upgrade the database to 10gR2 to attain stability, better performance and overcome current headaches.
      We cannot upgrade the database to 11g as the BO is at a version 6.5 which isn't compatible with Oracle 11g. And Client cannot afford to upgrade anything else other than the database.

      So, my role is very vital in providing a perfect solution towards better performance and take a successful migration of Oracle Database from one host to another (similar platform and OS) in addition to upgrade.

      I have till now thought of the following:

      Move the Oracle database and data mart to separate host.
      The host will be the same platform, that is, HP Superdome with HP-UX 32-bit OS (we cannot change to 64-bit as ETL tool doesn't support)
      Install new Oracle database 10g on the new host and move the data to it.
      Exploring all new features of 10gR2 to help data warehouse, that is, SQL MODEL Clause introduction, Parallel processing, Partitioning, Data Pump, SPA to study pre and post migrations.
      Also thinking of RAC to provide more better solution as our main motive is to show a tremendous performance enhancement.
      I need all your help to prepare a good road map for my assignment. Please suggest.

      Thanks,

      Tapan
        • 1. Re: What are the best solutions for data warehouse configuration in 10gR2
          Rob_J
          user1406770 wrote:
          I need help on solutions to be provided to my Client for upgrading the data warehouse.

          Current Configuration: Oracle database 9.2.0.8. This database contains the data warehouse and one more data mart on the same host.Sizes are respectively 6 Terabyte(retention policy of 3 years+current year) and 1 Terabyte. The ETL tool and BO Reporting tools are also hosted on the same host. This current configuration is really performing poor.
          Why is it performing poorly would be my first question. Contention, slow disk I/O, inefficient queries, something else?

          >
          Client cannot go for a major architectural or configuration changes to its existing environment now due to some constraints.

          However, they have agreed to separate out the databases on separate hosts from the ETL tools and BO objects. Also we are planning to upgrade the database to 10gR2 to attain stability, better performance and overcome current headaches.
          Not sure I agree with this. What instability do you currently have that you are trying to fix?
          What makes you think you will have better performance?
          9i to 10g was a big change and you may find that you have to re-write a lot of SQL queries just to get them to perform as well as 9i, in my experience. You might be OK, but there is no guarantee. It's quite a risk to assume that performance will be better without a lot of work.
          We cannot upgrade the database to 11g as the BO is at a version 6.5 which isn't compatible with Oracle 11g. And Client cannot afford to upgrade anything else other than the database.

          So, my role is very vital in providing a perfect solution towards better performance and take a successful migration of Oracle Database from one host to another (similar platform and OS) in addition to upgrade.

          I have till now thought of the following:

          Move the Oracle database and data mart to separate host.
          This could give you a performance boost if there is contention between the two
          The host will be the same platform, that is, HP Superdome with HP-UX 32-bit OS (we cannot change to 64-bit as ETL tool doesn't support)
          How much memory do you have available to your database running on 32bit? If you have a 6TB database and it's a data warehouse, running large sorts and scans I bet there is not much RAM to play with?
          Install new Oracle database 10g on the new host and move the data to it.
          How will you move it?
          Exploring all new features of 10gR2 to help data warehouse, that is, SQL MODEL Clause introduction, Parallel processing, Partitioning, Data Pump, SPA to study pre and post migrations.
          Do you even need to use the SQL MODEL clause? Will it help you?
          Have you identified specific cases for the use of each of these technologies which will help you? What's data pump going to help you with, performance wise?
          Also thinking of RAC to provide more better solution as our main motive is to show a tremendous performance enhancement.
          I need all your help to prepare a good road map for my assignment. Please suggest.

          Thanks,

          Tapan
          I hope that doesn't come across overly negative but I wouldn't be convinced that the upgrade will give you what you are looking for without you carrying out sufficient testing to make sure that what you are planning is going to work. Do you have somewhere to test this first?

          Rob
          • 2. Re: What are the best solutions for data warehouse configuration in 10gR2
            Tapan Kumar Saha
            Why is it performing poorly would be my first question. Contention, slow disk I/O, inefficient queries, something else?

            The performance has been seen degrading gradually. The size of the data warehouse is increasing continuously. Also the Client wants to increase the retention period from 3 years to 6 years. But just by increasing the retention period to 4 years, we could see that batch jobs started running long, Reports extraction taking long, ad hoc queries running long on the database, even though partitioning is in place. I could see some more scope of sub-partitioning for couple of tables though which are the candidate for long running queries. As the same server hosts, 2 databases, 1 ETL tool (DataStage) and 1 BO tool, the server resource utilization is always very high. It has 32 processors and 64GB RAM. There are definitely some inefficient queries but as a whole this system works slow compared to all other databases of the Client, which are all 10g/11g running on HPUX/EXADATA. Client is planning to move this DW to EXADATA as well with all other BI Tools to the latest releases, but due to budget freeze, we cannot plan it until 2015. But now as a Business Continuity Plan we have to make it little fast considering the constraints and available solutions.

            Not sure I agree with this. What instability do you currently have that you are trying to fix?
            What makes you think you will have better performance?
            +9i to 10g was a big change and you may find that you have to re-write a lot of SQL queries just to get them to perform as well as 9i, in my experience. You might be OK, but there is no guarantee. It's quite a risk to assume that performance will be better without a lot of work+

            Our database need a daily restart before it could start the ETL jobs. This has been since past 1 year. This is really causing worry to users whose reports take longer to run and finally fails due to restart. This restat has been recommended a workaround to huge contention seen 1 year ago in the ETL jobs. So we need to come out of this. When I started investigation to upgrade to 10g, I could see there are many new features introduced to boost the performance. Somehow we need to upgrade this environment later on to cope with the updated technologies. We cannot sit on this for ever. SO, I thought it is good to do it in chunk. Lets upgrade to 10G, restructure things to get benefitted out of 10G features and later on after 2 years move to Cloud technology. I am investigating on the SQL queries, ETL jobs which may need re-structuring, as I cannot say the performance didnt improve after 10G. Also in current configuration, I am unable to avail DataPump/Transportable tablespaces for Cloning Test environments and delays many issues. I agree with you Rob that it has risk, but until I take the risk, is there any way I can come out.

            This could give you a performance boost if there is contention between the two

            Yes as 1st solution, I requested them to separate out the databases from the DW tools.

            How much memory do you have available to your database running on 32bit? If you have a 6TB database and it's a data warehouse, running large sorts and scans I bet there is not much RAM to play with?

            Basic Memory Description
            Module Type: MEMORY
            Total Configured Memory : 65536 MB
            Page Size: 4096 Bytes
            Memory interleaving is supported on this machine and is ON.
            Client has been increasing RAM for the past 4 time as a measure to improve performance. But this way it cannot resolve just by scaling the hardware.

            How will you move it?

            I read the Doc ID 733205.1 "Migration Of An Oracle Database Across OS Platforms". So please review my understanding:

            My requirement is migrating database 9.2.0.8 to 10.2.0.4.
            So Datapump Export/Import and Transportable tablespaces are not an option until 10G.
            Traditional Export/Import is available for me, but the DB size is 6T. From my past experience, Datapump Export/Import of 4T ERP database from 10gR2 to 11gR2 took around 2.5 full days. So, if I know think of traditional Export/Import, it may extend to 7-10 days, I suppose. Definitely it cannot be afforded.
            Oracle streams works across versions from 9iR2, but again have performance limitations.
            So, could you assist me in deciding the migration strategy as well?


            Do you even need to use the SQL MODEL clause? Will it help you?
            Have you identified specific cases for the use of each of these technologies which will help you? What's data pump going to help you with, performance wise?

            I need to explore and investigate the existing codes where SQL MODEL needs to be implemented. I need to analyze if it will help me. I am just investigating on the technologies which are available to me with my limitations and need to test them if they at all will benefit me. I need DataPump for export/import requests, and regular archive/purge activities, which are now being done using traditional Exp/Imp and very time taking.


            I hope that doesn't come across overly negative but I wouldn't be convinced that the upgrade will give you what you are looking for without you carrying out sufficient testing to make sure that what you are planning is going to work. Do you have somewhere to test this first?

            I appreciate your thoughts Rob, but we cannot leave our Client sit on this configuration for ever. I have to start towards their betterment, and I think it could be a good start. We have DEV and UAT environments before we go for PROD. In DEV, I will be able to test everything except migration and RAC, that is, upgrade to 10G and benefit of new features. In UAT, I am planning to test all including migration and implementing RAC.
            Please suggest if how should I proceed.

            Thanks,
            Tapan
            • 3. Re: What are the best solutions for data warehouse configuration in 10gR2
              Rob_J
              Hi,

              There are a lot of different factors here. The first thing to establish is why is it slow. So, you need to look at a statspack report for the time when it is slow and see what the cause is.

              That is probably worth starting a new thread in the general section when you have the report. No need to post the whole thing, just the summary details at the top with the wait events, load profile (can't remember if that's in 9i), and perhaps some of the top queries section.

              Why were you restarting the DB before the ETL?

              So how much memory do you have assigned to the SGA and PGA in your database?

              I thought you were staying on the same OS? Just upgrading the database? If that's the case, just do it on the same server and upgrade the DB. Export/import on 6TB, as you say, is not an option because it will take an eternity. Setting up streams seems like a large task to me. If it were me I'd prefer to upgrade the DB where it is. It'll be the quickest way, but you will need to make sure you have a good backup plan if it doesn't work.

              The main problem here is performance, so as I said at the top it's definitely worth looking at that first. There is no guarantee that by upgrading you will have better performance.
              • 4. Re: What are the best solutions for data warehouse configuration in 10gR2
                606331
                Hi,

                actually we have faced same issue oracle 9.2.0.5 RAC.3TB db....what we did is we have analyzed and re-org tables/indexes the most used ones....it has taken 3 days....but now working fine than before....

                coming to your scenario i hope .... lack of updated statstics your query performing poor i suppose.....
                if you have time ....try to analyze the most used tables....(any way it is too big....i hope) and then re-org/rebuild the tables+indexes hope it will solve the issue...

                and allocate more SGA ....share statspack report and SGA details...

                my advise is ...

                go for 3 or 4 node oracle 10g RAC.....it will be better than worst......

                thanks,
                DBC,
                Sr DBA.
                • 5. Re: What are the best solutions for data warehouse configuration in 10gR2
                  Hemant K Chitale
                  Two major changes from 9i to 10g that will impact performance are
                  a. Changes in defaults when running GATHER_%_STATS
                  b. Change in the optimizer behaviour

                  Oracle had published a couple of white papers on these.


                  Since 10g is no longer in Premier Support, you will have some difficulties when logging SRs with Oracle Support --- they'll keep asking you to use 11g.
                  The host will be the same platform, that is, HP Superdome
                  Why do you need export-import if you are not changing platforms ? You could install 9.2.0.8 on the new server, clone the database and then upgrade it to 10gR2 / 11gR2.


                  Hemant K Chitale
                  • 6. Re: What are the best solutions for data warehouse configuration in 10gR2
                    Hemant K Chitale
                    See http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bidw-optimizer-10gr2-0208-130973.pdf on the GATHER_%_STATS and Optimizer changes from 9i to 10g



                    Hemant K Chitale
                    • 7. Re: What are the best solutions for data warehouse configuration in 10gR2
                      Tapan Kumar Saha
                      SGA=27.5 GB and PGA=50 MB

                      Also I am pasting part of STATSPACK Report, eliminating the snaps of DB bounce. Please suggest the scope of improvement in this case.

                      STATSPACK report for

                      Snap Id Snap Time Sessions Curs/Sess Comment
                      --------- ------------------ -------- --------- -------------------
                      Begin Snap: 582946 11-Mar-13 20:02:16 46 12.8
                      End Snap: 583036 12-Mar-13 18:24:24 60 118.9
                      Elapsed: 1,342.13 (mins)

                      Cache Sizes (end)
                      ~~~~~~~~~~~~~~~~~
                      Buffer Cache: 21,296M Std Block Size: 16K
                      Shared Pool Size: 6,144M Log Buffer: 16,384K

                      Load Profile
                      ~~~~~~~~~~~~ Per Second Per Transaction
                      --------------- ---------------
                      Redo size: 1,343,739.01 139,883.39
                      Logical reads: 100,102.54 10,420.69
                      Block changes: 3,757.42 391.15
                      Physical reads: 6,670.84 694.44
                      Physical writes: 874.34 91.02
                      User calls: 1,986.04 206.75
                      Parses: 247.87 25.80
                      Hard parses: 5.82 0.61
                      Sorts: 1,566.76 163.10
                      Logons: 10.99 1.14
                      Executes: 1,309.79 136.35
                      Transactions: 9.61

                      % Blocks changed per Read: 3.75 Recursive Call %: 43.34
                      Rollback per transaction %: 3.49 Rows per Sort: 190.61

                      Instance Efficiency Percentages (Target 100%)
                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                      Buffer Nowait %: 99.90 Redo NoWait %: 100.00
                      Buffer Hit %: 96.97 In-memory Sort %: 100.00
                      Library Hit %: 99.27 Soft Parse %: 97.65
                      Execute to Parse %: 81.08 Latch Hit %: 99.58
                      Parse CPU to Parse Elapsd %: 3.85 % Non-Parse CPU: 99.34

                      Shared Pool Statistics Begin End
                      ------ ------
                      Memory Usage %: 7.11 50.37
                      % SQL with executions>1: 62.31 46.46
                      % Memory for SQL w/exec>1: 26.75 13.47

                      Top 5 Timed Events
                      ~~~~~~~~~~~~~~~~~~ % Total
                      Event Waits Time (s) Ela Time
                      -------------------------------------------- ------------ ----------- --------
                      CPU time 492,062 43.66
                      db file sequential read 157,418,414 343,549 30.49
                      library cache pin 92,339 66,759 5.92
                      PX qref latch 63,635 43,845 3.89
                      db file scattered read 2,506,806 41,677 3.70
                      -------------------------------------------------------------
                      Background Wait Events for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036
                      -> ordered by wait time desc, waits desc (idle events last)

                      Avg
                      Total Wait wait Waits
                      Event Waits Timeouts Time (s) (ms) /txn
                      ---------------------------- ------------ ---------- ---------- ------ --------
                      log file sequential read 176,386 0 3,793 22 0.2
                      log file parallel write 2,685,833 0 1,813 1 3.5
                      db file parallel write 239,166 0 1,350 6 0.3
                      control file parallel write 33,432 0 79 2 0.0
                      LGWR wait for redo copy 478,120 536 75 0 0.6
                      rdbms ipc reply 10,027 0 47 5 0.0
                      control file sequential read 32,414 0 40 1 0.0
                      db file scattered read 4,101 0 30 7 0.0
                      db file sequential read 13,946 0 29 2 0.0
                      direct path read 203,694 0 14 0 0.3
                      log buffer space 363 0 13 37 0.0
                      latch free 3,766 0 9 2 0.0
                      direct path write 80,491 0 6 0 0.1
                      async disk IO 351,955 0 4 0 0.5
                      enqueue 28 0 1 21 0.0
                      buffer busy waits 1,281 0 1 0 0.0
                      log file single write 172 0 0 1 0.0
                      rdbms ipc message 10,563,204 251,286 992,837 94 13.7
                      pmon timer 34,751 34,736 78,600 2262 0.0
                      smon timer 7,462 113 76,463 10247 0.0
                      -------------------------------------------------------------

                      Instance Activity Stats for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036

                      Statistic Total per Second per Trans
                      --------------------------------- ------------------ -------------- ------------
                      CPU used by this session 49,206,154 611.0 63.6
                      CPU used when call started 49,435,735 613.9 63.9
                      CR blocks created 6,740,777 83.7 8.7
                      Cached Commit SCN referenced 423,253,503 5,256.0 547.2
                      Commit SCN cached 19,165 0.2 0.0
                      DBWR buffers scanned 48,276,489 599.5 62.4
                      DBWR checkpoint buffers written 6,959,752 86.4 9.0
                      DBWR checkpoints 454 0.0 0.0
                      DBWR free buffers found 44,817,183 556.5 57.9
                      DBWR lru scans 137,149 1.7 0.2
                      DBWR make free requests 162,528 2.0 0.2
                      DBWR revisited being-written buff 4,220 0.1 0.0
                      DBWR summed scan depth 48,276,489 599.5 62.4
                      DBWR transaction table writes 5,036 0.1 0.0
                      DBWR undo block writes 2,989,436 37.1 3.9
                      DDL statements parallelized 3,723 0.1 0.0
                      DFO trees parallelized 4,157 0.1 0.0
                      DML statements parallelized 3 0.0 0.0
                      OS Block input operations 29,850 0.4 0.0
                      OS Block output operations 1,591 0.0 0.0
                      OS Characters read/written 182,109,814,791 2,261,447.1 235,416.9
                      OS Integral unshared data size ################## 242,463,432.4 ############
                      OS Involuntary context switches 188,257,786 2,337.8 243.4
                      OS Maximum resident set size 43,518,730,619 540,417.4 56,257.5
                      OS Page reclaims 159,430,953 1,979.8 206.1
                      OS Signals received 5,260,938 65.3 6.8
                      OS Socket messages received 79,438,383 986.5 102.7
                      OS Socket messages sent 93,064,176 1,155.7 120.3
                      OS System time used 10,936,430 135.8 14.1
                      OS User time used 132,043,884 1,639.7 170.7
                      OS Voluntary context switches 746,207,739 9,266.4 964.6
                      PX local messages recv'd 55,120,663 684.5 71.3
                      PX local messages sent 55,120,817 684.5 71.3
                      Parallel operations downgraded 1 3 0.0 0.0
                      Parallel operations not downgrade 4,154 0.1 0.0
                      SQL*Net roundtrips to/from client 155,422,335 1,930.0 200.9
                      SQL*Net roundtrips to/from dblink 18 0.0 0.0
                      active txn count during cleanout 16,529,551 205.3 21.4
                      background checkpoints completed 43 0.0 0.0
                      background checkpoints started 43 0.0 0.0
                      background timeouts 280,202 3.5 0.4
                      branch node splits 4,428 0.1 0.0
                      buffer is not pinned count 6,382,440,322 79,257.4 8,250.7
                      buffer is pinned count 9,675,661,370 120,152.8 12,507.9
                      bytes received via SQL*Net from c 67,384,496,376 836,783.4 87,109.3
                      bytes received via SQL*Net from d 6,142 0.1 0.0
                      bytes sent via SQL*Net to client 50,240,643,657 623,890.4 64,947.1
                      bytes sent via SQL*Net to dblink 3,701 0.1 0.0
                      calls to get snapshot scn: kcmgss 145,385,064 1,805.4 187.9
                      calls to kcmgas 36,816,132 457.2 47.6
                      calls to kcmgcs 3,514,770 43.7 4.5
                      change write time 369,373 4.6 0.5
                      cleanout - number of ktugct calls 20,954,488 260.2 27.1
                      cleanouts and rollbacks - consist 6,357,174 78.9 8.2
                      cleanouts only - consistent read 10,078,802 125.2 13.0
                      cluster key scan block gets 69,403,565 861.9 89.7
                      Instance Activity Stats for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036

                      Statistic Total per Second per Trans
                      --------------------------------- ------------------ -------------- ------------
                      cluster key scans 41,311,211 513.0 53.4
                      commit cleanout failures: block l 413,776 5.1 0.5
                      commit cleanout failures: buffer 414 0.0 0.0
                      commit cleanout failures: callbac 41,194 0.5 0.1
                      commit cleanout failures: cannot 174,382 2.2 0.2
                      commit cleanouts 11,469,056 142.4 14.8
                      commit cleanouts successfully com 10,839,290 134.6 14.0
                      commit txn count during cleanout 17,155,424 213.0 22.2
                      consistent changes 145,418,277 1,805.8 188.0
                      consistent gets 8,043,252,188 99,881.4 10,397.7
                      consistent gets - examination 3,180,028,047 39,489.7 4,110.9
                      current blocks converted for CR 9 0.0 0.0
                      cursor authentications 14,926 0.2 0.0
                      data blocks consistent reads - un 143,706,500 1,784.6 185.8
                      db block changes 302,577,666 3,757.4 391.2
                      db block gets 336,562,217 4,179.4 435.1
                      deferred (CURRENT) block cleanout 2,912,793 36.2 3.8
                      dirty buffers inspected 627,174 7.8 0.8
                      enqueue conversions 1,296,337 16.1 1.7
                      enqueue releases 13,053,200 162.1 16.9
                      enqueue requests 13,239,092 164.4 17.1
                      enqueue timeouts 185,878 2.3 0.2
                      enqueue waits 114,120 1.4 0.2
                      exchange deadlocks 7,390 0.1 0.0
                      execute count 105,475,101 1,309.8 136.4
                      free buffer inspected 1,604,407 19.9 2.1
                      free buffer requested 258,126,047 3,205.4 333.7
                      hot buffers moved to head of LRU 22,793,576 283.1 29.5
                      immediate (CR) block cleanout app 16,436,010 204.1 21.3
                      immediate (CURRENT) block cleanou 2,860,013 35.5 3.7
                      index fast full scans (direct rea 12,375 0.2 0.0
                      index fast full scans (full) 3,733 0.1 0.0
                      index fast full scans (rowid rang 192,148 2.4 0.3
                      index fetch by key 1,321,024,486 16,404.5 1,707.7
                      index scans kdiixs1 406,165,684 5,043.8 525.1
                      leaf node 90-10 splits 50,373 0.6 0.1
                      leaf node splits 697,235 8.7 0.9
                      logons cumulative 884,756 11.0 1.1
                      messages received 3,276,719 40.7 4.2
                      messages sent 3,257,171 40.5 4.2
                      no buffer to keep pinned count 569 0.0 0.0
                      no work - consistent read gets 4,406,092,172 54,715.0 5,695.8
                      opened cursors cumulative 20,527,704 254.9 26.5
                      parse count (failures) 267,088 3.3 0.4
                      parse count (hard) 468,996 5.8 0.6
                      parse count (total) 19,960,548 247.9 25.8
                      parse time cpu 323,024 4.0 0.4
                      parse time elapsed 8,393,422 104.2 10.9
                      physical reads 537,189,332 6,670.8 694.4
                      physical reads direct 292,545,140 3,632.8 378.2
                      physical writes 70,409,002 874.3 91.0
                      physical writes direct 59,248,394 735.8 76.6
                      physical writes non checkpoint 69,103,391 858.1 89.3
                      pinned buffers inspected 11,893 0.2 0.0
                      prefetched blocks 95,892,161 1,190.8 124.0
                      prefetched blocks aged out before 1,495,883 18.6 1.9
                      Instance Activity Stats for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036

                      Statistic Total per Second per Trans
                      --------------------------------- ------------------ -------------- ------------
                      process last non-idle time ################## ############## ############
                      queries parallelized 417 0.0 0.0
                      recursive calls 122,323,299 1,519.0 158.1
                      recursive cpu usage 3,144,533 39.1 4.1
                      redo blocks written 180,881,558 2,246.2 233.8
                      redo buffer allocation retries 5,400 0.1 0.0
                      redo entries 164,728,513 2,045.6 213.0
                      redo log space requests 1,006 0.0 0.0
                      redo log space wait time 2,230 0.0 0.0
                      redo ordering marks 2,563 0.0 0.0
                      redo size 108,208,614,904 1,343,739.0 139,883.4
                      redo synch time 558,520 6.9 0.7
                      redo synch writes 2,343,824 29.1 3.0
                      redo wastage 1,126,585,600 13,990.0 1,456.4
                      redo write time 718,655 8.9 0.9
                      redo writer latching time 7,763 0.1 0.0
                      redo writes 2,685,833 33.4 3.5
                      rollback changes - undo records a 522,742 6.5 0.7
                      rollbacks only - consistent read 335,177 4.2 0.4
                      rows fetched via callback 1,100,990,382 13,672.1 1,423.3
                      session connect time ################## ############## ############
                      session cursor cache count 1,061 0.0 0.0
                      session cursor cache hits 1,687,796 21.0 2.2
                      session logical reads 8,061,057,193 100,102.5 10,420.7
                      session pga memory 1,573,228,913,832 19,536,421.0 2,033,743.8
                      session pga memory max 1,841,357,626,496 22,866,054.4 2,380,359.0
                      session uga memory 1,074,114,630,336 13,338,399.4 1,388,529.0
                      session uga memory max 386,645,043,296 4,801,374.0 499,823.6
                      shared hash latch upgrades - no w 410,360,146 5,095.9 530.5
                      sorts (disk) 2,657 0.0 0.0
                      sorts (memory) 126,165,625 1,566.7 163.1
                      sorts (rows) 24,048,783,304 298,638.8 31,088.3
                      summed dirty queue length 5,438,201 67.5 7.0
                      switch current to new buffer 1,302,798 16.2 1.7
                      table fetch by rowid 6,201,503,534 77,010.5 8,016.8
                      table fetch continued row 26,649,697 330.9 34.5
                      table scan blocks gotten 1,864,435,032 23,152.6 2,410.2
                      table scan rows gotten 43,639,997,280 541,923.3 56,414.3
                      table scans (cache partitions) 26,112 0.3 0.0
                      table scans (direct read) 246,243 3.1 0.3
                      table scans (long tables) 340,200 4.2 0.4
                      table scans (rowid ranges) 359,617 4.5 0.5
                      table scans (short tables) 9,111,559 113.2 11.8
                      transaction rollbacks 4,819 0.1 0.0
                      transaction tables consistent rea 824 0.0 0.0
                      transaction tables consistent rea 1,386,848 17.2 1.8
                      user calls 159,931,913 1,986.0 206.8
                      user commits 746,543 9.3 1.0
                      user rollbacks 27,020 0.3 0.0
                      write clones created in backgroun 7 0.0 0.0
                      write clones created in foregroun 4,350 0.1 0.0
                      -------------------------------------------------------------
                      Buffer Pool Statistics for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036
                      -> Standard block size Pools D: default, K: keep, R: recycle
                      -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

                      Free Write Buffer
                      Number of Cache Buffer Physical Physical Buffer Complete Busy
                      P Buffers Hit % Gets Reads Writes Waits Waits Waits
                      --- ---------- ----- ----------- ----------- ---------- ------- -------- ------
                      D 774,144 95.6############ 233,869,082 10,089,734 0 0########
                      K 504,000 99.9############ 3,260,227 1,070,338 0 0 65,898
                      R 63,504 96.2 196,079,539 7,511,863 535 0 0 0
                      -------------------------------------------------------------

                      Buffer wait Statistics for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036
                      -> ordered by wait time desc, waits desc

                      Tot Wait Avg
                      Class Waits Time (s) Time (ms)
                      ------------------ ----------- ---------- ---------
                      data block 7,791,121 14,676 2
                      file header block 587 101 172
                      undo header 151,617 71 0
                      segment header 299,312 58 0
                      1st level bmb 45,235 7 0
                      bitmap index block 392 1 3
                      undo block 4,250 1 0
                      2nd level bmb 14 0 0
                      system undo header 2 0 0
                      3rd level bmb 1 0 0
                      -------------------------------------------------------------

                      Latch Activity for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036
                      ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
                      willing-to-wait latch get requests
                      ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
                      ->"Pct Misses" for both should be very close to 0.0

                      Pct Avg Wait Pct
                      Get Get Slps Time NoWait NoWait
                      Latch Requests Miss /Miss (s) Requests Miss
                      ------------------------ -------------- ------ ------ ------ ------------ ------
                      Consistent RBA 2,686,230 0.0 0.2 0 0
                      FAL request queue 86 0.0 0 0
                      FAL subheap alocation 0 0 2 0.0
                      FIB s.o chain latch 1,089 0.0 0 0
                      FOB s.o list latch 4,589,986 0.5 0.0 2 0
                      NLS data objects 1 0.0 0 0
                      SQL memory manager worka 5,963 0.0 0 0
                      Token Manager 0 0 2 0.0
                      active checkpoint queue 719,439 0.3 0.1 0 1 0.0
                      alert log latch 184 0.0 0 2 0.0
                      archive control 4,365 0.0 0 0
                      archive process latch 1,808 0.6 0.6 0 0
                      begin backup scn array 3,387,572 0.0 0.0 0 0
                      cache buffer handles 1,577,222 0.2 0.0 0 0
                      cache buffers chains ############## 0.5 0.0 430 354,357,972 0.3
                      cache buffers lru chain 17,153,023 0.1 0.0 1 385,505,654 0.5
                      cas latch 538,804,153 0.3 0.0 7 0
                      channel handle pool latc 1,776,950 0.5 0.0 0 0
                      channel operations paren 2,901,371 0.3 0.0 0 0
                      checkpoint queue latch 99,329,722 0.0 0.0 0 11,153,369 0.1
                      child cursor hash table 3,927,427 0.0 0.0 0 0
                      commit callback allocati 8,739 0.0 0 0
                      dictionary lookup 7,980 0.0 0 0
                      dml lock allocation 6,767,990 0.1 0.0 0 0
                      dummy allocation 1,898,183 0.2 0.1 0 0
                      enqueue hash chains 27,741,348 0.1 0.1 4 0
                      enqueues 17,450,161 0.3 0.1 6 0
                      error message lists 132,828 2.6 0.2 1 0
                      event group latch 884,066 0.0 0.7 0 0
                      event range base latch 1 0.0 0 0
                      file number translation 34 38.2 0.9 0 0
                      global tx hash mapping 577,859 0.0 0 0
                      hash table column usage 4,062 0.0 0 8,757,234 0.0
                      hash table modification 16 0.0 0 2 0.0
                      i/o slave adaptor 0 0 2 0.0
                      job workq parent latch 4 100.0 0.3 0 494 8.7
                      job_queue_processes para 1,950 0.0 0 2 0.0
                      ksfv messages 0 0 4 0.0
                      ktm global data 8,219 0.0 0 0
                      lgwr LWN SCN 2,687,862 0.0 0.0 0 0
                      library cache 310,882,781 0.9 0.0 34 104,759 4.0
                      library cache load lock 30,369 0.0 0.3 0 0
                      library cache pin 153,821,358 0.1 0.0 2 0
                      library cache pin alloca 126,316,296 0.1 0.0 4 0
                      list of block allocation 2,730,808 0.3 0.0 0 0
                      loader state object free 566,036 0.1 0.0 0 0
                      longop free list parent 197,368 0.0 0 8,390 0.0
                      message pool operations 14,424 0.0 0.0 0 0
                      messages 25,931,764 0.1 0.0 1 0
                      mostly latch-free SCN 40,124,948 0.3 0.0 5 0

                      -------------------------------------------------------------
                      Latch Sleep breakdown for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036
                      -> ordered by misses desc

                      Get Spin &
                      Latch Name Requests Misses Sleeps Sleeps 1->4
                      -------------------------- -------------- ----------- ----------- ------------
                      cache buffers chains ############## 74,770,083 1,062,119 73803903/884
                      159/71439/10
                      582/0
                      redo allocation 170,107,983 3,441,055 149,631 3292872/1467
                      48/1426/9/0
                      library cache 310,882,781 2,831,747 89,240 2754499/6780
                      6/7405/2037/
                      0
                      shared pool 158,471,190 1,755,922 55,268 1704342/4836
                      9/2826/385/0
                      cas latch 538,804,153 1,553,992 6,927 1547125/6808
                      /58/1/0
                      row cache objects 161,142,207 1,176,998 27,658 1154070/1952
                      0/2560/848/0
                      process queue reference 1,893,917,184 1,119,215 106,454 78758/4351/1
                      36/0/0
                      -------------------------------------------------------------
                      Library Cache Activity for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036
                      ->"Pct Misses" should be very low

                      Get Pct Pin Pct Invali-
                      Namespace Requests Miss Requests Miss Reloads dations
                      --------------- ------------ ------ -------------- ------ ---------- --------
                      BODY 3,137,721 0.0 3,137,722 0.0 0 0
                      CLUSTER 6,741 0.1 4,420 0.2 0 0
                      INDEX 353,708 0.8 361,065 1.2 0 0
                      SQL AREA 17,052,073 0.3 54,615,678 0.9 410,682 19,628
                      TABLE/PROCEDURE 3,521,884 0.2 12,922,737 0.1 619 0
                      TRIGGER 1,975,977 0.0 1,975,977 0.0 1 0
                      -------------------------------------------------------------

                      SGA Memory Summary for DB: P7IN1 Instance: P7IN1 Snaps: 582946 -583036

                      SGA regions Size in Bytes
                      ------------------------------ ----------------
                      Database Buffers 22,330,474,496
                      Fixed Size 779,288
                      Redo Buffers 17,051,648
                      Variable Size 7,180,648,448
                      ----------------
                      sum 29,528,953,880
                      • 8. Re: What are the best solutions for data warehouse configuration in 10gR2
                        Tapan Kumar Saha
                        Hello Hemant,

                        Your solution is much appreciated by the Client, which I presented before them.
                        Now the next question raised was the duration of the downtime. Could you help me estimate how much would be the downtime for Production for such an activity.

                        I will be definitely performing the activity in our Dev/Test environment to reach the exact figures, but I need to present a rough breakdown.
                        Do we have any such document mentioning it? or excel sheet with this solution?

                        Thanks a lot for all you valuable thoughts.

                        Thanks,
                        Tapan
                        • 9. Re: What are the best solutions for data warehouse configuration in 10gR2
                          Srini Chavali-Oracle
                          Downtime estimates can only be determined by performing the same steps on identical hardware - your dev/test upgrades should give you a good estimate.

                          Best Practices to Minimize Downtime During Upgrade [ID 455744.1]

                          HTH
                          Srini