Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Queries and useful(?) stuff, infodump

Former Member
‎08-06-2014 3:27 PM

Queries and other useful stuff, infodump


See The Future of SAP IDM by Matt Pollicove for more information but in short; IdM is moving from my office in Trondheim to Sofia. This entry will simply be an infodump of some of the IdM related and non-IdM related queries I've thought about using in future blogs, have used in support-cases and things I've found elsewhere online that I thought would be useful at a later time. This might be a bit heavy on execution plan stuff and might not be useful for "plain" IdM solution implementation but I've seen some of you guys getting pretty advanced so...


Please excuse the lack of (or in some cases misleading) descriptions, formatting, testing and organization. I've tried to keep this limited to harmless queries, but don't use them if you don't understand what they do, or atleast use them in a non-critical system while figuring them out.


Thank you all for the feedback and the great learning experience this community has offered!


Best regards, thanks and goodbye,

Per "Chris" Christian Krabsetsve

🙂




Related blogs I've written

The blogs that are already published that are still in progress will not be finished by me. Anyone who wants to take the texts, partial or whole, to finalized or improve is welcome to do so.

IDM SQL Basics #1: Queries against the Identity Store

IdM SQL Basics #2: Locating problem queries

IDM SQL Basics #3: Testing and improving queries


On queue processing, or the lack thereof. Part #1

On queue processing, or the lack thereof. Part #2

On queue processing, what (just) happened? Part #3


IDM and provision queue counters & statistics gathering


Misc queries, might work on either ora or sql or both.

Provisioning/queue

Actions ready to run group by actiontype



select A.Name as actiontype,COUNT(mskey) as availableEntries from MXP_Provision P, mxp_actiontype A

where P.State=2 and A.ActType=P.ActionType group by A.Name

Provisioning actions(jobs) that could be running but are not



select T.TaskName,COUNT(mskey) as availableEntries from MXP_Tasks T,MXP_Provision P where P.ActionID=T.TaskID and

P.ActionType=0 and P.state=2 and T.JobGuid not in (select JobGuid from mc_jobs where State=1)

group by taskname

Actions ready to run grouped by taskname



select t.taskname,count(p.mskey) from MXP_Provision P with(nolock) , mxp_tasks T with(nolock)

where T.taskid = P.actionid group by T.taskname

Actions ready to run grouped by action type name,taskid



select T.TaskName ,A.Name as actiontype,COUNT(mskey) as available

from MXP_Provision P, mxp_actiontype A, MXP_Tasks T

where P.State=2 and A.ActType=P.ActionType and T.taskid=P.actionid group by T.taskname,A.Name

Running provision jobs and regular jobs



select case when Provision=1 then 'provision' when provision=0 then 'job' else 'huh' end "type", count(jobid) running

from mc_jobs where state=2 group by provision

Provision queue entries ready to process/run grouped by actiontype and name



select T.Name,COUNT(mskey) as available from MXP_Provision P, mxp_actiontype T

where P.State=2 and T.ActType=P.ActionType group by T.Name

Provision queue entries ready to process/run grouped by actiontype and name with statename



select count(P.mskey),t.taskname,s.name from mxp_provision P, mxp_Tasks T, mxp_state S

where S.StatID=P.State and T.taskid = P.ActionID  group by T.taskname, S.name

Task usage, list number of times a task has been executed since xx.yy.zz



select T.taskname,COUNT(A.aud_ref) from MXP_Ext_Audit A, MXP_Tasks T

where T.TaskID = A.aud_task and A.Aud_datetime > '2012-06-20'

group by T.TaskName

order by COUNT(A.aud_ref) desc

Display number of jobs and provision jobs available for a dispatcher



SET NOCOUNT ON

declare @sj int, @jj int, @sp int, @jp int, @dn varchar(20), @dv varchar(20)

SET @dn = 'dispatcher name' -- dispatcher name

SET @dv = '8.0' -- version, not important

exec mc_dispatcher_check @dn,@dv,1,1,1,1,@sj output,@jj output,@sp output,@jp output

print 'Jobs and provisioning jobs available for '+@dn

print 'WinRT jobs:'+cast(@sj as varchar)

print 'javaRT jobs:'+cast(@jj as varchar)

print 'WinRT prov jobs:'+cast(@sp as varchar)

print 'JavaRT prov jobs:'+cast(@jp as varchar)

Job processing


Use the logs to summarize the number of entries a job has processed, average processing time and some more.



select J.name,sum(L.Num_Adds) adds,SUM(L.NUM_MODS) mods, SUM(timeused) tUsed, (sum(L.Num_Adds)+SUM(L.NUM_MODS))/SUM(timeused) entryPerSec

from mc_logs L with (nolock), mc_jobs J with (nolock) where J.JobId=L.JobId and TIMEUSED > 0 and L.LOGDATE > '2012-06-20'

group by J.name

order by (sum(L.Num_Adds)+SUM(L.NUM_MODS))/SUM(timeused) desc















































select LA.jobname,LA.JobId,case when J.provision = 1 then 'action' else 'job' end as "jobtype",

sum(LA.TotalEntries) totalEntries,sum(LA.TimeUsed) totalTime

,round(cast(sum(LA.TotalEntries) as float)/cast(sum(LA.TimeUsed) as float),2) entriesPerSecond

from mcv_logall LA, mc_jobs J

where LA.JobId = J.JobId

group by LA.jobname,LA.jobid, J.Provision











































select jobname,JobId,sum(TotalEntries) totalEntries,sum(TimeUsed) totalTime

,Round(sum(TotalEntries) /sum(TimeUsed),2) entriesPerSecond

from mcv_logall

group by jobname,jobid

order by entriesPerSecond











































select jobname,JobId,sum(TotalEntries) totalEntries,sum(TimeUsed) totalTime,

round(cast(sum(TotalEntries) as float)/cast(sum(TimeUsed) as float),2) entriesPerSecond

from mcv_logall group by jobname,jobid

order by round(cast(sum(TotalEntries) as float)/cast(sum(TimeUsed) as float),2)  asc

Provisioning audit

Some audit stuff (oracle). Execution time for task X executed after date Y



select taskid,to_char(posteddate,'YYYY-MM-DD') "date",count(auditid) "numExecs",AVG(round(statusdate-posteddate,2)*24*60*60) "avgTimeToComplete"

from mxp_audit

where taskid = 20 and provstatus > 1000 and postedDate > to_date('2014-01-01','YYYY-MM-DD')

group by taskid,to_char(posteddate,'YYYY-MM-DD')

order by taskid,to_char(posteddate,'YYYY-MM-DD')

Some audit stuff (SQL Server). Execution time for task X executed after date Y



select avg(datediff(SS,posteddate,statusdate)) Seconds,cast(posteddate as date) Date

from MXP_AUDIT

where taskid = 1406 and posteddate > '2013-06-01'

group by cast(posteddate as date)

order by cast(posteddate as date)

Some audit stuff (oracle). Executed task for user X



select A.auditid, A.AuditRoot, A.RefAudit auditParent, A.userid, A.StartedBy, A.taskid, T.taskname, A.mskey, A.PostedDate, A.StatusDate, A.provstatus, A.LastAction, A.msg

from MXP_AUDIT A, MXP_Tasks T where A.TaskId = T.TaskID

and A.msKey = (select mcmskey from idmv_entry_simple where mcMskeyValue = 'ADMINISTRATOR')

order by auditroot,RefAudit

Some audit stuff (SQL Server), mainlylisting seconds from task being posted to its completion



select taskid,convert(varchar(10),posteddate,20) Date,count(auditid) as numExecs,avg(datediff(ss,A.posteddate,A.statusdate)) AvgTimeToComplete

from mxp_audit A

where taskid = 1 and posteddate > '2014-02-01' and ProvStatus > 1000

group by taskid,convert(varchar(10),posteddate,20)

order by taskid,convert(varchar(10),posteddate,20)

Some audit stuff (SQL Server), mainlylisting seconds from task being posted to its completion inc. count of substasks



select auditid,postedDate,taskid,datediff(ss,A.posteddate,A.statusdate) seconds,

   (select count(aud_ref) from MXP_Ext_Audit AE with(nolock) where a.AuditID=ae.Aud_ref)

from mxp_audit A with(nolock) order by posteddate

Average time between posting and completion of task between two dates



select avg(datediff(ss,A.posteddate,A.statusdate)) seconds

from mxp_audit A where postedDate >='2013-12-05 16:49:35' and postedDate <'2013-12-05 16:49:40'

Number of executions of tasknames after  specified date



select

       count(a.aud_ref),t.taskname,

       convert(varchar,A.aud_datetime,20)

from

       mxp_ext_audit A with(nolock), mxp_tasks T with(nolock)

where

       a.Aud_task = T.TaskID and

       (aud_datetime > '2013-11-18 12:00')

group by t.taskname,convert(varchar,A.aud_datetime,20)

List number root tasks started in minute intervals for a period of time



-- Create helper table for intervals

declare @intervaltbl table(st datetime,et datetime)

declare @it datetime

declare @interval int

set @interval = 5 -- minutes

-- Interval Start time

set @it = '2014-05-27 10:00:00'

-- Interval End time

while @it <= '2014-05-27 12:30:00'

begin

    insert into @intervaltbl values (@it,dateadd(mi, @interval, @it))

    set @it = dateadd(mi, @interval, @it)  -- 5 minute interval, change as needed

end

-- count per interval

select it.st startinterval, it.et endinterval, count(*) troottaskstarted

  from @intervaltbl it, MXP_AUDIT A with(nolock)

  where A.PostedDate between it.st and it.et

group by it.st,it.et

List tasks and the number of execs per task in 10 minute intervals for a timeframe



select count(aud_ref) taskexecutions, t.taskname, left(CONVERT(varchar,aud_datetime,21),15)

from mxp_ext_audit AE with(nolock), MXP_Tasks T with(nolock)

where T.taskid = AE.aud_task

and   Aud_datetime between '2013-04-19 00:01' and '2013-04-19 03:30'

group by T.taskname, left(CONVERT(varchar,aud_datetime,21),15)


List how many times each individual task was run in a time-interval



-- Create helper table for intervals

declare @intervaltbl table(st datetime,et datetime)

declare @it datetime

declare @interval int

set @interval = 5 -- minutes

-- Interval Start time

set @it = '2014-05-27 10:00:00'

-- Interval End time

while @it <= '2014-05-27 12:30:00'

begin

    insert into @intervaltbl values (@it,dateadd(mi, @interval, @it))

    set @it = dateadd(mi, @interval, @it)  -- 5 minute interval, change as needed

end

-- Do a count per interval

select it.st startinterval, it.et endinterval, T.taskname, count(*) TimesStarted

  from @intervaltbl it, MXP_EXT_AUDIT A with(nolock), MXP_Tasks T with(nolock)

  where A.Aud_datetime between it.st and it.et and A.Aud_task = T.taskid

group by it.st,it.et, T.taskname

order by it.st, T.taskname

an example of a query extracting details of tasks executed for a user



SELECT        dbo.MXP_Ext_Audit.Aud_ref AS Auditref, dbo.MXP_Ext_Audit.Aud_OnEntry AS EntryId, dbo.MXP_Ext_Audit.Aud_datetime AS Time, MXP_Tasks_1.TaskName AS Task,

                         dbo.MXP_ProvStatus.Name AS Status, dbo.MXP_Tasks.TaskName AS [End task], dbo.MXP_Ext_Audit.Aud_Approver AS Approver,

                         dbo.MXP_Ext_Audit.Aud_Info AS Info, dbo.MXP_Ext_Audit.Aud_StartedBy AS [Started by], dbo.MXP_AUDIT.MSG AS Message,

                         dbo.MC_REPOSITORY.rep_name AS Repository

FROM dbo.MXP_Ext_Audit WITH (nolock) INNER JOIN

 dbo.MXP_AUDIT ON dbo.MXP_Ext_Audit.Aud_ref = dbo.MXP_AUDIT.AuditID INNER JOIN

 dbo.MXP_ProvStatus ON dbo.MXP_AUDIT.ProvStatus = dbo.MXP_ProvStatus.ProvStatus INNER JOIN

 dbo.MXP_Tasks ON dbo.MXP_AUDIT.LastAction = dbo.MXP_Tasks.TaskID INNER JOIN

 dbo.MXP_Tasks AS MXP_Tasks_1 ON dbo.MXP_Ext_Audit.Aud_task = MXP_Tasks_1.TaskID LEFT OUTER JOIN

 dbo.MC_REPOSITORY ON dbo.MXP_AUDIT.repository = dbo.MC_REPOSITORY.rep_id

WHERE (dbo.MXP_Ext_Audit.Aud_ref IN

 (SELECT AuditID

 FROM dbo.MXP_AUDIT AS MXP_AUDIT_1 with (nolock)

 WHERE        (MSKey =

 (SELECT mcMSKEY

 FROM dbo.idmv_entry_simple with (nolock)

 WHERE        (mcMSKEYVALUE = 'TEDA')))))

ORDER BY Auditref, Time

Initial Load data stuff

Using the passes from Initial Load (or using initial load with the To IdStore passes disabled) read the repository data to temporary tables; users, roles, assignments, stop the job from doing anything else.

Look at the mapping of users from the repository to the IdStore ACCOUNT%REP.NAME% attribute. This you can use  to

Find users from the repository tempoarary table not in the idstore



select REP.* from

sap%$rep.$NAME%User REP

left outer join idmv_value_basic IDSVAL on REP.accountname = IDSVAL.SearchValue and IDSVAL.ATTRNAME='ACCOUNT%$rep.$NAME%'

where IDSVAL.SearchValue IS NULL

Find IdStore users in the IdStore not in the repository



select E.mcmskeyvalue

from idmv_value_basic IDS

inner join idmv_entry_simple E on E.mcmskey = ids.mskey and E.mcEntryType='MX_PERSON'

left outer join mxmc_rt_u.sap%$REP.$NAME%user REP on IDS.SearchValue=REP.accountname

where   ids.attrname = 'sap%$REP.$NAME%user'

and REP.accountname IS NULL

Identity Store Flattening





select mskey,

  max (case when AttrName = 'MSKEYVALUE' then aValue end ) mskeyvalue,

  max (case when AttrName = 'DISPLAYNAME' then aValue end ) DisplayName,

  max (case when AttrName = 'MX_ENTRYTYPE' then aValue end ) EntryType

from idmv_value_basic

where attrname in ('MSKEYVALUE','DISPLAYNAME','MX_ENTRYTYPE')

group by mskey

Using pivot:





select * from

(

  select mskey,avalue,attrname from idmv_value_basic where attrname in ('MSKEYVALUE','DISPLAYNAME','MX_ENTRYTYPE')

) s

pivot

(

  max(avalue) for attrname in (MSKEYVALUE,DISPLAYNAME,MX_ENTRYTYPE)

) p


Also using the STUFF + for xml path to flatten things is cool TSQL &amp;#8211; Concatenate Rows using FOR XML PATH() | Sql And Me


IdM 7.1 example, easy to adapt:



select 'My roles:'+stuff((select ','+searchvalue

      from mxiv_sentries where attrname = 'MX_AUTOROLE' and mskey =128108

for xml path('')),1,1,'')

as MyRoles



SQL Server

Show running queries



SELECT database_id,st.text, r.session_id, r.status, r.command, r.cpu_time,r.total_elapsed_time

FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st order by database_id

Finding execution plan handle (prepared or adhoc, or filter for just one of them as needed):



SELECT objtype, p.size_in_bytes, t.[text], usecounts, p.plan_handle

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) t

WHERE objtype IN ('Prepared', 'Adhoc')

and T.[text] like '%mxi_values%'







































SELECT plan_handle,UseCounts, Cacheobjtype, Objtype, TEXT, query_plan

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

CROSS APPLY sys.dm_exec_query_plan(plan_handle)

WHERE TEXT like '%mcuniqueid%mcothermskey%mcotherentrytype%mcdisplayname%mcreason%mcvalidfrom%mcvalidto%mcExecState%mcExecStateHierarchy%'







































SELECT qplan.query_plan AS [Query Plan],qtext.text

FROM sys.dm_exec_query_stats AS qstats

CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan

cross apply sys.dm_exec_sql_text(qstats.plan_handle) as qtext

where text like '%mcuniqueid%mcothermskey%mcotherentrytype%mcdisplayname%mcreason%mcvalidfrom%mcvalidto%mcExecState%mcExecStateHierarchy%';

Retrieve an execution plan using plan handle (see above)

If forced to use RT account you might need someone to run this for you first



GRANT SHOWPLAN TO mxmc_user_u






































SELECT * FROM sys.dm_exec_query_plan (0x06000600ACC0D612F05E7C5F0200000001000000000000000000000000000000000000000000000000000000);

Clear plan cache and more

DBCC FREEPROCCACHE

Check memory states

DBCC MEMORYSTATUS

Show statistics for table/index

DBCC SHOW_STATISTICS (mxi_values,ix_mxi_values_perf)

List indexes and index usage stats



SELECT DB_NAME(DATABASE_ID) AS DATABASENAME, OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,

       INDEX_NAME = (SELECT NAME FROM   SYS.INDEXES A WHERE  A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID),

       USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES

FROM   SYS.DM_DB_INDEX_USAGE_STATS B INNER JOIN SYS.OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID

WHERE  DATABASE_ID = DB_ID(DB_NAME()) AND C.TYPE <> 'S'







































SELECT   PVT.TABLENAME, PVT.INDEXNAME, [1] AS COL1, [2] AS COL2, [3] AS COL3,

         [4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7, B.USER_SEEKS,

         B.USER_SCANS, B.USER_LOOKUPS

FROM     (SELECT A.NAME AS TABLENAME,A.OBJECT_ID,B.NAME AS INDEXNAME,B.INDEX_ID,D.NAME AS COLUMNNAME,C.KEY_ORDINAL

          FROM   SYS.OBJECTS A INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID

                 INNER JOIN SYS.INDEX_COLUMNS C ON B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID

                            INNER JOIN SYS.COLUMNS D ON C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID

          WHERE  A.TYPE <> 'S') P

         PIVOT

         (MIN(COLUMNNAME)

          FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT

         INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B

           ON PVT.OBJECT_ID = B.OBJECT_ID AND PVT.INDEX_ID = B.INDEX_ID AND B.DATABASE_ID = DB_ID()

ORDER BY TABLENAME, INDEXNAME;

Update statistics for a table



update statistics mxi_values

List system processes/sessions with state/login/db and more



SELECT

spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,

convert(sysname, rtrim(loginame)) as loginname,spid as 'spid_sort',

substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch, DB_NAME(dbid)

from master.dbo.sysprocesses (nolock)

order by loginname

Missing Indexes hints



SELECT * FROM sys.dm_db_missing_index_details

SELECT * FROM sys.dm_db_missing_index_group_stats

SELECT * FROM sys.dm_db_missing_index_groups

SELECT * FROM sys.dm_db_missing_index_columns(1)






































SELECT mig.*, statement AS table_name,

column_id, column_name, column_usage

FROM sys.dm_db_missing_index_details AS mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id;

Statistics, Timing, IO, CPU, XML, PLAN…

set statistics io on

set statistics time on

set statistics profile on

SET SHOWPLAN_TEXT ON

SET SHOWPLAN_ALL ON

SET STATISTICS XML ON

Backup, transaction log shrinking and other stuff



BACKUP LOG [mxmc_db]

TO  DISK = N'C:\temp\mxmcdbfullcopy'

WITH NOFORMAT, INIT,  NAME = N'mxmc_db-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO







































select name,recovery_model_desc from sys.databases

Alter database mxmc_db SET Recovery simple

select name,size from sys.database_files

DBCC SHRINKFILE (N'mastlog' , 1)

Alter database mxmc_db SET Recovery full

Clearing all transactionlogs on dev and test-systems



SET NOCOUNT ON

CREATE TABLE #TransactionLogFiles (DBName VARCHAR(150), LogFileName VARCHAR(150) )

DECLARE

DBList CURSOR FOR SELECT name FROM master..sysdatabases WHERE NAME NOT IN ('master','tempdb','model','msdb','distribution')

DECLARE

@DB VARCHAR(100)DECLARE @SQL VARCHAR(8000)

OPEN DBList

FETCH NEXT FROM DBList INTO @DB

WHILE @@FETCH_STATUS <> -1

BEGIN

      SET @SQL = 'USE ' + @DB + ' INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT ''' + @DB + ''', Name FROM sysfiles WHERE FileID=2'

      EXEC(@SQL) FETCH NEXT FROM DBList INTO @DB

END

DEALLOCATE DBList

DECLARE TranLogList CURSOR FOR

SELECT DBName, LogFileName FROM #TransactionLogFiles

DECLARE @LogFile VARCHAR(100)

OPEN TranLogList

FETCH NEXT FROM TranLogList INTO @DB, @LogFile

WHILE @@FETCH_STATUS <> -1

BEGIN

      PRINT @DB

      --SELECT @SQL = 'EXEC sp_dboption ' + @DB + ', ''trunc. log on chkpt.'', ''True'''

         SELECT @SQL = ' ALTER DATABASE ' + @DB + ' SET AUTO_SHRINK ON '

-- ALTER DATABASE [dbname] SET RECOVERY SIMPLE

      EXEC (@SQL)

      SELECT @SQL = 'USE ' + @DB + ' DBCC SHRINKFILE(''' + @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'

      EXEC (@SQL)

      --SELECT @SQL = 'EXEC sp_dboption ' + @DB + ', ''trunc. log on chkpt.'', ''False'''

           SELECT @SQL = ' ALTER DATABASE ' + @DB + ' SET AUTO_SHRINK OFF '

         EXEC(@SQL) FETCH NEXT FROM TranLogList INTO @DB, @LogFile END

DEALLOCATE TranLogList

DROP TABLE #TransactionLogFiles

List a tables constraints, w. optional only default values



SELECT SCHEMA_NAME(schema_id) AS SchemaName,

       OBJECT_NAME(parent_object_id) AS TableName,

       OBJECT_NAME(OBJECT_ID) AS NameofConstraint

FROM sys.objects WHERE OBJECT_NAME(parent_object_id) = 'MXP_TASKS'

-- and type_desc = 'DEFAULT_CONSTRAINT'

Drop all constraints for a table



BEGIN TRANSACTION

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'

ALTER TABLE ' + SCHEMA_NAME(schema_id)

    + '.' +OBJECT_NAME(parent_object_id) +

    ' DROP CONSTRAINT ' + OBJECT_NAME(OBJECT_ID) + ';'

FROM sys.objects where OBJECT_NAME(OBJECT_ID) = 'MXP_TASKS'

PRINT 'Dropping constraints: ' + @sql;

EXEC sp_executesql @sql;

                -- COMMIT

Find tables containing specific column type



select TABLE_NAME,COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE

from INFORMATION_SCHEMA.COLUMNS IC

where COLUMN_NAME = 'aLong'

Refresh statistics for ALL tables in system, and basic exception handling



DECLARE @statName varchar(255)

DECLARE @lastUpd datetime

DECLARE @tableName varchar(255)

DECLARE @updName varchar(512)

DECLARE @OPTION int

DECLARE cStatsList CURSOR LOCAL FAST_FORWARD FOR

       SELECT t.name tablename,s.name statname,STATS_DATE(s.[object_id], s.stats_id) AS lastupd

       FROM sys.stats s JOIN sys.tables t ON s.[object_id] = t.[object_id]

       WHERE t.is_ms_shipped = 0 ORDER BY t.name,s.name

       OPEN cStatsList

       FETCH NEXT FROM cStatsList INTO @tableName,@statName,@lastUpd

       WHILE (@@FETCH_STATUS = 0)

       BEGIN

              PRINT 'Refreshing statistics '+@statName+' for table '+@tableName+' which was last updated '+ISNULL(convert(varchar,@lastUpd),'never (probably an empty table)')

              SET @updName = @tableName+'('+@statName+')'

              BEGIN TRY

                     exec ('UPDATE STATISTICS '+@updName+'WITH FULLSCAN')

              END TRY

              BEGIN CATCH

                     PRINT 'Unable to refresh statistics '+@statName+' for table '+@tableName

              END CATCH

              FETCH NEXT FROM cStatsList INTO @tableName,@statName,@lastUpd

       END

CLOSE cStatsList

DEALLOCATE cStatsList

List tables and their  indexes



SELECT o.name AS tablename ,i.name AS indexname ,i.type_desc AS indextype ,STATS_DATE(i.[object_id], i.index_id) AS statisticsdate

FROM sys.indexes i INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE o.TYPE = 'U' -- Only get indexes for User Created Tables

AND i.name IS NOT NULL

ORDER BY o.name ,i.TYPE

List tables indexes with last update time



SELECT t.name tablename,s.name statname,STATS_DATE(s.[object_id], s.stats_id) AS lastupd

FROM sys.stats s JOIN sys.tables t ON s.[object_id] = t.[object_id]

WHERE t.is_ms_shipped = 0 ORDER BY t.name,s.name

List tables indexes with more detail



SELECT

t.name AS TableName,

c.name AS ColumnName,

s.name AS StatName,

STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,

s.has_filter,

s.filter_definition,

s.auto_created,

s.user_created,

s.no_recompute

FROM sys.stats s

JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id

JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id

JOIN sys.tables t ON s.[object_id] = t.[object_id]

WHERE t.is_ms_shipped = 0

ORDER BY t.name,s.name,c.name;

Testing a prepared statement issued by webui



declare @p1 int

set @p1=0

exec sp_prepexec @p1 output,N'@P0 int,@P1 int,@P2 int,@P3 int,@P4 int',N'

select distinct  top 200 mskey from idmv_jmx_entries VALS WITH (NOLOCK) where Attr_id IN (@P0,@P1,@P2,@P3) AND disabled=0

AND EXISTS (SELECT mcMskey FROM mxiv_entry E WHERE mcEntryTypeId=@P4 and mcMskey=VALS.mskey  and ((mcACEntry=0) or (mcACentry=1 AND

((23 IN (SELECT MemberMskey FROM idmv_members WHERE EntryMskey=E.mcMSKEY)) OR (23 IN (SELECT OwnerExpandedMskey FROM idmv_owners

WHERE EntryMSKEY=E.mcMSKEY)))) or ( mcACEntry=2 AND 23 IN (SELECT OwnerExpandedMskey FROM idmv_owners WHERE EntryMSKEY=E.mcMSKEY))))

',2,4,59,60,4

select @p1

Used and available table space



SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sys.database_files;

Display role/priv hierarcies on versions of IdM 7.2 that has full structure table



select U.mcDisplayName, A.mcDisplayName assignment,L.mcAssignedDirect isDirect,SR.mcParentMskeyassignmentParentMskey,AP.mcDisplayName assignmentParentName,SR.mcRootMskey assignmentRootMskey, AR.mcDisplayNameassignmentRootName

from idmv_entry_simple U

inner join mxi_link L on L.mcThisMSkey = U.mcMSkey andL.mcOtherEntryType in (select ocId from mxi_objectclasses whereocName='MX_PRIVILEGE')

left outer join idmv_entry_simple A ON A.mcMskey = L.mcOtherMSKEY

left outer join mxi_structure_root SR ON SR.mcChildMskey = A.mcMSKEY

left outer join idmv_entry_simple AP ON AP.mcMSKEY = SR.mcParentMskey

left outer join idmv_entry_simple AR ON AR.mcMSKEY = SR.mcRootMskey

where U.mcEntryType='MX_PERSON' -- and U.mcMskeyValue = 'some.user'

order by U.mcMskey,A.mcMskey,ap.mcMSKEY,ar.mcMSKEY desc

Display role/priv hierarcies on versions of IdM 7.2 that don't have full structure table by creating a temporary table



DECLARE @MSKEY int, @MSKEYV VARCHAR(255),@ROOTMSKEY int,@level int,@MAXLEVEL int,@Mylevel int, @Lnum int

SET @MAXLEVEL = 20

-- Not setting below values returns all users.

SET @MSKEY = NULL -- or add user mskey, such as 58

SET @MSKEYV = NULL -- or add user mskeyvlaue, such as 'User.Test.A'

-- Create temporary table

CREATE TABLE #STRUCT (mcChildMskey INT, mcRootMskey INT, mcParentMskey INT, mcEntryType INT, mcLevel INT)

-- Insert first level links

insert into #STRUCT (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)

    select A.mcThisMskey, A.mcOtherMskey, A.mcOtherMskey, A.mcthisentrytype, 1 from mxi_link A with (nolock)

    where not exists (select 1 from mxi_link B with (nolock) where  A.mcOtherMskey = B.mcThisMskey and B.mcLinkType = 1 AND B.mcLinkState = 0)

    and   A.mcLinkType = 1 AND A.mcLinkState = 0

    and   A.mcAttrId in (select attr_id from mxi_attributes with (nolock) where AttrName = 'MXREF_MX_ROLE')

    and   A.mcotherentrytype in (select ocid from mxi_objectclasses with (nolock) where ocName = 'MX_ROLE')

    and   A.mcthisentrytype in (select ocid from mxi_objectclasses with (nolock) where ocName in ('MX_ROLE', 'MX_PRIVILEGE'))

  -- Other level links

  set @Mylevel = 2

  while @Mylevel < @MAXLEVEL

  begin

  insert into #STRUCT (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)

  select A.mcThisMskey, A.mcOtherMskey, A.mcOtherMskey, A.mcthisentrytype, @Mylevel from mxi_link A  with (nolock)

  where A.mcLinkType = 1 and A.mcLinkState = 0

  and   A.mcattrid in (select attr_id from mxi_attributes with (nolock) where AttrName = 'MXREF_MX_ROLE')

  and   A.mcotherentrytype in (select ocid from mxi_objectclasses with (nolock) where ocName = 'MX_ROLE')

  and   A.mcthisentrytype in (select ocid from mxi_objectclasses with (nolock) where ocName in ('MX_ROLE', 'MX_PRIVILEGE'))

  and   A.mcOtherMskey in (select B.mcChildMskey from #STRUCT B with (nolock) where B.mcLevel = @Mylevel - 1)

  and   not exists (select 1 from #STRUCT C with (nolock) where A.mcThisMskey = C.mcChildMskey and A.mcOtherMskey = C.mcRootMskey and A.mcOtherMskey = C.mcParentMskey)

  set @Lnum = @@Rowcount

  -- Build inherit link structure

  insert into #STRUCT (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)

  select distinct A.mcChildMskey, B.mcRootMskey, A.mcParentMskey, A.mcEntryType, 0 from #STRUCT A with (nolock)

  inner join #STRUCT B with (nolock) on A.mcParentMskey = B.mcChildMskey

  where A.mcLevel > 1

  and   not exists (select 1 from #STRUCT C with (nolock) where A.mcChildMskey = C.mcChildMskey and B.mcRootMskey = C.mcRootMskey and A.mcParentMskey = C.mcParentMskey)

  if @Lnum = 0

  begin

  break

  end

  set @Mylevel = @Mylevel + 1

  end

  -- Update sub tree nodes to level 0.

  update #STRUCT set mcLevel = 0 where mcLevel > 1

  select U.mcDisplayName, A.mcDisplayName assignment, L.mcAssignedDirect isDirect,SR.mcParentMskey assignmentParentMskey,AP.mcDisplayName assignmentParentName, SR.mcRootMskey assignmentRootMskey, AR.mcDisplayName assignmentRootName

  from idmv_entry_simple U

  inner join mxi_link L on L.mcThisMSkey = U.mcMSkey and L.mcOtherEntryType in (select ocId from mxi_objectclasses where ocName='MX_PRIVILEGE')

  left outer join idmv_entry_simple A ON A.mcMskey = L.mcOtherMSKEY

  left outer join #STRUCT SR ON SR.mcChildMskey = A.mcMSKEY

  left outer join idmv_entry_simple AP ON AP.mcMSKEY = SR.mcParentMskey

  left outer join idmv_entry_simple AR ON AR.mcMSKEY = SR.mcRootMskey

  where U.mcEntryType='MX_PERSON'

  AND ((@MSKEYV IS NOT NULL AND U.mcMskeyValue = @MSKEYV) OR (@MSKEY IS NOT NULL AND U.mcMskey = @MSKEY) OR (@MSKEY IS NULL AND @MSKEYV IS NULL))

  order by U.mcMskey,A.mcMskey,ap.mcMSKEY,ar.mcMSKEY desc

-- Clean up

DROP TABLE #STRUCT

ORACLE

Measuring execution time of query with milliseconds:



set time on timing on;

select 1 from DUAL;

Use F5 (Run script) to get timing, result example_

      1

Elapsed: 00:00:00.002

List number of sessions per username/host



select s.username as username,

(case when grouping(s.machine) = 1 then '**** All Machines ****' else s.machine end) AS machine,

count(*) as session_count

from v$session s, v$process p

where s.paddr   = p.addr and s.username is not null

group by rollup (s.username, s.machine) order by s.username, s.machine ;

List username, state, host etc for logged in sessions

SELECT username, seconds_in_wait, machine, port, terminal, program, module, service_name FROM v$session WHERE type = 'USER';

Add states and some more stuff:



SELECT sess.machine,sess.process, sess.status,sess.state, sess.username, sess.schemaname,sess.wait_time,to_char(sess.sql_exec_start,'YYYY-MM-DD HH24:MI:SS'), sess.blocking_session,sql.sql_text

FROM v$session sess, v$sql sql

WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER' and schemaname LIKE 'MXMC%' order by status


Listing sessions and blocking sessions



List running queries with basic information:

SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER'






































SELECT sess.username,sess.sid,sess.machine,sess.process,sess.blocking_session,sess.status, sess.username, sess.schemaname,sess.wait_time,sess.sql_exec_start,sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER' and schemaname LIKE 'MXMC%' order by blocking_session,sid

List statements that are blocking others



SELECT sess.username,sess.sid,sess.machine,sess.process,sess.blocking_session,sess.status, sess.username, sess.schemaname,sess.wait_time,sess.sql_exec_start,sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id and sess.sid in (SELECT sess.blocking_session

FROM v$session sess

WHERE sess.blocking_session is not null and sess.type = 'USER' and schemaname LIKE 'MXMC%')






SELECT t.status, t.start_time,sess.username,sess.sid,sess.machine,sess.process,sess.blocking_session,sess.status, sess.username, sess.schemaname,sess.wait_time,sess.sql_exec_start,sql.sql_text

FROM v$session sess, v$sql sql , V$TRANSACTION t WHERE sql.sql_id(+) = sess.sql_id and t.ses_addr = sess.saddr and sess.sid in

(SELECT sess.blocking_session FROM v$session sess WHERE sess.blocking_session is not null and sess.type = 'USER' and schemaname LIKE 'MXMC%')

List running queries with additional information



select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text from v$sqltext_with_newlines t,V$SESSION s

where t.address =s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE' and s.username <> 'SYSTEM' order by s.sid,t.piece

List sessions and blocking sessions, specific schema, other related stuff



SELECT sess.process, sess.status, sess.username, sess.schemaname,sess.wait_time,sess.sql_exec_start,sess.blocking_session,sql.sql_text

FROM v$session sess, v$sql sql

WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER' and schemaname LIKE 'MXMC%' order by status







































select sess.USERNAME, sess.sid, sqlt.sql_id, sqlt.sql_text from v$sqltext_with_newlines sqlt, V$SESSION sess

where sqlt.address = sess.sql_address and sqlt.hash_value = sess.sql_hash_value and sess.status = 'ACTIVE'

and sess.username like 'MVIEWS%' order by sess.sid,sqlt.piece







































set lines 400

column "Time left" format A15

column "Time spent" format A15

column message format a100

column username format a20

select inst_id, sid, serial#, username, context,

trunc(time_remaining / 60,2)||' Minutes' "Time left",

trunc(elapsed_seconds / 60,2)||' Minutes' "Time spent",

round(sofar/totalwork*100,2) "% Completed",

message

from gv$session_longops

where sofar != totalwork

and totalwork != 0

order by 1

This seems broken, but could be useful if checked further



SELECT * FROM (select username,opname,sid,serial#,context,sofar,totalwork ,round(sofar/((totalwork+1)*100),2) "% Complete" from v$session_longops) WHERE "% Complete" != 100;

List queries averaging more than  X ms worktime



set lines 400

SELECT cast(ROUND(A.ELAPSED_TIME / A.EXECUTIONS / 1000000) as number(10)) SQL_AVG,

       cast(A.SQL_ID as varchar(20)) SQL_ID,

       cast(DBMS_LOB.SUBSTR(A.SQL_FULLTEXT,300) as varchar(300)) SQL_TEXT,

       cast(A.EXECUTIONS as number(10)) EXECCOUNT,

        cast(NVL(S.PROGRAM,A.MODULE) as varchar(20)) PNAME,

       cast(NVL(S.USERNAME,A.PARSING_SCHEMA_NAME) as varchar(20)) USERID

  FROM  V$SQLAREA A, V$SESSION S

  WHERE A.SQL_ID = S.SQL_ID(+)

    AND A.EXECUTIONS > 0

    AND ROUND(A.ELAPSED_TIME / A.EXECUTIONS / 1000000) > 10

Profiling procedures

SQL Plus part:



sqlplus system/abcd1234 AS SYSDBA

@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\tracetab.sql

@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmshptab.sql

grant execute on DBMS_HPROF to mxmc_oper

Rest can be done in SQL Dev



CREATE OR REPLACE directory PLSHPROF_DIR as 'C:\temp';

grant READ, WRITE on directory PLSHPROF_DIR to MXMC_OPER;

grant create table to MXMC_OPER;

grant create sequence to MXMC_OPER;

grant unlimited tablespace to MXMC_OPER;

Then profile procedures from SQL Developer or run file:



DECLARE

v_runid NUMBER;

BEGIN

v_runid := DBMS_HPROF.ANALYZE (LOCATION => ‘PROFILE_DATA’,

FILENAME => ‘myteststuff.txt’);

DBMS_OUTPUT.PUT_LINE(‘Run ID: ‘ || v_runid);

END;

More info: http://dbaora.com/plsql-hierarchical-profiler-oracle-database-11g-release-2-11-2/

Undo tablespace and transactions  stuff



SELECT SUM(a.bytes) "UNDO_SIZE"

  FROM v$datafile a, v$tablespace b, dba_tablespaces c

WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#;







































select sum(used_ublk) from v$transaction;







































select * from dba_data_files where tablespace_name='UNDOTBS1';







































select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,

to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention

from v$undostat order by end_time;







































SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,

         TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,

         UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"

         FROM v$UNDOSTAT WHERE rownum <= 144;






































select cast(file_name as varchar2(60)) filename,cast(tablespace_name as varchar2(10)) tblspc,cast(bytes as varchar2(10)) tblsize,

cast(autoextensible as varchar2(5)) isAutoExt, cast(maxbytes as varchar2(12)) maxSize,cast(increment_by as varchar2(6)) autoIncr

FROM DBA_DATA_FILES;

List index states



select index_name,status,num_rows,sample_size,last_analyzed,visibility from all_indexes where owner = 'MXMC_OPER' ORDER BY last_analyzed

Gather statistics for a table



exec dbms_stats.gather_table_stats('MXMC_OPER','mxi_values',no_invalidate=>false);






































begin

  dbms_stats.gather_schema_stats (ownname    => 'mViews_oper', estimate_percent=>dbms_stats.auto_sample_size, cascade=>TRUE);

end;

Index state, size, samplesize …



select index_name,status,num_rows,sample_size,last_analyzed,visibility

from all_indexes where owner = 'MXMC_OPER' --and status='UNUSABLE';

Execution Plan Stuff

List plans for given schema



SELECT * FROM

(SELECT  sql_fulltext,sql_id,child_number,disk_reads,  executions,first_load_time,last_load_time

FROM    v$sql where parsing_schema_name = 'MXMC_OPER'

ORDER BY elapsed_time DESC)

WHERE ROWNUM < 40

Plan for adhoc statement:



EXPLAIN PLAN for

select distinct mskey from (

SELECT mskey from mxi_values VALS where  SEARCHVALUE='MX_PRIVILEGE' AND

ATTR_ID=1  AND disabled=0 AND mskey in

(select mskey from mxi_values VALS where searchvalue  like

'%uncritical%EEP%' AND Attr_id IN (2, 4, 18))

) where rownum < 200;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Plan for adhoc statement with output formatting



EXPLAIN PLAN FOR

select distinct(mskey) from mxmc_oper.mxiv_values where searchvalue = 'POSITION ID' and attr_id in (select attr_id from mxi_attributes where attrname = 'Z_MX_ROLE_TYPE');

SELECT *

FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'BASIC'));

Explain plan setting statement id and getting plan:



EXPLAIN PLAN

SET STATEMENT_ID = 'SAPTEST_1' FOR

select * from (

  SELECT mskey from idmv_jmx_entries VALS where mskey in

    (select distinct mskey from mxi_values where SearchValue ='LAMBERT' AND Attr_ID=60)

  AND mskey in

    (select distinct mskey from mxi_values where SearchValue ='BOSKAMP' AND Attr_ID=59)

  AND disabled=0

);

SET PAGESIZE= 1000;

SET LINESIZE= 500;

SET LONG=100000;

COL xplan format a100;

SELECT dbms_xplan.build_plan_xml(statement_id => 'SAPTEST_1') AS XPLAN FROM dual;

Find the plan for a statement:



SELECT

  --sql_fulltext,sql_id,cpu_time,elapsed_time,user_io_wait_time,disk_reads,executions

  *

FROM    v$sql where sql_fulltext like '%select distinct mskey from (SELECT mskey from mxiv_values VALS where%'

Get plan by planid:



select DBMS_SQLTUNE.REPORT_SQL_MONITOR(type=>'HTML',report_level=>'ALL',sql_id=>'1zf0h9sw688bx') as report FROM dual;

History of executions of plan



select * from DBA_HIST_SQLSTAT where sql_id = 'a2zqghuq57f2y'

Executions for a timeframe



select *

from   dba_hist_sqlstat t, dba_hist_snapshot s

where  t.snap_id = s.snap_id

and    t.dbid = s.dbid

and    t.instance_number = s.instance_number

and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate)







































select *

from   dba_hist_sqlstat t, dba_hist_snapshot s

where  t.snap_id = s.snap_id

and    t.dbid = s.dbid

and    t.instance_number = s.instance_number

and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate)






































select cast(st.sql_text as varchar(400)), sum(t.elapsed_time_delta/1000000)/sum(t.executions_delta)

from   dba_hist_sqlstat t, dba_hist_snapshot s, dba_hist_sqltext st

where  t.snap_id = s.snap_id and st.sql_id = t.sql_id

and    t.dbid = s.dbid

and    t.instance_number = s.instance_number

and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats

group  by cast(st.sql_text as varchar(400))

Empty/clear buffer, pools, execution plans



ALTER SYSTEM FLUSH BUFFER_CACHE;

alter system flush shared_pool;

Search using locally defined var



DEFINE vSearchString = "'%QUALITY%'";

select distinct mskey from (SELECT mskey from mxi_values VALS where SEARCHVALUE='MX_PERSON' AND ATTR_ID=1  AND disabled=0

AND mskey in (select mskey from mxi_values VALS where searchvalue like &&vSearchString AND Attr_id IN (2,4,5))) where rownum

< 200;







































var vSearchString varchar2(30);

exec :vSearchString := '%QUALITY%STA%';

--print vSearchString;

select distinct mskey from (SELECT mskey from mxi_values VALS where SEARCHVALUE='MX_PERSON' AND ATTR_ID=1  AND disabled=0

AND mskey in (select mskey from mxi_values VALS where searchvalue like :vSearchString AND Attr_id IN (2,4,5))) where rownum

< 200;
7 Comments
Labels in this area
Popular Blog Posts