ORACLEのINDEX
SELECT I.TABLE_OWNER
,I.TABLE_NAME
,I.INDEX_NAME
,I.COLUMN_POSITION
,I.COLUMN_NAME
,CASE NVL(C.DATA_TYPE,'FUNCTION')
WHEN 'VARCHAR2' THEN C.DATA_TYPE||'('||C.CHAR_LENGTH||' '||decode(C.CHAR_USED,'B','BYTE','C','CHAR','?')||')'
WHEN 'CHAR' THEN C.DATA_TYPE||'('||C.CHAR_LENGTH||' '||decode(C.CHAR_USED,'B','BYTE','C','CHAR','?')||')'
WHEN 'NVARCHAR2' THEN C.DATA_TYPE||'('||C.CHAR_LENGTH||' '||decode(C.CHAR_USED,'B','BYTE','C','CHAR','?')||')'
WHEN 'NCHAR' THEN C.DATA_TYPE||'('||C.CHAR_LENGTH||' '||decode(C.CHAR_USED,'B','BYTE','C','CHAR','?')||')'
WHEN 'NUMBER' THEN NVL2(C.DATA_PRECISION,C.DATA_TYPE||'('||C.DATA_PRECISION||','||C.DATA_SCALE||')',C.DATA_TYPE)
WHEN 'BLOB' THEN C.DATA_TYPE||'('||C.DATA_LENGTH||')'
WHEN 'CLOB' THEN C.DATA_TYPE||'('||C.DATA_LENGTH||')'
WHEN 'LOB' THEN C.DATA_TYPE||'('||C.DATA_LENGTH||')'
WHEN 'RAW' THEN C.DATA_TYPE||'('||C.DATA_LENGTH||')'
WHEN 'FUNCTION' THEN 'FUNCTION'
ELSE C.DATA_TYPE
END AS "DATA_TYPE"
,CASE C.NULLABLE
WHEN 'Y' THEN 'NULL'
WHEN 'N' THEN 'NOT NULL'
ELSE ' '
END AS "NULL"
,NVL(C.DATA_LENGTH,0) AS "DATA_BYTES"
,EX.COLUMN_EXPRESSION
FROM DBA_IND_COLUMNS I
LEFT OUTER JOIN DBA_TAB_COLUMNS C
ON( I.TABLE_OWNER = C.OWNER
AND I.TABLE_NAME = C.TABLE_NAME
AND I.COLUMN_NAME = C.COLUMN_NAME )
LEFT OUTER JOIN DBA_IND_EXPRESSIONS EX
ON( I.TABLE_OWNER = EX.TABLE_OWNER
AND I.TABLE_NAME = EX.TABLE_NAME
AND I.INDEX_NAME = EX.INDEX_NAME
AND I.COLUMN_POSITION = EX.COLUMN_POSITION )
WHERE I.TABLE_OWNER = 'OWNER'
AND I.TABLE_NAME = 'TAB01'
-- WHERE I.TABLE_OWNER = 'OWNER2'
-- AND I.TABLE_NAME = 'TAB02'
ORDER BY I.TABLE_OWNER,I.TABLE_NAME,I.INDEX_NAME,I.COLUMN_POSITION
■
SELECT
d.*
FROM
(
SELECT
d.*,
ROWNUM row#
FROM
(
SELECT
d.*
FROM
(
SELECT
sql_text AS sql,
s.cpu_time / 1000 AS cpu_mseconds,
CASE
WHEN s.cpu_time < 1000 THEN
'< 1 ms'
WHEN s.cpu_time < 1000000 THEN
TO_CHAR(round(s.cpu_time /1000,1))
|| ' ms'
WHEN s.cpu_time < 60000000 THEN
TO_CHAR(round(s.cpu_time / 1000000,1))
|| ' s'
ELSE
TO_CHAR(round(s.cpu_time / 60000000, 1))
|| ' m'
END AS cpu_seconds_form,
DECODE(l.max_cpu_time, 0, 0, s.cpu_time / l.max_cpu_time) AS cpu_seconds_prop,
s.elapsed_time / 1000 AS elapsed_mseconds,
CASE
WHEN s.elapsed_time < 1000 THEN
'< 1 ms'
WHEN s.elapsed_time < 1000000 THEN
TO_CHAR(round(s.elapsed_time / 1000, 1))
|| ' ms'
WHEN s.elapsed_time < 60000000 THEN
TO_CHAR(round(s.elapsed_time / 1000000, 1))
|| ' s'
ELSE
TO_CHAR(round(s.elapsed_time / 60000000, 1))
|| ' m'
END AS elapsed_seconds_form,
DECODE(l.max_elapsed_time, 0, 0, s.elapsed_time / l.max_elapsed_time) AS elapsed_seconds_prop,
s.disk_reads AS disk_reads,
CASE
WHEN s.disk_reads < 1000 THEN
TO_CHAR(s.disk_reads)
WHEN s.disk_reads < 1000000 THEN
TO_CHAR(round(s.disk_reads / 1000, 1))
|| 'K'
WHEN s.disk_reads < 1000000000 THEN
TO_CHAR(round(s.disk_reads / 1000000, 1))
|| 'M'
ELSE
TO_CHAR(round(s.disk_reads / 1000000000, 1))
|| 'G'
END AS disk_reads_form,
DECODE(l.max_disk_reads, 0, 0, s.disk_reads / l.max_disk_reads) AS disk_reads_prop,
s.buffer_gets AS buffer_gets,
CASE
WHEN s.buffer_gets < 1000 THEN
TO_CHAR(s.buffer_gets)
WHEN s.buffer_gets < 1000000 THEN
TO_CHAR(round(s.buffer_gets / 1000, 1))
|| 'K'
WHEN s.buffer_gets < 1000000000 THEN
TO_CHAR(round(s.buffer_gets / 1000000, 1))
|| 'M'
ELSE
TO_CHAR(round(s.buffer_gets / 1000000000, 1))
|| 'G'
END AS buffer_gets_form,
DECODE(l.max_buffer_gets, 0, 0, s.buffer_gets / l.max_buffer_gets) AS buffer_gets_prop,
s.executions AS executions,
CASE
WHEN s.executions < 1000 THEN
TO_CHAR(s.executions)
WHEN s.executions < 1000000 THEN
TO_CHAR(round(s.executions / 1000, 1))
|| 'K'
WHEN s.executions < 1000000000 THEN
TO_CHAR(round(s.executions / 1000000, 1))
|| 'M'
ELSE
TO_CHAR(round(s.executions / 1000000000, 1))
|| 'G'
END AS executions_form,
DECODE(l.max_executions, 0, 0, s.executions / l.max_executions) AS executions_prop,
DECODE(s.module, NULL, ' ', s.module) AS module,
s.last_active_time AS last_active_time,
DECODE(s.last_active_time, NULL, ' ', TO_CHAR(s.last_active_time, 'DD-Mon-YYYY HH24:MI:SS')) AS last_active_time_form
,
s.sql_id AS sql_id,
s.child_number AS child_number,
s.inst_id AS inst_id
FROM
(
SELECT * FROM gv$sql
where parsing_schema_name in ('REFNAVI','PRONAVI','KVRG00000001')
) s,
(
SELECT
MAX(cpu_time) AS max_cpu_time,
MAX(elapsed_time) AS max_elapsed_time,
MAX(disk_reads) AS max_disk_reads,
MAX(buffer_gets) AS max_buffer_gets,
MAX(executions) AS max_executions
FROM
gv$sql
) l
) d
ORDER BY
cpu_mseconds DESC,
sql,
disk_reads DESC,
buffer_gets DESC,
executions DESC,
elapsed_mseconds DESC,
inst_id DESC,
module DESC,
last_active_time DESC
) d
) d
WHERE 1 = 1
-- and row# >= :minrowno AND row# <= :maxrowno
ORA-32773 でALTER TABLESPACEが失敗する
10gR2で
ALTER TABLESPACE USERS AUTOEXTEND OFF ;
と実行すると
行1でエラーが発生しました。:
ORA-32773: SMALLFILE表領域USERSに対する操作はサポートされていません
となりました。
10gからSMALLFILE/BIGFILEという考え方となり
従来の表領域はSMALLFILEとなります。
このケースでは
alter database datafile 'E:\oradata\ORCL\USERS.DBF' autoextend off ;
データベースが変更されました。
とするとOK
KERNRATE
KERNRATE - Version: 5.2.3790.1101
KERNRATE [-l] [-lx] [-r] [-m] [-p ProcessId] [-z ModuleName] [-j SymbolPath] [-c RateInMsec] [-s Seconds] [-i [SrcShortName] Rate]
[-n ProcessName] [-w]
-a Do a combined Kernel and User mode profile
-av Do a combined Kernel and User mode profile and get task list and system threads info
-b BucketSize Specify profiling bucket size (default = 16 bytes, must be a power of 2)
-c RateInMsec Change source every N milliseconds (default 1000ms). Optional. By default all sources will be profiled simultaneously
-d Generate output rounding buckets up and down
-e Exclude system-wide and process specific general information (context switches, memory usage, etc.)
-f Process the collected data at high priority (useful on busy systems if the overhead is not an issue)
-g Rate Get interesting processor-counters statistics (Rate optional in events/hit), output not guarantied
-i SrcShortName Rate Specify interrupt interval rate (in events/hit)for the source specified by its ShortName, see notes below
-j SymbolPath Prepend SymbolPath to the default imagehlp search path
-k MinHitCount Limit the output to modules that have at least MinHitCount hits
-l List the default interval rates for supported sources
-lx List the default interval rates for supported sources and then exit
-m 0xN Generate per-CPU profiles on multi-processor machines, Hex CPU affinity mask optional for profiling on selected processors
-n ProcessName Monitor process by its name (default limited to first 8 by the same name), multiple usage allowed
-nv# N ProcessName Monitor up to N processes by the same name, v will print thread info and list of all running processes (optional)
-nd ProcessName Monitor process by name as debugger - stop all profiling on process exit and process data. See comments below
-ns ProcessName Monitor process by name, suspend its main execution thread after profiling done to prevent early exit, see comments below
-o ProcessName {CmdLine}Create and monitor ProcessName (path OK), Command Line parameters optional and must be enclosed in curly brackets
-ov# N ProcessName { } Create N instances of ProcessName, v will print thread info and list of running processes (optional), {command line} optional
-od ProcessName { } Create and monitor ProcessName as a debugger, same as in '-nd', see comment below
-odk ProcessName { } Create and monitor ProcessName as a debugger, kill the debuggee upon Kernrate exit
-os ProcessName { } Create and monitor ProcessName, suspend its main execution thread after profiling done, same as in '-ns', see comment below
-oi ProcessName { } Create ProcessName, wait for input idle (up to 10 seconds) before continuing (not affecting console processes)
-pv ProcessId Monitor process by its ProcessId, multiple usage allowed - see notes below, v (optional) same as in '-nv'
-pd ProcessId Monitor process by its ProcessId as a debugger, same as in '-nd', see comment below
-ps ProcessId Monitor process by its ProcessId, suspend its main execution thread after profiling done, same as in '-ns', see comment below
-q Synchronize kernrate with any monitored process exit (stop profiling immediately and process data)
-r Raw data from zoomed modules
-rd Raw data from zoomed modules with disassembly
-s Seconds Stop collecting data after N seconds
-t Display process list + CPU usage summary for the profiling period
-ts Display process list + CPU usage summary for the profiling period + running services info
-ts MaxTasks As above + Change the maximum no. of processes allowed in Kernrate's list to MaxTasks (default: 256)
-u Present symbols in undecorated form
-w Wait for the user to press ENTER before starting to collect profile data
-w Seconds Wait for N seconds before starting to collect profile data (default is no wait)
-wp Wait for the user to press enter to indicate that created processes (see -0 option) are settled (idle)
-wp Seconds Wait for N seconds to allow created processes settle (go idle), default is 2 seconds, (see the -o option)
-wx Wait for the user to press ENTER before exiting Kernrate. Useful for debug process mode or Kernrate created console processes
-wx Seconds Wait for N seconds before exiting Kernrate
-x Get both system and user-process locks information
-x# count Get both system and user-process locks information for (optional) contention >= count [def. 1000]
-xk# count Get only system lock information for (optional) contention >= count [def. 1000]
-xu# count Get only user-process lock information for (optional) contention >= count [def. 1000]
-yr filename Create a raw-data output file, tab delimited (filename optional)
-z module Name of module to zoom on (no extension needed by default) such as ntdll, multiple usage allowed, see notes below
-v Verbose Verbose Printout, if specified with no level the default is Imagehlp symbol information
-v [VerboseLevels] Verbose output where VerboseLevels:
- 0x0 None
- 0x1 Displays ImageHlp Operations
- 0x2 Displays Profiling Operations, Bucket Sharing Information Totals and Profile Interrupt Based CPU Usage
- 0x4 Displays Internals Operations
- 0x8 Displays Modules Operations
- 0x10 Displays Load/Unload Debug Events for Modules (Debug Mode Only)
- 0x20 Displays Detailed Sharing Information for Each Bucket and Bucket Sharing Information Totals
- 0x40 Displays Exception Debug Events (Debug Mode Only)
- Default value: 0x0
These verbose levels can be OR'ed.
Multi-Processes are allowed (each process ID needs to be preceded by -P except for the system process)
Typical multi-process profiling command line should look like:
kernrate .... -a -z ntoskrnl -z ntdll -z kernel32 -p 1234 -z w3svc -z iisrtl -p 4321 -z comdlg32 -z msvcrt ...
The first group of -z denotes either kernel modules and-or modules common across processes
The other -z groups are process specific and should always follow the appropriate -p xxx
The -z option requires to add the extension (.dll etc.) only if two or more binaries carry the same name and differ only by the extension
The '-g' option will attempt to turn on multiple sources. One source at a time profiling mode will automatically be forced
The '-i' option can be followed by only a source name (system default interrupt interval rate will then be assumed)
A '-i' option followed by a rate amount (no profile source name) will change the interval rate for the default source (time)
Profiling of the default source (Time) can be disabled by setting its profile interval to zero
With the '-n' option, use the common modules -Z option if you expect more than one process with the same name
The '-c' option will cause elapsed time to be devided equally between the sources and the monitored processes
The '-o' option supports redirection of input/output/error streams within the curly brackets. Each redirection character must be escaped with a '^' character
The '-nd' '-ns' '-od' '-os' options allow specifying 'v', '#' or any other valid sub-options, e.g. '-nsv# N ProcessName'
The '-pd' '-ps' options allow specifying the 'v' sub-option, e.g. '-psv ProcessId'
If debugging mode is selected ('-nd', '-od' or '-pd') the debuggee will not get killed upon Kernrate exit on WinXP and above, see also the '-wx' option
If suspend mode is selected ('-ns', '-os' or '-ps') Kernrate will attempt to resume the suspended thread after post processing is done
There is no guaranty as to the stability or behavior of the monitored process after that