Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-14712

PPDB Performance test on NCSA Oracle instances

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: L1 Database
    • Labels:
      None

      Description

      Run ap_proto against Oracle instance at NCSA and extract some performance numbers from these tests.

        Attachments

        1. dm-14712-oracle-iot-narrow-15x15-visit_real.png
          67 kB
          Andy Salnikov
        2. dm-14712-oracle-iot-narrow-15x15-visit_real-fit.png
          30 kB
          Andy Salnikov
        3. dm-14712-oracle-spin-15x15-1-graphana-load.png
          296 kB
          Andy Salnikov
        4. dm-14712-oracle-spin-15x15-1-graphana-net-out.png
          95 kB
          Andy Salnikov
        5. dm-14712-oracle-spin-15x15-1-visit_real.png
          38 kB
          Andy Salnikov
        6. dm-14712-oracle-ssd-15x15-20180712T172555-hint.png
          50 kB
          Andy Salnikov
        7. dm-14712-oracle-ssd-15x15-20180718T135354-pre-load-stats.png
          77 kB
          Andy Salnikov
        8. dm-14712-oracle-ssd-15x15-20180724T154618-iot-visit_real.png
          56 kB
          Andy Salnikov
        9. dm-14712-oracle-ssd-15x15-2-graphana-load.png
          383 kB
          Andy Salnikov
        10. dm-14712-oracle-ssd-15x15-2-visit_real.png
          37 kB
          Andy Salnikov
        11. dm-14712-oracle-ssd-5x5-visit_real.png
          35 kB
          Andy Salnikov
        12. dm-9301-pg-15x15-nvme-upsert-06-long-visit_real.png
          54 kB
          Andy Salnikov
        13. dm-9301-pg-15x15-nvme-upsert-06-long-visit_real-fit.png
          25 kB
          Andy Salnikov
        14. lsst_ap_awr_reports.tar.gz
          873 kB
          Christopher Stephens
        15. ora-1-def-5x5.png
          34 kB
          Andy Salnikov
        16. ora-2-ssd-1x1.png
          85 kB
          Andy Salnikov
        17. ora-3-ssd-5x5.png
          54 kB
          Andy Salnikov
        18. ora-4-spin-5x5.png
          47 kB
          Andy Salnikov
        19. pg-ssd-1x1.png
          94 kB
          Andy Salnikov
        20. pg-ssd-5x5.png
          86 kB
          Andy Salnikov

          Issue Links

            Activity

            Hide
            salnikov Andy Salnikov added a comment - - edited

            More hints tests

            Tried once again to use hints with DiaObjectLast select to force it to do INDEX RANGE scan instead of FULL INDEX scan during the bootstrap stage. Here are variations that I tried:

            • SELECT /*+ INDEX_RS_ASC("X15_DiaObjectLast" "X15_PK_DiaObjectLast") */ ...
            • SELECT /*+ INDEX_RS("X15_DiaObjectLast" "X15_PK_DiaObjectLast") */ ...
            • SELECT /*+ INDEX_RS_ASC(@SEL$1 "X15_DiaObjectLast" "X15_PK_DiaObjectLast") */ ... (SEL$1 comes from analyzer when it chooses FULL INDEX)

            Nothing worked so far, Oracle looks unconvinced   or I'm doing it wrong.

            What is also interesting to me is that INDEX_RS hint does not appear in official Oracle docs, but there are some articles on the Internet which say that it should work. I'm not convinced that INDEX_RS is a thing.

            Show
            salnikov Andy Salnikov added a comment - - edited More hints tests Tried once again to use hints with DiaObjectLast select to force it to do INDEX RANGE scan instead of FULL INDEX scan during the bootstrap stage. Here are variations that I tried: SELECT /*+ INDEX_RS_ASC("X15_DiaObjectLast" "X15_PK_DiaObjectLast") */ ... SELECT /*+ INDEX_RS("X15_DiaObjectLast" "X15_PK_DiaObjectLast") */ ... SELECT /*+ INDEX_RS_ASC(@SEL$1 "X15_DiaObjectLast" "X15_PK_DiaObjectLast") */ ... (SEL$1 comes from analyzer when it chooses FULL INDEX) Nothing worked so far, Oracle looks unconvinced   or I'm doing it wrong. What is also interesting to me is that INDEX_RS hint does not appear in official Oracle docs, but there are some articles on the Internet which say that it should work. I'm not convinced that INDEX_RS is a thing.
            Hide
            salnikov Andy Salnikov added a comment -

            Christopher Stephens, I asked above for details about Netapp storage array that you have in RAC. Do you have access to that info and can you share it with us?

             

            Show
            salnikov Andy Salnikov added a comment - Christopher Stephens , I asked above for details about Netapp storage array that you have in RAC. Do you have access to that info and can you share it with us?  
            Hide
            salnikov Andy Salnikov added a comment -

            For the record - Chris suggested couple of other hints to try with DiaObjectLast table:

            • /*+ dynamic_sampling(“X15_DiaObjectLast” 4) */
            • /*+ cardinality("X15_DiaObjectLast" 10000000) */

            None of these two changed optimizer behavior. For latter we also tried variation with large number of records:

            • /*+ cardinality("X15_DiaObjectLast" 1000000000) */

            and extra hint:

            • /*+ FIRST_ROWS_1 cardinality(“X15_DiaObjectLast” 1000000000) */

            without any improvement.

            Show
            salnikov Andy Salnikov added a comment - For the record - Chris suggested couple of other hints to try with DiaObjectLast table: /*+ dynamic_sampling(“X15_DiaObjectLast” 4) */ /*+ cardinality("X15_DiaObjectLast" 10000000) */ None of these two changed optimizer behavior. For latter we also tried variation with large number of records: /*+ cardinality("X15_DiaObjectLast" 1000000000) */ and extra hint: /*+ FIRST_ROWS_1 cardinality(“X15_DiaObjectLast” 1000000000) */ without any improvement.
            Hide
            salnikov Andy Salnikov added a comment -

            To summarize current situation with DiaObjectLast table:

            • With freshly initialized schema optimizer prefers (FAST) FULL INDEX SCAN which is significantly worse than INDEX RANGE SCAN plan.
            • It looks like optimizer needs to have significant volume of data in a table before it switches to a more efficient plan, I estimate some thing like 10-20 million rows. I think stats collection has to be enabled for that too.
            • We failed to find a way to force Oracle to lock into a better plan using query hints.
            • IOT works reasonably well if table has small number of columns, I think this is what we want for production.

            I think I'm going to close the ticket for now and switch to studying DiaSource performance on a new ticket.

            Christopher Pond, if you manage to find specs for storage array, please add those here even if ticket is closed.

            Show
            salnikov Andy Salnikov added a comment - To summarize current situation with DiaObjectLast table: With freshly initialized schema optimizer prefers (FAST) FULL INDEX SCAN which is significantly worse than INDEX RANGE SCAN plan. It looks like optimizer needs to have significant volume of data in a table before it switches to a more efficient plan, I estimate some thing like 10-20 million rows. I think stats collection has to be enabled for that too. We failed to find a way to force Oracle to lock into a better plan using query hints. IOT works reasonably well if table has small number of columns, I think this is what we want for production. I think I'm going to close the ticket for now and switch to studying DiaSource performance on a new ticket. Christopher Pond , if you manage to find specs for storage array, please add those here even if ticket is closed.
            Hide
            salnikov Andy Salnikov added a comment -

            Closing. Nothing to review, but please add anything relevant if you think it belongs here.

            Show
            salnikov Andy Salnikov added a comment - Closing. Nothing to review, but please add anything relevant if you think it belongs here.

              People

              Assignee:
              salnikov Andy Salnikov
              Reporter:
              vaikunth Vaikunth Thukral
              Watchers:
              Andy Salnikov, Christopher Pond, Christopher Stephens, Colin Slater, Fritz Mueller, Vaikunth Thukral
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  CI Builds

                  No builds found.