SQL语句优化实践之一SQL_TRACE
SQL语句优化实践之一SQL_TRACE
环境:在PL/sql上调试数据
Pl/sql developer工具连接实例后即作为一个用户进程占用一个session;
select * from v$session t where t.PROGRAM='plsqldev.exe' and t.USERNAME='DZJC'
查询结果显示了几个关键的字段
SADDR RAW(4) Session address 内存地址
SID NUMBER Session identifier 唯一标识
SERIAL# NUMBER
Session serial number. Used to identify uniquely a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID
STATUS VARCHAR2(8)
Status of the session: ACTIVE (currently executing SQL), INACTIVE, KILLED (marked to be killed), CACHED (temporarily cached for use by Oracle*XA), SNIPED (session inactive, waiting on the client) 当期状态
MACHINE VARCHAR2(64) Operating system machine name
PROGRAM VARCHAR2(48) Operating system program name 应用程序名称。
打开窗口依次执行如下命令:
启动当期sesison级别 跟踪
启动SQL跟踪
实例级别
Alter system set sql_trace=true scope=both;
当期session级别
Alter session set sql_trace=true;
或
Execute dbms_session.set_sql_trace(true);
EXECUTE dbms_system.set_sql_trace_in_session
(session_id, serial_id, true);
Alter session set sql_trace=true;
执行待调试的语句
select c.code, nvl(b.con, 0)
from t_sys_codemap c,
(select COUNT(1) con, m.bljg
from V_JC_XZXK_BUSI_TIMELIMIT t, V_JC_XZXK_BUSIINDEX m
where t.busiindexid = m.ywlsh
and t.LIMITTYPE = 1
and trunc(m.tjsj) >= trunc(SYSDATE, 'year')
&nb
相关文档:
Select * from t_user_profile where convert ( varchar ( 21 ),regDate, 120 ) like ' 2008-05-07% ' 表名称:t_user_profile 日期字段名称:regDate
Select * from t_user_profile where convert(varchar(21),regDate,120) like '2008-05-07%'< ......
游标是从数据库中提取出来的数据,以临时表的形式存放在内存中,在游标中有一个数据指针,在初始状态下指向首记录, 利用fetch语句移动该指针,从而对游标中的数据进行各种操作。
1.定义游标
cursor 游标名 is select语句;
2.打开游标
open 游标名;
3.提取游标数据
fetch 游标名 into 变量名1, 变量名2, ....;
或
......
Oracle9i异常处理分为系统预定义异常处理和自定义异常处理两部分。
自定义异常处理
1.定义异常处理
declare 异常名 exception;
2.触发异常处理
raise 异常名
3.处理异常
exception
when 异常名1 then
异常处理语句段1;
when 异常名2 then
异常处理语句段2;
示例:
se ......
What is SQL*Plus and where does it come from?
SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server software. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers ......
全文搜索的核心引擎建立在Microsoft Full-Text Engine for SQL Server (MSFTESQL) 服务提供支持
面对海量的数据,如何才能找到我需要的?对数百万行文本数据执行的LIKE 查询可能需要花费几分钟时间才能返回结果;但对同样的数据,全文查询只需要几秒或更少 ......