1. New advanced module XGRAM. It allows to insert, update and delete CBO histograms and individual buckets without the need to gather stats or to have the data. 2. TC includes a new script sqlt_s99999_del_hgrm.sql which deletes all histograms in TC schema. It can be used to verify plan without histograms. 3. Escape character has been changed on SQLT XECUTE from default "\" to "!". This allows now to use "\" within the SQL passed to SQLT XECUTE. 4. Health-check on OPTIMIZER_SECURE_VIEW_MERGING excludes now validation of USER executing SQLT. It still validates PARSING_SCHEMA_OWNER. 5. Change name of sys.gv$parameter_cbo to sys.sqlt$_gv$parameter_cbo. Same for similar views created and used by SQLT. 6. New health-check to review when a column has a HEIGHT BALANCED histogram with no popular values and this column is referenced in a predicate. 7. New section "Modified System Parameters" based on DBA_HIST_PARAMETER is now part of main report. 8. Added a Bucket Decription for Adaptive Cursor Sharing "Histograms" section (GV$SQL_CS_HISTOGRAM). 9. Skip SQL Tuning Advisor STA if max Elapsed Time for known plans is greater than "sta_time_limit_secs" threshold parameter (1800 secs). 10. Readme section "Load SQL Plan from SQL Set" includes now a plan performance summary for each plan baseline that can be created out of syntax provided. 11. Readme includes syntax to gather statistics if application is PeopleSoft PSFT as per 1322888.1. 12. New set of health-checks to validate CBO statistics if application is PeopleSoft PSFT as per 1322888.1. 13. New parameter "search_sql_by_sqltext" allows XPLAIN to control searches in memory and AWR using SQL text while trying to find known plans. 14. Include GV$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY on main report (under SQL Execution).
i am trying to understand SQLT reports. main html report contains lot no of information. i need you help to understand this report to tune the poor performing queries.
can you pls provide me some documents or method to interpret this report . if you have any document pls send me logasamit@gmail.com
BUG: Estimated MBRC was computed by SQLT as db_file_multiblock_read_count instead of _db_file_optimizer_read_count. BUG: Module XGRAM is now capable to handle CHAR columns in addition to VARCHAR2, NUMBER and DATE. BUG: Eliminate from SQLT$_CAPTURED_BINDS_V those rows where LAST_CAPTURED was NULL. BUG: Handle occasional LOB memory leak affecting MAIN report when processing othe_xml. You had this bug if you see garbage with traces of some hints at the end of the MAIN report. BUG: SQLT Profile when asked to generate script on plan X it was doing so for plan Y. BUG: Metadata script used to fail replacing table owner on CREATE INDEX command when different than index owner. BUG: Avoid following error when using SQLT on a distributed environment and remote SQL is executed as some user other than local user. ORA-20210: To download this file you must connect as "SYS", "SYSTEM", "SQLTXPLAIN", or "remote user" BUG: SQLT COMPARE was not showing plans when comparing files older than 11.4.3.8. ENH: RAW variables are now handled by SQLT TC scripts as VARCHAR2, and their values are passed within single-quotes, ENH: Added "REPRODUCED" flag to DBA_SQL_PLAN_BASELINES view and corresponding section in MAIN. ENH: Plan Info is now more accurate on Plans Summary and Execution Plans sections. ENH: RESTORE nows matches hidden columns by expression and data_default when restoring CBO stats into TC schema objects. ENH: Set the sequence sqlt$_sql_statement_id_s to NOCACHE. Caching is intended to speed frequent access to the sequence (which does not happen here), and results in the sequence tending jump by units of 20. This makes it harder to identify consecutive runs. This also applies to trca$_tool_execution_id. ENH: When setting system stats in the test case script, use "scope=memory" to avoid changing the spfile. Same for XPLORE. ENH: Report columns that use the ADD COLUMN...DEFAULT optimization in the "Column Properties" section. Plan generation for such columns differs from normal columns because references to it are internally turned into the equivalent of NVL(column, default). ENH: Improve performance of sqlt$t.remap_metadata on 10g. This API was using recursive code on a CLOB and it performed poorly on 10g when there were a large number of partitions or subpartitions.
11.4.3.2 July 9, 2011
ReplyDelete1. New advanced module XGRAM. It allows to insert, update and delete CBO histograms and individual buckets without the need to gather stats or to have the data.
2. TC includes a new script sqlt_s99999_del_hgrm.sql which deletes all histograms in TC schema. It can be used to verify plan without histograms.
3. Escape character has been changed on SQLT XECUTE from default "\" to "!". This allows now to use "\" within the SQL passed to SQLT XECUTE.
4. Health-check on OPTIMIZER_SECURE_VIEW_MERGING excludes now validation of USER executing SQLT. It still validates PARSING_SCHEMA_OWNER.
5. Change name of sys.gv$parameter_cbo to sys.sqlt$_gv$parameter_cbo. Same for similar views created and used by SQLT.
6. New health-check to review when a column has a HEIGHT BALANCED histogram with no popular values and this column is referenced in a predicate.
7. New section "Modified System Parameters" based on DBA_HIST_PARAMETER is now part of main report.
8. Added a Bucket Decription for Adaptive Cursor Sharing "Histograms" section (GV$SQL_CS_HISTOGRAM).
9. Skip SQL Tuning Advisor STA if max Elapsed Time for known plans is greater than "sta_time_limit_secs" threshold parameter (1800 secs).
10. Readme section "Load SQL Plan from SQL Set" includes now a plan performance summary for each plan baseline that can be created out of syntax provided.
11. Readme includes syntax to gather statistics if application is PeopleSoft PSFT as per 1322888.1.
12. New set of health-checks to validate CBO statistics if application is PeopleSoft PSFT as per 1322888.1.
13. New parameter "search_sql_by_sqltext" allows XPLAIN to control searches in memory and AWR using SQL text while trying to find known plans.
14. Include GV$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY on main report (under SQL Execution).
HI,
ReplyDeletei am trying to understand SQLT reports. main html report contains lot no of information. i need you help to understand this report to tune the poor performing queries.
can you pls provide me some documents or method to interpret this report . if you have any document pls send me logasamit@gmail.com
thanks
11.4.4.0 is now available
ReplyDeleteBUG: Estimated MBRC was computed by SQLT as db_file_multiblock_read_count instead of _db_file_optimizer_read_count.
BUG: Module XGRAM is now capable to handle CHAR columns in addition to VARCHAR2, NUMBER and DATE.
BUG: Eliminate from SQLT$_CAPTURED_BINDS_V those rows where LAST_CAPTURED was NULL.
BUG: Handle occasional LOB memory leak affecting MAIN report when processing othe_xml.
You had this bug if you see garbage with traces of some hints at the end of the MAIN report.
BUG: SQLT Profile when asked to generate script on plan X it was doing so for plan Y.
BUG: Metadata script used to fail replacing table owner on CREATE INDEX command when different than index owner.
BUG: Avoid following error when using SQLT on a distributed environment and remote SQL is executed as some user other than local user.
ORA-20210: To download this file you must connect as "SYS", "SYSTEM", "SQLTXPLAIN", or "remote user"
BUG: SQLT COMPARE was not showing plans when comparing files older than 11.4.3.8.
ENH: RAW variables are now handled by SQLT TC scripts as VARCHAR2, and their values are passed within single-quotes,
ENH: Added "REPRODUCED" flag to DBA_SQL_PLAN_BASELINES view and corresponding section in MAIN.
ENH: Plan Info is now more accurate on Plans Summary and Execution Plans sections.
ENH: RESTORE nows matches hidden columns by expression and data_default when restoring CBO stats into TC schema objects.
ENH: Set the sequence sqlt$_sql_statement_id_s to NOCACHE.
Caching is intended to speed frequent access to the sequence (which does not happen here), and results in the sequence tending jump by units of 20.
This makes it harder to identify consecutive runs. This also applies to trca$_tool_execution_id.
ENH: When setting system stats in the test case script, use "scope=memory" to avoid changing the spfile. Same for XPLORE.
ENH: Report columns that use the ADD COLUMN...DEFAULT optimization in the "Column Properties" section.
Plan generation for such columns differs from normal columns because references to it are internally turned into the equivalent of NVL(column, default).
ENH: Improve performance of sqlt$t.remap_metadata on 10g.
This API was using recursive code on a CLOB and it performed poorly on 10g when there were a large number of partitions or subpartitions.