❶ 如何查看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的優先順序高。