DB AUTOTASK HISTORY

Oracle11g

 set colsep "|"
 set linesize 32767
 set pagesize 10000
 column WINDOW_NAME         format A20
 column CLIENT_NAME         format A35
 column JOB_STATUS          format A15
 column JOB_START_TIME      format A25
 column JOB_DURATION        format A20
 column JOB_ERROR           format 9999
 SELECT
 WINDOW_NAME       ,
 CLIENT_NAME       ,
 JOB_STATUS        ,
 JOB_START_TIME    ,
 JOB_DURATION      ,
 JOB_ERROR
 FROM DBA_AUTOTASK_JOB_HISTORY
 WHERE job_start_time >= SYSDATE -7
 ORDER BY job_start_time DESC;

Example

WINDOW_NAME         |CLIENT_NAME                        |JOB_STATUS     |JOB_START_TIME           |JOB_DURATION        |JOB_ERROR
--------------------|-----------------------------------|---------------|-------------------------|--------------------|---------
SATURDAY_WINDOW     |auto optimizer stats collection    |SUCCEEDED      |31-MAY-14 10.02.15.939349|+000 00:08:12       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |auto space advisor                 |SUCCEEDED      |31-MAY-14 10.02.11.861930|+000 00:03:00       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |auto space advisor                 |SUCCEEDED      |31-MAY-14 06.00.07.041989|+000 00:03:43       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |sql tuning advisor                 |SUCCEEDED      |31-MAY-14 06.00.02.070044|+000 00:00:34       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |auto optimizer stats collection    |SUCCEEDED      |31-MAY-14 06.00.02.050251|+000 00:14:06       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |
FRIDAY_WINDOW       |auto space advisor                 |SUCCEEDED      |30-MAY-14 10.00.08.857137|+000 00:11:15       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

FRIDAY_WINDOW       |sql tuning advisor                 |SUCCEEDED      |30-MAY-14 10.00.03.001194|+000 00:04:27       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

FRIDAY_WINDOW       |auto optimizer stats collection    |SUCCEEDED      |30-MAY-14 10.00.02.975182|+000 00:37:38       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

THURSDAY_WINDOW     |auto space advisor                 |SUCCEEDED      |29-MAY-14 10.00.08.739959|+000 00:09:14       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

THURSDAY_WINDOW     |sql tuning advisor                 |SUCCEEDED      |29-MAY-14 10.00.03.313542|+000 00:00:49       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

THURSDAY_WINDOW     |auto optimizer stats collection    |SUCCEEDED      |29-MAY-14 10.00.03.293317|+000 00:28:57       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

WEDNESDAY_WINDOW    |auto space advisor                 |SUCCEEDED      |28-MAY-14 10.00.07.928878|+000 00:09:56       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

WEDNESDAY_WINDOW    |sql tuning advisor                 |SUCCEEDED      |28-MAY-14 10.00.02.807485|+000 00:00:37       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

WEDNESDAY_WINDOW    |auto optimizer stats collection    |SUCCEEDED      |28-MAY-14 10.00.02.786171|+000 00:27:14       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

TUESDAY_WINDOW      |auto space advisor                 |SUCCEEDED      |27-MAY-14 10.00.07.365168|+000 00:09:57       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

TUESDAY_WINDOW      |sql tuning advisor                 |SUCCEEDED      |27-MAY-14 10.00.02.432287|+000 00:00:38       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

TUESDAY_WINDOW      |auto optimizer stats collection    |SUCCEEDED      |27-MAY-14 10.00.02.409101|+000 00:28:39       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

MONDAY_WINDOW       |auto space advisor                 |SUCCEEDED      |26-MAY-14 10.00.07.915476|+000 00:09:25       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

MONDAY_WINDOW       |sql tuning advisor                 |SUCCEEDED      |26-MAY-14 10.00.03.017651|+000 00:01:07       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

MONDAY_WINDOW       |auto optimizer stats collection    |SUCCEEDED      |26-MAY-14 10.00.02.992055|+000 00:24:50       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |
SUNDAY_WINDOW       |auto space advisor                 |SUCCEEDED      |25-MAY-14 10.08.36.654064|+000 00:05:50       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto optimizer stats collection    |SUCCEEDED      |25-MAY-14 10.08.30.915042|+000 00:15:09       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto space advisor                 |SUCCEEDED      |25-MAY-14 06.08.08.127302|+000 00:03:32       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto optimizer stats collection    |SUCCEEDED      |25-MAY-14 06.08.03.072491|+000 00:11:32       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto space advisor                 |SUCCEEDED      |25-MAY-14 02.07.52.097414|+000 00:03:08       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto optimizer stats collection    |SUCCEEDED      |25-MAY-14 02.07.46.551639|+000 00:11:26       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto optimizer stats collection    |SUCCEEDED      |25-MAY-14 10.07.15.905325|+000 00:07:36       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto space advisor                 |SUCCEEDED      |25-MAY-14 10.07.11.389227|+000 00:02:35       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto space advisor                 |SUCCEEDED      |25-MAY-14 06.00.08.351805|+000 00:04:00       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |sql tuning advisor                 |SUCCEEDED      |25-MAY-14 06.00.01.830105|+000 00:05:30       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |

SUNDAY_WINDOW       |auto optimizer stats collection    |SUCCEEDED      |25-MAY-14 06.00.01.779975|+000 00:17:18       |        0
                    |                                   |               | AM ASIA/CALCUTTA        |                    |
SATURDAY_WINDOW     |auto optimizer stats collection    |SUCCEEDED      |24-MAY-14 10.05.11.587535|+000 00:12:27       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |auto space advisor                 |SUCCEEDED      |24-MAY-14 10.05.07.284781|+000 00:03:25       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |auto optimizer stats collection    |SUCCEEDED      |24-MAY-14 06.04.28.354804|+000 00:11:43       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |auto space advisor                 |SUCCEEDED      |24-MAY-14 06.04.23.117976|+000 00:03:45       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |auto optimizer stats collection    |SUCCEEDED      |24-MAY-14 02.03.45.766039|+000 00:09:40       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

SATURDAY_WINDOW     |auto space advisor                 |SUCCEEDED      |24-MAY-14 02.03.41.703904|+000 00:03:32       |        0
                    |                                   |               | PM ASIA/CALCUTTA        |                    |

http://blog.yannickjaquier.com/oracle/automatic-sql-tuning-task-overview.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s