Oracle自治数据库和自动化运维新特性概述

1、Oracle自治数据库和自动化运维新特性概述技术创新，变革未来目录Oracle自治数据库Oracle自动化运维特性19x 自动索引19c 自动large page20c AUTO MLPart1 Oracle自治数据库Oracle自治数据库Oracle Database 18c / 19cDozens of automated database features+Oracle Cloud infrastructureSingle, standard platform as focus for self-driving automation+Policy-Driven AutomationAut

2、omation for optimal configuration for each workloadOracle自治数据库ADW 自动化的数据仓库ATP 更简单、更快速的自治事务处理AJD 集成了ORACLE多种特性的JSON DatabaseADWATPAJD主要特征快速的复杂分析快速的事务交易用于json数据数据格式列存行存数据处理自动并行串行压缩自动HCC压缩默认不压缩优化器hint默认忽略使用hintOracle自治数据库自治驱动自动供应、监控、备份、恢 复、调优、扩展和升级数据 库自治安全自动化数据保护和安全性， 自动应用安全补丁自治修复自动化故障检测、故障转移和 修复，实例数据库

3、最大高可用使用机器学习加上积累的技术实现低成本、低风险、简单的关键维护Oracle自治数据库一个自治数据库两种部署选择公有云Severless按需购买使用非常简单私有云客户定制的专用私有云Exadata CloudCustomerOracle自治数据库Oracle自治数据库Oracle自治数据库MAA进化Part2 Oracle自动化运维特性Oracle自动化运维特性Automatic Query RewriteAutomatic Undo Management9i10g11g18c19c20cAutomatic Memory Management Automatic Segment Spac

4、e Mgmt Automatic Statistics Gathering Automatic Storage Management Automatic Workload Repository Automatic Diagnostic MonitorAutomatic SQL TuningAutomatic Workload Capture/Replay Automatic SQL Plan Management Automatic Capture of SQL Monitor Automatic Data Optimization12cAutonomous Health Framework

5、Automatic Diagnostic Framework Automatic Refresh of ClonesAutomatic Columnar Flash Automatic IM population Automatic Application ContinuityAutomatic Indexing Automatic ADG DML redirectAutomatic Flashback Of StandbyAutomatic MLOracle投入了大量工作在自动化和优化 数据库上zhangweizhaoOracle 自动化索引DEV: 应该在哪些列创建索引? 每列创建行不？O

6、racle 自动化索引自动索引使用了哪些技术？Automatic SQL tuning setsSQL Performance AnalyzerIndex VisibilitySQL Plan Managementcreate index ANBOB.SYS_AI_XXXXXX ON ANBOB.T1(ID) TABLESPACE USERS UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW ONLINE;Oracle 自动化索引自动索引创建的 “debugging”DBACDB_AUTO_INDEX_VERIFICATIONS 列出自动索引的PLAN

7、_HASH_VALUE,和BUFFER_GETS对比等DBA_AUTO_INDEX_IND_ACTIONS- 列出索引创建的时间、SQLTo trace fileupdate SMB$CONFIG set_AUTO_INDEX_TRACE=2- Or -call dbms_auto_index_internal.configure(_AUTO_INDEX_TRACE,2,true,true)Oracle 自动化索引自动索引创建错误DBA_ADVISOR_FINDINGSJOIN DBA_ADVISOR_EXECUTIONS USING EXECUTION_NAME Where type=ERR

8、OROracle 自动化索引自动索引删除Drop secondary indexesSQL exec sys.dbms_auto_index.drop_secondary_indexes(SCOTT,EMP);Cant drop an auto index manual “_optimizer_use_auto_indexes”=OFF19c自动大页HugePage 是Linux平台上oracle建议的配置，USE_LARGE_PAGES是oracle共享内存使用大页 相关的参数。USE_LARGE_PAGES有效值True: 默认值，如果操作系统分配hugepage可用，尝试使用hugepa

9、ge False: 不使用hugepageOnly:如是hugepage不能用于整个实例所需要共享内存，将启动失败Auto: 一种可能值，12.2 如果可能使用在启动时尝试调用linux kernel增加hugepagesAuto-only: auto+ only 19c引入，Exadata环境默认值19c自动大页rootoel7db1 # cat /proc/meminfo3765384 kB3557852 kB3623672 kB2108 kB107400 kB0 kB 8704 kB 28048 kB 15932 kB 4432 kBMemTotal: MemFree: MemAvail

10、able: Buffers: Cached: SwapCached: Shmem:Slab: SReclaimable: PageTables:.CmaFree: HugePages_Total: HugePages_Free: HugePages_Rsvd: HugePages_Surp:0 kB0000SQL alter system set memory_target=0; System altered.SQL show parameter sgaPARAMETER_NAMETYPEVALUE allow_group_access_to_sgabooleanbooleanFALSE FA

11、LSEbooleanTRUElock_sga pre_page_sga sga_max_size sga_min_size sga_targetbig integer 668Mbig integer 0 big integer 668Munified_audit_sga_queue_sizeinteger104857619c自动大页SQL alter system set use_large_pages=AUTO_ONLY scope=spfile;System altered.SQL shut immediate;Starting ORACLE instance (normal) (OS i

12、d: 3567)* Sys-V shared memory will be used for creating SGA* DISM started, OS id=3579*SQL startup mount ORACLE instance started.Dump of system resources acquired for SHARED GLOBAL AREA (SGA)Per process system memlock (soft) limit = 128G Expected per process system memlock (soft) limit to lockTotal S

13、ystem Global Area700445640bytesinstance MAX SHARED GLOBAL AREA (SGA) into memory: 672MFixed Size9139144bytesAvailable system pagesizes:Variable Size419430400bytes4K, 2048KDatabase Buffers268435456 bytes Redo Buffers3440640 bytes Database mounted.Supported system pagesize(s):PAGESIZE AVAILABLE_PAGES

14、EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)2048K336336336NONEReason for not supporting certain system pagesizes: 4K - Large pagesizes only*19c自动大页$cat /proc/meminfoMemTotal: 3765384 kBMemFree: 2246868 kBMemAvailable: 2721796 kBBuffers: 2108 kBCached: 515388 kBShmem: 8800 kBHardwareCorrupted: 0 kBAnonHug

15、ePages: 0 kBShmemHugePages: 0 kBShmemPmdMapped: 0 kBCmaTotal: 0 kBCmaFree: 0 kBHugePages_Total: 336HugePages_Free: 3HugePages_Rsvd: 3HugePages_Surp: 0Hugepagesize: 2048 kB$sysctl -a|grep page vm.hugepages_treat_as_movable = 0vm.nr_hugepages = 336vm.nr_hugepages_mempolicy = 336vm.nr_overcommit_hugepa

16、ges = 0vm.cluster = 3oracleoel7db1 dbs$ cat /etc/sysctl.conf |grep page oracleoel7db1 dbs$oracleoel7db1 $ ps -ef|grep 3579|grep -v greproot35791 0 08:19 ?00:00:00 ora_dism_anbob19coracleoel7db1 $ ls -l which oradism-rwsr-x 1 root oinstall 147848 Apr 17 2019/u01/app/oracle/product/19.2.0/db_1/bin/oradism20c AUTO MLOracle Machine Learning 通过Oracle数据库、Oracle自治数据库以及与Oracle大数据相关的产品和技术来支持数据科学和机器学习。移动的是算法， 而不是数据！20c AUTO ML20c AUTO ML增加了数据库科学家的生产力 - 使用数据库内的函数，降低总体的计算时间自动模型选择确定实现最高模型质量的算法比穷举搜索更快地找到最佳模型自动特征选择通过确定最具预测性的特