Exadata Flash disk use for TEMP tablespace — oracle-tech

    Forum Stats

  • 3,715,917 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 Flash disk use for TEMP tablespace

Abhijit Gour
Abhijit Gour Member Posts: 88
edited January 2020 in Exadata

Hi All,

We've situation in data warehouse env. - users have ability to create their own reports, so SQL queries do sorting, grouping, aggregation on runtime. TEMP tablespace usage is high for some SQL's, so there're high temp waits, one of the solution was to improve IO of TEMP by creating it on to faster disk (flash disk).

As per support note 1545103.1 – “According to Oracle Exadata best practices MOS Note: 1274318.1, there should be no griddisks configured on the flashdisks; however, this setup is supported if configuration is desired.”

Anyway, we decided to test in our non-prod by using very limited flash disk space, probably less than 2 % of total flash capacity. Results of database front testing has shown some improvement but not a great, no surprise there; still probably extend it to application testing.

Meantime, would like to know if anyone has this configuration? If yes, any suggestions? Any issues/challenges encountered while doing maintenance/patching?

Storage - Exadata X6-2, X7-2
Exadata version - 18.1.*
Flashcachemode - WriteThrough

Thank you in advance!

** I've seen found 10 year old archived post for same question, but since lot of things changed on platform, decided to post again.

-Abhijit

Abhijit GourDenis Axler

Answers

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Posts: 1,099 Employee
    edited December 2019

    What is the reason to use Flashcachemode = WriteThrough? For current systems the general recommendation is to use WriteBack. There may, of course, be some use cases where WriteThrough can be beneficial.

    With Flashcachemode=WriteBack, a current Exadata version, and DB patches not older than ~2 years, you can benefit from an Exadata feature that allows to cache large writes in flash cache, so there shouldn't be the need for a disk group on flash devices. See https://docs.oracle.com/en/engineered-systems/exadata-database-machine/dbmso/new-features-exadata-system-software-releas…  for details.

    Andris

    Abhijit GourDenis Axler
  • Abhijit Gour
    Abhijit Gour Member Posts: 88
    edited December 2019

    Thanks Andris, we've mixed workload on same appliance - ERP, OLTP, DW so deciding to change mode is being delayed because of mixed suggestions. what is your recommendation for these workloads, any detailed analysis on flashcachemode mode to share ?

    Secondly, since we're on Exadata 18.1, we've seen use of flash for Temp however those are for small to medium operations; some of our scheduled refresh job can use up to 60 GB TEMP; which is hardly using flashcache (<5%); hence would like to see if it's makes difference with flash.

    Thanks

    -Abhijit

  • User_GI0QU
    User_GI0QU Member Posts: 1 Green Ribbon
    edited January 2020

    There is no answer for this question because there is a missing piece in the I/O stack.  For large ODS or DW queries where hash joins are used and the result sets do not fit in PGA then they go to Temporary Tablespaces.  These tablespaces are on the rotating media which basically perform abysmally slow sometimes in seconds per read or write when the system is busy.  Apparently, Oracle in their haste to cover for this shortfall they are recommending placing temporary tablespace in flash however it is not on the engineering worksheet (big surprise).  You can then discover if you have 75TB of flash you need to purge it, resize it and rebuild it.  This takes 1-1.5TB per hour to purge causing huge performance degradation during the purge and resize, rebuild.  In the end Exadata is not for DW or ODS in this state.  Oracle also claims that they added a feature to accommodate this behavior but the feature for all intents and purposes does not work.  Don't bother opening a service request as it has become very apparent Oracle Support cannot help you and will not forward the problem to Server Technologies as they are too busy covering up and drinking the Exadata Kool-Aide.  Other than this terrible performance shortfall at this exorbitant price point the only thing worse is paying 25-35% Oracle Support annually for a bunch of dead beats trying to provide their boiler plate solutions to real life problems.  Oracle Support needs to go away in favor of real customer support with real product knowledge.

Sign In or Register to comment.