0[1].Oracle 性能查询脚本(17)

LoadRunner连接查询Oracle脚本Loadrunner 如何访问数据库在Loadrunner中也提供了C对数据库操作的相关功能函数，以下这些数据库功能函数只能用于Web Services协议。

各函数语法如下：lr_db_connect("StepName", "ConnectionString=","ConnectionName=", "ConnectionType=", LAST);lr_db_disconnect("StepName=", "ConnectionName=", LAST);lr_db_executeSQLStatement("StepName=", "ConnectionName=", "SQLStatement=", ["DatasetName=",] LAST);lr_db_dataset_action("StepName=", "DatasetName=","Action=", LAST);lr_db_getValue("StepName=", "DatasetName=", "Column=", "Row=", "OutParam=", LAST);下面以oracle数据库为例：运行后，输出信息如下：Virtual User Script startedStarting action vuser_init.Ending action vuser_init.Running Vuser...Starting iteration 1.Starting action Action.Action.c(6): DB Connection "DatabaseConnection" started ... Wait ....Action.c(6): DB connection was successfulAction.c(13): SQL Statement execution "PerformQuery" startedAction.c(13): SQL Statement execution was successfulAction.c(13):========================================= ======= Action.c(18): The query returned 9 rows.Action.c(20): Dataset action "PrintDataset" startedAction.c(20):Action.c(20): ******** Start printing ***********Action.c(20): Number of records in dataset = 9Action.c(20): Num. CONS_NO CONS_IDAction.c(20):====================================== =================== Action.c(20): 1 3096451321 310306247Action.c(20): 2 3097730300 3376536Action.c(20): 3 0000265443 3376537Action.c(20): 4 0000265456 3376538Action.c(20): 5 0000265469 3376539Action.c(20): 6 0000265472 3376540Action.c(20): 7 3098976354 314348099Action.c(20): 8 0000265498 3376542Action.c(20): 9 0000265502 3376543Action.c(20): ********End printing ***********Action.c(20):Action.c(20): Dataset action was successfulAction.c(27): Get db value "GetValue" startedAction.c(27): Get db value was successfulAction.c(27):========================================= =======Action.c(27): Get db value "GetValue" startedAction.c(27): Get db value was successfulAction.c(27):========================================= ======= Action.c(33): The value is: 3097730300Action.c(27): Get db value "GetValue" startedAction.c(27): Get db value was successfulAction.c(27):========================================= ======= Action.c(33): The value is: 0000265443Action.c(27): Get db value "GetValue" startedAction.c(27): Get db value was successfulAction.c(27):========================================= ======= Action.c(33): The value is: 0000265456Action.c(27): Get db value "GetValue" startedAction.c(27): Get db value was successfulAction.c(27):================================================ Action.c(33): The value is: 0000265469Action.c(27): Get db value "GetValue" startedAction.c(27): Get db value was successfulAction.c(27):========================================= ======= Action.c(33): The value is: 0000265472Action.c(27): Get db value "GetValue" startedAction.c(27): Get db value was successfulAction.c(27):========================================= ======= Action.c(33): The value is: 3098976354Action.c(27): Get db value "GetValue" startedAction.c(27): Get db value was successfulAction.c(27):========================================= ======= Action.c(33): The value is: 0000265498Action.c(27): Get db value "GetValue" startedAction.c(27):========================================= ======= Action.c(33): The value is: 0000265502Action.c(36): Dataset action "RemoveDataset" startedAction.c(36): Dataset action was successfulAction.c(41): DB disconnect "Disconnect" startedAction.c(41): DB disconnect was successfulAction.c(41):========================================= ======= Ending action Action.Ending iteration 1.Ending Vuser...Starting action vuser_end.Ending action vuser_end. Vuser Terminated.。

oracle开发常用脚本总结--查询数据泵jobselect * from sys.dba_datapump_jobs where owner_name='G_PSR_NW0824';--关闭impdp的jobDECLAREhdl number;beginhdl := dbms_datapump.attach('SYS_IMPORT_FULL_01','G_PSR_NW0824 ');DBMS_DATAPUMP.STOP_JOB(hdl,1,0);end;1.查询数据库中的锁select * from v$lock;select * from v$lock where block=1;2.查询被锁的对象select * from v$locked_object;3.查询阻塞查被阻塞的会话select * from v$lock where lmode=0 and type in ('TM','TX');查阻塞别的会话锁select * from v$lock where lmode>0 and type in ('TM','TX');4.查询数据库正在等待锁的进程select * from v$session where lockwait is not null;5.查询会话之间锁等待的关系select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock bwhere a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;6.查询锁等待事件select * from v$session_wait where event='enqueue';--锁表查询SELECT object_name, machine, s.sid, s.serial#FROM gv$locked_object l, dba_objects o, gv$session sWHERE l.object_id = o.object_idAND l.session_id = s.sid;--杀死锁表进程ALTER system kill session '11, 253';查询及删除重复记录的SQL语句1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断select * from 表where Id in (select Id from 表group byId having count(Id) > 1)2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);3、查找表中多余的重复记录（多个字段）select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表group by Id,seq having count(*) > 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)--批量删除分区--select part_id from sys_partition where (part_id in (0,1,5)) or (part_id>=700 and part_id <=749)delete sys_partition where part_id not in(select part_id from sys_partition where (part_id in (0,1,5)) or (part_id>=700 and part_id <=749));----快速删除海量数据（对于有主键或者唯一约束的表特别有效）declareTYPE type_rowid IS TABLE OF rowid INDEX BY PLS_INTEGER;v_rowid type_rowid;v_count int default 0;--定义游标，查询需要删除的rowidcursor v_cursor isselect n.rowid from net_df_trans nwhere exists (select 1 from net_df_branch n1 where n.dev_id=n1.dev_id) for update;beginopen v_cursor;loop--分批次删除fetch v_cursor bulk collect into v_rowid limit 10000;exit when v_cursor%notfound;--删除forall i in v_rowid.first..v_/doc/6710331651.html,st delete net_df_trans n where n.rowid=v_rowid(i);commit;v_count:=v_count+v_rowid.count;end loop;close v_cursor;commit;dbms_output.put_line(v_count);end;--更改最大连接数select value from V$parameter where name='processes'; alter system set processes=300 scope=spfile; shutdown immediate;----给空表表扩展段，否则exp不能导出declarev_sql varchar(200);v_count int;v_table_name varchar(50);v_partition_name varchar(20);beginfor i in (select * from user_tables u where u.num_rows=0) loopbegin--普通表v_table_name:=i.table_name;execute immediate 'alter table '||v_table_name||' allocate extent';dbms_output.put_line('表'||v_table_name||'扩展成功');exception when others then--分区表dbms_output.put_line(v_table_name||' '||sqlerrm);select min(partition_name) into v_partition_name from user_part_col_statistics where table_name=v_table_name;v_sql:='alter table '||v_table_name||' modify partition '||v_partition_name||' allocate extent';execute immediate v_sql;dbms_output.put_line('表'||v_table_name||'分区'||v_partition_name||'扩展成功');end;end loop;end;--速度由高到低select * from (select n. *,rownum rn from NET_LF_SEGMENT_GROUP nwhere n.flags in (0,10) and n.ver_flags=2 and n.part_id<>1 and rownum<10000) twhere t.rn>9990;select * from (select n. *,rownum rn from NET_LF_SEGMENT_GROUP nwhere n.flags in (0,10) and n.ver_flags=2 and n.part_id<>1) t where (t.rn between 9990 and 10000);--and rownum <20select * from (select t.*,row_number() over(order by dev_id) as num from NET_LF_SEGMENT_GROUP t)where num between 9990 and 10000;DROP TABLE STUDY;CREATE TABLE STUDY(SID VARCHAR(10),CID VARCHAR(10),SCORE INTEGER DEFAULT 0 CHECK (SCORE between 0 and 100),constraint PK_STUDY PRIMARY KEY (SID) --注意此处设置主键约束名,便于后面修改表的主键);--1.增加列alter table student add (sex varchar2(2) DEFAULT '女',age integer,address varchar(100)) ;--2.修改列定义ALTER TABLE student MODIFY sex VARCHAR2(4) DEFAULT '男';--3.删除列ALTER TABLE student DROP COLUMN address;--4.修改列名ALTER TABLEstudent RENAME COLUMN sid to sno;ALTER TABLE student RENAME COLUMN sno to sid;--5.修改表名RENAME study TO Learn;RENAME Learn TO study;--6.增加注释-- 给表添加释COMMENT ON TABLE STUDY IS '学习信息表';--给列添加注释COMMENT ON COLUMN STUDY.SID IS '学员编号';--7.增加外键关联ALTER TABLE study ADD CONSTRAINT FK_study_R_student_S FOREIGN KEY (SID) REFERENCES student(SID);--8.修改主键--查找主键约束名select T.constraint_name from USER_CONSTRAINTS T where table_name='STUDY' and constraint_type='P' AND ROWNUM<2;/--删除主键约束ALTER TABLE study DROP CONSTRAINT PK_STUDY/--添加主键alter table study add constraint PK_STUDY primary key (cid,sid);--查询表被其他对象引用Select owner, object_type, object_name, object_id, statusfrom sys.DBA_OBJECTSwhere object_id in (Select object_idfrom public_dependencyconnect by prior object_id = referenced_object_idstart with referenced_object_id =(Select object_idfrom sys.DBA_OBJECTSwhere owner = 'G_PSR_MID0612'-- 这里放 :Ownerand object_name = 'TAB' -- 这里放 :name/*and object_type = :type*/ -- 这里放 TABLE / SYNONYM ) )--表空间使用查询SELECT a.tablespace_name "表空间名",/*total "表空间大小",free "表空间剩余大小",(total - free) "表空间使用大小", */total / (1024 * 1024 * 1024) "表空间大小(G)",free / (1024 * 1024 * 1024) "表空间剩余大小(G)", (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %"FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_spaceGROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) totalFROM dba_data_filesGROUP BY tablespace_name) bWHERE a.tablespace_name = b.tablespace_name。

原始SQL语句如下：select *from (select c.product_name,a.prd_inst_name, prd_state,a.accept_date, basic_state,a.evolution_pty_id,decode(a.if_prepay, 1, '是', '否') prepay,a.install_addr,a.install_date,a.service_nbr,a.ofr_id, ofr_name,d.cust_name,rownum numfrom crm_tb_prd_prd_inst a,crm_tb_prd_prd c,crm_tb_pty_cust d,m_prd_inst_stas m,m_prd_inst_base_state m1,m_prdmark_inst_base_state m2,ext_crm_tb_prd_prd_inst extwhere a.prd_id = c.product_idand m.gwm_fid(+) = a.prd_inst_stas_idand m1.basic_state(+) = a.basic_stateand m2.ofr_id(+) = a.ofr_idand a.prd_inst_id(+) = ext.prd_inst_idand d.cust_id(+) = a.own_cust_idand ext.building_fid = 693253and rownum <= 10)where num > 0;数据库中，a、c、d 三张表中数据十万数量级，每查询一次，时间约为75s。

查询表结构，发现a表关联的m表的外键ofr_id ，m1表的外键basic_state，d表的外键own_cust_id均无索引，为其添加索引。

索引添加后，执行速度提升为53s左右，效果仍不明显。

Oracle查询指定索引提高查询效率一个1600万数据表一个1600万数据表－－短信上行表TBL_SMS_MO结构：CREATE TABLE TBL_SMS_MO(SMS_ID NUMBER,MO_ID VARCHAR2(50),MOBILE VARCHAR2(11),SPNUMBER VARCHAR2(20),MESSAGE VARCHAR2(150),TRADE_CODE VARCHAR2(20),LINK_ID VARCHAR2(50),GATEWAY_ID NUMBER,GATEWAY_PORT NUMBER,MO_TIME DATE DEFAULT SYSDATE);CREATE INDEX IDX_MO_DATE ON TBL_SMS_MO (MO_TIME)PCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 1MNEXT 1MMINEXTENTS 1MAXEXTENTS UNLIMITEDPCTINCREASE 0);CREATE INDEX IDX_MO_MOBILE ON TBL_SMS_MO (MOBILE)PCTFREE 10INITRANS 2MAXTRANS 255STORAGE(INITIAL 64KNEXT 1MMINEXTENTS 1MAXEXTENTS UNLIMITEDPCTINCREASE 0);问题：从表中查询某时间段内某手机发送的短消息，如下SQL语句：SELECT MOBILE,MESSAGE,TRADE_CODE,MO_TIMEFROM TBL_SMS_MOWHERE MOBILE='130XXXXXXXX'AND MO_TIME BETWEEN TO_DATE('2006-04-01','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2006-04-07','YYYY-MM-DD HH24:MI:SS')ORDER BY MO_TIME DESC返回结果大约需要10分钟，应用于网页查询，简直难以忍受。