Re: Cloud Control 13c & Oracle 11 Databases
Date: Tue, 19 Feb 2019 15:05:32 -0500
Message-Id: <5C6C618C0200000B000523C3_at_groupwise2014.gcrta.org>
Take a look at EM13c : A Recurring Query In 11.2.0.4 Database has
performance issues (SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS) (Doc ID
2235163.1)
>>> Scott Canaan <srcdco_at_rit.edu> 2/19/19 2:26 PM >>>
We are in the process of upgrading to Cloud Control 13c. In the
process of testing the agent deployment, an issue has come up with
Oracle 11g databases (11.2.0.4). Every 15 minutes the database runs out
of temp space. I’ve increased temp from a maximum of 750M to 1G. The
offending SQL is from Cloud Control. It is:
SELECT MEDIA
FROM V$BACKUP_PIECE_DETAILS
WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 AND
DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1;
The explain plan for this is a mess. Unfortunately, it ends up
referencing a bunch of X$ tables in the SYS schema that I can’t get to
show up in TOAD.
Cloud Control 12c doesn’t have this problem. Oracle 12c and 18c databases also don’t have this problem. They have a maximum temp file size of 750M and haven’t used more than 250M at any one time.
In looking at the script for the v$backup_piece_details view, I see that there is a difference. The 12c version adds “bequeath definer” before the select.
The question is this: should I change the view in the 11g databases, or just allow the temp to grow as big as it needs, or ignore the errors until we upgrade the 11g databases?
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu | c:(585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 19 2019 - 21:05:32 CET