SQL-Profile fundamentally are additional optimizer statistics at the SQL statement level generated by SQL-Tuning-Advisor to fill the gaps of Oracle Optimizer.
My question is, can these additional finer optimizer-statistics within profiles, be shared/used by other similar SQL statements. Or is it that only that specific SQL for which the sql-profile was generated benefits?
Yes - these hints are supported even outside a SQL profile, eg
SQL> set autotrace traceonly explain
SQL> select *
2 from t
3 where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2290 | 315K| 474 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2290 | 315K| 474 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
SQL>
SQL> select /*+ opt_estimate(@"SEL$1", TABLE, "T"@"SEL$1", SCALE_ROWS=2) */ *
2 from t
3 where owner = 'SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4579 | 630K| 474 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4579 | 630K| 474 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
MOS note 1955195.1 has a nice little script to assist here.
But I stress - the optimal number of hints in your code base is...zero. Look at things like optimizer stats, histograms, extended states, sql plan management etc before resorting to hints