Consider the follow query against SH schema.
select /*MY5*/ cust_id, sum(amount_sold) from sales where prod_id=:prod_id group by cust_id;
With DBMS_XPLAN.Display_cursor, user is able to retrieve and see execution plan.
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%/*MY5*/%';
This reveals quite a bit of information, however, what if user would like to know how much memory allocation during the course of this query, how many rows Oracle visited, etc...
DBMS_XPLAN.display_cursor provide a way to get these information.
alter session set statistics_level=ALL;
Default value is 'TYPICAL'. In addition to statistics collected at 'TYPICAL' level, 'ALL' instructs Oracle to gather timed OS statistics and plan execution statistics.
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'TYPICAL ALLSTATS')) t WHERE sql_text LIKE '%/*MY5*/%';
select /*MY5*/ cust_id, sum(amount_sold) from sales where prod_id=:prod_id group by cust_id;
With DBMS_XPLAN.Display_cursor, user is able to retrieve and see execution plan.
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%/*MY5*/%';
This reveals quite a bit of information, however, what if user would like to know how much memory allocation during the course of this query, how many rows Oracle visited, etc...
DBMS_XPLAN.display_cursor provide a way to get these information.
alter session set statistics_level=ALL;
Default value is 'TYPICAL'. In addition to statistics collected at 'TYPICAL' level, 'ALL' instructs Oracle to gather timed OS statistics and plan execution statistics.
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'TYPICAL ALLSTATS')) t WHERE sql_text LIKE '%/*MY5*/%';
ALLSTATS: 'IOSTATS MEMSTATS', shows both memory and IO statistics. In order for the memory statistics to be shown, the PGA management should be enabled. That is, PAG_AGGREGATE_TARGET=. The statistics are shown for all executions of the cursor.
To view the statistics of last execution, use 'TYPICAL ALLSTATS LAST'.
Interpreting statistics
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'TYPICAL ALLSTATS LAST')) t WHERE sql_text LIKE '%/*MY5*/%';
Starts:
E-Rows: Estimated Number of Rows. "Rows" column from the output of "explain plan" command.
E-Bytes: Estimated Bytes
E-Time: Estimated Time
A-Rows: Actual Rows
A-Time: Actual Time
Buffers: Logical Reads. consistent gets + current gets
Reads: Physical Reads. Only show up when it has a non-zero value.
Writes: Number of writes to temp space.
0Mem: Estimated_Optimal_Size of v$sql_workarea. Esimated size in KB to perform the operation totally in memory (optimal execution). This is the ideal size.
1Mem: Esimated_OnePass_size of v$sql_workarea. Estimated size in KB to execute the operation in one pass. With one pass size, the response time is increased.
Used-Mem: Last_Memory_Used of v$sql_workarea. Memory used during the last execution of the cursor.
Used-Temp: Size of temp space used.
Both optimal size and one-pass size memory are allocated from PGA.
To view the statistics of last execution, use 'TYPICAL ALLSTATS LAST'.
Interpreting statistics
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'TYPICAL ALLSTATS LAST')) t WHERE sql_text LIKE '%/*MY5*/%';
Starts:
E-Rows: Estimated Number of Rows. "Rows" column from the output of "explain plan" command.
E-Bytes: Estimated Bytes
E-Time: Estimated Time
A-Rows: Actual Rows
A-Time: Actual Time
Buffers: Logical Reads. consistent gets + current gets
Reads: Physical Reads. Only show up when it has a non-zero value.
Writes: Number of writes to temp space.
0Mem: Estimated_Optimal_Size of v$sql_workarea. Esimated size in KB to perform the operation totally in memory (optimal execution). This is the ideal size.
1Mem: Esimated_OnePass_size of v$sql_workarea. Estimated size in KB to execute the operation in one pass. With one pass size, the response time is increased.
Used-Mem: Last_Memory_Used of v$sql_workarea. Memory used during the last execution of the cursor.
Used-Temp: Size of temp space used.
Both optimal size and one-pass size memory are allocated from PGA.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/638844/viewspace-776254/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
