Exadata - Any side effects on smart scan and storage index? — oracle-tech

    Forum Stats

  • 3,715,918 Users
  • 2,242,907 Discussions
  • 7,845,683 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Exadata - Any side effects on smart scan and storage index?

swethphoenix
swethphoenix Member Posts: 2
edited June 2020 in Exadata

Greetings All,

We are using Exadata6 and for our application [OLTP] we have a requirement to use a table with 100 million rows. The table is straight forward with 30 columns of varchar2 data type  [no blob/clob]. We dont have any index defined for the table. We are querying this table [100 million rows] with few conditions and we noticed our response is good(with in a sec) and explain plan shows storage full scan. We did a load testing by inserts and updates happening in background and multiple searches concurrently. 5000 searches per 5 mins and we continued this for 15 mins. The response is good even.

Question : Is there any side effects of purely going with storage index - will this have any effects on CPU/Memory. We are in a shared database , will this impact others?

Kindly advise.

Thanks

Tagged:
swethphoenixKazuhiro

Answers

  • Kazuhiro
    Kazuhiro Member Posts: 20 Blue Ribbon
    edited June 2020

    Hi

    >Is there any side effects of purely going with storage index - will this have any effects on CPU/Memory.

    It will impact *storage server's* CPU because of the consequence of offloading operation (such as predicate filtering, uncompressing and other data manipulations) from database servers to storage servers. It may reduce the maximum number of transactions because all the same query running concurrently will execute independent direct path reads bypassing buffer cache in database servers and increase the load of storage servers. Besides, if CPU utilization becomes high in storage servers, it will stop smart scan and return back normal blocks. This is kind of complex topic and I can't explain all, but there's some clue written in Expert Oracle Exadata (pp.55).

    >We are in a shared database , will this impact others?

    Yes. Storage servers are shared with databases that use the same disk groups. If the OLTP load becomes somehow higher than expected and CPU utilization of storage servers become 100%, then I/O performance from other databases will be affected.

    I think for OLTP applications I would prefer using conventional index to reduce the number of blocks to read in the first place. In my opinion, smart scan is basically best suited for DWH type of queries that need to read and transfer a lot of blocks from storage layer and filter and aggregate in database servers, with a relatively small amount of concurrency.

    Best regards,

    Kazuhiro

    swethphoenixswethphoenix
  • swethphoenix
    swethphoenix Member Posts: 2
    edited June 2020

    Thank you for your reply and giving additional insight. I will check on Expert Oracle exadata. We will take on route with using conventional index.

    Thank you

    Kazuhiro
Sign In or Register to comment.