使用外部表分析eygle.com的网站访问日志

　　经过使用Oracle的外部表对Oracle的警告日志文件、跟踪文件进行获取和分析之后，我发现外部表实在是非常易用，甚至到了随心所欲的境地(当然外部表尚不能修改外部文件)。

　　使用外部表可以很容易的实现网站的访问日志分析。

　　虽然使用Awstats等工具也可以实现，可是使用Oracle来分析我们更应该得心应手。

　　而且这一切还是有那么一点点Cool的。

　　好了，闲言少叙，让我们来看一下我分析的过程。

　　首先创建路径指向日志存放目录:

　　[oracle@jumper elog]$ pwd

　　/opt/oracle/elog

　　[oracle@jumper elog]$ ls

　　eygle_access_log.20061016

　　[oracle@jumper elog]$ sqlplus "/ as sysdba"

　　SQL*Plus: Release 9.2.0.4.0 - Production on Wed Oct 18 08:59:35 2006

　　Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

　　Connected to:

　　Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

　　With the Partitioning option

　　JServer Release 9.2.0.4.0 - Production

　　SQL> create or replace directory elog

　　2 as '/opt/oracle/elog';

　　Directory created.

　　然后我将这个路径的访问权限授予eygle用户来进行具体操作:

　　SQL> grant read,write on directory elog to eygle;

　　Grant succeeded.

　　选择合适的分隔符创建外部表:

　　SQL> connect eygle/eygle

　　Connected.

　　SQL> create table eygle_access_log_20061016

　　2 ( ip_address_date varchar2(100),

　　3 acc_file varchar2(400),

　　4 acc_cdsz varchar2(20),

　　5 acc_url varchar2(400),

　　6 left_blank varchar2(10),

　　7 acc_agent varchar2(400))

　　8 organization external (

　　9 type oracle_loader

　　10 default directory ELOG

　　11 access parameters (

　　12 records delimited by newline

　　13 nobadfile

　　14 nodiscardfile

　　15 nologfile

　　16 fields terminated by '"'

　　17 missing field values are null

　　18 )

　　19 location('eygle_access_log.20061016')

　　20 ) reject limit unlimited

　　21 /

　　Table created.

　　此时我们就可以对eygle.com的2006年10月16日的访问日志进行分析了。

　　我们可以先看一下各个字段的分界结果，示例如下:

　　SQL> select ip_address_date from eygle_access_log_20061016

　　2 where rownum <11;

　　IP_ADDRESS_DATE

　　-------------------------------------------------------------

　　38.102.128.140 - - [16/Oct/2006:00:00:17 +0800]

　　66.249.65.113 - - [16/Oct/2006:00:00:19 +0800]

　　202.160.178.221 - - [16/Oct/2006:00:00:35 +0800]

　　59.36.78.100 - - [16/Oct/2006:00:00:37 +0800]

　　59.36.78.100 - - [16/Oct/2006:00:00:38 +0800]

　　72.30.61.8 - - [16/Oct/2006:00:00:38 +0800]

　　221.217.84.230 - - [16/Oct/2006:00:00:42 +0800]

　　221.217.84.230 - - [16/Oct/2006:00:00:42 +0800]

　　74.6.65.236 - - [16/Oct/2006:00:01:07 +0800]

　　74.6.73.36 - - [16/Oct/2006:00:01:09 +0800]

　　10 rows selected.

　　通过SQL析取出访问的ip地址:

　　SQL> select substr(ip_address_date,1,instr(ip_address_date,' ')) ip_address

　　2 from eygle_access_log_20061016 where rownum <11;

　　IP_ADDRESS

　　---------------------------------------------------------------------------

　　38.102.128.140

　　66.249.65.113

　　202.160.178.221

　　59.36.78.100

　　59.36.78.100

　　72.30.61.8

　　221.217.84.230

　　221.217.84.230

　　74.6.65.236

　　74.6.73.36

　　10 rows selected.

　　接下来我们就可以很容易的获得当日访问我站点的独立IP数量了:

　　SQL> set timing on

　　SQL> select count(distinct(substr(ip_address_date,1,instr(ip_address_date,' ')))) uip

　　2 from eygle_access_log_20061016;

　　UIP

　　----------

　　7534

　　Elapsed: 00:00:06.86

　　因为外部表的处理性能上要差一些，我们记录了一下时间，以上查询大约用了7秒的时间。

　　我们可以对比一下数据库表的性能。

　　首先将日志加载到数据库表中:

　　SQL> create table ealog as

　　2 select * from eygle_access_log_20061016;

　　Table created.

　　SQL> desc ealog;

　　Name Null? Type

　　----------------------------------------------------------------- -------- --------------------------------------------

　　IP_ADDRESS_DATE VARCHAR2(100)

　　ACC_FILE VARCHAR2(400)

　　ACC_CDSZ VARCHAR2(20)

　　ACC_URL VARCHAR2(400)

　　LEFT_BLANK VARCHAR2(10)

　　ACC_AGENT VARCHAR2(400)

　　SQL> select count(*) from ealog;

　　COUNT(*)

　　----------

　　165443

　　然后我们强制刷新Buffer Cache，消除Cache的影响，再次执行查询：

　　SQL> alter session set events = 'immediate trace name flush_cache';

　　Session altered.

　　Elapsed: 00:00:00.03

　　SQL> select count(distinct(substr(ip_address_date,1,instr(ip_address_date,' ')))) uip

　　2 from ealog;

　　UIP

　　----------

　　7528

　　Elapsed: 00:00:02.15

　　此时用了大约2秒的时间，也就是说，外部表的性能较数据库表大约慢了3倍左右。

　　继续，我们可以查询当日网站中，哪些网页是被最频繁访问的:

　　SQL> select replace((replace(acc_file,'GET ','www.eygle.com')),'HTTP/1.1') accfile,ct from (

　　2 select ACC_FILE,count(*) ct from eygle_access_log_20061016

　　3 where acc_file like '%htm%'

　　4 group by acc_file order by ct desc)

　　5 where rownum <21;

　　ACCFILE CT

　　-------------------------------------------------------------------------------- ----------

　　www.eygle.com/index-tech.htm 110

　　www.eygle.com/archives/2006/10/wish_home.html 103

　　www.eygle.com/index-ha.htm 79

　　www.eygle.com/me/fairy_tale_leaf.htm 77

　　www.eygle.com/archives/2006/11/use_oracle_external_table.html 73

　　www.eygle.com/index-sql.htm 69

　　www.eygle.com/archives/2006/10/tom_oracle_9i10g.html 68

　　www.eygle.com/archives/2008/08/my_book_services.html 63

　　www.eygle.com/archives/2006/11/welcome_friend.html 62

　　www.eygle.com/archives/2006/10/veritas_vcs_simulator.html 61

　　www.eygle.com/index-case.htm 60

　　www.eygle.com/archives/2004/08/aoaouiiciona.html 59

　　www.eygle.com/archives/2006/08/oracle_fundbook_recommand.html 52

　　www.eygle.com/archives/2006/08/5460_8174.html 49

　　www.eygle.com/archives/2004/12/gmailaeaeoa.html 48

　　www.eygle.com/archives/2005/06/howlsmovingcast.html 48

　　www.eygle.com/gbook/index.html 48

　　www.eygle.com/index-hist.htm 44

　　www.eygle.com/index-special.htm 41

　　www.eygle.com/index-f&l.htm 37

　　20 rows selected.

　　Elapsed: 00:00:06.31

　　SQL>

　　通过外部表及SQL查询，只要日志文件中存在的信息，都可以很容易的被获取和分析.

　　-The End-