Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Parthasaradhi.

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

Answered by: Connor McDonald - Last updated: March 26, 2020 - 3:25 am UTC

Category: Database Administration - Version: 12.1.0.2

Viewed 100+ 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 we 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.

and you rated our response

  (1 rating)

Reviews

MARKHOT

March 26, 2020 - 3:13 pm UTC

Reviewer: Partha from VA, USA

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