블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30            
  • 35,390total
  • 16today
  • 2yesterday
2019.05.30 10:14 Zabbix

Service로 등록하는 방법

 

zabbix_agentd.exe -i -c zabbix_agentd.conf

https://sangchul.kr/542

'Zabbix' 카테고리의 다른 글

Windows Zabbix Agent 설치  (0) 2019.05.30
Postgresql 모니터링  (0) 2019.02.19
Linux DISK IO 모니터링  (0) 2018.11.30
zabbix Agent 설치  (0) 2018.10.26
SAN Switch 모니터링 적용  (0) 2018.10.26
brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
posted by bedbmsguru
2019.05.23 16:09 SQL SERVER

SELECT IndexName = i.Name, ColName = c.Name FROM sys.indexes i INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE ic.is_included_column = 1 ORDER BY i.Name

 

 

https://stackoverflow.com/questions/18172359/list-all-indexes-with-included-columnsnonkeys

 

 

 

posted by bedbmsguru
2019.05.10 16:40 오라클

SELECT owner, table_name, TRUNC(sum(bytes) / 1024 / 1024) Meg
  FROM (SELECT segment_name table_name, owner, bytes
          FROM dba_segments
         WHERE segment_type = 'TABLE'
        UNION ALL
        SELECT i.table_name, i.owner, s.bytes
          FROM dba_indexes i, dba_segments s
         WHERE s.segment_name = i.index_name
           AND s.owner = i.owner
           AND s.segment_type = 'INDEX'
        UNION ALL
        SELECT l.table_name, l.owner, s.bytes
          FROM dba_lobs l, dba_segments s
         WHERE s.segment_name = l.segment_name
           AND s.owner = l.owner
           AND s.segment_type = 'LOBSEGMENT'
        UNION ALL
        SELECT l.table_name, l.owner, s.bytes
          FROM dba_lobs l, dba_segments s
         WHERE s.segment_name = l.index_name
           AND s.owner = l.owner
           AND s.segment_type = 'LOBINDEX')
 WHERE owner in UPPER('&owner')
 GROUP BY table_name, owner
HAVING SUM(bytes) / 1024 / 1024 > 10 /* Ignore really small tables */
 ORDER BY SUM(bytes) desc;

 

출처: http://a2zakir.blogspot.com/2012/02/script-for-getting-oracle-table-size.html

posted by bedbmsguru
2019.05.02 13:12 오라클

https://ksmk.tistory.com/28

posted by bedbmsguru
2019.03.06 15:08 오라클

Object 종류 등등 여러가지로 변경 응용 가능


SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'procedure이름', '스키마이름') FROM DUAL


posted by bedbmsguru
2019.02.19 15:27 SQL SERVER

아주 괜챃은 방법이다.


Extended Event Data 조회

http://www.sqldoubleg.com/2016/05/31/extended-events-file-target-reader/


CREATE EVENT SESSION [ALL_SQL_SAVE] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([object_name]<>N'sp_reset_connection')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'F:\temp\ALL_QUERY.xel',max_file_size=(500),max_rollover_files=(30))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO



IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent
SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
INTO #ExEvent
from sys.fn_xe_file_target_read_file ( 'f:\Temp\AAA.xel',null, null, null ) ;




(39266개 행이 영향을 받음)
SELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId
, ISNULL(t_action.event_name, t_data.event_name) AS event_name
, t_action.[client_hostname], t_action.[collect_system_time], t_action.[client_app_name], t_action.[session_id]
, t_data.[cpu_time], t_data.[duration], t_data.[logical_reads], t_data.[object_name], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]
FROM (
SELECT RowId, event_name, [client_hostname], [collect_system_time], [client_app_name], [session_id]
FROM (
SELECT RowId
, event_name
, T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
, T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
FROM #ExEvent
CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
IN ('client_hostname', 'collect_system_time', 'client_app_name', 'session_id')
) AS SourceTable
PIVOT(
MAX(att_value)
FOR att_name IN ([client_hostname], [collect_system_time], [client_app_name], [session_id])
) AS PivotTable
) AS t_action

-- Full outer because it might be no events selected only the payload
FULL OUTER JOIN (
SELECT RowId, event_name, [cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes]
FROM (
SELECT RowId
, event_name
, T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)') AS att_name
, T3.Loc.query('.').value('(/data/value)[1]', 'varchar(max)') AS att_value

FROM #ExEvent
CROSS APPLY event_data.nodes('/event/data') as T3(Loc)
WHERE T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)')
IN ( 'cpu_time', 'duration', 'logical_reads', 'object_name', 'physical_reads', 'result', 'row_count', 'statement', 'writes')
) AS SourceTable
PIVOT (
MAX(att_value)
FOR att_name IN ([cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes])
) AS PivotTable

) AS t_data
ON t_data.RowId = t_action.RowId




posted by bedbmsguru
2019.02.19 13:13 Zabbix

Linux Version 가능


http://cavaliercoder.com/libzbxpgsql/

'Zabbix' 카테고리의 다른 글

Windows Zabbix Agent 설치  (0) 2019.05.30
Postgresql 모니터링  (0) 2019.02.19
Linux DISK IO 모니터링  (0) 2018.11.30
zabbix Agent 설치  (0) 2018.10.26
SAN Switch 모니터링 적용  (0) 2018.10.26
brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
posted by bedbmsguru
2018.12.31 16:12 오라클

출처:http://oracle.tistory.com/430


1. 세션별 CPU Time, Memory 사용량
 
select s.sid, s.serial#, p.spid as "os pid", s.username, s.module, s.sql_id, event, seconds_in_wait,
st.value/100 as "cpu sec",
round(pga_used_mem/1024/1024) "pga_tot(mb)",
round(pga_used_mem/1024/1024) "pga_per_sess(mb)"
from v$sesstat st, v$statname sn, v$session s, v$process p
where sn.name = 'CPU used by this session' -- cpu
and st.statistic# = sn.statistic#
and st.sid = s.sid
and s.paddr = p.addr
and s.last_call_et < 1800 -- active within last 1/2 hour
and s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
order by st.value

 
 
 
 
2. Client별 Memory 사용량
 
select machine,status,count(*) cnt, 
       round(sum(pga_used_mem)/1024/1024) "pga_tot(mb)",
       round(sum(pga_used_mem)/count(*)/1024/1024) "pga_per_sess(mb)"
from v$session s, v$process p
where 1=1
--and s.status='active'
and s.paddr=p.addr
and type <> 'BACKGROUND'
group by machine,status
order by 1



출처: http://oracle.tistory.com/430 [안나푸르나]

'오라클' 카테고리의 다른 글

Oracle 11g 설치 오류 PRVF-0002  (0) 2019.05.02
오라클 Object DDL 스크립트 추출  (0) 2019.03.06
오라클 세션별 CPU, PGA 사용량 확인 쿼리  (0) 2018.12.31
DML쿼리 튜닝방법  (0) 2018.09.19
스케줄러 사용법  (0) 2018.08.27
특정테이블 index Size 조회  (0) 2018.08.13
posted by bedbmsguru
2018.12.26 16:24 Powershell

column customizing 포함


$a = @{Expression={$_.SamAccountName}; Label="Column 1"; Width=50},
@{Expression={$_.surName}; Label="Column 2"; Width=50},
@{Expression={$_.Description}; Label="Column 3"; Width=50},
@{Expression={$_.Created}; Label="Column 4"; Width=50},
@{Expression={$_.LastLogonDate}; Label="Column 4"; Width=50}

Get-ADUser -Properties SamAccountName, surName, Description, Created, LastLogonDate -Filter {ObjectClass -eq "user"} -SearchBase "OU=MYOU,DC=mydc,DC=co,DC=kr" | Sort-Object -property SamAccountName | Select-object SamAccountName, surName, Description, Created, LastLogonDate | Format-Table -property $a > d:\aa.txt 

posted by bedbmsguru