Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Parthasaradhi.

Asked: March 25, 2020 - 10:01 pm UTC

Last updated: March 26, 2020 - 3:25 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked


Following query was taking longer time.

1) We try to make markhot with following SQL

SYS.DBMS_SHARED_POOL.MARKHOT(hash=>e130457a4520f54c18acb0131777d76d,namespace=>0);


ERROR at line 1:
ORA-06550: line 1, column 42:
PLS-00114: identifier 'E130457A4520F54C18ACB0131777D7' too long


NOTE: Then we tried following to make it markhot(It is running more than 90 minutes)

2)


SET ECHO OFF
SET SERVEROUTPUT ON
DECLARE
BEGIN
FOR hash_id in (
select KGLNAHSV from (
select 
case when (kglhdadr =  kglhdpar) then 'Parent' else 'Child' ||kglobt09 end,
kglhdadr ADDRESS,substr(kglnaobj,1,20),
parsing_schema_name, kglnahsh, KGLNAHSV, kglobtyd, kglobt23, kglobt24, kglhdexc, kglhdnsp
from sys.v_x$kglob x$kglob, v$sql
where x$kglob.kglnahsh = v$sql.hash_value
and v$sql.parsing_schema_name='ADM'
and kglhdexc > 1
and kglobt24 > 1
and kglobtyd = 'CURSOR'
order by kglobt24 desc) where rownum < 7
    ) LOOP
DBMS_OUTPUT.PUT_LINE('hash=>'|| hash_id.KGLNAHSV ||',namespace=>0');
SYS.DBMS_SHARED_POOL.MARKHOT(hash=>hash_id.KGLNAHSV,namespace=>0);
END LOOP;
END;
/


Please suggest how we can proceed on this?

Thank you
-Partha


and Connor said...

The hash parameter is a string, so it would be

SYS.DBMS_SHARED_POOL.MARKHOT(hash=>'e130457a4520f54c18acb0131777d76d',namespace=>0);

but I would like to see your motivation for why you want to running this in the first place.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

MARKHOT

Partha, March 26, 2020 - 3:13 pm UTC

This object was not MARKHOT, too many hardparsing(Millions).

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database