Open johnnyq72 opened 9 years ago
johnnyq72
commented
9 years ago plan_hashvalue is present in v$sqlstats (at least on EE & XE) we would need to define what child_number should represent and / or if it's value is of any significance in having this number (ie if it could be "faked").
johnnyq72
commented
9 years ago Table SASH_SQLTXT added column plan_hash_value (number) Package SASH_PKG, body, line 439, added on the end ", plan_hash_value" Added ", plan_hash_value" to the v$sql view. This hack records the plan_hash_value. At least this takes care of one of the two issues.
pioro
commented
9 years ago Hi,
There was (is?) a reason to don't have a plan hash value in sash_sqltxt as I wanted to avoid copy same SQL over and over due to plan changes and then I had a problems with displaying a SQL plan when there was no plan hash value specified. Maybe we should change a definition of v$sql view to take data from sash_sqlstats and join it with sash_sqltxt ? sash_sqlstats already have a plan hash value.
Do you think it will solve your issue ?
Marcin
johnnyq72
commented
9 years ago Hi Marcin, Yes, this join could also work quite well, it would avoid any copying as the data is already there. Do we also have a sql_id child_number there?
I will reverse engineer my hack and then I can already test the view.
Cheers!
pioro
commented
9 years ago Hi,
No - child number is not included in v$sqlstats which aggregate data across child cursors if I'm not mistaken. We can fake it ;)
Marcin
On Thu, Apr 16, 2015 at 3:19 PM, johnnyq72 notifications@github.com wrote:
Hi Marcin, Yes, this join could also work quite well, it would avoid any copying as the data is already there. Do we also have a sql_id child_number there?
I will reverse engineer my hack and then I can already test the view.
Cheers!
— Reply to this email directly or view it on GitHub https://github.com/pioro/orasash/issues/14#issuecomment-93746215.
Marcin Przepiorowski http://oracleprof.blogspot.com
johnnyq72
commented
9 years ago Hi Marcin,
I guess we can see what happens with a faked child cursor number, I guess it is of no great importance anyway.
Cheers, Jan
Would it be possible to add two more columns to v$sql?
It now obviously holds: Name Null Type
SQL_ID VARCHAR2(13)
COMMAND_TYPE NUMBER
SQL_TEXT VARCHAR2(4000)
I am looking to get moats running against ORA SASH and from what I gather v$sql is missing two columns: child_number plan_hash_value Rather than breaking moats, it would be worth to see if these two columns could be added.