Oracle自治數(shù)據(jù)庫和自動化運維新特性概述

1、Oracle自治數(shù)據(jù)庫和自動化運維新特性概述技術(shù)創(chuàng)新，變革未來目錄Oracle自治數(shù)據(jù)庫Oracle自動化運維特性19x 自動索引19c 自動large page20c AUTO MLPart1 Oracle自治數(shù)據(jù)庫Oracle自治數(shù)據(jù)庫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自治數(shù)據(jù)庫ADW 自動化的數(shù)據(jù)倉庫ATP 更簡單、更快速的自治事務(wù)處理AJD 集成了ORACLE多種特性的JSON DatabaseADWATPAJD主要特征快速的復(fù)雜分析快速的事務(wù)交易用于json數(shù)據(jù)數(shù)據(jù)格式列存行存數(shù)據(jù)處理自動并行串行壓縮自動HCC壓縮默認(rèn)不壓縮優(yōu)化器hint默認(rèn)忽略使用hintOracle自治數(shù)據(jù)庫自治驅(qū)動自動供應(yīng)、監(jiān)控、備份、恢 復(fù)、調(diào)優(yōu)、擴展和升級數(shù)據(jù) 庫自治安全自動化數(shù)據(jù)保護和安全性， 自動應(yīng)用安全補丁自治修復(fù)自動化故障檢測、故障轉(zhuǎn)移和 修復(fù)，實例數(shù)據(jù)庫

3、最大高可用使用機器學(xué)習(xí)加上積累的技術(shù)實現(xiàn)低成本、低風(fēng)險、簡單的關(guān)鍵維護Oracle自治數(shù)據(jù)庫一個自治數(shù)據(jù)庫兩種部署選擇公有云Severless按需購買使用非常簡單私有云客戶定制的專用私有云Exadata CloudCustomerOracle自治數(shù)據(jù)庫Oracle自治數(shù)據(jù)庫Oracle自治數(shù)據(jù)庫MAA進化Part2 Oracle自動化運維特性O(shè)racle自動化運維特性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投入了大量工作在自動化和優(yōu)化 數(shù)據(jù)庫上zhangweizhaoOracle 自動化索引DEV: 應(yīng)該在哪些列創(chuàng)建索引? 每列創(chuàng)建行不？O

6、racle 自動化索引自動索引使用了哪些技術(shù)？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 自動化索引自動索引創(chuàng)建的 “debugging”DBACDB_AUTO_INDEX_VERIFICATIONS 列出自動索引的PLAN

7、_HASH_VALUE,和BUFFER_GETS對比等DBA_AUTO_INDEX_IND_ACTIONS- 列出索引創(chuàng)建的時間、SQLTo trace fileupdate SMB$CONFIG set_AUTO_INDEX_TRACE=2- Or -call dbms_auto_index_internal.configure(_AUTO_INDEX_TRACE,2,true,true)Oracle 自動化索引自動索引創(chuàng)建錯誤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共享內(nèi)存使用大頁 相關(guān)的參數(shù)。USE_LARGE_PAGES有效值True: 默認(rèn)值，如果操作系統(tǒng)分配hugepage可用，嘗試使用hugepa

9、ge False: 不使用hugepageOnly:如是hugepage不能用于整個實例所需要共享內(nèi)存，將啟動失敗Auto: 一種可能值，12.2 如果可能使用在啟動時嘗試調(diào)用linux kernel增加hugepagesAuto-only: auto+ only 19c引入，Exadata環(huán)境默認(rèn)值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數(shù)據(jù)庫、Oracle自治數(shù)據(jù)庫以及與Oracle大數(shù)據(jù)相關(guān)的產(chǎn)品和技術(shù)來支持?jǐn)?shù)據(jù)科學(xué)和機器學(xué)習(xí)。移動的是算法， 而不是數(shù)據(jù)！20c AUTO ML20c AUTO ML增加了數(shù)據(jù)庫科學(xué)家的生產(chǎn)力 - 使用數(shù)據(jù)庫內(nèi)的函數(shù)，降低總體的計算時間自動模型選擇確定實現(xiàn)最高模型質(zhì)量的算法比窮舉搜索更快地找到最佳模型自動特征選擇通過確定最具預(yù)測性的特