DBA常用脚步汇总

常用软件下载地址：我网盘：http://pan.baidu.com/s/1jGl6svdba 常用管理脚本收集

历史CPU繁忙查询：

set linesize 600 pagesize 600col time for a40WITH cpu AS (SELECT INSTANCE_NUMBER, SNAP_ID, STAT_NAME,       VALUE - LAG(VALUE, 1, 0) OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP_ID) cpu  FROM DBA_HIST_SYS_TIME_MODEL WHERE STAT_NAME = 'DB CPU'),DB AS (SELECT INSTANCE_NUMBER, SNAP_ID, STAT_NAME,       VALUE - LAG(VALUE, 1, 0) OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP_ID) dbtime  FROM DBA_HIST_SYS_TIME_MODEL WHERE STAT_NAME = 'DB time')select INSTANCE_NUMBER,SNAP_ID,TIME,cputime,dbtime,snap_times,       trunc(dbtime/(snap_times*io.VALUE)*100,2)||'%' "db%" from (SELECT CPU.INSTANCE_NUMBER, CPU.SNAP_ID, round(CPU.CPU/1000000) cputime, round(db.DBTIME/1000000) dbtime,       to_char(begin_interval_time, 'yyyymmdd hh24:mi') || '-' ||       to_char(end_interval_time, 'yyyymmdd hh24:mi') TIME,       (to_date(to_char(end_interval_time,'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss')-       to_date(to_char(begin_interval_time,'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss'))*24*3600 snap_times  FROM CPU, DB,dba_hist_snapshot s WHERE CPU.INSTANCE_NUMBER = DB.INSTANCE_NUMBER   and CPU.SNAP_ID = DB.SNAP_ID    and s.snap_id=CPU.SNAP_ID   and s.snap_id=DB.SNAP_ID) , V$osstat io where io.stat_name='NUM_CPUS'    order by INSTANCE_NUMBER,SNAP_ID;   

历史会话与等待关系：

select EVENT, count(*)  from DBA_HIST_ACTIVE_SESS_HISTORY where (SAMPLE_TIME between to_date('20150126 09:40', 'yyyymmdd hh24:mi') and       to_date('20150126 09:45', 'yyyymmdd hh24:mi'))   and WAIT_CLASS <> 'Idle' group by eventhaving count(*) >= 0 order by 2;    select EVENT,blocking_session, count(*)  from v$active_session_history where (SAMPLE_TIME between to_date('20150126 09:40', 'yyyymmdd hh24:mi') and       to_date('20150126 09:46', 'yyyymmdd hh24:mi'))   and WAIT_CLASS <> 'Idle'   and blocking_session is not null group by event,blocking_sessionhaving count(*) >= 0 order by 3;   col event for a30 set linesize 300select EVENT,sql_id,blocking_session,instance_number, count(*)  from DBA_HIST_ACTIVE_SESS_HISTORY where (SAMPLE_TIME between to_date('20150118 17:00', 'yyyymmdd hh24:mi') and       to_date('20150118 18:00', 'yyyymmdd hh24:mi'))   and WAIT_CLASS <> 'Idle'   and event='gc buffer busy acquire' group by EVENT,sql_id,blocking_session,instance_number order by 4;  col event for a20col PROGRAM for a15col MACHINE for a15 set linesize 500select EVENT,sql_id,blocking_session,instance_number,a.program,a.blocking_session_status,a.module,a.machine,a.session_id  from DBA_HIST_ACTIVE_SESS_HISTORY a where (SAMPLE_TIME between to_date('20150119 17:00', 'yyyymmdd hh24:mi') and       to_date('20150119 18:00', 'yyyymmdd hh24:mi'))   and WAIT_CLASS <> 'Idle'   and a.session_id=868   and rownum<20set linesize 150 pagesize 500with hist_block AS (select distinct EVENT ,       instance_number,       session_id,       blocking_session,       blocking_inst_id   from DBA_HIST_ACTIVE_SESS_HISTORY where(SAMPLE_TIME between to_date('20150118 17:00', 'yyyymmdd hh24:mi')    and to_date('20150118 17:40', 'yyyymmdd hh24:mi'))    and WAIT_CLASS <> 'Idle' and blocking_session is not null)select waiting_events from (select  distinct 'evt:'||a.EVENT||' _inst: '||a.instance_number||'_sess: '||a.session_id||': - > '        ||'_evt:'||b.EVENT||' _inst: '||b.instance_number||'_sess:'||b.session_id||': - > '        ||'_evt:'||c.EVENT||' _inst: '||c.instance_number||'_sess:'||c.session_id waiting_events,        a.event a1,b.event b1 ,c.event c1 from   hist_block a,hist_block b,hist_block c where  a.blocking_session=b.session_id(+)   and  a.blocking_inst_id=b.instance_number(+)   and  b.blocking_session=c.session_id(+)   and  b.blocking_inst_id=c.instance_number(+)    and a.event='gc buffer busy acquire') order by c1 desc,b1 desc ,a1 desc

未整理：--

##################################################

--Space.sql
set time on
set lines 500
set pages 100
col tspace form a25 Heading "Tablespace"
col tot_ts_size form 99999999 Heading "Size (Mb)"
col free_ts_size form 99999999 Heading "Free (Mb)"
col used_ts_size form 99999999 Heading "Used (Mb)"
col used_pct form 99999 Heading "% Used"
col free_pct form 99999 Heading "% Free"
col warning form a10 Heading "Message"
break on report
compute sum label total of tot_ts_size on report
compute sum label total of used_ts_size on report
compute sum label total of free_ts_size on report
(select  df.tablespace_name tspace
,       round(sum(fs.bytes_free + fs.bytes_used) / 1024 / 1024, 2) tot_ts_size
,       round(sum(fs.Bytes_used) / 1024 / 1024, 2)  used_ts_size
,       round(sum(fs.bytes_free) / 1024 / 1024, 2)  free_ts_size
,       round(sum(fs.Bytes_used ) * 100 / sum((fs.bytes_free + fs.bytes_used))) used_pct
,       round(sum(fs.Bytes_free ) * 100 / sum((fs.bytes_free + fs.bytes_used))) free_pct
,      decode(sign(sum(round(((fs.bytes_free + fs.bytes_used)-fs.bytes_free)*100/(fs.bytes_free + fs.bytes_used))) - 80), 1, '
!ALERT', '') warning
from   SYS.V_$TEMP_SPACE_HEADER fs
,      dba_temp_files df
where fs.tablespace_name(+) = df.tablespace_name
  and fs.file_id(+) = df.file_id
group by df.tablespace_name
union
SELECT df.tablespace_name tspace
,      df.bytes/(1024*1024) tot_ts_size
,      round((df.bytes-sum(fs.bytes))/(1024*1024)) used_ts_size
,      sum(fs.bytes)/(1024*1024) free_ts_size
,      round((df.bytes-sum(fs.bytes))*100/df.bytes) used_pct
,      round(sum(fs.bytes)*100/df.bytes) free_pct
,      decode(sign(round((df.bytes-sum(fs.bytes))*100/df.bytes) - 80), 1, '!ALERT', '') warning
FROM dba_free_space fs
, (select tablespace_name, sum(bytes) bytes
   from dba_data_files
   group by tablespace_name
   ) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes)
union
(select tablespace_name tspace,
1,1,0 free_ts_size,100 used_pct,0 free_pct,'!' warning from dba_data_files
group by tablespace_name
minus
select tablespace_name tspace,1,1,0 free_ts_size,100 used_pct,0 free_pct,'!' warning
from dba_free_space
group by tablespace_name)
order by 4;
##################################################
--ops.sql
set line 150;
col target for a35
col EST_COMPLETION_TIME for a20
col SOFAR for a14
col sid_serial for a10
set pages 1000
SELECT /*+ rule */ a.sid||','||a.serial#  "sid_serial", b.status, a.target || a.opname target , a.TOTALWORK, a.SOFAR SOFAR, a.TIME_REMAINING "TIME_REMAIN",
to_char(start_time+(sysdate-start_time) /(a.sofar/a.totalwork),'dd-mon-yy:hh:mi:ss') Est_completion_time,
round((a.sofar/a.totalwork)*100,3) pct_complete,
ELAPSED_SECONDS "ELAPS_SECS" FROM V$SESSION_LONGOPS a, V$SESSION b
where a.time_remaining > 0
and a.sid=b.sid and b.status='ACTIVE' 
order by a.time_remaining desc
/
##################################################
--top_sqls.sql
SET LINESIZE 500
SET PAGESIZE 100
col sid_serial for a10
col sql_text for a30
col osuser for a10
SELECT *
FROM   (SELECT /*+ rule */ sid||','||serial# sid_serial,Substr(a.sql_text,1,550) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) read_pr_exe,
               a.buffer_gets buff_g,
               a.disk_reads disk_r,
               a.executions exec,
               a.sorts,
               a.address,osuser
        FROM   v$sqlarea a,v$session b
   where  a.address = b.sql_address and b.status = 'ACTIVE'
        ORDER BY 3 DESC)
WHERE  rownum <= &1
/
############################################################################################
--top_sessions.sql


COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
       a.osuser,
       a.sid,
       a.serial#,
       c.value AS &1,
       a.lockwait,
       a.status,
       a.module,
       a.machine,
       a.program,
       TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session a,
       v$sesstat c,
       v$statname d
WHERE  a.sid        = c.sid
AND    c.statistic# = d.statistic#
AND    d.name       = DECODE(UPPER('&1'), 'READS', 'session logical reads',
                                          'EXECS', 'execute count',
                                          'CPU',   'CPU used by this session',
                                                   'CPU used by this session')
ORDER BY c.value DESC;
############################################################################################
undo_used.sql


set pages 10000 lines 200
col username heading "User"
col name format a22 wrapped heading "Undo Segment Name"
col xidusn heading "Undo|Seg #"
col xidslot heading "Undo|Slot #"
col xidsqn heading "Undo|Seq #"
col ubafil heading "File #"
col ubablk heading "Block #"
col start_time format a20 word_wrapped heading "Started"
col status format a8 heading "Status"
col blk format 999,999,999 heading "KBytes"
col used_urec heading "Rows"


select /*+ rule */
 start_time,
 username,
 r.name,
 ubafil,
 ubablk,
 t.status,
 (used_ublk * p.value) / 1024 blk,
 used_urec
  from v$transaction t, v$rollname r, v$session s, v$parameter p
 where xidusn = usn
   and s.saddr = t.ses_addr
   and p.name = 'db_block_size'
 order by 1;
############################################################################################
cpu_cost.sql


SELECT /*+ Rule */
 'SID : ' || sid || chr(10) || 'Serial# ' || serial# || chr(10) ||
 'Username : ' || username || chr(10) || 'Logon Time : ' || logon_time ||
 chr(10) || 'Last Called (in Secs) :' || last_call_et || chr(10) ||
 'Status : ' || status || chr(10) || 'SQL Address : ' || address || chr(10) ||
 'HASH Value : ' || hash_value || chr(10) || 'Buffer Gets : ' ||
 buffer_gets || chr(10) || 'Executions : ' || executions || chr(10) ||
 'Buffer Gets / Execution :' || buffer_gets / executions || chr(10) ||
 'Text of SQL : ' || sql_text
  from (select sid,
               serial#,
               username,
               logon_time,
               last_call_et,
               address,
               hash_value,
               buffer_gets,
               executions,
               buffer_gets / executions,
               sql_text,
               status
          FROM v$sqlarea, v$session
         WHERE buffer_gets > 50000
           and executions > 0
           and sql_address = address
           and sql_hash_value = hash_value
         order by 8)
/
############################################################################################
--library_pin.sql


select /*+ ordered */
 w1.sid waiting_session,
 h1.sid holding_session,
 h1.username,
 w.kgllktype lock_or_pin,
 w.kgllkhdl address,
 decode(h.kgllkmod,
        0,
        'None',
        1,
        'Null',
        2,
        'Share',
        3,
        'Exclusive',
        'Unknown') mode_held,
 decode(w.kgllkreq,
        0,
        'None',
        1,
        'Null',
        2,
        'Share',
        3,
        'Exclusive',
        'Unknown') mode_requested
  from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and
       ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and
       (((w.kgllkmod = 0) or (w.kgllkmod = 1)) and
       ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
   and w.kgllktype = h.kgllktype
   and w.kgllkhdl = h.kgllkhdl
   and w.kgllkuse = w1.saddr
   and h.kgllkuse = h1.saddr
/
  SELECT  /*+ rule */ 'alter system kill session ' ||chr(39)||sid||','||SERIAL#||chr(39)||' immediate ;'
          FROM x$kglpn p, v$session s
         WHERE p.kglpnuse=s.saddr
           AND kglpnhdl in (select P1RAW  from gv$session_wait where state='WAITING'
and event like 'library cache lock' or event like 'library cache pin' )
/
############################################################################################
--temp tbs used
SELECT d.tablespace_name "Name", 
                 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
                 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999')  "HWM (M)",
                 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
                 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)", 
                 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %" 
            FROM sys.dba_tablespaces d, 
                 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
                 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
           WHERE d.tablespace_name = a.tablespace_name(+) 
             AND d.tablespace_name = t.tablespace_name(+) 
             AND d.extent_management like 'LOCAL' 
             AND d.contents like 'TEMPORARY'
select TABLESPACE_NAME, BYTES_USED/1024/1024 used_MB,BYTES_FREE/1024/1024 free_MB
from V$TEMP_SPACE_HEADER;


GV$TEMP_SPACE_HEADER
select /*+ ordered use_nl(hc) */ hc.inst_id, ts.name, hc.ktfthctfno, (hc.ktfthcsz - hc.ktfthcfree)*ts.blocksize, (hc.ktfthcsz - hc.ktfthcfre
e), hc.ktfthcfree*ts.blocksize, hc.ktfthcfree, hc.ktfthcfno from ts$ ts, x$ktfthc hc where ts.contents$ = 1 and ts.bitmapped <> 0 and ts.onl
ine$ = 1 and ts.ts# = hc.ktfthctsn and hc.ktfthccval = 0
############################################################################################
--hist.sql
--查看某时间段内等待事件情况
select EVENT, count(*)
  from DBA_HIST_ACTIVE_SESS_HISTORY
 where snap_id in (745, 746)
   and (SAMPLE_TIME between to_date('20120113 20:00', 'yyyymmdd hh24:mi') and
       to_date('20120113 23:00', 'yyyymmdd hh24:mi'))
   and WAIT_CLASS <> 'Idle'
 group by event
having count(*) >= 30
 order by 2;
--查询某时间段内的等待事件情况(含SQL_ID)
select sql_id, event, count(*)
  from DBA_HIST_ACTIVE_SESS_HISTORY
 where snap_id in (745, 746)
   and (SAMPLE_TIME between to_date('20120114 14:00', 'yyyymmdd hh24:mi') and
       to_date('20120114 15:40', 'yyyymmdd hh24:mi'))
   and WAIT_CLASS <> 'Idle'
 group by sql_id, event
having count(*) > 30
 order by 3;
--查询某时间段内的等待事件情况(含username和SQL_ID)
set line 3000 col username for a10 col event for a33
  select c.username, b.sql_id, a.event, count(*)
    from DBA_HIST_ACTIVE_SESS_HISTORY a,
         DBA_HIST_SQLTEXT             b,
         dba_users                    c,
         v$instance                   i
   where a.SQL_ID = b.SQL_ID
     and a.user_id = c.user_id
     and a.instance_number = i.instance_number
     and (SAMPLE_TIME >=
         to_date('2012-01-05 11:00:00', 'yyyy-mm-dd hh24:mi:ss') and
         SAMPLE_TIME <=
         to_date('2012-01-05 13:00:00', 'yyyy-mm-dd hh24:mi:ss'))
     and snap_id in (526, 527)
   group by c.username, b.sql_id, a.event
  having count(*) > 30
   order by 4;
------根据SQL_ID查询SQL
set line 300 col sql_text for a200
  select sql_id, SQL_TEXT
    from DBA_HIST_SQLTEXT
   where sql_id = 'f3hc7r4trnn1d';


############################################################################################
--spid.sql
   知道有问题的SPID情况下查看正在运行的SQL .
SELECT a.username,
       a.machine,
       a.program,
       a.sid,
       a.serial#,
       a.status,
       c.piece,
       c.sql_text
  from v$session a, v$process b, v$sqltext c
 WHERE b.spid = '14150'
   AND b.addr = a.paddr
   AND a.sql_address = c.address(+)
 order BY c.piece
 


############################################################################################
--lock.sql
   查询Oracle库中的Lock (或通过Toad中session browser参看lock情况)
   查看数据库中的锁(LOCK),找出程序及SQL
SELECT   se.inst_id, se.SID, se.serial#,lk.SID,
         se.username,se.OSUser,se.Machine,se.program,
         DECODE (lk.TYPE,
                 'TX', 'Transaction',
                 'TM', 'DML',
                 'UL', 'PL/SQL User Lock',
                 lk.TYPE)
            lock_type,
         DECODE (lk.lmode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 TO_CHAR (lk.lmode))
            mode_held,
         DECODE (lk.request,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 TO_CHAR (lk.request))
            mode_requested,
         TO_CHAR (lk.id1) lock_id1,
         TO_CHAR (lk.id2) lock_id2,
         ob.owner,
         ob.object_type,
         ob.object_name,
         DECODE (lk.Block,
                 0,
                 'No',
                 1,
                 'Yes',
                 2,
                 'Global')
            block,
         se.lockwait,
         sq.sql_fulltext
  FROM   GV$lock lk, dba_objects ob, GV$session se, GV$sql sq
 WHERE       lk.TYPE IN ('TM', 'UL')
         AND lk.SID = se.SID
         AND lk.id1 = ob.object_id(+)
         AND (lk.inst_id = se.inst_id)
         AND sq.address=se.sql_address ;   


############################################################################################
--py.sql
查询物理读写严重的SQL及查询哪个SID最消耗资源


查看占I/O较大的正在运行的session
SELECT se.sid,
       se.serial#,
       pr.SPID,
       se.username,
       se.status,
       se.terminal,
       se.program,
       se.MODULE,
       se.sql_address,
       st.event,
       st.p1text,
       si.physical_reads,
       si.block_changes
  FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
 WHERE st.sid = se.sid
   AND st.sid = si.sid
   AND se.PADDR = pr.ADDR
   AND se.sid > 6
   AND st.wait_time = 0
   AND st.event NOT LIKE '%SQL%'
 ORDER BY physical_reads DESC;
 
############################################################################################
--long_wait.sql
 --长时间锁定进程（某时间段内）
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set linesize 1400 pagesize 1000
set linesize 1400 pagesize 1000
select username || '^' || PROGRAM || '^' || SQL_TEXT || '^' || object_name || '^' ||
       to_char(max(ctime)) ctime
  from (select username,
               PROGRAM,
               SQL_TEXT,
               nvl((select object_name
                     from dba_objects
                    where object_id = id1
                      and object_type = 'TM'),
                   null) object_name,
               ctime
          from V$SESSION_LONGOPS
         where collect_date between to_date('20120110 0', 'yyyymmdd hh24') and
               sysdate
           and kill_status = '1'
           and SQL_TEXT is not null)
 group by username, PROGRAM, SQL_TEXT, object_name
 order by username, PROGRAM;
############################################################################################
--log.sql
--按小时查看归档日志个数
set linesize 300 pagesize 1000
col Date for a10
col Day for a10
col Total for 99999
col "h00" for a10
col "h01" for a10
col "h02" for a10
col "h03" for a10
col "h04" for a10
col "h05" for a10
col "h06" for a10
col "h07" for a10
col "h08" for a10
col "h09" for a10
col "h10" for a10
col "h11" for a10
col "h12" for a10
col "h13" for a10
col "h14" for a10
col "h15" for a10
col "h16" for a10
col "h17" for a10
col "h18" for a10
col "h19" for a10
col "h20" for a10
col "h21" for a10
col "h22" for a10
col "h23" for a10
SELECT  trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) as "Total",
substr(SUM(decode(to_char(first_time, 'hh24'),'00',1,0)),1,3) as "h00",
substr(SUM(decode(to_char(first_time, 'hh24'),'01',1,0)),1,3) as "h01",
substr(SUM(decode(to_char(first_time, 'hh24'),'02',1,0)),1,3) as "h02",
substr(SUM(decode(to_char(first_time, 'hh24'),'03',1,0)),1,3) as "h03",
substr(SUM(decode(to_char(first_time, 'hh24'),'04',1,0)),1,3) as "h04",
substr(SUM(decode(to_char(first_time, 'hh24'),'05',1,0)),1,3) as "h05",
substr(SUM(decode(to_char(first_time, 'hh24'),'06',1,0)),1,3) as "h06",
substr(SUM(decode(to_char(first_time, 'hh24'),'07',1,0)),1,3) as "h07",
substr(SUM(decode(to_char(first_time, 'hh24'),'08',1,0)),1,3) as "h08",
substr(SUM(decode(to_char(first_time, 'hh24'),'09',1,0)),1,3) as "h09",
substr(SUM(decode(to_char(first_time, 'hh24'),'10',1,0)),1,3) as "h10",
substr(SUM(decode(to_char(first_time, 'hh24'),'11',1,0)),1,3) as "h11",
substr(SUM(decode(to_char(first_time, 'hh24'),'12',1,0)),1,3) as "h12",
substr(SUM(decode(to_char(first_time, 'hh24'),'13',1,0)),1,3) as "h13",
substr(SUM(decode(to_char(first_time, 'hh24'),'14',1,0)),1,3) as "h14",
substr(SUM(decode(to_char(first_time, 'hh24'),'15',1,0)),1,3) as "h15",
substr(SUM(decode(to_char(first_time, 'hh24'),'16',1,0)),1,3) as "h16",
substr(SUM(decode(to_char(first_time, 'hh24'),'17',1,0)),1,3) as "h17",
substr(SUM(decode(to_char(first_time, 'hh24'),'18',1,0)),1,3) as "h18",
substr(SUM(decode(to_char(first_time, 'hh24'),'19',1,0)),1,3) as "h19",
substr(SUM(decode(to_char(first_time, 'hh24'),'20',1,0)),1,3) as "h20",
substr(SUM(decode(to_char(first_time, 'hh24'),'21',1,0)),1,3) as "h21",
substr(SUM(decode(to_char(first_time, 'hh24'),'22',1,0)),1,3) as "h22",
substr(SUM(decode(to_char(first_time, 'hh24'),'23',1,0)),1,3) as "h23"
FROM    V$log_history
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1;