Disclaimer

Wednesday, 6 April 2022

Oracle 1Z0-082 and 1Z0-083 Certification Exams Syllabus

 

Oracle 1Z0-082 Exam Details:

Exam NameOracle Database Administration I
Exam Code1Z0-082
Exam PriceUSD $245 (Pricing may vary by country or by localized currency)
Duration150 minutes
Number of Questions90
Passing Score60%
FormatMultiple Choice Questions (MCQ)
Recommended TrainingOracle Database 19c: SQL Workshop
Oracle Database 19c: Administration Workshop
Oracle Database Administration 2019 Certified Professional
Oracle Database Learning Subscription
Unlimited Learning Subscription - All Technology
Schedule ExamPearson VUE
Sample QuestionsOracle Database Administration 2019 Certified Professional (OCP)
Recommended Practice1Z0-082 Online Practice Exam

Oracle 1Z0-082 Syllabus Topics:

Understanding Oracle Database Architecture - Oracle Database: Administration Workshop- Understanding Oracle Database Instance Configurations
- Understanding Oracle Database Memory and Process Structures
- Understanding Logical and Physical Database Structures
- Understanding Oracle Database Server Architecture
Managing Database Instances - Oracle Database: Administration Workshop
- Starting Up Oracle Database Instances
- Using Data Dictionary Views
- Shutting Down Oracle Database Instances
- Using Dynamic Performance Views
- Using the Automatic Diagnostic Repository (ADR)
- Using the Alert Log and Trace Files
- Managing Initialization Parameter Files
Managing Users, Roles and Privileges - Oracle Database: Administration Workshop- Assigning Quotas to Users
- Applying the Principal of Least Privilege
- Creating and Assigning Profiles
- Administering User Authentication Methods
- Managing Oracle Database Users, Privileges, and Roles
Managing Storage - Oracle Database: Administration Workshop
- Managing Resumable Space Allocation
- Shrinking Segments
- Deferring Segment Creation
- Using Space-Saving Features
- Deploying Oracle Database Space Management Features
- Managing Different Types of Segments
- Using Table and Row Compression
- Understanding Block Space Management
Moving Data - Oracle Database: Administration Workshop- Using External Tables
- Using Oracle Data Pump
- Using SQL*Loader
Accessing an Oracle Database with Oracle supplied Tools - Oracle Database: Administration Workshop
- Using the Database Configuration Assistant (DBCA)
- Using Oracle Enterprise Manager Cloud Control
- Using racle enterprise Manager Database Express
- Using SQL Developer
- Using SQL Plus
Configuring Oracle Net Services - Oracle Database: Administration Workshop- Using Oracle Net Services Administration Tools
- Configuring Communication Between Database Instances
- Configuring the Oracle Net Listener
- Connecting to an Oracle Database Instance
- Comparing Dedicated and Shared Server Configurations
- Administering Naming Methods
Managing Tablespaces and Datafiles - Oracle Database: Administration Workshop
- Viewing Tablespace Information
- Creating, Altering and Dropping Tablespaces
- Managing Table Data Storage
- Implementing Oracle Managed Files
- Moving and Renaming Online Data Files
Managing Undo - Oracle Database: Administration Workshop- Understanding Transactions and Undo Data
- Storing Undo Information
- Configuring Undo Retention
- Comparing Undo Data and Redo Data
- Understanding Temporary Undo
Restricting and Sorting Data - Oracle Database: Introduction to SQL
- Applying Rules of precedence for operators in an expression
- Limiting Rows Returned in a SQL Statement
- Using Substitution Variables
- Using the DEFINE and VERIFY commands
Using Conversion Functions and Conditional Expressions - Oracle Database: Introduction to SQL- Applying the NVL, NULLIF, and COALESCE functions to data
- Understanding implicit and explicit data type conversion
- Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
- Nesting multiple functions
Displaying Data from Multiple Tables Using Joins - Oracle Database: Introduction to SQL
- Using Self-joins
- Using Various Types of Joins
- Using Non equijoins
- Using OUTER joins
Using SET Operators - Oracle Database: Introduction to SQL- Matching the SELECT statements
- Using the ORDER BY clause in set operations
- Using The INTERSECT operator
- Using The MINUS operator
- Using The UNION and UNION ALL operators
Understanding Data Definition Language - Oracle Database: Introduction to SQL- Using Data Definition Language
Managing Views - Oracle Database: Introduction to SQL- Managing Views
Managing Data in Different Time Zones - Oracle Database: Introduction to SQL- Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP
- Working with INTERVAL data types
Retrieving Data using the SQL SELECT Statement - Oracle Database: Introduction to SQL- Using Column aliases
- Using The DESCRIBE command
- Using The SQL SELECT statement
- Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
- Using Arithmetic expressions and NULL values in the SELECT statement
Using Single-Row Functions to Customize Output - Oracle Database: Introduction to SQL
- Manipulating strings with character functions in SQL SELECT and WHERE clauses
- Performing arithmetic with date data
- Manipulating numbers with the ROUND, TRUNC and MOD functions
- Manipulating dates with the date function
Reporting Aggregated Data Using Group Functions - Oracle Database: Introduction to SQL- Restricting Group Results
- Creating Groups of Data
- Using Group Functions
Using Subqueries to Solve Queries - Oracle Database: Introduction to SQL
- Using Single Row Subqueries
- Using Multiple Row Subqueries
Managing Tables using DML statements - Oracle Database: Introduction to SQL- Managing Database Transactions
- Using Data Manipulation Language
- Controlling transactions
Managing Sequences, Synonyms, Indexes - Oracle Database: Introduction to SQL
- Managing Indexes
- Managing Synonyms
- Managing Sequences
Managing Schema Objects - Oracle Database: Introduction to SQL- Creating and using temporary tables
- Managing constraints

The Oracle Database Administration I Certification Program certifies candidates on skills and knowledge related to Oracle Database Administration products and technologies. The Oracle 1Z0-082 is granted based on a combination of passing exams, training, and performance-based assignments, depending on the level of certification. Oracle Database Administration I certification is a real benchmark of experience and expertise that helps you stand out in a crowd among employers. To ensure success, Oracle recommends combining education courses, practice exams, and hands-on experience to prepare for your Oracle Database Administration 2019 Certified Professional (OCP) certification exam as questions will test your ability to apply the knowledge you have gained in hands-on practice or professional experience.



Oracle 1Z0-083 Exam Details:

Exam NameOracle Database Administration II
Exam Code1Z0-083
Exam PriceUSD $245 (Pricing may vary by country or by localized currency)
Duration150 minutes
Number of Questions85
Passing Score57%
FormatMultiple Choice Questions (MCQ)
Recommended TrainingOracle Database: Managing Multitenant Architecture
Oracle Database: Backup and Recovery Workshop
Oracle Database: Deploy, Patch and Upgrade Workshop Ed 1
Oracle Database: Administration Workshop
Oracle Database 19c: New Features for Administrators Ed 1
Oracle Database Learning Subscription
Unlimited Learning Subscription - All Technology
Schedule ExamPearson VUE
Sample QuestionsOracle Database Administration 2019 Certified Professional (OCP)
Recommended Practice1Z0-083 Online Practice Exam

Oracle 1Z0-083 Syllabus Topics:

Creating CDBs and Regular PDBs - Oracle Database: Managing Multitenant Architecture Ed 1- Configure and create a CDB
- Create a new PDB from the CDB seed
- Explore the structure of PDBs
Manage CDBs and PDBs - Oracle Database: Managing Multitenant Architecture Ed 1
- Manage PDB service names and connections
- Manage startup, shutdown and availability of CDBs and PDBs
- Change the different modes and settings of PDBs
- Evaluate the impact of parameter value changes
- Performance management in CDBs and PDBs
- Control CDB and PDB resource usage with the Oracle Resource Manager
Backup and Duplicate - Oracle Database: Managing Multitenant Architecture Ed 1- Perform Backup and Recover CDBs and PDBs
- Duplicate an active PDB
- Duplicate a Database
Upgrading and Transporting CDBs and Regular PDBs - Oracle Database: Managing Multitenant Architecture Ed 1
- Upgrade an Oracle Database
- Transport Data
Manage Application PDBs - Oracle Database: Managing Multitenant Architecture Ed 1- Explain the purpose of application root and application seed
- Define and create application PDBs
- Install, upgrade and Patch applications
- Create and administer Application PDBS
- Clone PDBs and Application containers
- Plug and unplug operations with PDBs and application containers
- Comparing Local Undo Mode and Shared Undo Mode
Manage Security in Multitenant databases - Oracle Database: Managing Multitenant Architecture Ed 1
- Manage Security in Multitenant databases
- Manage PDB lockdown profiles
- Audit Users in CDBs and PDBs
- Manage other types of policies in application containers
Recovery and Flashback - Oracle Database: Managing Multitenant Architecture Ed 1- Restore and Recovering Databases with RMAN
- Perform CDB and PDB flashback
Backup Strategies and Terminology - Oracle Database: Backup and Recovery Workshop- Perform Full and Incremental Backups and Recoveries
- Compress and Encrypt RMAN Backups
- Use a media manager
- Create multi-section backups of very large files
- Create duplexed backup sets
- Create archival backups
- Backup of recovery files
- Backup non database files
- Back up ASM meta data
Restore and Recovery Concepts - Oracle Database: Backup and Recovery Workshop- Employ the best Oracle Database recovery technology for your failure situation
- Describe and use Recovery technology for Crash, Complete, and Point-in-time recovery
Using Flashback Technologies - Oracle Database: Backup and Recovery Workshop- Configure your Database to support Flashback
- Perform flashback operations
Duplicating a Database - Oracle Database: Backup and Recovery Workshop- Duplicate Databases
Configuring and Using RMAN - Oracle Database: Backup and Recovery Workshop
- Configure RMAN and the Database for Recoverability
- Configure and Using an RMAN recovery catalog
Diagnosing Failures - Oracle Database: Backup and Recovery Workshop- Detect and repair database and database block corruption
- Diagnosing Database Issues
Performing Recovery - Oracle Database: Backup and Recovery Workshop
- Restore and Recovering Databases with RMAN
- Perform Non RMAN database recovery
Transporting Data - Oracle Database: Backup and Recovery Workshop- Transport Data
RMAN Troubleshooting and Tuning - Oracle Database: Backup and Recovery Workshop
- Interpret the RMAN message output
- Diagnose RMAN performance issues
Install Grid Infrastructure and Oracle Database - Oracle Database: Deploy, Patch and Upgrade Workshop- Install Grid Infrastructure for a Standalone server
- Install Oracle Database software
Patching Grid Infrastructure and Oracle Database - Oracle Database: Deploy, Patch and Upgrade Workshop- Patch Grid Infrastructure and Oracle Database
Upgrading to Oracle Grid Infrastructure - Oracle Database: Deploy, Patch and Upgrade Workshop- Upgrade Oracle Grid Infrastructure
Oracle Database 18c: New Features - Oracle Database: Deploy, Patch and Upgrade Workshop- Image and RPM based Database Installation
Creating an Oracle Database by using DBCA - Oracle Database: Deploy, Patch and Upgrade Workshop- Create, Delete and Configure Databases using DBCA
Oracle Restart - Oracle Database: Deploy, Patch and Upgrade Workshop- Configure and use Oracle Restart to manage components
Upgrade the Oracle Database - Oracle Database: Deploy, Patch and Upgrade Workshop
- Plan for Upgrading an Oracle Database
- Upgrade an Oracle Database
- Perform Post-Upgrade tasks
Install Grid Infrastructure for a Standalone server - Oracle Database: Deploy, Patch and Upgrade Workshop- Rapid Home Provisioning
Using General Overall Database Enhancements - Oracle Database 19c: New Features for Administrators
- Install Oracle Database software
- Create, Delete and Configure Databases using DBCA
- Creating CDBs and Regular PDBs
- Use Miscellaneaous 19c New Features
Using Availability Enhancements - Oracle Database 19c: New Features for Administrators- Use an RMAN recovery catalog
- Use Flashback Database
Using Diagnosibility Enhancements - Oracle Database 19c: New Features for Administrators- Use new Diagnoseability Features
Monitoring and Tuning Database Performance - Oracle Database: Administration Workshop- Managing Memory Components
- Understanding The Automatic Workload Repository (AWR)
- Understanding The Advisory Framework
- Monitoring Wait Events, Sessions, and Services
- Managing Metric Thresholds and Alerts
- Understanding and Using The Performance Tuning Methodology
- Performing Performance Planning
- Understanding The Automatic Database Diagnostic Monitor (ADDM)
Tuning SQL Statements - Oracle Database: Administration Workshop- Understanding The Oracle Optimizer
- Using The SQL Tuning Advisor
- Managing Optimizer Statistics
- Using The SQL Access Advisor
- Understanding The SQL Tuning Process

The Oracle Database Administration II Certification Program certifies candidates on skills and knowledge related to Oracle Database Administration products and technologies. The Oracle 1Z0-083 is granted based on a combination of passing exams, training, and performance-based assignments, depending on the level of certification. Oracle Database Administration II certification is a real benchmark of experience and expertise that helps you stand out in a crowd among employers. To ensure success, Oracle recommends combining education courses, practice exams, and hands-on experience to prepare for your Oracle Database Administration 2019 Certified Professional (OCP) certification exam as questions will test your ability to apply the knowledge you have gained in hands-on practice or professional experience.

Poor
Okay
Good
Great
Awesome
at No comments:

Thursday, 3 March 2022

Optimizer Adaptive feature parameter in Oracle

Oracle optimizer is used to find the most effective execution plan for each SQL statement. Oracle released adaptive feature in Oracle 12c.

Adaptive Query Optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. 
This new approach is extremely helpful when existing statistics are not sufficient to generate an optimal plan. 

Adaptive query optimization means during runtime of SQL statement find better execution plan with adjust statistics.

There are two distinct aspects in Adaptive Query Optimization:

Adaptive plans: focuses on improving the execution of a query

Adaptive statistics: uses additional information to improve query execution plans.





Following are the parameter as optimizer adaptive plan and optimizer adaptive statistics.

SQL> show parameter adaptive

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
optimizer_adaptive_plans             boolean      TRUE
optimizer_adaptive_reporting_only    boolean      FALSE
optimizer_adaptive_statistics        boolean      FALSE
parallel_adaptive_multi_user         boolean      FALSE

For Enable and Disable Adaptive features
In Oracle 12101 version only one parameter defines for adaptive but in 12102 version use two parameter as adaptive:

-- Enable
In 12101 version:
alter system set optimizer_adaptive_features=true scope=both;

In 12102 onward:
alter system set optimizer_adaptive_plans=true scope=both;
alter system set optimizer_adaptive_statistics=true scope=both;

-- Disable
In 12101 version:
alter system set optimizer_adaptive_features=false scope=both;

In 12102 onward:
alter system set optimizer_adaptive_plans=false scope=both;
alter system set optimizer_adaptive_statistics=false scope=both;

1) OPTIMIZER_ADAPTIVE_PLANS
It used by query optimizer for considers adaptive plans. If it’s set to TRUE (the default), the feature is enabled.
Following are the hidden parameter used by adaptive plans:
_OPTIMIZER_NLJ_HJ_ADAPTIVE_JOIN:
It is used the adaptive join methods. If it’s set to TRUE (the default), the feature is enabled.


_PX_ADAPTIVE_DIST_METHOD:
It controls the adaptive parallel distribution methods. If it’s set to CHOOSE (the default), the feature is enabled. If it’s set to OFF, the feature is disabled.

_OPTIMIZER_STRANS_ADAPTIVE_PRUNING:
It will controls the adaptive star transformations. If it’s set to TRUE (the default), the feature is enabled.

2) OPTIMIZER_ADAPTIVE_STATISTICS
It used by query optimizer for considers adaptive statistics, It is set to TRUE then enabled, FALSE (DEFAULT)

Following are the hidden parameter used by adaptive statistics:
_OPTIMIZER_DSDIR_USAGE_CONTROL
It controls whether the query optimizer uses SQL plan directives. If it’s set to 126 (the default), the feature is enabled.

If it’s set to 0, the feature is disabled. This parameter also supports other integer values, but I never spent time investigating their behavior.

_OPTIMIZER_GATHER_FEEDBACK
It controls whether the query optimizer asks the execution engine to check for mis-estimates then create feedback, If it’s set to TRUE (the default), the feature is enabled.


_OPTIMIZER_USE_FEEDBACK
It controls whether the query optimizer choose to use the feedback’s and produced more execution plan for compare. If it’s set to TRUE (the default), the feature is enabled.
_sql_plan_directive_mgmt_control
O means disable creation of directives, 3 value means enable creation of directives.

Check discription of these parameter

select ksppinm,ksppdesc from x$ksppi where ksppinm in ('_optimizer_nlj_hj_adaptive_join', '_optimizer_strans_adaptive_pruning',
'_px_adaptive_dist_method', '_sql_plan_directive_mgmt_control', '_optimizer_dsdir_usage_control', '_optimizer_use_feedback', '_optimizer_gather_feedback', '_optimizer_performance_feedback');

Change the adaptive parameter inner functionality with hidden parameters

-- Backup of the parameter
COLUMN ksppinm FORMAT A40
COLUMN ksppstvl FORMAT A20
SELECT ksppinm,ksppstvl FROM x$ksppi a,x$ksppsv b WHERE a.indx=b.indx
AND ksppinm in ('_optimizer_nlj_hj_adaptive_join', '_optimizer_strans_adaptive_pruning', '_px_adaptive_dist_method', '_sql_plan_directive_mgmt_control', '_optimizer_dsdir_usage_control',
'_optimizer_use_feedback', '_optimizer_gather_feedback', '_optimizer_performance_feedback') ORDER BY ksppinm;

KSPPINM                                KSPPDESC
-------------------------------------- -------------------------------------------------------------
_optimizer_use_feedback                optimizer use feedback
_optimizer_gather_feedback             optimizer gather feedback
_sql_plan_directive_mgmt_control       controls internal SQL Plan Directive management activities
_px_adaptive_dist_method               determines the behavior of adaptive distribution methods
_optimizer_dsdir_usage_control         controls optimizer usage of dynamic sampling directives
_optimizer_strans_adaptive_pruning     allow adaptive pruning of star transformation bitmap trees
_optimizer_performance_feedback        controls the performance feedback
_optimizer_nlj_hj_adaptive_join        allow adaptive NL Hash joins


KSPPINM                                  KSPPSTVL
---------------------------------------- --------------------
_optimizer_dsdir_usage_control           0
_optimizer_gather_feedback               TRUE
_optimizer_nlj_hj_adaptive_join          TRUE
_optimizer_performance_feedback          OFF
_optimizer_strans_adaptive_pruning       TRUE
_optimizer_use_feedback                  TRUE
_px_adaptive_dist_method                 CHOOSE
_sql_plan_directive_mgmt_control         67

--Disable the parameters
alter system set "_optimizer_nlj_hj_adaptive_join"= FALSE scope=both;
alter system set "_optimizer_strans_adaptive_pruning"= FALSE scope=both;
alter system set "_px_adaptive_dist_method" = OFF scope=both;
alter system set "_sql_plan_directive_mgmt_control" = 0 scope=both;
alter system set "_optimizer_dsdir_usage_control" = 0 scope=both;
alter system set "_optimizer_use_feedback" = FALSE scope=both;
alter system set "_optimizer_gather_feedback" = FALSE scope=both;
alter system set "_optimizer_performance_feedback" = OFF scope=both;

-- Reset the parameter
alter system reset "_optimizer_nlj_hj_adaptive_join" scope=both;
alter system reset "_optimizer_strans_adaptive_pruning" scope=both;
alter system reset "_px_adaptive_dist_method" scope=both;
alter system reset "_sql_plan_directive_mgmt_control" scope=both;
alter system reset "_optimizer_dsdir_usage_control" scope=both;
alter system reset "_optimizer_use_feedback" scope=both;
alter system reset "_optimizer_gather_feedback" scope=both;
alter system reset "_optimizer_performance_feedback" scope=both;

You can also use the parameter as hints:

select /*+ opt_param('_optimizer_use_feedback' 'false') */ * from table;













at No comments:
Labels:

Oracle 19c Import with disable_archive_logging

Oracle 19c Import with disable_archive_logging Tips

Oracle19c feature is the ability to run in nologging mode in import execution, disabling archive logging during a large import. Only for table and index Not for tablespace.

    transform=disable_archive_logging:Y
    transform=disable_archive_logging:Y:tablename
    transform=disable_archive_logging:Y:indexname

Test

Create directory and user;

SYS@orcl> create directory imp_home as '/home/oracle';

Directory created.

SYS@orcl> 
SYS@orcl> show parameter user_pre

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix                   string      C##

SYS@orcl> create user c##lin identified by oracle;

User created.

SYS@orcl> grant dba to c##lin;

Grant succeeded.

SYS@orcl>

Generate data

SYS@orcl> conn c##lin/oracle
Connected.
C##LIN@orcl>
C##LIN@orcl> select count(*) from dba_objects;

  COUNT(*)
----------
     72549

C##LIN@orcl> select count(*) from cdb_objects;

  COUNT(*)
----------
     72549

C##LIN@orcl> create table imp_test as select * from dba_objects;

Table created.

C##LIN@orcl> insert into imp_test (select * from imp_test);

72549 rows created.

C##LIN@orcl> /

145098 rows created.

C##LIN@orcl> /

290196 rows created.

C##LIN@orcl> /

580392 rows created.

C##LIN@orcl> /

1160784 rows created.

C##LIN@orcl> commit;

Commit complete.

C##LIN@orcl>

C##LIN@orcl> col segment_name for a20
C##LIN@orcl> select bytes/1024/1024  mb, segment_name, segment_type from user_segments;

        MB SEGMENT_NAME         SEGMENT_TYPE
---------- -------------------- ------------------
       360 IMP_TEST             TABLE

Export the test data;


C##LIN@orcl> !expdp \"/as sysdba\" directory=imp_home dumpfile=imp_test.dmp logfile=imp_test.log schemas="C##LIN"

Export: Release 19.0.0.0.0 - Production on Thu Aug 22 14:03:58 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production


Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=imp_home dumpfile=imp_test.dmp logfile=imp_test.log schemas=C##LIN
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "C##LIN"."IMP_TEST"                         305.8 MB 2321568 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/imp_test.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Aug 22 14:07:32 2019 elapsed 0 00:03:10


C##LIN@orcl> 
C##LIN@orcl> !ls -ltr /home/oracle/imp_test.dmp
-rw-r-----. 1 oracle oinstall 321089536 Aug 22 14:07 /home/oracle/imp_test.dmp

C##LIN@orcl> !du -sm /home/oracle/imp_test.dmp
307     /home/oracle/imp_test.dmp

Test import transform_enable mode: redo log from 724mb to 1081mb / elapsed 0 00:02:20


C##LIN@orcl> drop table c##lin.imp_test purge;

Table dropped.

C##LIN@orcl> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;

NAME                                                                     MB
---------------------------------------------------------------- ----------
redo size                                                        724.179874

C##LIN@orcl> !impdp \"/as sysdba\" directory=imp_home dumpfile=imp_test.dmp logfile=transform_disable.log schemas="C##LIN"

Import: Release 19.0.0.0.0 - Production on Thu Aug 22 14:22:11 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=imp_home dumpfile=imp_test.dmp logfile=transform_disable.log schemas=C##LIN
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "C##LIN"."IMP_TEST"                         305.8 MB 2321568 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Aug 22 14:24:39 2019 elapsed 0 00:02:20


C##LIN@orcl> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;

NAME                                                                     MB
---------------------------------------------------------------- ----------
redo size                                                        1081.06575

C##LIN@orcl>


Test import transform_disable mode: redo log from 1081mb to 1084mb / elapsed 0 00:01:28


C##LIN@orcl> drop table c##lin.imp_test purge;

Table dropped.

C##LIN@orcl> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;

NAME                                                                     MB
---------------------------------------------------------------- ----------
redo size                                                        1081.18863

C##LIN@orcl>
C##LIN@orcl> !impdp \"/as sysdba\" directory=imp_home dumpfile=imp_test.dmp logfile=transform_disable.log schemas="C##LIN" transform=disable_archive_logging:Y

Import: Release 19.0.0.0.0 - Production on Thu Aug 22 14:28:48 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=imp_home dumpfile=imp_test.dmp logfile=transform_disable.log schemas=C##LIN transform=disable_archive_logging:Y
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "C##LIN"."IMP_TEST"                         305.8 MB 2321568 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Aug 22 14:30:23 2019 elapsed 0 00:01:28


C##LIN@orcl> select name, value/1024/1024 mb from v$sysstat where name ='redo size' ;

NAME                                                                     MB
---------------------------------------------------------------- ----------
redo size                                                        1084.21175

C##LIN@orcl>


The Result of importing with transform/disable_archive_logging


Datatransform(archive_logging)Generated Redo log sizeImport elapsed
360MBenable(default)357 MB02:20
360MBdisable3.1 MB01:28





at No comments:
Labels:

SQL Analyze in AWR report - 19c

Oracle 19c Analysis SQL in AWR report

There are some sql in SQL ordered by CPU Time AWR report.

These sql were using so many cpu time…








/* SQL Analyze(0) */ select /*+ full(t) parallel(t, 6) parallel_index(t, 6) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */to_char...


What are these sqls? what are they doing?

Simulate test

LIN@pdb1> drop table lin.test_tab purge;

Table dropped.

LIN@pdb1> create table lin.test_tab(col1 number, col2 varchar(20));

Table created.

LIN@pdb1>
begin
 for i in 1..10000 loop
 insert into lin.test_tab values (i, i||'aaaabbbb');
 end loop;
 commit;
end;
  7  /

PL/SQL procedure successfully completed.

LIN@pdb1> select count(*) from lin.test_tab;

  COUNT(*)
----------
     10000

LIN@pdb1>
LIN@pdb1> conn sys/oracle@pdb1 as sysdba
Connected.
SYS@pdb1> alter session set max_dump_file_size = UNLIMITED;

Session altered.

SYS@pdb1> alter session set events '10046 trace name context forever, level 12';

Session altered.

SYS@pdb1> exec DBMS_STATS.GATHER_TABLE_STATS('LIN','TEST_TAB');

PL/SQL procedure successfully completed.

SYS@pdb1> alter session set events '10046 trace name context off';

Session altered.

SYS@pdb1>
SYS@pdb1> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17733.trc

SYS@pdb1> !ls -ltr /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17733.trc
-rw-r-----. 1 oracle oinstall 520812 Feb 19 09:12 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17733.trc

SYS@pdb1>

the /* SQL Analyze(0) */ sql can be found in the trace file

PARSING IN CURSOR #139867558904224 len=513 dep=1 uid=126 oct=3 lid=0 tim=16068278669 hv=2609264150 ad='71e71608' sqlid='1391vrydscdhq'
/* SQL Analyze(0) */ select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad  */to_char(count("COL1")),substrb(dump(min("COL1"),16,0,64),1,240),substrb(dump(max("COL1"),16,0,64),1,240),to_char(count("COL2")),substrb(dump(min("COL2"),16,0,64),1,240),substrb(dump(max("COL2"),16,0,64),1,240) from "LIN"."TEST_TAB" t  /* NDV,NIL,NIL,NDV,NIL,NIL*/


Result

the sql - /* SQL Analyze(0) */ is similar with the internal sql which are from using DBMS_STATS package to gather statistics with objects

Maybe happened in mid-night, there are some batch job doing insert append sql and gather statistics with objects online.


at No comments:
Labels:
Subscribe to: Posts (Atom)

How to Install Oracle Enterprise Manager 24-AI - OEM

Table of Contents Why OEM 24ai? Step 1: Prepare Your Oracle 19c Database Step 2: Verify and Tune Database Parameters Step 3: Patch the Datab...