How does the segment advisor (dbms_space.asa_recommendations) determine that table compression is recommended and why does it only recommend to "compress for oltp" when other compression options are available?
why does it only recommend...Straight out of the docs
https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-space-for-schema-objects.html#GUID-E9B57A45-2688-42B6-9782-D49702D51F24 "In addition, the Automatic Segment Advisor evaluates tables that are at least 10MB and that have at least three indexes to determine the amount of space saved if the tables are compressed with the advanced row compression method."
This makes sense, because OLTP compression can be done really without much impact to an application - you get a small bump in CPU usage, but the usage of the rows/blocks is (by and large) unaltered. (You might get some increased contention but you'd have to be unlucky).
Conversely, other compressions (full compression, HCC etc etc) are changing the concurrency characteristics of those rows. It might impact on their row locking behaviour etc. We can't really advise you to do that without knowing your application requirements.
In terms of the "how" it comes up with reecommendations - we'll look at the statistics and also sample some data. You might seen the occasional "create table as select...sample" in your AWR reports as we look at some real data.