# PPDB Performance test on NCSA Oracle instances

XMLWordPrintable

#### Details

• Type: Story
• Status: Done
• Resolution: Done
• Fix Version/s: None
• Component/s:
• Labels:
None
• Story Points:
20
• Sprint:
DB_F18_06, DB_F18_07, DB_F18_08
• Team:
Data Access and Database

#### Description

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

#### Activity

Hide
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
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
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
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
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
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
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
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
Andy Salnikov added a comment -

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

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

#### People

Assignee:
Andy Salnikov
Reporter:
Vaikunth Thukral
Watchers:
Andy Salnikov, Christopher Pond, Christopher Stephens, Colin Slater, Fritz Mueller, Vaikunth Thukral