## AA.VV. - Trattatello definitivo sul tuning di Oracle da CLI ###############################################################
##                                                             ########################Usa Notepad++!!########################
##                       Prosegui per 4km su SS675. Ricalcolo. ###############################################################
##                                                Don Burleson ###############################################################
------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------sql_Tuning
### SQL Tuning Advisor
1. Create Tuning Task
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '74cpnuu24wmx7',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 10800,      ----3 ore
                          task_name   => 'sqltune_74cpnuu24wmx7',
                          description => 'Tuning task for statement 74cpnuu24wmx7');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

	#### Se la query non è presente nella cursor, ma negli AWR ####
	select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime,
	abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour
	from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
	executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)"
	from dba_hist_SQLSTAT a, dba_hist_snapshot b
	where sql_id='74cpnuu24wmx7' and a.snap_id=b.snap_id
	and a.instance_number=b.instance_number
	order by snap_id, a.instance_number;

	DECLARE
	 l_sql_tune_task_id  VARCHAR2(100);
	BEGIN
	 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
							 begin_snap  => 16515,
							 end_snap    => 16700,
							 sql_id      => '74cpnuu24wmx7',
							 scope       => DBMS_SQLTUNE.scope_comprehensive,
							 time_limit  => 10800,      ----3 ore
							 task_name   => 'sqltune_74cpnuu24wmx7',
							 description => 'Tuning task for statement 74cpnuu24wmx7 in AWR');
	 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
	END;
	/

2. Execute Tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sqltune_74cpnuu24wmx7');


3. Get the Tuning advisor report
set pages 444
set long 65536
set longchunksize 65536
set linesize 200
select dbms_sqltune.report_tuning_task('sqltune_74cpnuu24wmx7') from dual;

	# Different owner
select dbms_sqltune.report_tuning_task(task_name => 'SQL_TUNING_74cpnuu24wmx7', owner_name => 'DBA_CLOUD') from dual;


4. Get list of tuning task present in database
SELECT TASK_NAME, STATUS, OWNER, EXECUTION_END FROM DBA_ADVISOR_LOG WHERE TASK_NAME like '%74cpnuu24wmx7%';


5i. Drop Tuning task
SQL> execute dbms_sqltune.drop_tuning_task('DBSNMP.SQL_TUNING_74cpnuu24wmx7')

EXEC DBMS_SQLTUNE.DROP_SQLSET ('74cpnuu24wmx7');

BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'SQL_TUNING_74cpnuu24wmx7', owner => 'DBSNMP');
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'SQL_TUNING_74cpnuu24wmx7_2', owner => 'DBA_CLOUD');
END;
/

5ii. Enable Disable and drop of an existing profile:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_027b2b9529af0000');
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------History
### Find sql_ID from sql_text
SELECT * FROM V$SQLAREA WHERE sql_text like '%UPDATE_TEMP_XML%';
SELECT SQL_TEXT, SQL_ID FROM V$SQLAREA WHERE sql_text like '%UPDATE_TEMP_XML%';

### Find sql_text from sql_ID
select  sql_text as sqltext from v$sqltext where sql_id = '74cpnuu24wmx7' order by piece;


### Query per piani utilizzati e tempi esecuzione
set pages 2000
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
--add to the row below if needed: PARSING_SCHEMA_NAME,
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, CPU_TIME_TOTAL,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '74cpnuu24wmx7'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

   SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS AVG_ETIME_MS CPU_TIME_TOTAL        AVG_LIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ -------------- --------------

------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------SQL_Monitor_report
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   type=>'HTML',
   report_level=>'ALL',sql_id=>'74cpnuu24wmx7') as report
FROM dual;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------oratop(!)
oracle$ locate oratop
oracle$ oratop -i 10 / as sysdba


# Display Snapshots (append 'desc' to see the oldest):
select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;

### To get top N sql ordered by Elapsed Time (3), or by CPU Time (4), Buffer gets (7) etc.
### Provide the BEGIN & END SNAP_ID and the num. N of SQLs to be displayed.

set head on
column module format a20
set lines 200
prompt SQL Ordered by Elapsed Time

select * from (
select sql_id, module,
sum(ELAPSED_TIME_DELTA)/1000000 "Elapsed Time(s)",
sum(CPU_TIME_DELTA)/1000000 "CPU Time(s)",
sum(executions_delta) "Executions",
sum(ROWS_PROCESSED_DELTA) rows1,
sum(BUFFER_GETS_DELTA) "Buffer Gets",
sum(DISK_READS_DELTA) "Physical Reads",
sum(iowait_delta)/1000000 "IO Wait",
sum(ccwait_delta)/1000000 cc_wait,
sum(apwait_delta)/1000000 ap_wait,
sum(clwait_delta)/1000000 cl_wait,
sum(BUFFER_GETS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) gets_per_row,
sum(DISK_READS_DELTA)/decode(sum(ROWS_PROCESSED_DELTA), 0, 1, sum(ROWS_PROCESSED_DELTA)) prds_per_row,
sum(BUFFER_GETS_DELTA)/decode(sum(executions_delta), 0, 1, sum(executions_delta)) gets_per_exec
from dba_hist_sqlstat
where snap_id between &min_snap_id and &max_snap_id
group by sql_id, module
order by 4 desc
) where rownum <= &rnum;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------display_plan_SQL_ID
select * from table(dbms_xplan.display_awr('74cpnuu24wmx7'));
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------coe
## Retrieve source-code at http://kerryosborne.oracle-guy.com/scripts/coe.sql

		Local script				sql_ID			Desired plan
SQL>	@coe_xfr_sql_profile.sql	74cpnuu24wmx7	1278827506
...
SQL>@coe_xfr_sql_profile_74cpnuu24wmx7_1278827506.sql
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------find-query
### Find sql_ID from sql_TEXT
SELECT SQL_ID, SQL_TEXT, SQL_FULLTEXT
 FROM gV$SQLAREA
 WHERE sql_text like '%SP_EXTRACT_DS_IBS_FOR_RULE_STATS%' and PARSING_SCHEMA_NAME='MM_USAGE';

### by SID, SERIAL#
SELECT SID, SERIAL#, STATUS, SERVER, SQL_ID, USERNAME
FROM V$SESSION
WHERE SID = '1723';

### by SID, SERIAL# in history
SELECT SESSION_ID, SESSION_SERIAL#, SQL_ID, SAMPLE_TIME
FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE SESSION_ID = '420' and SESSION_SERIAL#='15975'
order by 4;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------display-bind-variables-for_sql_ID
column VALUE_STRING format a30
set linesize 222
set pages 444

select
sn.END_INTERVAL_TIME,
sb.NAME BIND_NAME,
sb.VALUE_STRING 
from 
DBA_HIST_SQLBIND sb,
DBA_HIST_SNAPSHOT sn
where 
sb.sql_id='74cpnuu24wmx7' and
sb.WAS_CAPTURED='YES' and
sn.snap_id=sb.snap_id
order by 
sb.snap_id,
sb.NAME;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------dba_sql_profiles
### Check the SQL Profile in the Database.
COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20
SELECT NAME,type, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;

### Check the SQL Profile internal hint information
SELECT
a.name
,b.comp_data
FROM dba_sql_profiles a
,dbmshsxp_sql_profile_attr b
WHERE a.name = b.profile_name;

## Check
select name, type, status, sql_text from dba_sql_profiles;

## Enable
exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

## Disable
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');

## Disable the SQL Profiles
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_74cpnuu24wmx7', attribute_name => 'STATUS', value => 'DISABLED');
END;
/

## Drop the SQL Profile
exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_017ba0c469720000');
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------Size-of-a-table
## Potresti essere interessato a questa query. Indica la quantità di spazio allocata per ogni tabella tenendo conto
## degli indici e degli eventuali LOB sulla tabella. Spesso sei interessato a sapere
## "Quanto spazio occupa la tabella degli ordini di acquisto, inclusi eventuali indici" anziché solo la tabella stessa.
## Puoi sempre approfondire i dettagli. Si noti che ciò richiede l'accesso alle viste DBA_ *.

COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
   owner, 
   table_name, 
   TRUNC(sum(bytes)/1024/1024) Meg,
   ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

-- Tables + Size MB
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB 
from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by MB desc -- Biggest first.
;

## Quanto spazio viene effettivamente utilizzato? Apparentemente, una volta assegnato un segmento a una tabella,
## viene sempre assegnato a quella tabella, anche se lo spazio viene liberato.

-- Tables + Rows
select owner, table_name, num_rows
 from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by num_rows desc -- Biggest first.
;

## Nota: queste sono stime, rese più accurate con la raccolta delle statistiche:
exec dbms_utility.analyze_schema(user,'COMPUTE');
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------Sessions
break on ist skip 1
set linesize 180
set pagesize 200
col machine for a15
col username for a15
col cmd for a15
col piece noprint
compute count label TOTALE of SID on ist
 select s.inst_id ist,s.status,s.sid, s.serial#,s.sql_id, decode(s.command,6,'UPDATE',2,'INSERT',3,'SELECT',to_char(s.command)) cmd,
 s.username, LAST_CALL_ET, lockwait, s.machine, s.sql_hash_value , p.spid OS_PROCESS
 from gv$session s, gV$process p
 where s.inst_id=p.inst_id
 and s.status in('ACTIVE','KILLED','INACTIVE') and s.username is not null and p.addr=s.paddr
 --and s.machine='iamapp05'
 --and s.machine like '%pecweb%'
 --and s.username='PERF_CASTELLIM'
 and s.sql_id='74cpnuu24wmx7'
 order by s.inst_id,s.LAST_CALL_ET
/

## You determine the runtime maximum number of sessions with the SESSIONS parameter, which derives the PROCESSES parameter.
SQL> show parameter sessions
SQL> select
   current_utilization, limit_value
from
   v$resource_limit
where
   resource_name='sessions';
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------Blocking-sessions+KILL
## Find blocking_session
set pages 444
set lines 444
select
   username,
   blocking_session, SQL_ID, ACTION,
   sid,
   serial#,
   wait_class,
   seconds_in_wait
from   v$session
where   blocking_session is not NULL
order by   seconds_in_wait desc;

## Kill
ALTER SYSTEM KILL SESSION 'sid, serial#' IMMEDIATE;

## Verify
SELECT USERNAME, SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = 'username'


## Tutte le sessioni sul DB (istanza corrente)
SQL> SELECT USERNAME, sid, serial#, status, username FROM v$session;

## Tutte le sessioni di USER
SQL> select username, inst_id, sid, serial# from gv$session where username='USER' order by 2, 3;

## Genera le espressioni di kill per tutte le sessioni di USER, su tutte le istanze (GlobalView)
SQL>
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate;' from gv$session where username='USER';

## Se hai USER e SERIAL# ti dico l'istanza per il kill
SQL> select inst_id,SQL_ID,sid,serial# from gv$session where username='EMONET' and SERIAL#='31385';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        265      31385

SQL> alter system kill session '265,31385,@1' IMMEDIATE;


### Senza il kill IMMEDIATE o con ORA-00026 (missing or invalid session ID) o ORA-00031 (session marked for kill),
### devi killare lo SPID da OS
SQL> SELECT s.sid, s.serial#, p.spid
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND s.username = 'EMONET';

       SID    SERIAL# SPID
---------- ---------- ------------------------
       265      31385 94962
	   ...

[oracle@oda]$ ps -afe|grep 94962|grep -v grep
oracle   94962     1  0 Apr07 ?        00:00:00 ora_j000_EMONET1
[oracle@oda]$ kill -9 94962
[oracle@oda]$ ps -afe|grep 94962|grep -v grep
[oracle@oda]$
-------------

SQL> select instance_number,session_serial#,min(sample_time) from dba_hist_active_sess_history
 where session_id=6247 and sql_id='20bha0krtzyrk' and blocking_session is not null
 group by instance_number,session_serial# order by 3
/
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------enq: TX - Row Lock Contention Error

### http://www.dba-oracle.com/t_enq_tx_row_lock_contention.htm

## For which SQL is currently waiting on (use gv$session in cluster env.)
select    sid,    sql_text
from    v$session s,    v$sql q
where    sid in    (select       sid    from       v$session   where       state in ('WAITING')   and       wait_class != 'Idle'
   and       event='enq: TX - row lock contention'
   and      (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

## The blocking session is:
select
    blocking_session,    sid,    serial#,    wait_class,    seconds_in_wait
from    v$session
where    blocking_session is not NULL
order by    blocking_session;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------Stats

### Oggetti con stats invalide, by date
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select
      'TABELLA' "OGGETTO", TABLE_NAME "Nome Oggetto", NUM_ROWS "Righe", LAST_ANALYZED
from dba_TAB_STATISTICS
where STALE_STATS='YES' and OWNER= 'AUDIENCE_DATA'
UNION
select
      'INDICE', INDEX_NAME, NUM_ROWS, LAST_ANALYZED
from dba_ind_statistics
where STALE_STATS='YES' and OWNER='AUDIENCE_DATA'
order by 4 desc;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------Interval+Retention
## The script returns the current AWR interval values IN MINUTES, awr_settings.sql
select
       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

Snapshot Interval Retention Interval
----------------- ------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------Report
# Display Snapshots (append 'desc' to see the oldest):
select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;

### ADDM – Automatic Database Diagnostic Monitor
@?/rdbms/admin/addmrpt.sql

### AWR – Automatic Workload Repository
@$ORACLE_HOME/rdbms/admin/awrrpt.sql					(for Single Instance Environment)
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql					(for Oracle RAC Environment)
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql					(for a sql_ID)
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql					(for Comparing two reports over time)

### ASH – Active Session History
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
@$ORACLE_HOME/rdbms/admin/ashrpti.sql					(for Oracle RAC Environment)
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------Are-u-RAC?
##You can query for these background processes to tell if you are using RAC:
select name, value from   v$parameter where   name='cluster_database';

NAME                  VALUE
--------------------- ---------------------
cluster_database      TRUE
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------About
SET LINES 200
SET PAGES 999
COLUMN INSTANCE_NAME FOR A20
SELECT INSTANCE_NAME, HOST_NAME, VERSION, TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME", STATUS
FROM V$INSTANCE;

## Check the startup history timing of Oracle Instance
COL INSTANCE_NAME FOR A10
SELECT INSTANCE_NAME, TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"
 FROM DBA_HIST_DATABASE_INSTANCE ORDER BY STARTUP_TIME DESC;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------Alert-log
## For 11g and later releases
$ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace/

## For 9i and 10g
$ORACLE_BASE/admin/$ORACLE_SID/bdump/

## For rest of cases without any clues
$ORACLE_HOME/rdbms/log/

## Find it
SQL> show parameter background_dump_dest;
SQL> select value from v$parameter where name = 'background_dump_dest';

oracle$ find $ORACLE_BASE -type f -name alert_$ORACLE_SID.log
oracle$ locate alert_$ORACLE_SID.log
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------RedoLog
## RedoLog Switch Frequency
SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <8;

## RedoLog Size
SELECT
   (SELECT
      ROUND (AVG (BYTES) / 1024 / 1024, 2)
   FROM
      V$LOG) AS "Redo size (MB)",
   ROUND ( (20 / AVERAGE_PERIOD) * (SELECT AVG (BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM
   (SELECT AVG ( (NEXT_TIME - FIRST_TIME) * 24 * 60) AS   
   AVERAGE_PERIOD
   FROM
   V$ARCHIVED_LOG
   WHERE
   FIRST_TIME > SYSDATE - 3
   AND
   TO_CHAR (FIRST_TIME, 'HH24:MI') BETWEEN '16:00' AND '17:00');
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------Tablespace
#### Sopra a soglia ####
set line  200
set pages 200
select  a.TABLESPACE_NAME,a.BYTES bytes_used,b.BYTES bytes_free,b.largest, a.MAX_BYTES, round(((a.BYTES-b.BYTES)/a.MAX_BYTES)*100,2) percent_used
from (select TABLESPACE_NAME,sum(BYTES) BYTES, SUM (decode(maxbytes,0,bytes,maxbytes)) MAX_BYTES from dba_data_files group by TABLESPACE_NAME )a,
(select TABLESPACE_NAME,sum(BYTES) BYTES ,max(BYTES) largest from dba_free_space group by TABLESPACE_NAME)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
AND ((a.BYTES-b.BYTES)/a.MAX_BYTES)*100 > 92
ORDER BY percent_used DESC;

#### Tutti ####
set line  200
set pages 200
select  a.TABLESPACE_NAME,a.BYTES bytes_used,b.BYTES bytes_free,b.largest, a.MAX_BYTES, round(((a.BYTES-b.BYTES)/a.MAX_BYTES)*100,2) percent_used
from (select TABLESPACE_NAME,sum(BYTES) BYTES, SUM (decode(maxbytes,0,bytes,maxbytes)) MAX_BYTES from dba_data_files group by TABLESPACE_NAME )a,
(select TABLESPACE_NAME,sum(BYTES) BYTES ,max(BYTES) largest from dba_free_space group by TABLESPACE_NAME)b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
ORDER BY percent_used DESC;

#### <TABLESPACE> ####
    select  a.TABLESPACE_NAME,a.BYTES bytes_used,b.BYTES bytes_free,b.largest, a.MAX_BYTES, round(((a.BYTES-b.BYTES)/a.MAX_BYTES)*100,2) percent_used
    from (select TABLESPACE_NAME,sum(BYTES) BYTES, SUM (decode(maxbytes,0,bytes,maxbytes)) MAX_BYTES from dba_data_files group by TABLESPACE_NAME )a,
    (select TABLESPACE_NAME,sum(BYTES) BYTES ,max(BYTES) largest from dba_free_space group by TABLESPACE_NAME)b
    where a.TABLESPACE_NAME=b.TABLESPACE_NAME
    AND a.TABLESPACE_NAME='TABLESPACE';

############################### temporaneo ##############

### Spazio utilizzato (non conta quello in extend) ###
set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,(free_blocks*8)/1024/1024 FreeSpaceGB,(used_blocks*8)/1024/1024 UsedSpaceGB,(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;



SELECT tablespace_name, file_name, bytes  FROM dba_temp_files;

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/oracle/oradata/netaprod/temp/temp_temp.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;   

    ALTER  TABLESPACE TEMP ADD
        TEMPFILE '/oracle/oradata/PROSAEL/temp_undo/HOPESAEL/temp04.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

########################## tablespace ################

SELECT file_name, TABLESPACE_NAME, bytes/1024/1024 size_mb, MAXBYTES/1024/1024, AUTOEXTENSIBLE, INCREMENT_BY/1024/1024
 FROM dba_data_files 
 WHERE tablespace_name = 'BAC_ADAPTER';

ALTER TABLESPACE SIGP_LAVORO ADD DATAFILE '/oradata/SGP/sigp_lavoro041' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;
alter database datafile '/u02/app/oracle/oradata/BDA/bda/datafile/bac_adapter.471.852036957' RESIZE 3072M;						--AMM
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------Oracle Sorting: In-Memory Versus Spill to Disk
--------------------------------------------------------------------------------------https://www.bassocantor.com/blog/sorting
select inst_id INST, sql_ID,
roud(MAX_TEMPSEG_SIZE/1000000000) TEMPSZ,
roud(ESTIMATED_OPTIMAL_SIZE/1000000000) OPT_SIZE,
operation_type,
roud(ESTIMATED_ONEPASS_SIZE/1000000000,1) ONEP_SIZE,
OPTIMAL_EXECUTIONS OPT_EXECS,
ONEPASS_EXECUTIONS ONEP_EXECS,
MULTIPASSES_EXECUTIONS MULTI_EXECS,
from gv$SQL_WORKAREA
where MAX_TEMPSEG_SIZE > 999999999
and inst_ID = 6
order by 3 desc;



The view V$Sql_Workarea has some great metrics, but this view can be confusing. Some of the fields refer to in-memory operations,
 and others refer to disk operations. Here are some of the key fields:

MAX_TEMPSEG_SIZE: 			For operations that spill to disk, this is the largest number of bytes used in temp segment.
LAST_TEMPSEG_SIZE: 			Similar to above, size (bytes) of the most recent temp segment used when operation spills to disk.
ESTIMATED_OPTIMAL_SIZE: 	Estimated size (bytes) to perform operation completely in-memory. This is a theoretical number.
LAST_MEMORY_USED: 			Size of memory used (bytes) in last execution.


Column											Datatype		Description
ADDRESS											RAW(4 | 8)		Address of the parent cursor handle

HASH_VALUE										NUMBER			Hash value of the parent statement in the library cache.
																Two columns PARENT_HANDLE and HASH_VALUE can be used to join with V$SQLAREA
																to locate the parent cursor.

SQL_ID											VARCHAR2(13)	SQL identifier of the parent statement in the library cache

CHILD_NUMBER									NUMBER			Number of the child cursor that uses this work area.
																The columns PARENT_HANDLE, HASH_VALUE, and CHILD_NUMBER can be used
																to join with V$SQL to locate the child cursor using this area.

WORKAREA_ADDRESS								RAW(4 | 8)		Address of the work area handle. This is the primary key for the view.

OPERATION_TYPE									VARCHAR2(40)	Type of operation using the work area. Can include values such as
																SORT, HASH JOIN, GROUP BY, BUFFER, BITMAP MERGE, and BITMAP CONSTRUCTION.

OPERATION_ID									NUMBER			A unique number used to identify the operation in the execution plan.
																This identifier can be joined to V$SQL_PLAN to locate the operation that uses this work area.

POLICY											VARCHAR2(10)	Sizing policy for this work area (MANUAL or AUTO)

ESTIMATED_OPTIMAL_SIZE							NUMBER			Estimated size (in bytes) required by this work area to execute the operation
																completely in memory (optimal execution).
																Derived from either optimizer statistics or previous executions.

ESTIMATED_ONEPASS_SIZE							NUMBER			Estimated size (in bytes) required by this work area to execute the operation
																in a single pass. Derived from either optimizer statistics or previous executions.

LAST_MEMORY_USED								NUMBER			Memory (in bytes) used by this work area during the last execution of the cursor

LAST_EXECUTION									VARCHAR2(10)	Indicates whether this work area runs using
																OPTIMAL, ONE PASS, or ONE PASS memory requirement (or MULTI-PASS),
																during the last execution of the cursor

LAST_DEGREE										NUMBER			Degree of parallelism used during the last execution of this operation

TOTAL_EXECUTIONS								NUMBER			Number of times this work area was active

OPTIMAL_EXECUTIONS								NUMBER			Number of times this work area ran in optimal mode

ONEPASS_EXECUTIONS								NUMBER			Number of times this work area ran in one-pass mode

MULTIPASSES_EXECUTIONS							NUMBER			Number of times this work area ran below the one-pass memory requirement

ACTIVE_TIME										NUMBER			Average time this work area is active (in hundredths of a second)

MAX_TEMPSEG_SIZE								NUMBER			Maximum temporary segment size (in bytes) created by an instantiation of this work area.
																This column is NULL if this work area has never spilled to disk.

LAST_TEMPSEG_SIZE								NUMBER			Temporary segment size (in bytes) created in the last instantiation of this work area.
																This column is NULL if the last instantiation of this work area did not spill to disk.

CON_ID											NUMBER			The ID of the container to which the data pertains. Possible values include:
																0: This value is used for rows containing data that pertain to the entire CDB.
																									This value is also used for rows in non-CDBs.
																1: This value is used for rows containing data that pertain to only the root
																n: Where n is the applicable container ID for the rows containing data
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------

E questo è tutto quello che so di Oracle tuning.
... bonus: le stats degli oggetti Siebel non si ricalcolano.
Matteo