Profile: /sample/dbm011_ora

This profile report is an aggregation of an Oracle extended SQL trace file that accounts for 8.244514 seconds. If this is longer than the duration of the experience you want to investigate, then use mrcrop to crop out the unwanted calls that have no bearing upon your analysis. For more information, consult the following resources:

If your trace file time scope is correct, then you can think of this report as an invoice explaining how your time was spent, with call counts and durations acting as quantities and prices. It will help you answer the four most important questions about an application’s performance:

The advisory paragraphs that accompany the data in this document are meant to enhance your interpretation of the data. In cases where they run afoul of sound judgment, please open a case with Method R Support .

1. Experience-Level Profiles

This section shows the total experience duration decomposed along different dimensions.

1.1. Profile by Subroutine

Your experience duration was dominated by “CPU: FETCH dbcalls” calls:

“CPU: FETCH dbcalls” consumed 84.9% of your experience duration. “CPU: FETCH dbcalls” are high-productivity calls that you do want dominating your duration. However, you do not want your application doing any more “CPU: FETCH dbcalls” work than necessary. When you are investigating a time-consuming statement, don’t be afraid to ask whether the statement is really necessary—sometimes an entire statement can be eliminated with no loss of functionality to the business. Investigate whether you can reduce your call count by using array processing, and whether each call can be made more efficient by eliminating unnecessary LIOs.
LIO count is one good measure of a SQL statement’s efficiency. An LIO is simply an access of a buffer in the database buffer cache. But an LIO is much more than just a memory access. It is a latch-serialized operation that consumes CPU. By reducing a statement’s LIO count, you reduce not just the number of memory accesses your code executes, but you also reduce the number of CPU cycles your Oracle session consumes, the number of blocks it reads from disk or other secondary storage, the number of latches it has to acquire, the number of global cache coherency instructions it has to execute, and more.

	Subroutine	Duration		Calls	Duration per call (seconds)
	Subroutine	seconds	% R	Calls	mean	min	max	skew
1	CPU: FETCH dbcalls	6.997937	84.9%	195,076	0.000036	·	5.038223	▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▂▁▂█▁▁▁
2	cell single block physical read	1.418016	17.2%	5,512	0.000257	0.000206	0.000534	▁▁▁█▁▁▁▁▁▁▁
3	gc cr disk read	0.161261	2.0%	2,747	0.000059	0.000018	0.000208	▁▁█▁▁▁▁▁▁▁▁
4	unaccounted-for between calls	0.006447	0.1%	12	0.000537	0.000024	0.005797	▁▁▁▁▁▁▁▁▁▁▁▁▁▂▁█▁▁▁▁▁▁
5	10 other subroutines	−0.339147	−4.1%	195,137	−0.000002	−0.363766	0.001370	▁▁▁▁▄▁▁█▁▁▁▁█▁▁▁▁▁▁▁▁▁
6	Total (14)	8.244514	100.0%	398,484	0.000021	−0.363766	5.038223

1.2. Profile by Database Call

It is good for your duration to be dominated by “FETCH” calls, but it is important for their work to be efficient. You want your application making as few “FETCH” calls as possible, and you want each call executing as few LIOs as possible.

	Database call	Duration		Contribution (seconds)		Calls	Rows	LIOs			PIOs (r)	Library cache misses
	Database call	seconds	% R	CPU	non-CPU	Calls	Rows	total	cr	cu	PIOs (r)	Library cache misses
1	FETCH	8.234913	99.9%	6.997937	1.236976	195,076	195,069	499,484	499,015	469	5,512	·
2	between dbcalls	0.007212	0.1%	·	0.007212	16	·	·	·	·	·	·
3	EXEC	0.002071	·	·	0.002071	8	1	·	·	·	·	·
4	PARSE	0.000207	·	·	0.000207	7	·	·	·	·	·	·
5	CLOSE	0.000094	·	·	0.000094	8	·	·	·	·	·	·
6	synthetic	0.000017	·	·	0.000017	1	·	·	·	·	·	·
7	STAT	·	·	·	·	3	·	·	·	·	·	·
8	COMMIT R-ONLY	·	·	·	·	1	·	·	·	·	·	·
9	Total (8)	8.244514	100.0%	6.997937	1.246577	195,120	195,070	499,484	499,015	469	5,512	·
10	Total per execution	1.030564	12.5%	0.874742	0.155822	24,390.00	24,383.75	62,435.50	62,376.88	58.62	689.00	·
11	Total per fetch	0.000042	·	0.000036	0.000006	1.00	1.00	2.56	2.56	·	0.03	·
12	Total per row	0.000042	·	0.000036	0.000006	1.00	1.00	2.56	2.56	·	0.03	·

1.3. Profile by Statement

This experience executed five statements. Just one statement consumed 87.2% of the total experience duration. mrprof aggregates into a single statement all similar but unshareable statements that could have used placeholders in their SQL text but did not.

	Statement	Duration		Executions	Rows	LIOs (cr+cu)	PIOs (r)	Distinct plans	Distinct cursors	Distinct texts	Statement size		Optimizer goal
	Statement	seconds	% R	Executions	Rows	LIOs (cr+cu)	PIOs (r)	Distinct plans	Distinct cursors	Distinct texts	lines	bytes	Optimizer goal
1	[fpmabaybx9hgf] select owner, segment_name from dba_extents wher…	7.188449	87.2%	1	1	282,056	5,512	·	1	1	2	119	ALL_ROWS
2	[1n1bm7xa28vtq] select file#, block#, type#, nvl(spare1,0), hwmi…	1.048032	12.7%	5	195,063	217,419	·	·	1	1	3	106	CHOOSE
3	[PROF000000006] r-only commit follows fpmabaybx9hgf	0.006235	0.1%	·	·	·	·	·	1	·	·	·
4	[3u1qfzgtyz494] BEGIN :file_id := 1; :block_id := 45888; dbms_mo…	0.001563	·	1	1	·	·	·	1	1	1	119	ALL_ROWS
5	[3fkaxqzh9g4vm] select ts#, flags from ts$ where bitmapped <> 0 …	0.000235	·	1	5	9	·	·	1	1	6	103	CHOOSE
6	Total (5)	8.244514	100.0%	8	195,070	499,484	5,512	·	5	4	12	447	ALL_ROWS, CHOOSE

1.4. Profile by Cursor

This application code path executed for this experience organized its SQL and PL/SQL statements into five cursors. The following grove (multi-root tree) depicts the parent-child relationships among these cursors, listing the most time-consuming cursors in a sibling group first. The Profile by Statement section aggregates all the appearances of each statement into one row.

	Cursor statement	Including descendants						Excluding descendants
		Duration		Executions	Rows	LIOs (cr+cu)	PIOs (r)	Duration		Executions	Rows	LIOs (cr+cu)	PIOs (r)
		seconds	% R	Executions	Rows	LIOs (cr+cu)	PIOs (r)	seconds	% R	Executions	Rows	LIOs (cr+cu)	PIOs (r)
1	[fpmabaybx9hgf] select owner, segment_name from dba_extents wher…	8.236716	99.9%	7	195,069	499,484	5,512	7.188449	87.2%	1	1	282,056	5,512
2	1[1n1bm7xa28vtq] select file#, block#, type#, nvl(spare1,0), hwmi…	1.048032	12.7%	5	195,063	217,419	·	1.048032	12.7%	5	195,063	217,419	·
3	1[3fkaxqzh9g4vm] select ts#, flags from ts$ where bitmapped <> 0 …	0.000235	·	1	5	9	·	0.000235	·	1	5	9	·
4	[PROF000000006] r-only commit follows fpmabaybx9hgf	0.006235	0.1%	·	·	·	·	0.006235	0.1%	·	·	·	·
5	[3u1qfzgtyz494] BEGIN :file_id := 1; :block_id := 45888; dbms_mo…	0.001563	·	1	1	·	·	0.001563	·	1	1	·	·
6	Total (5)							8.244514	100.0%	8	195,070	499,484	5,512

2. Skew Histograms

This section reveals the detail of each relevant skew pictogram from the Profile by Subroutine section above.

2.1. Skew Data for CPU: FETCH dbcalls

This call’s minimum duration was 0.000000 seconds; its maximum duration was 5.038223 seconds; and its average duration was 0.000036 seconds. The following table shows the distribution detail for your call durations. From this detail, you can estimate how much time you will save if you can eliminate certain calls.

Skew pictogram:

▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▂▁▂█▁▁▁

	Range {min ≤ e < max} (seconds)		Duration			Calls	Duration per call (seconds)
	min	max	seconds	%	% R	Calls	mean	min	max
1	−∞	−1,000.000000	·	·	·	·
2	−1,000.000000	−100.000000	·	·	·	·
3	−100.000000	−10.000000	·	·	·	·
4	−10.000000	−1.000000	·	·	·	·
5	−1.000000	−0.100000	·	·	·	·
6	−0.100000	−0.010000	·	·	·	·
7	−0.010000	−0.001000	·	·	·	·
8	−0.001000	−0.000100	·	·	·	·
9	−0.000100	−0.000010	·	·	·	·
10	−0.000010	−0.000001	·	·	·	·
11	−0.000001	0.000000	·	·	·	·
12	0.000000	0.000001	·	·	·	194,143	·	·	·
13	0.000001	0.000010	·	·	·	·
14	0.000010	0.000100	·	·	·	·
15	0.000100	0.001000	0.127872	1.8%	1.6%	128	0.000999	0.000999	0.000999
16	0.001000	0.010000	0.799999	11.4%	9.7%	799	0.001001	0.001000	0.001999
17	0.010000	0.100000	0.099985	1.4%	1.2%	4	0.024996	0.024996	0.024997
18	0.100000	1.000000	0.931858	13.3%	11.3%	1	0.931858	0.931858	0.931858
19	1.000000	10.000000	5.038223	72.0%	61.1%	1	5.038223	5.038223	5.038223
20	10.000000	100.000000	·	·	·	·
21	100.000000	1,000.000000	·	·	·	·
22	1,000.000000	∞	·	·	·	·
23	Total (22)		6.997937	100.0%	84.9%	195,076	0.000036	·	5.038223

2.2. Skew Data for cell single block physical read

This call’s minimum duration was 0.000206 seconds; its maximum duration was 0.000534 seconds; and its average duration was 0.000257 seconds. The following table shows the distribution detail for your call durations. From this detail, you can estimate how much time you will save if you can eliminate certain calls.

Skew pictogram:

▁▁▁█▁▁▁▁▁▁▁

	Range {min ≤ e < max} (seconds)		Duration			Calls	Duration per call (seconds)
	min	max	seconds	%	% R	Calls	mean	min	max
1	0.000000	0.000001	·	·	·	·
2	0.000001	0.000010	·	·	·	·
3	0.000010	0.000100	·	·	·	·
4	0.000100	0.001000	1.418016	100.0%	17.2%	5,512	0.000257	0.000206	0.000534
5	0.001000	0.010000	·	·	·	·
6	0.010000	0.100000	·	·	·	·
7	0.100000	1.000000	·	·	·	·
8	1.000000	10.000000	·	·	·	·
9	10.000000	100.000000	·	·	·	·
10	100.000000	1,000.000000	·	·	·	·
11	1,000.000000	∞	·	·	·	·
12	Total (11)		1.418016	100.0%	17.2%	5,512	0.000257	0.000206	0.000534

3. Contribution to Subroutine by Statement

This section shows which SQL or PL/SQL statements are responsible for each subroutine that is a dominant contributor to overall experience duration.

3.1. Statement Contributors to CPU: FETCH dbcalls

The following table lists the dominant SQL statement contributors to the CPU: FETCH dbcalls subroutine call type, which contributed 84.9% of the total experience duration.

	Statement	Duration			Calls	Duration per call (seconds)
	Statement	seconds	%	% R	Calls	mean	min	max
1	[fpmabaybx9hgf] select owner, segment_name from dba_extents wher…	5.970081	85.3%	72.4%	2	2.985040	0.931858	5.038223
2	[1n1bm7xa28vtq] select file#, block#, type#, nvl(spare1,0), hwmi…	1.027856	14.7%	12.5%	195,068	0.000005	·	0.024997
3	[3fkaxqzh9g4vm] select ts#, flags from ts$ where bitmapped <> 0 …	·	·	·	6	·	·	·
4	Total (3)	6.997937	100.0%	84.9%	195,076

3.2. Statement Contributors to cell single block physical read

The following table lists the dominant SQL statement contributors to the cell single block physical read subroutine call type, which contributed 17.2% of the total experience duration.

	Statement	Duration			Calls	Duration per call (seconds)
	Statement	seconds	%	% R	Calls	mean	min	max
1	[fpmabaybx9hgf] select owner, segment_name from dba_extents wher…	1.418016	100.0%	17.2%	5,512	0.000257	0.000206	0.000534
2	Total (1)	1.418016	100.0%	17.2%	5,512

4. Statement Detail

This section contains detail for each SQL or PL/SQL statement that is a dominant contributor to overall experience duration, ranked in descending order of contribution to experience duration.

4.1. Statement Report for SQL ID fpmabaybx9hgf

Statement fpmabaybx9hgf “select owner, segment_name from dba_extents wher…” was the most time-consuming statement of the overall 8.244514-second experience.

Its mrprof shared SQL ID is 2OT3J6uRiaK.

4.1.1. Profile by Statement

Statement fpmabaybx9hgf consumed 7.188449 seconds (87.2%) of the total experience duration.

	Statement	Duration			Executions	Rows	LIOs (cr+cu)	PIOs (r)	Distinct plans	Distinct cursors	Distinct texts	Statement size		Optimizer goal
	Statement	seconds	%	% R	Executions	Rows	LIOs (cr+cu)	PIOs (r)	Distinct plans	Distinct cursors	Distinct texts	lines	bytes	Optimizer goal
1	[fpmabaybx9hgf] select owner, segment_name from dba_extents wher…	7.188449	100.0%	87.2%	1	1	282,056	5,512	·	1	1	2	119	ALL_ROWS
2	Total (1)	7.188449	100.0%	87.2%	1	1	282,056	5,512	·	1	1	2	119	ALL_ROWS

4.1.2. Profile by Caller

Statement fpmabaybx9hgf was executed from only one place in your application source code.

	Context	Duration			Executions	Rows	LIOs (cr+cu)	PIOs (r)
	Context	seconds	%	% R	Executions	Rows	LIOs (cr+cu)	PIOs (r)
1	Root cursor	7.188449	100.0%	87.2%	1	1	282,056	5,512
2	Total (1)	7.188449	100.0%	87.2%	1	1	282,056	5,512

4.1.3. Profile by Subroutine

Statement fpmabaybx9hgf duration was dominated by “CPU: FETCH dbcalls” calls:

“CPU: FETCH dbcalls” consumed 83.1% of your experience duration. “CPU: FETCH dbcalls” are high-productivity calls that you do want dominating your duration. However, you do not want your application doing any more “CPU: FETCH dbcalls” work than necessary. When you are investigating a time-consuming statement, don’t be afraid to ask whether the statement is really necessary—sometimes an entire statement can be eliminated with no loss of functionality to the business. Investigate whether you can reduce your call count by using array processing, and whether each call can be made more efficient by eliminating unnecessary LIOs.
LIO count is one good measure of a SQL statement’s efficiency. An LIO is simply an access of a buffer in the database buffer cache. But an LIO is much more than just a memory access. It is a latch-serialized operation that consumes CPU. By reducing a statement’s LIO count, you reduce not just the number of memory accesses your code executes, but you also reduce the number of CPU cycles your Oracle session consumes, the number of blocks it reads from disk or other secondary storage, the number of latches it has to acquire, the number of global cache coherency instructions it has to execute, and more.

	Subroutine	Duration			Calls	Duration per call (seconds)
	Subroutine	seconds	%	% R	Calls	mean	min	max	skew
1	CPU: FETCH dbcalls	5.970081	83.1%	72.4%	2	2.985040	0.931858	5.038223	▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▂█▁▁▁
2	cell single block physical read	1.418016	19.7%	17.2%	5,512	0.000257	0.000206	0.000534	▁▁▁█▁▁▁▁▁▁▁
3	gc cr disk read	0.161261	2.2%	2.0%	2,747	0.000059	0.000018	0.000208	▁▁█▁▁▁▁▁▁▁▁
4	unaccounted-for between calls	0.000440	·	·	6	0.000073	0.000039	0.000186	▁▁▁▁▁▁▁▁▁▁▁▁▁█▆▁▁▁▁▁▁▁
5	8 other subroutines	−0.361349	−5.0%	−4.4%	15	−0.024090	−0.363766	0.001370	▁▁▁▁█▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁
6	Total (12)	7.188449	100.0%	87.2%	8,282	0.000868	−0.363766	5.038223

4.1.4. Profile by Database Call

Statement fpmabaybx9hgf touched the database buffer cache 282,056 times but processed only one row. The statement is efficient only if it joins 28,206 or more base tables, or if it is an aggregation.

To have excellent performance and scalability, it is vital to eliminate unnecessary database calls. Each database call of course incurs the CPU cost of executing the call, but additionally, each call executed by a client program increases your network I/O call count and the unaccounted-for time your application spends both within and between calls. Opportunities to eliminate database calls include:

It should be possible to return many rows per “FETCH” call, but this application returns an average of only 0.50 rows per “FETCH” call.

The statement obtained 5,512 Oracle blocks from OS read calls, which means that your kernel process found the block it needed in the database buffer cache on 98.0% of LIOs.

Your statement duration was dominated by one type of database call:

“FETCH” calls consumed 100.0% of the statement duration. It may be appropriate for these calls to dominate an experience duration. However, you can reduce the duration of this experience if you can reduce the call count, or if you can reduce the amount of work done by your most expensive “FETCH” calls.

	Database call	Duration			Contribution (seconds)		Calls	Rows	LIOs			PIOs (r)	Library cache misses
	Database call	seconds	%	% R	CPU	non-CPU	Calls	Rows	total	cr	cu	PIOs (r)	Library cache misses
1	FETCH	7.187342	100.0%	87.2%	5.970081	1.217261	2	1	282,056	281,587	469	5,512	·
2	between dbcalls	0.000723	·	·	·	0.000723	7	·	·	·	·	·	·
3	EXEC	0.000336	·	·	·	0.000336	1	·	·	·	·	·	·
4	PARSE	0.000025	·	·	·	0.000025	1	·	·	·	·	·	·
5	CLOSE	0.000023	·	·	·	0.000023	1	·	·	·	·	·	·
6	STAT	·	·	·	·	·	1	·	·	·	·	·	·
7	Total (6)	7.188449	100.0%	87.2%	5.970081	1.218368	13	1	282,056	281,587	469	5,512	·
8	Total per execution	7.188449	100.0%	87.2%	5.970081	1.218368	13.00	1.00	282,056.00	281,587.00	469.00	5,512.00	·
9	Total per fetch	3.594224	100.0%	43.6%	2.985040	0.609184	6.50	0.50	141,028.00	140,793.50	234.50	2,756.00	·
10	Total per row	7.188449	100.0%	87.2%	5.970081	1.218368	13.00	1.00	282,056.00	281,587.00	469.00	5,512.00	·

4.1.5. Profile by Execution Plan

Statement fpmabaybx9hgf recorded only one distinct execution plan in its trace file for this experience.

The LIO and PIO counts shown here, obtained from Oracle Database “STAT” lines, may include counts inclusive of statement descendants; thus, the counts shown here may not match the counts shown in other sections.

	Plan hash value	Duration			Contribution (seconds)		Duration per row	Executions	Rows	LIOs (cr)	PIOs (r+w)	Row source ops	Cost	Size (bytes)
	Plan hash value	seconds	%	% R	row source ops	other	Duration per row	Executions	Rows	LIOs (cr)	PIOs (r+w)	Row source ops	Cost	Size (bytes)
1	2144110080	7.188449	100.0%	87.2%	8.235556	−1.047107	7.188449	1	1	499,015	5,512	147	2,440	1,716
2	Total (1)	7.188449	100.0%	87.2%	8.235556	−1.047107	7.188449	1	1	499,015	5,512
3	Total per execution	7.188449	100.0%	87.2%	8.235556	−1.047107		1.00	1.00	499,015.00	5,512.00
4	Total per row	7.188449	100.0%	87.2%	8.235556	−1.047107		1.00	1.00	499,015.00	5,512.00

4.1.5.1. Execution Plan 2144110080

Statement fpmabaybx9hgf used execution plan 2144110080.

	Row source operation (object id)	Duration of self			Duration incl descendants			Rows	LIOs (cr)	PIOs		Cost	Size (bytes)	Cardinality
	Row source operation (object id)	seconds	%	% R	seconds	%	% R	Rows	LIOs (cr)	r	w	Cost	Size (bytes)	Cardinality
1	VIEW DBA_EXTENTS (6189)	0.000001	·	·	8.235556	100.0%	99.9%	1	·	·	·	2,440	1,716	13
2	1UNION-ALL ()	0.000010	·	·	8.235555	100.0%	99.9%	1	·	·	·
3	12NESTED LOOPS ()	0.000002	·	·	0.001553	·	·	·	·	·	·	1,042	232	1
4	123NESTED LOOPS ()	0.000003	·	·	0.001551	·	·	·	·	·	·	2	88	1
5	1234TABLE ACCESS BY INDEX ROWID FILE$ (17)	0.000019	·	·	0.000035	·	·	1	1	·	·	1	10	1
6	12345INDEX UNIQUE SCAN I_FILE1 (43)	0.000016	·	·	0.000016	·	·	1	1	·	·	·	·	1
7	1234TABLE ACCESS CLUSTER UET$ (13)	0.001224	·	·	0.001513	·	·	·	2,028	·	·	1	78	1
8	12345INDEX RANGE SCAN I_FILE#_BLOCK# (9)	0.000289	·	·	0.000289	·	·	2,028	13	·	·	1	·	1
9	123VIEW SYS_DBA_SEGS (6176)	·	·	·	·	·	·	·	·	·	·	1,040	144	1
10	1234UNION ALL PUSHED PREDICATE ()	·	·	·	·	·	·	·	·	·	·
11	12345NESTED LOOPS OUTER ()	·	·	·	·	·	·	·	·	·	·	1,028	139	1
12	123456NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	1,027	121	1
13	1234567HASH JOIN ()	·	·	·	·	·	·	·	·	·	·	1,025	89	1
14	12345678NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	2	31	1
15	123456789NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	1	10	1
16	12345678910INDEX UNIQUE SCAN I_FILE2 (44)	·	·	·	·	·	·	·	·	·	·	·	7	1
17	12345678910TABLE ACCESS CLUSTER TS$ (16)	·	·	·	·	·	·	·	·	·	·	1	3	1
18	1234567891011INDEX UNIQUE SCAN I_TS# (7)	·	·	·	·	·	·	·	·	·	·	·	·	1
19	123456789TABLE ACCESS CLUSTER SEG$ (14)	·	·	·	·	·	·	·	·	·	·	1	21	1
20	12345678910INDEX UNIQUE SCAN I_FILE#_BLOCK# (9)	·	·	·	·	·	·	·	·	·	·	·	·	1
21	12345678VIEW SYS_OBJECTS (6174)	·	·	·	·	·	·	·	·	·	·	1,022	10,237,348	176,506
22	123456789UNION-ALL ()	·	·	·	·	·	·	·	·	·	·
23	12345678910TABLE ACCESS STORAGE FULL TAB$ (4)	·	·	·	·	·	·	·	·	·	·	295	4,039,375	175,625
24	12345678910TABLE ACCESS STORAGE FULL TABPART$ (700)	·	·	·	·	·	·	·	·	·	·	2	16	1
25	12345678910TABLE ACCESS STORAGE FULL CLU$ (5)	·	·	·	·	·	·	·	·	·	·	295	14	1
26	12345678910TABLE ACCESS STORAGE FULL IND$ (19)	·	·	·	·	·	·	·	·	·	·	295	11,077	583
27	12345678910TABLE ACCESS STORAGE FULL INDPART$ (705)	·	·	·	·	·	·	·	·	·	·	2	16	1
28	12345678910TABLE ACCESS BY INDEX ROWID BATCHED LOB$ (108)	·	·	·	·	·	·	·	·	·	·	127	6,132	292
29	1234567891011INDEX FULL SCAN I_LOB2 (110)	·	·	·	·	·	·	·	·	·	·	1	·	519
30	12345678910TABLE ACCESS STORAGE FULL TABSUBPART$ (712)	·	·	·	·	·	·	·	·	·	·	2	16	1
31	12345678910TABLE ACCESS STORAGE FULL INDSUBPART$ (717)	·	·	·	·	·	·	·	·	·	·	2	52	1
32	12345678910TABLE ACCESS STORAGE FULL LOBFRAG$ (733)	·	·	·	·	·	·	·	·	·	·	2	18	1
33	1234567TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (18)	·	·	·	·	·	·	·	·	·	·	2	32	1
34	12345678INDEX RANGE SCAN I_OBJ1 (36)	·	·	·	·	·	·	·	·	·	·	1	·	1
35	123456TABLE ACCESS CLUSTER USER$ (22)	·	·	·	·	·	·	·	·	·	·	1	18	1
36	1234567INDEX UNIQUE SCAN I_USER# (11)	·	·	·	·	·	·	·	·	·	·	·	·	1
37	12345NESTED LOOPS OUTER ()	·	·	·	·	·	·	·	·	·	·	5	87	1
38	123456NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	4	69	1
39	1234567MERGE JOIN CARTESIAN ()	·	·	·	·	·	·	·	·	·	·	3	45	1
40	12345678NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	2	42	1
41	123456789INDEX UNIQUE SCAN I_FILE2 (44)	·	·	·	·	·	·	·	·	·	·	·	7	1
42	123456789TABLE ACCESS STORAGE FULL UNDO$ (15)	·	·	·	·	·	·	·	·	·	·	2	35	1
43	12345678BUFFER SORT ()	·	·	·	·	·	·	·	·	·	·	1	3	1
44	123456789TABLE ACCESS CLUSTER TS$ (16)	·	·	·	·	·	·	·	·	·	·	1	3	1
45	12345678910INDEX UNIQUE SCAN I_TS# (7)	·	·	·	·	·	·	·	·	·	·	·	·	1
46	1234567TABLE ACCESS CLUSTER SEG$ (14)	·	·	·	·	·	·	·	·	·	·	1	24	1
47	12345678INDEX UNIQUE SCAN I_FILE#_BLOCK# (9)	·	·	·	·	·	·	·	·	·	·	·	·	1
48	123456TABLE ACCESS CLUSTER USER$ (22)	·	·	·	·	·	·	·	·	·	·	1	18	1
49	1234567INDEX UNIQUE SCAN I_USER# (11)	·	·	·	·	·	·	·	·	·	·	·	·	1
50	12345NESTED LOOPS OUTER ()	·	·	·	·	·	·	·	·	·	·	4	55	1
51	123456NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	3	37	1
52	1234567NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	2	13	1
53	12345678TABLE ACCESS BY INDEX ROWID FILE$ (17)	·	·	·	·	·	·	·	·	·	·	1	10	1
54	123456789INDEX UNIQUE SCAN I_FILE2 (44)	·	·	·	·	·	·	·	·	·	·	·	·	1
55	12345678TABLE ACCESS CLUSTER TS$ (16)	·	·	·	·	·	·	·	·	·	·	1	3	1
56	123456789INDEX UNIQUE SCAN I_TS# (7)	·	·	·	·	·	·	·	·	·	·	·	·	1
57	1234567TABLE ACCESS CLUSTER SEG$ (14)	·	·	·	·	·	·	·	·	·	·	1	24	1
58	12345678INDEX UNIQUE SCAN I_FILE#_BLOCK# (9)	·	·	·	·	·	·	·	·	·	·	·	·	1
59	123456TABLE ACCESS CLUSTER USER$ (22)	·	·	·	·	·	·	·	·	·	·	1	18	1
60	1234567INDEX UNIQUE SCAN I_USER# (11)	·	·	·	·	·	·	·	·	·	·	·	·	1
61	12345NESTED LOOPS OUTER ()	·	·	·	·	·	·	·	·	·	·	3	52	1
62	123456NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	2	34	1
63	1234567NESTED LOOPS ()	·	·	·	·	·	·	·	·	·	·	1	10	1
64	12345678INDEX UNIQUE SCAN I_FILE2 (44)	·	·	·	·	·	·	·	·	·	·	·	7	1
65	12345678TABLE ACCESS CLUSTER TS$ (16)	·	·	·	·	·	·	·	·	·	·	1	3	1
66	123456789INDEX UNIQUE SCAN I_TS# (7)	·	·	·	·	·	·	·	·	·	·	·	·	1
67	1234567TABLE ACCESS CLUSTER SEG$ (14)	·	·	·	·	·	·	·	·	·	·	1	24	1
68	12345678INDEX UNIQUE SCAN I_FILE#_BLOCK# (9)	·	·	·	·	·	·	·	·	·	·	·	·	1
69	123456TABLE ACCESS CLUSTER USER$ (22)	·	·	·	·	·	·	·	·	·	·	1	18	1
70	1234567INDEX UNIQUE SCAN I_USER# (11)	·	·	·	·	·	·	·	·	·	·	·	·	1
71	12HASH JOIN ()	0.002000	·	·	8.233992	100.0%	99.9%	1	·	·	·	1,398	3,108	12
72	123FIXED TABLE FULL X$KTFBUE ()	7.012436	85.1%	85.1%	7.012436	85.1%	85.1%	3	414,267	5,512	·	26	19,500	250
73	123NESTED LOOPS ()	0.000220	·	·	1.219556	14.8%	14.8%	2,028	·	·	·	1,372	4,780,391	26,411
74	1234TABLE ACCESS BY INDEX ROWID FILE$ (17)	0.000015	·	·	0.000032	·	·	1	1	·	·	1	10	1
75	12345INDEX UNIQUE SCAN I_FILE1 (43)	0.000017	·	·	0.000017	·	·	1	1	·	·	·	·	1
76	1234VIEW SYS_DBA_SEGS (6176)	0.022990	0.3%	0.3%	1.219304	14.8%	14.8%	2,028	·	·	·	1,371	4,516,281	26,411
77	12345UNION-ALL ()	0.055200	0.7%	0.7%	1.196314	14.5%	14.5%	194,911	·	·	·
78	123456HASH JOIN RIGHT OUTER ()	0.067067	0.8%	0.8%	1.060739	12.9%	12.9%	194,889	·	·	·	1,180	26,308,391	189,269
79	1234567TABLE ACCESS STORAGE FULL USER$ (22)	0.000077	·	·	0.000077	·	·	75	6	·	·	2	1,350	75
80	1234567HASH JOIN ()	0.199279	2.4%	2.4%	0.993595	12.1%	12.1%	194,889	·	·	·	1,177	22,901,549	189,269
81	12345678INDEX STORAGE FAST FULL SCAN I_OBJ2 (37)	0.026168	0.3%	0.3%	0.026168	0.3%	0.3%	220,970	2,015	·	·	4	670,688	20,959
82	12345678HASH JOIN ()	0.063068	0.8%	0.8%	0.768148	9.3%	9.3%	194,889	·	·	·	1,172	16,844,941	189,269
83	123456789TABLE ACCESS STORAGE FULL TS$ (16)	0.000078	·	·	0.000078	·	·	7	9	·	·	2	21	7
84	123456789HASH JOIN ()	0.070693	0.9%	0.9%	0.705002	8.6%	8.6%	194,889	·	·	·	1,169	16,277,134	189,269
85	12345678910INDEX FULL SCAN I_FILE2 (44)	0.000022	·	·	0.000022	·	·	5	1	·	·	1	35	5
86	12345678910HASH JOIN ()	0.222598	2.7%	2.7%	0.634287	7.7%	7.7%	194,889	·	·	·	1,167	14,952,251	189,269
87	1234567891011TABLE ACCESS STORAGE FULL SEG$ (14)	0.095599	1.2%	1.2%	0.095599	1.2%	1.2%	194,911	5,585	·	·	79	3,974,649	189,269
88	1234567891011VIEW SYS_OBJECTS (6174)	0.028772	0.3%	0.3%	0.316090	3.8%	3.8%	205,435	·	·	·	1,022	10,298,480	177,560
89	123456789101112UNION-ALL ()	0.058156	0.7%	0.7%	0.287318	3.5%	3.5%	205,435	·	·	·
90	12345678910111213TABLE ACCESS STORAGE FULL TAB$ (4)	0.156352	1.9%	1.9%	0.156352	1.9%	1.9%	201,691	21,227	·	·	295	4,039,375	175,625
91	12345678910111213TABLE ACCESS STORAGE FULL TABPART$ (700)	0.000226	·	·	0.000226	·	·	529	15	·	·	2	8,416	526
92	12345678910111213TABLE ACCESS STORAGE FULL CLU$ (5)	0.035403	0.4%	0.4%	0.035403	0.4%	0.4%	10	21,226	·	·	295	140	10
93	12345678910111213TABLE ACCESS STORAGE FULL IND$ (19)	0.036300	0.4%	0.4%	0.036300	0.4%	0.4%	2,157	21,226	·	·	295	11,077	583
94	12345678910111213TABLE ACCESS STORAGE FULL INDPART$ (705)	0.000206	·	·	0.000206	·	·	463	23	·	·	2	7,328	458
95	12345678910111213TABLE ACCESS BY INDEX ROWID BATCHED LOB$ (108)	0.000539	·	·	0.000627	·	·	519	126	·	·	127	6,132	292
96	1234567891011121314INDEX FULL SCAN I_LOB2 (110)	0.000088	·	·	0.000088	·	·	519	1	·	·	1	·	519
97	12345678910111213TABLE ACCESS STORAGE FULL TABSUBPART$ (712)	0.000023	·	·	0.000023	·	·	32	3	·	·	2	512	32
98	12345678910111213TABLE ACCESS STORAGE FULL INDSUBPART$ (717)	0.000010	·	·	0.000010	·	·	·	3	·	·	2	52	1
99	12345678910111213TABLE ACCESS STORAGE FULL LOBFRAG$ (733)	0.000015	·	·	0.000015	·	·	34	3	·	·	2	594	33
100	123456HASH JOIN OUTER ()	0.000279	·	·	0.000984	·	·	21	·	·	·	27	783	9
101	1234567HASH JOIN ()	0.000409	·	·	0.000656	·	·	21	·	·	·	25	621	9
102	12345678HASH JOIN ()	0.000006	·	·	0.000215	·	·	21	·	·	·	23	594	9
103	123456789NESTED LOOPS ()	0.000009	·	·	0.000209	·	·	21	·	·	·	23	594	9
104	12345678910STATISTICS COLLECTOR ()	0.000010	·	·	0.000086	·	·	21	·	·	·
105	1234567891011NESTED LOOPS ()	0.000010	·	·	0.000076	·	·	21	·	·	·	2	882	21
106	123456789101112TABLE ACCESS STORAGE FULL UNDO$ (15)	0.000037	·	·	0.000037	·	·	21	3	·	·	2	735	21
107	123456789101112INDEX UNIQUE SCAN I_FILE2 (44)	0.000029	·	·	0.000029	·	·	21	4	·	·	·	7	1
108	12345678910TABLE ACCESS CLUSTER SEG$ (14)	0.000076	·	·	0.000114	·	·	21	21	·	·	1	24	1
109	1234567891011INDEX UNIQUE SCAN I_FILE#_BLOCK# (9)	0.000038	·	·	0.000038	·	·	21	16	·	·	·	·	1
110	123456789TABLE ACCESS STORAGE FULL SEG$ (14)	·	·	·	·	·	·	·	·	·	·	1	24	1
111	12345678TABLE ACCESS STORAGE FULL TS$ (16)	0.000032	·	·	0.000032	·	·	7	9	·	·	2	21	7
112	1234567TABLE ACCESS STORAGE FULL USER$ (22)	0.000049	·	·	0.000049	·	·	75	6	·	·	2	1,350	75
113	123456HASH JOIN ()	0.000004	·	·	0.041853	0.5%	0.5%	·	·	·	·	82	55	1
114	1234567NESTED LOOPS ()	0.000002	·	·	0.041849	0.5%	0.5%	·	·	·	·	82	55	1
115	12345678NESTED LOOPS ()	0.000001	·	·	0.041847	0.5%	0.5%	·	·	·	·	82	55	1
116	123456789STATISTICS COLLECTOR ()	0.000002	·	·	0.041846	0.5%	0.5%	·	·	·	·
117	12345678910HASH JOIN OUTER ()	0.000003	·	·	0.041844	0.5%	0.5%	·	·	·	·	81	45	1
118	1234567891011NESTED LOOPS OUTER ()	0.000002	·	·	0.041841	0.5%	0.5%	·	·	·	·	81	45	1
119	123456789101112STATISTICS COLLECTOR ()	0.000002	·	·	0.041839	0.5%	0.5%	·	·	·	·
120	12345678910111213HASH JOIN ()	0.000003	·	·	0.041837	0.5%	0.5%	·	·	·	·	80	27	1
121	1234567891011121314NESTED LOOPS ()	0.000001	·	·	0.041834	0.5%	0.5%	·	·	·	·	80	27	1
122	123456789101112131415STATISTICS COLLECTOR ()	0.000003	·	·	0.041833	0.5%	0.5%	·	·	·	·
123	12345678910111213141516TABLE ACCESS STORAGE FULL SEG$ (14)	0.041830	0.5%	0.5%	0.041830	0.5%	0.5%	·	5,585	·	·	79	24	1
124	123456789101112131415TABLE ACCESS CLUSTER TS$ (16)	·	·	·	·	·	·	·	·	·	·	1	3	1
125	12345678910111213141516INDEX UNIQUE SCAN I_TS# (7)	·	·	·	·	·	·	·	·	·	·	·	·	1
126	1234567891011121314TABLE ACCESS STORAGE FULL TS$ (16)	·	·	·	·	·	·	·	·	·	·	1	3	1
127	123456789101112TABLE ACCESS CLUSTER USER$ (22)	·	·	·	·	·	·	·	·	·	·	1	18	1
128	12345678910111213INDEX UNIQUE SCAN I_USER# (11)	·	·	·	·	·	·	·	·	·	·	·	·	1
129	1234567891011TABLE ACCESS STORAGE FULL USER$ (22)	·	·	·	·	·	·	·	·	·	·	1	18	1
130	123456789INDEX UNIQUE SCAN I_FILE2 (44)	·	·	·	·	·	·	·	·	·	·	·	·	1
131	12345678TABLE ACCESS BY INDEX ROWID FILE$ (17)	·	·	·	·	·	·	·	·	·	·	1	10	1
132	1234567TABLE ACCESS STORAGE FULL FILE$ (17)	·	·	·	·	·	·	·	·	·	·	1	10	1
133	123456HASH JOIN OUTER ()	0.000007	·	·	0.037538	0.5%	0.5%	1	·	·	·	81	52	1
134	1234567NESTED LOOPS OUTER ()	0.000005	·	·	0.037531	0.5%	0.5%	1	·	·	·	81	52	1
135	12345678STATISTICS COLLECTOR ()	0.000003	·	·	0.037515	0.5%	0.5%	1	·	·	·
136	123456789HASH JOIN ()	0.000003	·	·	0.037512	0.5%	0.5%	1	·	·	·	80	34	1
137	12345678910NESTED LOOPS ()	0.000011	·	·	0.037509	0.5%	0.5%	1	·	·	·	80	34	1
138	1234567891011STATISTICS COLLECTOR ()	0.000004	·	·	0.037483	0.5%	0.5%	1	·	·	·
139	123456789101112NESTED LOOPS ()	0.000003	·	·	0.037479	0.5%	0.5%	1	·	·	·	79	31	1
140	12345678910111213TABLE ACCESS STORAGE FULL SEG$ (14)	0.037462	0.5%	0.5%	0.037462	0.5%	0.5%	1	5,585	·	·	79	24	1
141	12345678910111213INDEX UNIQUE SCAN I_FILE2 (44)	0.000014	·	·	0.000014	·	·	1	1	·	·	·	7	1
142	1234567891011TABLE ACCESS CLUSTER TS$ (16)	0.000011	·	·	0.000015	·	·	1	1	·	·	1	3	1
143	123456789101112INDEX UNIQUE SCAN I_TS# (7)	0.000004	·	·	0.000004	·	·	1	1	·	·	·	·	1
144	12345678910TABLE ACCESS STORAGE FULL TS$ (16)	·	·	·	·	·	·	·	·	·	·	1	3	1
145	12345678TABLE ACCESS CLUSTER USER$ (22)	0.000007	·	·	0.000011	·	·	1	1	·	·	1	18	1
146	123456789INDEX UNIQUE SCAN I_USER# (11)	0.000004	·	·	0.000004	·	·	1	1	·	·	·	·	1
147	1234567TABLE ACCESS STORAGE FULL USER$ (22)	·	·	·	·	·	·	·	·	·	·	1	18	1
148	Total (147)	8.235556	100.0%	99.9%					499,015	5,512	·

There was only one set of placeholder values used with execution plan 2144110080.

	Name	Type code	Type	Value
1	`:file_id`	2	NUMBER	`1`
2	`:block_id`	2	NUMBER	`45888`

4.1.6. Statement Text

The following list is the complete text of statement fpmabaybx9hgf. Opportunities for placeholder use are highlighted.

select owner, segment_name from dba_extents
where file_id=:file_id and :block_id between block_id and block_id+blocks-1

4.2. Statement Report for SQL ID 1n1bm7xa28vtq

Statement 1n1bm7xa28vtq “select file#, block#, type#, nvl(spare1,0), hwmi…” was the 2nd most time-consuming statement of the overall 8.244514-second experience.

Its mrprof shared SQL ID is oTOoneTbj6.

4.2.1. Profile by Statement

Statement 1n1bm7xa28vtq consumed 1.048032 seconds (12.7%) of the total experience duration.

The statement is a query that returned 195,063 rows. Improvement opportunities may include:

Does the query really need to execute at all? If the application stopped executing the query, would it diminish the application’s value?
If the query is feeding an application for summarization or joining, consider doing such work in SQL within the database where the processing can be vastly more efficient.
This query returns an average of 39,013 rows per execution. If the query’s output is being displayed for a user to see, consider that people are normally not interested in seeing more than 10 rows at a time [Tow–Millsap law]. Consider aggregating or paginating.

	Statement	Duration			Executions	Rows	LIOs (cr+cu)	PIOs (r)	Distinct plans	Distinct cursors	Distinct texts	Statement size		Optimizer goal
	Statement	seconds	%	% R	Executions	Rows	LIOs (cr+cu)	PIOs (r)	Distinct plans	Distinct cursors	Distinct texts	lines	bytes	Optimizer goal
1	[1n1bm7xa28vtq] select file#, block#, type#, nvl(spare1,0), hwmi…	1.048032	100.0%	12.7%	5	195,063	217,419	·	·	1	1	3	106	CHOOSE
2	Total (1)	1.048032	100.0%	12.7%	5	195,063	217,419	·	·	1	1	3	106	CHOOSE

4.2.2. Profile by Caller

Statement 1n1bm7xa28vtq was executed from only one place in your application source code.

	Context	Duration			Executions	Rows	LIOs (cr+cu)	PIOs (r)
	Context	seconds	%	% R	Executions	Rows	LIOs (cr+cu)	PIOs (r)
1	Child of [fpmabaybx9hgf] select owner, segment_name from dba_extents wher…	1.048032	100.0%	12.7%	5	195,063	217,419	·
2	Total (1)	1.048032	100.0%	12.7%	5	195,063	217,419	·

4.2.3. Profile by Subroutine

Statement 1n1bm7xa28vtq duration was dominated by “CPU: FETCH dbcalls” calls:

“CPU: FETCH dbcalls” consumed 98.1% of your experience duration. “CPU: FETCH dbcalls” are high-productivity calls that you do want dominating your duration. However, you do not want your application doing any more “CPU: FETCH dbcalls” work than necessary. When you are investigating a time-consuming statement, don’t be afraid to ask whether the statement is really necessary—sometimes an entire statement can be eliminated with no loss of functionality to the business. Investigate whether you can reduce your call count by using array processing, and whether each call can be made more efficient by eliminating unnecessary LIOs.
LIO count is one good measure of a SQL statement’s efficiency. An LIO is simply an access of a buffer in the database buffer cache. But an LIO is much more than just a memory access. It is a latch-serialized operation that consumes CPU. By reducing a statement’s LIO count, you reduce not just the number of memory accesses your code executes, but you also reduce the number of CPU cycles your Oracle session consumes, the number of blocks it reads from disk or other secondary storage, the number of latches it has to acquire, the number of global cache coherency instructions it has to execute, and more.

	Subroutine	Duration			Calls	Duration per call (seconds)
	Subroutine	seconds	%	% R	Calls	mean	min	max	skew
1	CPU: FETCH dbcalls	1.027856	98.1%	12.5%	195,068	0.000005	·	0.024997	▁▁▁▁▁▁▁▁▁▁▁▁▁▁▂█▂▁▁▁▁▁
2	unaccounted-for within dbcalls	0.020176	1.9%	0.2%	195,083	·	−0.001974	0.001073	▁▁▁▁▁▁▁█▁▁▁▁█▁▁▁▁▁▁▁▁▁
3	CPU: CLOSE dbcalls	·	·	·	5	·	·	·	▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁
4	CPU: EXEC dbcalls	·	·	·	5	·	·	·	▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁
5	CPU: PARSE dbcalls	·	·	·	5	·	·	·	▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁▁
6	Total (5)	1.048032	100.0%	12.7%	390,166	0.000003	−0.001974	0.024997

4.2.4. Profile by Database Call

Statement 1n1bm7xa28vtq touched the database buffer cache 217,419 times and processed 195,063 rows, an average of 1.11 LIOs per row. The statement used the buffer cache efficiently.

It should be possible to accomplish everything this statement does with just one “PARSE” call, but the application has executed five “PARSE” calls for this statement. Eliminating unnecessary “PARSE” calls should improve the total experience duration by at least 0.000154 seconds.
It should be possible to return many rows per “FETCH” call, but this application returns an average of only 1.00 rows per “FETCH” call.

The statement obtained zero Oracle blocks from OS read calls, which means that your kernel process found the block it needed in the database buffer cache on 100.0% of LIOs.

	Database call	Duration			Contribution (seconds)		Calls	Rows	LIOs			PIOs (r)	Library cache misses
	Database call	seconds	%	% R	CPU	non-CPU	Calls	Rows	total	cr	cu	PIOs (r)	Library cache misses
1	FETCH	1.047390	99.9%	12.7%	1.027856	0.019534	195,068	195,063	217,419	217,419	·	·	·
2	EXEC	0.000436	·	·	·	0.000436	5	·	·	·	·	·	·
3	PARSE	0.000154	·	·	·	0.000154	5	·	·	·	·	·	·
4	CLOSE	0.000052	·	·	·	0.000052	5	·	·	·	·	·	·
5	STAT	·	·	·	·	·	1	·	·	·	·	·	·
6	Total (5)	1.048032	100.0%	12.7%	1.027856	0.020176	195,084	195,063	217,419	217,419	·	·	·
7	Total per execution	0.209606	100.0%	2.5%	0.205571	0.004035	39,016.80	39,012.60	43,483.80	43,483.80	·	·	·
8	Total per fetch	0.000005	100.0%	·	0.000005	·	1.00	1.00	1.11	1.11	·	·	·
9	Total per row	0.000005	100.0%	·	0.000005	·	1.00	1.00	1.11	1.11	·	·	·

4.2.5. Profile by Execution Plan

Statement 1n1bm7xa28vtq recorded only one distinct execution plan in its trace file for this experience.

	Plan hash value	Duration			Contribution (seconds)		Duration per row	Executions	Rows	LIOs (cr)	PIOs (r+w)	Row source ops	Cost	Size (bytes)
	Plan hash value	seconds	%	% R	row source ops	other	Duration per row	Executions	Rows	LIOs (cr)	PIOs (r+w)	Row source ops	Cost	Size (bytes)
1	1605285479	1.048032	100.0%	12.7%	0.034097	1.013935	0.000517	5	2,028	7,556	·	1	4	30,210
2	Total (1)	1.048032	100.0%	12.7%	0.034097	1.013935	0.000517	5	2,028	7,556	·
3	Total per execution	0.209606	100.0%	2.5%	0.006819	0.202787		1.00	405.60	1,511.20	·
4	Total per row	0.000517	100.0%	·	0.000017	0.000500		·	1.00	3.73	·

4.2.5.1. Execution Plan 1605285479

Statement 1n1bm7xa28vtq used execution plan 1605285479.

	Row source operation (object id)	Duration of self			Duration incl descendants			Rows	LIOs (cr)	PIOs		Cost	Size (bytes)	Cardinality
	Row source operation (object id)	seconds	%	% R	seconds	%	% R	Rows	LIOs (cr)	r	w	Cost	Size (bytes)	Cardinality
1	TABLE ACCESS STORAGE FULL SEG$ (14)	0.034097	100.0%	0.4%	0.034097	100.0%	0.4%	2,028	7,556	·	·	4	30,210	1,007
2	Total (1)	0.034097	100.0%	0.4%					7,556	·	·

There were five distinct placeholder value sets. The following placeholder values contributed the greatest duration (0.911353 seconds) for this execution plan 1605285479.

	Name	Type code	Type	Value
1	`:1`	2	NUMBER	`5`

4.2.6. Statement Text

The following list is the complete text of statement 1n1bm7xa28vtq. Opportunities for placeholder use are highlighted.

select file#, block#, type#, nvl(spare1,0), hwmincr, cachehint, nvl(scanhint,0)
from seg$
where ts# = :1

	Option	Value
1	--cdefs	/workbench/9.2.1.2/cdefs.xml
2	--encoding	UTF-8
3	--env	0
4	--error-emit	5
5	--first-member	1
6	--fix-tim-values	0
7	--force-match-plans	1
8	--force-match-statement-texts	1
9	--input-base
10	--input-type
11	--license	0
12	--member
13	--negative-betweeners-allowed	1
14	--patch-negative-counts	1
15	--script	unix
16	--script-comment	#
17	--script-extension
18	--sql-text-width	180
19	--stash	100
20	--trace
21	--trcsess	1
22	--trcunit	0.000001
23	--xsd

	Name	Default value	Params file value	Used value
1	ADVISORY	true	true	true
2	CONTRIBUTIONS-MINIMUM-LIST-LENGTH	4	4	4
3	CREATE-SHAREABLE-HTML	true	true	true
4	DISTINCT-TEXT-THRESHOLD	1	1	1
5	EXECS-PER-PARSE-THRESHOLD	2	2	2
6	LIBRARY-CACHE-MISSES-PER-STATEMENT-THRESHOLD	1	1	1
7	LIOS-PER-ROW-THRESHOLD	10	10	10
8	LOCALE-CODE	A	A	A
9	PIOS-PER-LIO-THRESHOLD	.1	0.1	0.1
10	PROFILE-DISPLAYED-THRESHOLD	.05	0.05	0.05
11	PROFILE-RELEVANCE-THRESHOLD	.20	0.2	0.2
12	ROWS-PER-EXEC-THRESHOLD	2	2	2
13	ROWS-PER-FETCH-THRESHOLD	2	2	2
14	SHOW-CONFIDENTIALITY-LABEL	false	false	false
15	SHOW-CUMULATIVE-DURATIONS	false	false	false
16	SHOW-LITERAL-HIGHLIGHTING	true	true	true
17	SHOW-PROFILER-STATEMENT-ID	false	false	false
18	SHOW-ZERO-VALUE-STRING	true	true	true
19	SQL-TEXT-PREFIX-LENGTH	48	48	48

	Subroutine	Qualifier	Label	Tolerance (seconds)
1	SQL*Net message from client	$e >= 1.000	think time
2	SQL*Net message from client			0.100
3	db file sequential read			0.005
4	db file scattered read	$p3 <= 16	blocks ≤ 16	0.004
5	db file scattered read	$p3 <= 32	16 < blocks ≤ 32	0.008
6	db file scattered read		blocks > 32	0.016
7	CPU: PARSE dbcalls			0.250

Method R Profile: /sample/dbm011_ora_33518.html