❶ 如何查看Oracle一条sql 消耗的内存多少谢谢！
用PL/SQL工具，里面有个 EXPLAIN PLAN(F5)功能，可以分析SQL语句执行性能。
1.能分析出SQL语句是否使用到索引，使用了那些索引。
2.语句执行的成本。
3.......
❷ sql占用内存瞬间爆满
在SQL2008会把整个数据库缓存到内存里面，就是说你数据库有多大，就会占内存多个空间，直到把内存占满，你可以限制SQL 内存
首先我们需要登录 sql server 2005的资源管理器
鼠标右击我们sql server 2005的服务器，然后选择“属性”选项
找到指定数据库服务器的属性中的“内存”属性，并点击
接下来就是配置数据库内存了
http://jingyan..com/article/fedf07377c353f35ac8977f8.html
❸ 如何查找最耗费cpu和内存的sql语句
CREATE procere sp_who3
@type int =1,
@host_name varchar(50) ='%'
as
declare @str varchar(200)
select @str =@type
if not (@str = '1' or @str ='2' or @str ='3' or @str ='4' or @str ='11' or @str ='12' or @str ='13' or @str ='22' or @str ='21' or @str ='31' or @str ='32' or @str ='14'or @str = '15' or @str ='23')
select '支持命令'= ' 1 - 请求信息' union all
select '支持命令'= ' 2 - 请求信息 by user' union all
select '支持命令'= ' 3 - 请求信息 info by hostname' union all
select '支持命令'= ' 4 - 请求信息 info by status' union all
select '支持命令'= '11 - 记录当前阻塞信息 ' union all
select '支持命令'= '12 - 阻塞其他session的session ' union all
select '支持命令'= '13 - 被阻塞时间最长的session ' union all
select '支持命令'= '14 - 递归查被阻塞' union all
select '支持命令'= '15 - 死锁信息' union all
select '支持命令'= '21 - 事务日志时间按用户指定用户 ' union all
select '支持命令'= '22 - 事务日志时间按 ' union all
select '支持命令'= '31 - 平均消费CPU的前30行语句 ' union all
select '支持命令'= '32 - 总耗CPU最多的前个SQL '
--request info
if @type = 1
select s.session_id, s.status,db_name(r.database_id) as database_name,
s.login_name,s.login_time, s.host_name,
c.client_net_address,c.client_tcp_port,s.program_name,
r.cpu_time, r.reads, r.writes,c.num_reads,c.num_writes,
s.client_interface_name,
s.last_request_start_time, s.last_request_end_time,
c.connect_time, c.net_transport, c.net_packet_size,
r.start_time, r.status, r.command,
r.blocking_session_id, r.wait_type,
r.wait_time, r.last_wait_type, r.wait_resource, r.open_transaction_count,
r.percent_complete,r.granted_query_memory
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50 and s.host_name like @host_name
order by s.session_id
if @type =2
--request info by user
select login_name,COUNT(0) user_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by login_name
order by 2 desc
--request info by hostname
if @type =3
select s.host_name,c.client_net_address,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50 and host_name like @host_name + '%'
group by host_name,client_net_address
order by 3 desc
--request info by status
if @type =4
select s.status,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by s.status
order by 2 desc
----------------------------------------Blocked Info----------------------------------
--记录当前阻塞信息
if @type =11
select t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_ration_ms as [wait time]
,(select text from sys.dm_exec_requests as r with(nolock) --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r with(nolock)
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- statement executing now
,t2.blocking_session_id as [blocker sid] --- spid of blocker
,(select text from sys.sysprocesses as p with(nolock) --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
--阻塞其他session的session
if @type =12
select t2.blocking_session_id,COUNT(0) counts
from sys.dm_tran_locks as t1 with(nolock) ,
sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
group by blocking_session_id
order by 2
--被阻塞时间最长的session
if @type =13
select top 10 t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_ration_ms as [wait time]
,(select text from sys.dm_exec_requests as r with(nolock) --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r with(nolock)
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- statement executing now
,t2.blocking_session_id as [blocker sid] --- spid of blocker
,(select text from sys.sysprocesses as p with(nolock) --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
order by t2.wait_ration_ms desc
if @type =21
select '事务日志时间'= b.elapsed_time_seconds, hh = b.elapsed_time_seconds/3600,
a.session_id,
a.login,
[host_name],
[database],
task_state,
a.command,
a.total_cpu_ms,
a.total_physical,
a.memory_use,
open_transactions,
login_time,
last_request_start_time,
net_address,
sqlsyntax
from v_sys_session a left join sys.dm_tran_active_snapshot_database_transactions b
on a.session_id =b.session_id where host_name like @host_name
order by a.session_id
if @type ='31'--最消费CPU的前30行语句
SELECT TOP 30
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC
--总耗CPU最多的前个SQL:
if @type ='32'
begin
SELECT TOP 30
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY total_worker_time DESC
end
GO
SP_WHO3 32
GO
❹ sql吃内存的问题，不管你的内存有多大，sql总是能占用你90+%的内存，增加了内存，他还是占用90+%的内存！
可以配置使用内存。
登录SQL Server Management Studio
在实例名上右键-属性-内存
在配置值中配置最大服务器内存。
❺ 如何查询当前执行过后最消耗内存的sql语句是那句
--当前执行sql语句
SELECT a.SID ,
a.SERIAL# ,
a.USERNAME ,
b.PARSE_CALLS ,
b.PARSING_SCHEMA_NAME ,
b.CPU_TIME / 1000000 ,
b.ELAPSED_TIME / 1000000 ,
b.DISK_READS ,
b.DIRECT_WRITES ,
b.BUFFER_GETS ,
a.event ,
b.sql_text ,
b.SQL_FULLTEXT
FROM v$session a
INNER JOIN v$sqlarea b ON a.SQL_HASH_VALUE = b.hash_value
AND b.PARSING_SCHEMA_NAME = UPPER('smsdb')
--物理读最高sql语句
SELECT a.USERNAME ,
a.USER_ID ,
b.PARSE_CALLS ,
b.PARSING_SCHEMA_NAME ,
b.CPU_TIME / 1000000 ,
b.ELAPSED_TIME / 1000000 ,
b.DISK_READS ,
b.DIRECT_WRITES ,
b.BUFFER_GETS ,
b.sql_text ,
b.SQL_FULLTEXT
FROM dba_users a
INNER JOIN v$sqlarea b ON a.USER_ID = b.PARSING_USER_ID
AND b.PARSING_SCHEMA_NAME = UPPER('smsdb')
AND disk_reads > 1000000
--查询前10名执行最多次数SQL语句
SELECT sql_text 'SQL语句' ,
executions '执行次数'
FROM ( SELECT sql_text ,
executions ,
RANK() OVER ( ORDER BY executions DESC ) exec_rank
FROM v$sqlarea
)
WHERE exec_rank <= 10;
--查询前10名占用CPU最高的SQL语句
select sql_text 'SQL语句',
c_t 'SQL执行时间（秒）',executions '执行次数',cs '每次执行时间（秒）'from (select sql_text,
cpu_time /1000000 c_t,executions,ceil(executions/(cpu_time/1000000))cs,
rank() over(order by cpu_time desc) top_time
from v$sqlarea) where top_time <= 10
--查询前10名执行时间最长SQL语句
SELECT sql_text 'SQL语句' ,
c_t '处理时间(秒)' ,
executions '执行次数' ,
cs '每次执行时间（秒）'
FROM ( SELECT sql_text ,
ELAPSED_TIME / 1000000 c_t ,
executions ,
ceil(executions / ( ELAPSED_TIME / 1000000 )) cs ,
RANK() OVER ( ORDER BY ELAPSED_TIME DESC ) top_time
FROM v$sqlarea
)
WHERE top_time <= 10
--查询前10名最耗资源SQL语句
SELECT sql_text 'SQL语句' ,
DISK_READS '物理读次数' ,
cs '每次执行时间（秒）'
FROM ( SELECT sql_text ,
ELAPSED_TIME / 1000000 c_t ,
executions ,
ceil(executions / ( ELAPSED_TIME / 1000000 )) cs ,
DISK_READS ,
RANK() OVER ( ORDER BY DISK_READS DESC ) top_disk
FROM v$sqlarea
)
WHERE top_disk <= 10
--查询前10名最耗内存SQL语句
select sql_text 'SQL语句',
BUFFER_GETS '内存读次数',cs '每次执行时间（秒）'
from (select sql_text,
ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,BUFFER_GETS,
rank() over(order by BUFFER_GETS desc) top_mem
from v$sqlarea) where top_mem <= 10
--查看锁表语句
Select
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
c.program,
c.osuser
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;
❻ sql数据库占用内存太大如何处理
sql server没有办法释放内存，内存都是系统自动管理的
除非你杀调哪些占用内存大的进程
sql 语句没写好，死锁...都会导致占用内存很大
select * from master..sysprocesses
kill spid --spid 就是上面查到的spid字段
❼ SQL数据库占用内存很大怎么解决如果设置固定大小的话，会不会影响应用程序
看怎么分配够用啦，评估一下你的几个关键应用要站多大内存，
然后分配一下。比如你的服务端程序，IIS各需要多大内存，操作系统需要多少内存，剩下的给Sql Server。
如果内存不够，那就加内存啰，再不行就加服务器，不是所有问题都可以靠软件配置解决的。
❽ 怎么看SQL执行消耗了多少内存怎么释放SQL消耗的资源
sql 消耗的资源不能释放。SQL会把数据放在内存上，这样下次再使用这些数据时，就不需要从硬盘上读取了，加快速度。
❾ 求教sql语句的写法与内存消耗的关系
会更节省内存，因为按照SQL的处理逻辑顺序，ON是比Where的优先级高。