tanelpoder / tpt-oracle

Tanel Poder's Performance & Troubleshooting Tools for Oracle Databases
https://tanelpoder.com
Other
657 stars 312 forks source link

New script to create SQL baseline from AWR and some changes in the hint.sql #34

Closed tomasz-sroka closed 5 months ago

ursusca commented 3 years ago

v$sql_hint was a new feature in 11.2. Unfortunately, I don’t have any 11.2 databases left to verify but I checked on 12.1 and the column exists on 12.1

tomasz-sroka commented 3 years ago

At least 11.2, not sure about the stuff below 11.2. Just tested it successfully on 11.2.0.1.

tanelpoder commented 3 years ago

11.2 support is good enough. I am even thinking of defaulting to 12.1+ support by default and people still running TPT scripts on older versions would have to comment out the fancy new columns that they don't have - or perhaps use a cloned script with "11" suffix its name, in the cases where 12c+ has a great new column that's not available in 11.2...

This way I don't have to add too much dynamic complexity into these scripts. Another major piece of work (for 12c+) would be to convert the scripts from using dba to cdb views with the right container filtering logic, so that one would be able to run queries at CDB level too, but yet join to the right object names, etc in appropriate containers.

I'll merge the change, thanks!

jgebal commented 3 years ago

You can create a version in github Each version can have supported DB list. So older version of DB could be using older scripts while with newer versions you only support new DBs.

tanelpoder commented 5 months ago

new columns for hint.sql

tanelpoder commented 5 months ago

forgot to merge before closing the PR apparently

tanelpoder commented 5 months ago

I changed the hint scope display column to one column:

SQL> @hint merge

NAME                                VERSION         VERSION_OUTLINE INVERSE                             HINT_SCOPE
----------------------------------- --------------- --------------- ----------------------------------- -------------------------
MERGE_CONST_ON                      8.0.0                                                               STATEMENT
MERGE_AJ                            8.1.0           8.1.7                                               QUERY_BLOCK JOIN
MERGE_SJ                            8.1.0           8.1.7                                               QUERY_BLOCK JOIN
MV_MERGE                            9.0.0                                                               QUERY_BLOCK JOIN
MERGE                               8.1.0           10.1.0          NO_MERGE                            QUERY_BLOCK OBJECT JOIN
NO_MERGE                            8.0.0           10.1.0          MERGE                               QUERY_BLOCK OBJECT JOIN
USE_MERGE_CARTESIAN                 11.1.0.6        11.1.0.6                                            OBJECT JOIN
USE_MERGE                           8.1.0           8.1.7           NO_USE_MERGE                        OBJECT JOIN
NO_USE_MERGE                        10.1.0.3        10.1.0.3        USE_MERGE                           OBJECT JOIN