The only thing that SQL Optimizer needs to create permanently in your database is a plan table in order for it to get plan when optimizing SQL. If you really don’t want the product to create its own plan table, then there is also an option to specify a pre-existing plan table for it to use.
Apart from the plan table, SQL Optimizer doesn’t create permanent objects in your database. During rewrite, it will only interacts with your database to get plan. And when you do test run, it will send the SQL to your database to execute. If your SQL is a DML (INSERT, UPDATE, DELETE, or MERGE statement), it will rollback the changes after test run so nothing will be modified in your database. If you are doing index related work in SQL Optimizer (e.g. generating index recommendations in Optimize Indexes, or evaluating the impact of additional indexes in Analyze Impact), then virtual indexes will be created temporarily during the index evaluation. There virtual indexes are created only for a short time during the evaluation and they will be dropped after the process. When you test run index alternatives, then the product will have to create the corresponding indexes to test run. You will be prompted for this index creation. Once the test run is done, the created indexes will be dropped.
I have listed all the details about how the product interacts with your database in the above. So in general, you can just perform the SQL optimization action in the product without worrying about it changing your database. Once you have found a better SQL or an index recommendation, you can review them in the product first and then manually deploy them in a later time.