Hello,
Any help would be much appreciated!! In prior versions of TOAD (I think I was using v10 maybe?), I saw good performance when using the “Copy data to another schema” feature… like 5000-10,000 rows/second. On the current version we are using (12.6.0.53), I just tried to copy data for the same table that had good performance in the past, and I’m seeing only a few rows per second and can tell it must be doing row-by-row inserts.
I dug into it and tried another table, to see if it was an issue with specific datatypes, and indeed it seems to be. The second table gave fast performance. Below are details. Please advise with any help. My only guess is that in the new version, perhaps it’s reverting to row-by-row inserts if the table has any TIMESTAMP columns? Similar to CLOB/LOB.
Here’s the table that runs SLOW:
CREATE TABLE D_CLARITY_RESOURCE
(
D_CLRTY_RSRC_IK NUMBER,
B_CLRTY_RSRC_IK NUMBER,
CLRTY_RSRC_ID VARCHAR2(20 CHAR),
CLRTY_RSRC_NM VARCHAR2(260 CHAR),
CLRTY_RSRC_TYP_ID VARCHAR2(80 CHAR),
CLRTY_RSRC_TYP_NM VARCHAR2(260 CHAR),
CLRTY_RSRC_ACTV_IN VARCHAR2(20 CHAR),
CLRTY_RSRC_INTRL_EXTL_IN VARCHAR2(20 CHAR),
SRC_CLRTY_RSRC_ID VARCHAR2(20 CHAR),
CRDS_SRC_DB_NM VARCHAR2(64 CHAR),
CRDS_SRC_TBL_NM VARCHAR2(64 CHAR),
CRDS_INS_TS TIMESTAMP(6),
CRDS_UPD_TS TIMESTAMP(6),
CRDS_DEL_TS TIMESTAMP(6),
CRDS_IUD_CD CHAR(1 CHAR),
CRDS_CHKSUM VARCHAR2(32 CHAR),
CRDS_REC_TYP_CD CHAR(1 CHAR),
CRDS_PRCS_CNTL_INS_ID NUMBER,
CRDS_PRCS_CNTL_UPD_ID NUMBER
)
TABLESPACE COBI_MEDIUM_DATA
RESULT_CACHE (MODE DEFAULT)
LOGGING
NOCOMPRESS
CACHE
NOPARALLEL
MONITORING;
Here’s the table that runs fast:
CREATE TABLE FMV_PTNT_RFRL_MM2
(
RCRD_SORT_NB NUMBER,
CLNDR_MM_IK NUMBER,
D_DMND_SDR_DEPT_RPT_IK NUMBER,
D_PTNT_RFRL_PRFL_IK NUMBER,
D_PTNT_RFRL_TYP_IK NUMBER,
D_SDR_PTNT_RFRL_TYP_IK NUMBER,
RFRD_SDR_SPCLTY_NM VARCHAR2(60 CHAR),
RFRD_SDR_SPCLTY_ASGMT_RULE_NM VARCHAR2(40 CHAR),
RCRD_CT NUMBER,
PTNT_RFRL_CT NUMBER,
PTNT_RFRL_ENCTR_UTLZTN_CT NUMBER,
PTNT_RFRL_ENCTR_CT NUMBER,
RFRL_APPT_BKD_LAG_DAY_CT NUMBER,
RFRL_APPT_LAG_DAY_CT NUMBER,
APPT_BKD_APPT_LAG_DAY_CT NUMBER,
CNT_RFRL_APPT_BKD_LAG_DAY_CT NUMBER,
CNT_RFRL_APPT_LAG_DAY_CT NUMBER,
CNT_APPT_BKD_APPT_LAG_DAY_CT NUMBER
)
TABLESPACE COBI_MEDIUM_DATA
RESULT_CACHE (MODE DEFAULT)
NOLOGGING
COMPRESS BASIC
NOCACHE
NOPARALLEL
MONITORING;