How to enable and disable Automatic SQL tuning

Enable:

BEGIN
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

Disable:

BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

If desirable, Oracle also lets you enable or disable all automated maintenance tasks for all windows. Do this by calling the ENABLE or DISABLE procedure with no arguments:

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

Sources:
dbms_auto_task_admin
Configuring Automated Maintenance Tasks

How to join v$database with v$instance

Here is a useful query which displays basic information about the database, using v$database and v$instance.
Take the opportunity to calculate the uptime for the database by subtracting the value of startup_time from the current date.

col platform_name format a30
col open_mode format a20
col host_name format a20
col version format a10
col status format a20
col uptime format a30
col name format a10
set lines 300

SELECT D.NAME,D.PLATFORM_NAME,D.CREATED, D.OPEN_MODE,I.HOST_NAME,I.VERSION, I.ARCHIVER,I.STATUS,
   TO_DSINTERVAL( TO_CHAR(
                                    TO_TIMESTAMP(SYSDATE)-I.STARTUP_TIME
                                   )
                      ) "UPTIME", 
                      (SELECT ROUND(SUM(BYTES)/1024/1024/1024) FROM DBA_DATA_FILES) "DB size GB",  
                      ( SELECT ROUND(BYTES/1024/1024/1024) "mem GB" FROM V$SGAINFO WHERE NAME = 'Maximum SGA Size' ) "SGA max size GB"
  FROM V$DATABASE D INNER JOIN V$INSTANCE I 
  ON UPPER(D.NAME) = UPPER(I.INSTANCE_NAME);

Result may look like the following:

NAME	PLATFORM_NAME	CREATED	OPEN_MODE	HOST_NAME	VERSION	ARCHIVER	STATUS	UPTIME	DB size GB	SGA max size GB
proddb01	Linux x86 64-bit	10.12.2015 14:16:46	READ WRITE	prodserver01.mycompany.com	11.2.0.4.0	STARTED	OPEN	+00 13:59:55.000000	929	12

What are AWR and ADDM and how are they related?

In Oracle 10g, one of the major changes to the database was the AWR and the ADDM.

AWR (Automatic Workload Repository)
Purpose: To collect statistics about the database automatically, without DBA intervention.
Snapshots of the database are taken every hour by default, and statistics derived from these snapshots are saved for 7 days by default.
The statistics are created through a separate background process called MMON (manageability monitor process).

ADDM (Automatic Database Diagnostic Monitor)
Purpose: To analyze the data in the AWR, without DBA intervention. The ADDM will analyze the statistics and save the results in the database. If the parameter STATISTICS_LEVEL is set to TYPICAL or ALL, ADDM is triggered every hour by default, right after a new snapshot has been taken by the AWR.

How to find the AWR snapshots currently available in the database

SET LINES 100 PAGES 999

SELECT SNAP_ID,SNAP_LEVEL, TO_CHAR(BEGIN_INTERVAL_TIME, 'dd.mm.yy hh24:mi:ss') "Starttime"
FROM DBA_HIST_SNAPSHOT 
ORDER BY 1
/

How to use the dbms_explan.display_awr function to find the execution plan of a query in the AWR

set linesize 200
set pagesize 0
select * from table 
(dbms_xplan.display_awr( '44snxh96pfuxb',1084138060,null, 'TYPICAL'));

Where
- the first argument is the SQL id
- the second argument is plan hash value (optional)
- the third argument the DB_ID. If not specified, the value from V$DATABASE is used
- the fourth argument is the format string. TYPICAL is the default value.

Source: Oracle Documentation