Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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