|
||||
|
Hello, Thanks to my colleague, I have just buyed the following from my Friend Naga Satish from his email and sharing here. “Edited Explain plan” —————————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————— | 0 | SELECT STATEMENT | | 3626K| 390M| | 1217K (1)| 04:03:28 […] If you want to understand more about SQL Plan Management, read this post before reading further, https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines Debate- SPA vs. SPM vs. ACS, SPM vs. sql_profiles etc As you see in the figure, Each and every sql baselines has multiple plans associated with it and stored in sql plan management base. Some of the statements […] In Oracle execution plan, you often see Nested loops/Hash Joins/Merge Joins etc. These are called Join methods between row sources (tables/result sets of query). You are can find more about Rowsource Join Methods here in the documentation There are many join methods, out of all the following were common and generic Nested loop join […] Typically we would have the following patterns when we use like operator. SEARCH-STRING% %SEARCH-STRING %SEARCH-STRING% SEARCH%STRING 1. a)The SEARCH-STRING% will perform INDEX RANGE SCAN data in least possible time. b)Also uses INDEX FULL SCAN when you have only indexed column list in the queries. c) If multiple predicates used, this may loose […] Hi, In 10g, you can use data dictionary view v$sql_bind_capture or AWR view dba_hist_sqlbind (historical version of v$sql_bind_capture) to find bind variables’ values. However it has some significant limitations: Captured periodically (_cursor_bind_capture_interval=900 seconds by default), not at real time. Captured under maximum size(_cursor_bind_capture_area_size=400) Only bind variables in WHERE clause are captured (e.g bind variables passed […] Somtimes you might be into a situation, that you have created an sql profile and unfortunately that has not been picked up? Well as per my reads and understanding and limited knowledge, these may be the causes. 1) Signature mismatch – SQL statements as converted to signature (with possible extra space truncation, replace literals etc) […] The following is the real nice work from http://intermediatesql.com/oracle/what-is-the-difference-between-sql-profile-and-spm-baseline/ This was very good post and awesome write up which help my blog readers too. End to end on sqlprofiles and spm, happy reading, thanks to Maxym.
SQL Profiles Note —– – SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement
and […] In the previous post we discussed about the Parent & Child Cursors in Oracle and how do they work or shared. To get into deeper, Oracle controls the sharing of cursors with parameter called cursor_sharing. It has three values EXACT SIMILAR FORCE In this post we will create a table which has some records with […] What are PARENT AND CHILD CURSORS IN ORACLE A cursor is a memory area in library cache allocated to a SQL statement which stores various info about the SQL statement like its text, execution plan, statistics etc. Each SQL statement has One Parent cursor One or more child cursors PARENT CURSOR It […] The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display […] |
||||
Oracle 23c New Features for DBA's
Follow Me!!!