trace python fetchmany rows from oracle

python有个模块叫做cx_Oracle可以访问oracle数据库并进行一些运维或者开发的工作。
cx_Oracle可以提供DDL和DML操作数据库,对于DML,cx_Oracle可以提供非绑定变量、绑定变量、ARRAY操作等,本文针对fetchmany和arraysize进行了简单的测试,并使用10046事件进行了跟踪会话。

本文的实验环境是eclipse+cx_Oracle+oracle 11.2.0.4
使用cx_Oracle的前提是安装package,windows下安装方法是pip install cx_Oracle.

python代码如下

import sys
import cx_Oracle

username = 'blue'
password = 'blue'
databasename = 'scan_name:1521/service_name'
sql_text = """select first_name,last_name from hr.employees"""

try:
    con = cx_Oracle.connect(username,password,databasename)
except cx_Oracle.DatabaseError, exception:
    print ('Failed to connect to %s\n', databasename)
    exit(1)

cur = con.cursor()

cur.arraysize = 25  --25 rows per fetch

try:
    cur.execute(sql_text)
except cx_Oracle.DatabaseError, exception:
    print('Failed to execute the sql\n')
    exit(1)

while True:
    rows = cur.fetchmany()
    if rows == []:
        break;
    print ("fetch number is %d" % len(rows))
    
    print("Name List:")
    for first_name,last_name in rows:
        
        print(first_name,last_name)
cur.close()
con.close()

exit(0)

Oracle使用dbms_session包+数据库触发器进行跟踪

trigger code:
create or replace trigger tri_blue_logon
  after logon on database
begin
  if ora_login_user = 'BLUE' then
    dbms_session.set_identifier(client_id => 'blue');
    EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
    EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier = 10046';
  end if;
end;

实验步骤

step1:
exec dbms_monitor.client_id_trace_enable(client_id=>'blue',waits=>true, binds=>true);

step2:
运行python代码

step3:
exec dbms_monitor.client_id_trace_disable(client_id=>'blue');

hr.employees表中有107条记录,每次fetch 25行

 

10046文件内容如下

select first_name,last_name from hr.employees
END OF STMT
PARSE #139955975247632:c=0,e=1699,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2228653197,tim=1470984962368413
EXEC #139955975247632:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2228653197,tim=1470984962368522
WAIT #139955975247632: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962368608
FETCH #139955975247632:c=0,e=59,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2228653197,tim=1470984962368722
WAIT #139955975247632: nam='SQL*Net message from client' ela= 318420 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962687209
WAIT #139955975247632: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962687331
FETCH #139955975247632:c=0,e=71,p=0,cr=1,cu=0,mis=0,r=25,dep=0,og=1,plh=2228653197,tim=1470984962687361
WAIT #139955975247632: nam='SQL*Net message from client' ela= 56199 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962743620
WAIT #139955975247632: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962743740
FETCH #139955975247632:c=0,e=66,p=0,cr=1,cu=0,mis=0,r=25,dep=0,og=1,plh=2228653197,tim=1470984962743767
WAIT #139955975247632: nam='SQL*Net message from client' ela= 50332 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962794142
WAIT #139955975247632: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962794259
FETCH #139955975247632:c=0,e=54,p=0,cr=1,cu=0,mis=0,r=25,dep=0,og=1,plh=2228653197,tim=1470984962794284
WAIT #139955975247632: nam='SQL*Net message from client' ela= 45736 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962840064
WAIT #139955975247632: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962840201
FETCH #139955975247632:c=0,e=56,p=0,cr=1,cu=0,mis=0,r=25,dep=0,og=1,plh=2228653197,tim=1470984962840227
WAIT #139955975247632: nam='SQL*Net message from client' ela= 28393 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962868664
WAIT #139955975247632: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962868771
FETCH #139955975247632:c=0,e=62,p=0,cr=1,cu=0,mis=0,r=6,dep=0,og=1,plh=2228653197,tim=1470984962868791
STAT #139955975247632 id=1 cnt=107 pid=0 pos=1 obj=119083 op='INDEX FULL SCAN EMP_NAME_IX (cr=6 pr=0 pw=0 time=46 us cost=1 size=1605 card=107)'
WAIT #139955975247632: nam='SQL*Net message from client' ela= 89186 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962958139
CLOSE #139955975247632:c=0,e=16,dep=0,type=1,tim=1470984962958240
XCTEND rlbk=1, rd_only=1, tim=1470984962958317
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984962958389
WAIT #0: nam='SQL*Net message from client' ela= 52885 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1470984963011288
XCTEND rlbk=0, rd_only=1, tim=1470984963011392
此条目发表在Python, Trace分类目录。将固定链接加入收藏夹。

发表评论

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 / 更改 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s