Thanks for the question, Almir.
Asked: September 26, 2016 - 9:42 am UTC
Last updated: September 27, 2016 - 1:55 am UTC
Version: 12.1.0.4
Viewed 1000+ times
You Asked
After initial creation of user and import of db, queries were fast.
Used following commands:
" CREATE USER TESTUSR IDENTIFIED BY TESTUSR default tablespace TESTTAB quota unlimited on TESTTAB ACCOUNT UNLOCK;
GRANT CREATE SESSION TO TESTUSR;
GRANT CREATE TABLE TO TESTUSR;
GRANT CREATE VIEW TO TESTUSR;
GRANT CREATE SYNONYM TO TESTUSR;
GRANT CREATE SEQUENCE TO TESTUSR;
GRANT CREATE TRIGGER TO TESTUSR;
GRANT CREATE PROCEDURE TO TESTUSR;
GRANT CREATE CLUSTER TO TESTUSR;
GRANT UNLIMITED TABLESPACE TO TESTUSR;
GRANT SELECT ON sys.dba_pending_transactions to TESTUSR; "
" impdp TESTUSR/TESTUSR@IP/db01cd3 DUMPFILE=TESTPTP.dmp LOGFILE=import-test.log DIRECTORY=DATA_PUMP_DIR REMAP_SCHEMA=TESTUSR:TESTUSR REMAP_TABLESPACE=USERS:TESTTAB "
After droping the user and recreating the user and importing again:
" DROP USER TESTUSR CASCADE;
DROP USER TESTUSR CASCADE;
CREATE USER TESTUSR IDENTIFIED BY TESTUSR default tablespace TESTTAB quota unlimited on TESTTAB ACCOUNT UNLOCK;
GRANT CREATE SESSION TO TESTUSR;
GRANT CREATE TABLE TO TESTUSR;
GRANT CREATE VIEW TO TESTUSR;
GRANT CREATE SYNONYM TO TESTUSR;
GRANT CREATE SEQUENCE TO TESTUSR;
GRANT CREATE TRIGGER TO TESTUSR;
GRANT CREATE PROCEDURE TO TESTUSR;
GRANT CREATE CLUSTER TO TESTUSR;
GRANT UNLIMITED TABLESPACE TO TESTUSR;
GRANT SELECT ON sys.dba_pending_transactions to TESTUSR; "
" impdp TESTUSR/TESTUSR@IP/db01cd3 DUMPFILE=TESTPTP.dmp LOGFILE=import-test.log DIRECTORY=DATA_PUMP_DIR REMAP_SCHEMA=TESTUSR:TESTUSR REMAP_TABLESPACE=USERS:TESTTAB "
Queries got extremely slow, I have done the statistics but it didn't help.
Other information:
OS: RHEL 6.7
and Connor said...
*Something* has changed, and since you've unloaded/reloaded everything there could all sorts of reasons for that, eg data distribution, statistics, etc.
When you use expdp/impdp you are basically creating a brand new version of everything.
If you are looking to replicate a database from one environment to the other and keep everything the same, you would be better to use rman duplicate.
So to solve your problem, now its case of normal troubleshooting - look for the worst queries, examine the execution plans etc. Alternatively, if you still have access to the source environment, look at using sql plan baselines to extract all the good plans and utilising them in the target environment. But I stress - because all of the data has been reloaded, those plans might *not* be the best plans anymore.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment