The important thing in life is to have a great aim , and the determination

常用链接

统计

IT技术链接

保险相关

友情链接

基金知识

生活相关

最新评论

2014年5月3日 #

POI操作Excel

一、POI概述   Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。   结构: HSSF - 提供读写Microsoft Excel格式档案的功能。 XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。 HWPF - 提供读写Microsoft Word格式档案的功能。 HSLF - 提供读写Microsoft PowerPoint格式档案的功能。 HDGF - 提供读写Microsoft Visio格式档案的功能。  使用必须引入依赖 org.apache.poi poi 3.17 注:3.17版本是支持jdk6的最后版本 二、HSSF概况   HSSF 是Horrible SpreadSheet Format的缩写,通过HSSF,你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API:usermodel和eventusermodel,即“用户模型”和“事件-用户模型”。 三、 POI EXCEL文档结构类 HSSFWorkbook excel文档对象 HSSFSheet excel的sheet HSSFRow excel的行 HSSFCell excel的单元格 HSSFFont excel字体 HSSFName 名称 HSSFDataFormat 日期格式 HSSFHeader sheet头 HSSFFooter sheet尾 HSSFCellStyle cell样式 HSSFDateUtil 日期 HSSFPrintSetup 打印 HSSFErrorConstants 错误信息表 四、EXCEL的读写操作 1、读取“区域数据.xls”并储存于list集合中,“区域数据.xls”如下图 public List importXLS(){ ArrayList list = new ArrayList<>(); try {      //1、获取文件输入流      InputStream inputStream = new FileInputStream("/Users/Shared/区域数据.xls");      //2、获取Excel工作簿对象 HSSFWorkbook workbook = new HSSFWorkbook(inputStream);      //3、得到Excel工作表对象 HSSFSheet sheetAt = workbook.getSheetAt(0); //4、循环读取表格数据      for (Row row : sheetAt) {        //首行(即表头)不读取 if (row.getRowNum() == 0) { continue; } //读取当前行中单元格数据,索引从0开始        String areaNum = row.getCell(0).getStringCellValue(); String province = row.getCell(1).getStringCellValue(); String city = row.getCell(2).getStringCellValue(); String district = row.getCell(3).getStringCellValue(); String postcode = row.getCell(4).getStringCellValue(); Area area = new Area(); area.setCity(city); area.setDistrict(district); area.setProvince(province);        area.setPostCode(postcode); list.add(area); }      //5、关闭流 workbook.close(); } catch (IOException e) { e.printStackTrace(); }   return list; } 2、导出数据到“区域数据.xls”文件中,页面数据如下图: public void exportExcel() throws IOException { Page page = areaService.pageQuery(null); List list = page.getContent(); //1.在内存中创建一个excel文件 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); //2.创建工作簿 HSSFSheet sheet = hssfWorkbook.createSheet(); //3.创建标题行 HSSFRow titlerRow = sheet.createRow(0); titlerRow.createCell(0).setCellValue("省"); titlerRow.createCell(1).setCellValue("市"); titlerRow.createCell(2).setCellValue("区"); titlerRow.createCell(3).setCellValue("邮编"); titlerRow.createCell(4).setCellValue("简码"); titlerRow.createCell(5).setCellValue("城市编码"); //4.遍历数据,创建数据行 for (Area area : list) { //获取最后一行的行号 int lastRowNum = sheet.getLastRowNum(); HSSFRow dataRow = sheet.createRow(lastRowNum + 1); dataRow.createCell(0).setCellValue(area.getProvince()); dataRow.createCell(1).setCellValue(area.getCity()); dataRow.createCell(2).setCellValue(area.getDistrict()); dataRow.createCell(3).setCellValue(area.getPostcode()); dataRow.createCell(4).setCellValue(area.getShortcode()); dataRow.createCell(5).setCellValue(area.getCitycode()); } //5.创建文件名 String fileName = "区域数据统计.xls"; //6.获取输出流对象 HttpServletResponse response = ServletActionContext.getResponse(); ServletOutputStream outputStream = response.getOutputStream(); //7.获取mimeType ServletContext servletContext = ServletActionContext.getServletContext(); String mimeType = servletContext.getMimeType(fileName); //8.获取浏览器信息,对文件名进行重新编码 HttpServletRequest request = ServletActionContext.getRequest(); fileName = FileUtils.filenameEncoding(fileName, request); //9.设置信息头 response.setContentType(mimeType); response.setHeader("Content-Disposition","attachment;filename="+fileName); //10.写出文件,关闭流 hssfWorkbook.write(outputStream); hssfWorkbook.close(); } 工具类 public class FileUtils { public static String filenameEncoding(String filename, HttpServletRequest request) throws IOException { String agent = request.getHeader("User-Agent"); //获取浏览器 if (agent.contains("Firefox")) { BASE64Encoder base64Encoder = new BASE64Encoder(); filename = "=?utf-8?B?" + base64Encoder.encode(filename.getBytes("utf-8")) + "?="; } else if(agent.contains("MSIE")) { filename = URLEncoder.encode(filename, "utf-8"); } else if(agent.contains ("Safari")) { filename = new String (filename.getBytes ("utf-8"),"ISO8859-1"); } else { filename = URLEncoder.encode(filename, "utf-8"); } return filename; } } 写出xls文件: 五、 EXCEL常用操作方法 1、 得到Excel常用对象 POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls")); //得到Excel工作簿对象 HSSFWorkbook wb = new HSSFWorkbook(fs); //得到Excel工作表对象 HSSFSheet sheet = wb.getSheetAt(0); //得到Excel工作表的行 HSSFRow row = sheet.getRow(i); //得到Excel工作表指定行的单元格 HSSFCell cell = row.getCell((short) j); cellStyle = cell.getCellStyle();//得到单元格样式 2、建立Excel常用对象 HSSFWorkbook wb = new HSSFWorkbook();//创建Excel工作簿对象 HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel工作表对象 HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行 cellStyle = wb.createCellStyle();//创建单元格样式 row.createCell((short)0).setCellStyle(cellStyle); //创建Excel工作表指定行的单元格 row.createCell((short)0).setCellValue(1); //设置Excel工作表的值 3、设置sheet名称和单元格内容 wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16); cell.setEncoding((short) 1); cell.setCellValue("单元格内容"); 4、取得sheet的数目 wb.getNumberOfSheets() 5、 根据index取得sheet对象 HSSFSheet sheet = wb.getSheetAt(0); 6、取得有效的行数 int rowcount = sheet.getLastRowNum(); 7、取得一行的有效单元格个数 row.getLastCellNum(); 8、单元格值类型读写 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型 cell.getNumericCellValue();//读取为数值类型的单元格内容 9、设置列宽、行高 sheet.setColumnWidth((short)column,(short)width); row.setHeight((short)height); 10、添加区域,合并单元格 Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo ,(short)columnTo);//合并从第rowFrom行columnFrom列 sheet.addMergedRegion(region);// 到rowTo行columnTo的区域 //得到所有区域 sheet.getNumMergedRegions() 11、保存Excel文件 FileOutputStream fileOut = new FileOutputStream(path); wb.write(fileOut); 12、根据单元格不同属性返回字符串数值 public String getCellStringValue(HSSFCell cell) {   String cellValue = "";   switch (cell.getCellType()) {     case HSSFCell.CELL_TYPE_STRING://字符串类型         cellValue = cell.getStringCellValue();         if(cellValue.trim().equals("")||cellValue.trim().length()<=0)           cellValue=" ";         break;     case HSSFCell.CELL_TYPE_NUMERIC: //数值类型         cellValue = String.valueOf(cell.getNumericCellValue());         break;     case HSSFCell.CELL_TYPE_FORMULA: //公式         cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);         cellValue = String.valueOf(cell.getNumericCellValue());         break;     case HSSFCell.CELL_TYPE_BLANK:         cellValue=" ";         break;     case HSSFCell.CELL_TYPE_BOOLEAN:         break;     case HSSFCell.CELL_TYPE_ERROR:         break;     default:         break;   }   return cellValue; } 13、常用单元格边框格式 HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框 style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 14、设置字体和内容位置 HSSFFont f = wb.createFont(); f.setFontHeightInPoints((short) 11);//字号 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗 style.setFont(f); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中 style.setRotation(short rotation);//单元格内容的旋转的角度 HSSFDataFormat df = wb.createDataFormat(); style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式 cell.setCellFormula(string);//给单元格设公式 style.setRotation(short rotation);//单元格内容的旋转的角度 15、插入图片 //先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new File("ok.jpg")); ImageIO.write(bufferImg,"jpg",byteArrayOut); //读进一个excel模版 FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt"); fs = new POIFSFileSystem(fos); //创建一个工作薄 HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10); patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG)); 16、调整工作表位置 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("format sheet"); HSSFPrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short)1); ps.setFitWidth((short)1);

posted @ 2020-03-20 10:13 鸿雁 阅读(39) | 评论 (0)编辑 收藏

python操作文件存在的问题解决办法

1、在学习从文件读取数据中,写了个示例代码,读取不在同一个目录的file.txt,运行后报这个Python OSError: [Errno 22] Invalid argument:错误: (1)、首先,在F盘的python_stu中新增了一个file.txt,同时在F盘的python_stu文件目录底下新增一个file文件夹,里面有个file_reader.py来读取python_stu文件目录底下的file.txt,代码分别如下: file.txt: 测试 测试2 测试3 file_reader.py: with open('F:\python_stu\file.txt') as file_obj: contents = file_obj.read(); print(contents.rstrip()); (2)、运行后报错: (3)、出现这种错误的原因是由于读取不到这个文件,看Traceback报的错误,最后一行,很明显读取不到file.txt,前面的F:\\python_stu没错,后面的名称怎么变了,还是x0cile.txt。 (4)、解决办法,可修改上述第一行代码为: with open('F:\python_stu/file.txt') as file_obj: 或者: with open('F:/python_stu/file.txt') as file_obj: 或者: with open('F://python_stu//file.txt') as file_obj: 又或者: with open('F:\\python_stu\\file.txt') as file_obj: 还有一些我就不附上了,上面第一种方式不统一,最好不要用,用统一的方式,而且有时候还有注意一些转义字符,比如 \t,\n也会导致报错。

posted @ 2019-05-14 23:04 鸿雁 阅读(81) | 评论 (0)编辑 收藏

Hadoop-2.4.1学习之QJM HA的自动故障转移

前面学习了使用命令hdfs haadmin -failover手动进行故障转移,在该模式下,即使现役NameNode已经失效,系统也不会自动从现役NameNode转移到待机NameNode,下面学习如何配置部署HA自动进行故障转移。自动故障转移为HDFS部署增加了两个新组件:ZooKeeper和ZKFailoverController(ZKFC)进程。ZooKeeper是维护少量协调数据,通知客户端这些数据的改变和监视客户端故障的高可用服务。HA的自动故障转移依赖于ZooKeeper的以下功能:

  • 故障检测:集群中的每个NameNode在ZooKeeper中维护了一个持久会话,如果机器崩溃,ZooKeeper中的会话将终止,ZooKeeper通知另一个NameNode需要触发故障转移。
  • 现役NameNode选择:ZooKeeper提供了一个简单的机制用于唯一的选择一个节点为active状态。如果目前现役NameNode崩溃,另一个节点可能从ZooKeeper获得特殊的排外锁以表明它应该成为现役NameNode。

       ZKFC是自动故障转移中的另一个新组件,是ZooKeeper的客户端,也监视和管理NameNode的状态。每个运行NameNode的主机也运行了一个ZKFC进程,ZKFC负责:

  • 健康监测:ZKFC使用一个健康检查命令定期地ping与之在相同主机的NameNode,只要该NameNode及时地回复健康状态,ZKFC认为该节点是健康的。如果该节点崩溃,冻结或进入不健康状态,健康监测器标识该节点为非健康的。
  • ZooKeeper会话管理:当本地NameNode是健康的,ZKFC保持一个在ZooKeeper中打开的会话。如果本地NameNode处于active状态,ZKFC也保持一个特殊的znode锁,该锁使用了ZooKeeper对短暂节点的支持,如果会话终止,锁节点将自动删除。
  • 基于ZooKeeper的选择:如果本地NameNode是健康的,且ZKFC发现没有其它的节点当前持有znode锁,它将为自己获取该锁。如果成功,则它已经赢得了选择,并负责运行故障转移进程以使它的本地NameNode为active。故障转移进城与前面描述的手动故障转移相似,首先如果必要保护之前的现役NameNode,然后本地NameNode转换为active状态。

       在典型部署中,ZooKeeper守护进程运行在三个或者五个节点上,但由于ZooKeeper本身需要较少的资源,所以将ZooKeeper部署在与现役NameNode和待机NameNode相同的主机上,还可以将ZooKeeper部署到与YARN的ResourceManager相同的节点上。建议配置ZooKeeper将数据存储在与HDFS元数据不同的硬盘上以得到最好的性能和隔离性。在配置自动故障转移之前需要先停掉集群,目前在集群运行时还不可能将手动故障转移的安装转换为自动故障转移的安装。接下来看看如何配置HA的自动故障转移。首先在hdfs-site.xml中添加下面的参数,该参数的值默认为false:

  1. <property>  
  2.    <name>dfs.ha.automatic-failover.enabled</name>  
  3.    <value>true</value>  
  4. </property>  

       在core-site.xml文件中添加下面的参数,该参数的值为ZooKeeper服务器的地址,ZKFC将使用该地址。

  1. <property>  
  2.    <name>ha.zookeeper.quorum</name> <value>zk1.example.com:2181,zk2.example.com:2181,zk3.example.com:2181</value>  
  3. </property>  

       在HA或者HDFS联盟中,上面的两个参数还需要以NameServiceID为后缀,比如dfs.ha.automatic-failover.enabled.mycluster。除了上面的两个参数外,还有其它几个参数用于自动故障转移,比如ha.zookeeper.session-timeout.ms,但对于大多数安装来说都不是必须的。

       在添加了上述的配置参数后,下一步就是在ZooKeeper中初始化要求的状态,可以在任一NameNode中运行下面的命令实现该目的,该命令将在ZooKeeper中创建znode:

  1. $ hdfs zkfc -formatZK  

       在启用自动故障转移的集群中,start-dfs.sh脚本将在任何运行NameNode的主机上自动启动ZKFC守护进程,一旦ZKFC启动完毕,它们将自动选择一个NameNode为现役NameNode。如果手动管理集群中的服务,需要在每台运行NameNode的主机上手动启动ZKFC,命令为:

  1. hadoop-daemon.sh start zkfc  
  2. hdfs zkfc  

       如果正在运行一个安全的集群,可能想确保存储在ZooKeeper中的信息也是安全的,这将阻止恶意的客户端修改ZooKeeper中的元数据或者潜在地触发一个错误的故障转移。为了保护ZooKeeper中的信息,首先在core-site.xml中添加下面的参数:

  1. <property>  
  2.    <name>ha.zookeeper.auth</name>  
  3.    <value>@/path/to/zk-auth.txt</value>  
  4.  </property>  
  5.  <property>  
  6.    <name>ha.zookeeper.acl</name>  
  7.    <value>@/path/to/zk-acl.txt</value>  
  8.  </property>  

       参数值中的@字符表示参数值保存在@后的硬盘文件中。第一个配置文件指定了ZooKeeper的认证列表,其格式与ZK CLI使用的相同,例如:digest:hdfs-zkfcs:mypassword,其中hdfs-zkfcs为ZooKeeper的用户名,mypassword为密码。其次使用下面的命令为该认证生成一个ZooKeeper访问控制列表:

  1. $ java -cp $ZK_HOME/lib/*:$ZK_HOME/zookeeper-3.4.2.jar org.apache.zookeeper.server.auth.DigestAuthenticationProvider hdfs-zkfcs:mypassword  
  2. output: hdfs-zkfcs:mypassword->hdfs-zkfcs:P/OQvnYyU/nF/mGYvB/xurX8dYs=   

       拷贝->之后的字符串并添加digest:前缀,然后粘贴到zk-acls.txt中,例如:digest:hdfs-zkfcs:vlUvLnd8MlacsE80rDuu6ONESbM=:rwcda。要想使ACLs生效,需要再次运行zkfc –formatZK。最后可能像下面这样在ZK CLI中验证ACLs:

  1. [zk: localhost:2181(CONNECTED) 1] getAcl /hadoop-ha  
  2. 'digest,'hdfs-zkfcs:vlUvLnd8MlacsE80rDuu6ONESbM=  
  3. : cdrwa  

       在安装完成自动故障转移后,或许需要测试一下。首先定位现役NameNode,可以通过访问NameNode的web页面来确定哪个NameNode是active状态的。一旦确定了处于active状态的NameNode,就需要在该节点上制造点故障,比如使用命令kill -9 <pid of NN>模拟JVM崩溃,或重启主机或拔掉网线来模拟不同的中断。一旦触发了自动故障转移,另一个NameNode应该自动在几秒钟内变为active状态。检测到故障并触发故障转移由参数ha.zookeeper.session-timeout.ms控制,该参数为与core-site.xml中,默认为5秒。如果测试不成功,可能是配置问题,检查ZKFC和NameNode进程的日志以进一步诊断问题,通常错误都是很明显的。

posted @ 2017-08-13 18:49 鸿雁 阅读(324) | 评论 (0)编辑 收藏

Yarn 调度器Scheduler详解

理想情况下,我们应用对Yarn资源的请求应该立刻得到满足,但现实情况资源往往是有限的,特别是在一个很繁忙的集群,一个应用资源的请求经常需要等待一段时间才能的到相应的资源。在Yarn中,负责给应用分配资源的就是Scheduler。其实调度本身就是一个难题,很难找到一个完美的策略可以解决所有的应用场景。为此,Yarn提供了多种调度器和可配置的策略供我们选择。

一、调度器的选择

在Yarn中有三种调度器可以选择:FIFO Scheduler ,Capacity SchedulerFairS cheduler

FIFO Scheduler把应用按提交的顺序排成一个队列,这是一个先进先出队列,在进行资源分配的时候,先给队列中最头上的应用进行分配资源,待最头上的应用需求满足后再给下一个分配,以此类推。

FIFO Scheduler是最简单也是最容易理解的调度器,也不需要任何配置,但它并不适用于共享集群。大的应用可能会占用所有集群资源,这就导致其它应用被阻塞。在共享集群中,更适合采用Capacity SchedulerFair Scheduler,这两个调度器都允许大任务和小任务在提交的同时获得一定的系统资源。

下面“Yarn调度器对比图”展示了这几个调度器的区别,从图中可以看出,在FIFO 调度器中,小任务会被大任务阻塞。

而对于Capacity调度器,有一个专门的队列用来运行小任务,但是为小任务专门设置一个队列会预先占用一定的集群资源,这就导致大任务的执行时间会落后于使用FIFO调度器时的时间。

在Fair调度器中,我们不需要预先占用一定的系统资源,Fair调度器会为所有运行的job动态的调整系统资源。如下图所示,当第一个大job提交时,只有这一个job在运行,此时它获得了所有集群资源;当第二个小任务提交后,Fair调度器会分配一半资源给这个小任务,让这两个任务公平的共享集群资源。

需要注意的是,在下图Fair调度器中,从第二个任务提交到获得资源会有一定的延迟,因为它需要等待第一个任务释放占用的Container。小任务执行完成之后也会释放自己占用的资源,大任务又获得了全部的系统资源。最终的效果就是Fair调度器即得到了高的资源利用率又能保证小任务及时完成。

Yarn调度器对比图: 
技术分享

二、Capacity Scheduler(容器调度器)的配置

2.1 容器调度介绍

Capacity 调度器允许多个组织共享整个集群,每个组织可以获得集群的一部分计算能力。通过为每个组织分配专门的队列,然后再为每个队列分配一定的集群资源,这样整个集群就可以通过设置多个队列的方式给多个组织提供服务了。除此之外,队列内部又可以垂直划分,这样一个组织内部的多个成员就可以共享这个队列资源了,在一个队列内部,资源的调度是采用的是先进先出(FIFO)策略。

通过上面那幅图,我们已经知道一个job可能使用不了整个队列的资源。然而如果这个队列中运行多个job,如果这个队列的资源够用,那么就分配给这些job,如果这个队列的资源不够用了呢?其实Capacity调度器仍可能分配额外的资源给这个队列,这就是“弹性队列”(queue elasticity)的概念。

在正常的操作中,Capacity调度器不会强制释放Container,当一个队列资源不够用时,这个队列只能获得其它队列释放后的Container资源。当然,我们可以为队列设置一个最大资源使用量,以免这个队列过多的占用空闲资源,导致其它队列无法使用这些空闲资源,这就是”弹性队列”需要权衡的地方。

2.2 容器调度的配置

假设我们有如下层次的队列:

root ├── prod └── dev     ├── eng     └── science 

下面是一个简单的Capacity调度器的配置文件,文件名为capacity-scheduler.xml。在这个配置中,在root队列下面定义了两个子队列proddev,分别占40%和60%的容量。需要注意,一个队列的配置是通过属性yarn.sheduler.capacity.<queue-path>.<sub-property>指定的,<queue-path>代表的是队列的继承树,如root.prod队列,<sub-property>一般指capacitymaximum-capacity

技术分享

我们可以看到,dev队列又被分成了engscience两个相同容量的子队列。devmaximum-capacity属性被设置成了75%,所以即使prod队列完全空闲dev也不会占用全部集群资源,也就是说,prod队列仍有25%的可用资源用来应急。我们注意到,engscience两个队列没有设置maximum-capacity属性,也就是说engscience队列中的job可能会用到整个dev队列的所有资源(最多为集群的75%)。而类似的,prod由于没有设置maximum-capacity属性,它有可能会占用集群全部资源。

Capacity容器除了可以配置队列及其容量外,我们还可以配置一个用户或应用可以分配的最大资源数量、可以同时运行多少应用、队列的ACL认证等。

2.3 队列的设置

关于队列的设置,这取决于我们具体的应用。比如,在MapReduce中,我们可以通过mapreduce.job.queuename属性指定要用的队列。如果队列不存在,我们在提交任务时就会收到错误。如果我们没有定义任何队列,所有的应用将会放在一个default队列中。

注意:对于Capacity调度器,我们的队列名必须是队列树中的最后一部分,如果我们使用队列树则不会被识别。比如,在上面配置中,我们使用prodeng作为队列名是可以的,但是如果我们用root.dev.eng或者dev.eng是无效的。

三、Fair Scheduler(公平调度器)的配置

3.1 公平调度

Fair调度器的设计目标是为所有的应用分配公平的资源(对公平的定义可以通过参数来设置)。在上面的“Yarn调度器对比图”展示了一个队列中两个应用的公平调度;当然,公平调度在也可以在多个队列间工作。举个例子,假设有两个用户A和B,他们分别拥有一个队列。当A启动一个job而B没有任务时,A会获得全部集群资源;当B启动一个job后,A的job会继续运行,不过一会儿之后两个任务会各自获得一半的集群资源。如果此时B再启动第二个job并且其它job还在运行,则它将会和B的第一个job共享B这个队列的资源,也就是B的两个job会用于四分之一的集群资源,而A的job仍然用于集群一半的资源,结果就是资源最终在两个用户之间平等的共享。过程如下图所示: 
技术分享

3.2 启用Fair Scheduler

调度器的使用是通过yarn-site.xml配置文件中的yarn.resourcemanager.scheduler.class参数进行配置的,默认采用Capacity Scheduler调度器。如果我们要使用Fair调度器,需要在这个参数上配置FairScheduler类的全限定名: org.apache.hadoop.yarn.server.resourcemanager.scheduler.fair.FairScheduler

3.3 队列的配置

Fair调度器的配置文件位于类路径下的fair-scheduler.xml文件中,这个路径可以通过yarn.scheduler.fair.allocation.file属性进行修改。若没有这个配置文件,Fair调度器采用的分配策略,这个策略和3.1节介绍的类似:调度器会在用户提交第一个应用时为其自动创建一个队列,队列的名字就是用户名,所有的应用都会被分配到相应的用户队列中。

我们可以在配置文件中配置每一个队列,并且可以像Capacity 调度器一样分层次配置队列。比如,参考capacity-scheduler.xml来配置fair-scheduler: 
技术分享

队列的层次是通过嵌套<queue>元素实现的。所有的队列都是root队列的孩子,即使我们没有配到<root>元素里。在这个配置中,我们把dev队列有分成了engscience两个队列。

Fair调度器中的队列有一个权重属性(这个权重就是对公平的定义),并把这个属性作为公平调度的依据。在这个例子中,当调度器分配集群40:60资源给proddev时便视作公平,engscience队列没有定义权重,则会被平均分配。这里的权重并不是百分比,我们把上面的40和60分别替换成2和3,效果也是一样的。注意,对于在没有配置文件时按用户自动创建的队列,它们仍有权重并且权重值为1。

每个队列内部仍可以有不同的调度策略。队列的默认调度策略可以通过顶级元素<defaultQueueSchedulingPolicy>进行配置,如果没有配置,默认采用公平调度。

尽管是Fair调度器,其仍支持在队列级别进行FIFO调度。每个队列的调度策略可以被其内部的<schedulingPolicy> 元素覆盖,在上面这个例子中,prod队列就被指定采用FIFO进行调度,所以,对于提交到prod队列的任务就可以按照FIFO规则顺序的执行了。需要注意,proddev之间的调度仍然是公平调度,同样engscience也是公平调度。

尽管上面的配置中没有展示,每个队列仍可配置最大、最小资源占用数和最大可运行的应用的数量。

3.4 队列的设置

Fair调度器采用了一套基于规则的系统来确定应用应该放到哪个队列。在上面的例子中,<queuePlacementPolicy> 元素定义了一个规则列表,其中的每个规则会被逐个尝试直到匹配成功。例如,上例第一个规则specified,则会把应用放到它指定的队列中,若这个应用没有指定队列名或队列名不存在,则说明不匹配这个规则,然后尝试下一个规则。primaryGroup规则会尝试把应用放在以用户所在的Unix组名命名的队列中,如果没有这个队列,不创建队列转而尝试下一个规则。当前面所有规则不满足时,则触发default规则,把应用放在dev.eng队列中。

当然,我们可以不配置queuePlacementPolicy规则,调度器则默认采用如下规则:

<queuePlacementPolicy> <rule name="specified" /> <rule name="user" /> </queuePlacementPolicy> 

上面规则可以归结成一句话,除非队列被准确的定义,否则会以用户名为队列名创建队列。

还有一个简单的配置策略可以使得所有的应用放入同一个队列(default),这样就可以让所有应用之间平等共享集群而不是在用户之间。这个配置的定义如下:

<queuePlacementPolicy> <rule name="default" /> </queuePlacementPolicy> 

实现上面功能我们还可以不使用配置文件,直接设置yarn.scheduler.fair.user-as-default-queue=false,这样应用便会被放入default 队列,而不是各个用户名队列。另外,我们还可以设置yarn.scheduler.fair.allow-undeclared-pools=false,这样用户就无法创建队列了。

3.5 抢占(Preemption)

当一个job提交到一个繁忙集群中的空队列时,job并不会马上执行,而是阻塞直到正在运行的job释放系统资源。为了使提交job的执行时间更具预测性(可以设置等待的超时时间),Fair调度器支持抢占。

抢占就是允许调度器杀掉占用超过其应占份额资源队列的containers,这些containers资源便可被分配到应该享有这些份额资源的队列中。需要注意抢占会降低集群的执行效率,因为被终止的containers需要被重新执行。

可以通过设置一个全局的参数yarn.scheduler.fair.preemption=true来启用抢占功能。此外,还有两个参数用来控制抢占的过期时间(这两个参数默认没有配置,需要至少配置一个来允许抢占Container):

- minimum share preemption timeout - fair share preemption timeout 

如果队列在minimum share preemption timeout指定的时间内未获得最小的资源保障,调度器就会抢占containers。我们可以通过配置文件中的顶级元素<defaultMinSharePreemptionTimeout>为所有队列配置这个超时时间;我们还可以在<queue>元素内配置<minSharePreemptionTimeout>元素来为某个队列指定超时时间。

与之类似,如果队列在fair share preemption timeout指定时间内未获得平等的资源的一半(这个比例可以配置),调度器则会进行抢占containers。这个超时时间可以通过顶级元素<defaultFairSharePreemptionTimeout>和元素级元素<fairSharePreemptionTimeout>分别配置所有队列和某个队列的超时时间。上面提到的比例可以通过<defaultFairSharePreemptionThreshold>(配置所有队列)和<fairSharePreemptionThreshold>(配置某个队列)进行配置,默认是0.5。

posted @ 2017-08-05 00:35 鸿雁 阅读(280) | 评论 (0)编辑 收藏

hive大数据倾斜总结

在做Shuffle阶段的优化过程中,遇到了数据倾斜的问题,造成了对一些情况下优化效果不明显。主要是因为在Job完成后的所得到的Counters是整个Job的总和,优化是基于这些Counters得出的平均值,而由于数据倾斜的原因造成map处理数据量的差异过大,使得这些平均值能代表的价值降低。Hive的执行是分阶段的,map处理数据量的差异取决于上一个stage的reduce输出,所以如何将数据均匀的分配到各个reduce中,就是解决数据倾斜的根本所在。规避错误来更好的运行比解决错误更高效。在查看了一些资料后,总结如下。

1数据倾斜的原因

1.1操作:

关键词

情形

后果

Join

其中一个表较小,

但是key集中

分发到某一个或几个Reduce上的数据远高于平均值

大表与大表,但是分桶的判断字段0值或空值过多

这些空值都由一个reduce处理,灰常慢

group by

group by 维度过小,

某值的数量过多

处理某值的reduce灰常耗时

Count Distinct

某特殊值过多

处理此特殊值的reduce耗时

1.2原因:

1)、key分布不均匀

2)、业务数据本身的特性

3)、建表时考虑不周

4)、某些SQL语句本身就有数据倾斜

 

1.3表现:

任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。

单一reduce的记录数与平均记录数差异过大,通常可能达到3倍甚至更多。 最长时长远大于平均时长。

 

2数据倾斜的解决方案

2.1参数调节:

hive.map.aggr=true

Map 端部分聚合,相当于Combiner

hive.groupby.skewindata=true

有数据倾斜的时候进行负载均衡,当选项设定为 true,生成的查询计划会有两个 MR Job。第一个 MR Job 中,Map 的输出结果集合会随机分布到 Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中(这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作。

 

2.2 SQL语句调节:

如何Join

关于驱动表的选取,选用join key分布最均匀的表作为驱动表

做好列裁剪和filter操作,以达到两表做join的时候,数据量相对变小的效果。

大小表Join

使用map join让小的维度表(1000条以下的记录条数) 先进内存。在map端完成reduce.

大表Join大表:

把空值的key变成一个字符串加上随机数,把倾斜的数据分到不同的reduce上,由于null值关联不上,处理后并不影响最终结果。

count distinct大量相同特殊值

count distinct时,将值为空的情况单独处理,如果是计算count distinct,可以不用处理,直接过滤,在最后结果中加1。如果还有其他计算,需要进行group by,可以先将值为空的记录单独处理,再和其他计算结果进行union。

group by维度过小:

采用sum() group by的方式来替换count(distinct)完成计算。

特殊情况特殊处理:

在业务逻辑优化效果的不大情况下,有些时候是可以将倾斜的数据单独拿出来处理。最后union回去。

 

3典型的业务场景

3.1空值产生的数据倾斜

场景:如日志中,常会有信息丢失的问题,比如日志中的 user_id,如果取其中的 user_id 和 用户表中的user_id 关联,会碰到数据倾斜的问题。

解决方法1 user_id为空的不参与关联(红色字体为修改后)

复制代码
select * from log a   join users b   on a.user_id is not null   and a.user_id = b.user_id union all select * from log a   where a.user_id is null;
复制代码

 

解决方法赋与空值分新的key值

select *   from log a   left outer join users b   on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;

 

结论:方法2比方法1效率更好,不但io少了,而且作业数也少了。解决方法1中 log读取两次,jobs是2。解决方法2 job数是1 。这个优化适合无效 id (比如 -99 , ’’, null 等) 产生的倾斜问题。把空值的 key 变成一个字符串加上随机数,就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。

 

3.2不同数据类型关联产生数据倾斜

场景:用户表中user_id字段为int,log表中user_id字段既有string类型也有int类型。当按照user_id进行两个表的Join操作时,默认的Hash操作会按int型的id来进行分配,这样会导致所有string类型id的记录都分配到一个Reducer中。

解决方法:把数字类型转换成字符串类型

select * from users a   left outer join logs b   on a.usr_id = cast(b.user_id as string)

 

3.3小表不小不大,怎么用 map join 解决倾斜问题

使用 map join 解决小表(记录数少)关联大表的数据倾斜问题,这个方法使用的频率非常高,但如果小表很大,大到map join会出现bug或异常,这时就需要特别的处理。 以下例子:

select * from log a   left outer join users b   on a.user_id = b.user_id;

 

users 表有 600w+ 的记录,把 users 分发到所有的 map 上也是个不小的开销,而且 map join 不支持这么大的小表。如果用普通的 join,又会碰到数据倾斜的问题。

解决方法:

select /*+mapjoin(x)*/* from log a   
left outer join (
select /*+mapjoin(c)*/d.* from (
select distinct user_id from log ) c join users d
on c.user_id = d.user_id ) x
on a.user_id = b.user_id;

假如,log里user_id有上百万个,这就又回到原来map join问题。所幸,每日的会员uv不会太多,有交易的会员不会太多,有点击的会员不会太多,有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。

4总结

使map的输出数据更均匀的分布到reduce中去,是我们的最终目标。由于Hash算法的局限性,按key Hash会或多或少的造成数据倾斜。大量经验表明数据倾斜的原因是人为的建表疏忽或业务逻辑可以规避的。在此给出较为通用的步骤:

1、采样log表,哪些user_id比较倾斜,得到一个结果表tmp1。由于对计算框架来说,所有的数据过来,他都是不知道数据分布情况的,所以采样是并不可少的。

2、数据的分布符合社会学统计规则,贫富不均。倾斜的key不会太多,就像一个社会的富人不多,奇特的人不多一样。所以tmp1记录数会很少。把tmp1和users做map join生成tmp2,把tmp2读到distribute file cache。这是一个map过程。

3、map读入users和log,假如记录来自log,则检查user_id是否在tmp2里,如果是,输出到本地文件a,否则生成<user_id,value>的key,value对,假如记录来自member,生成<user_id,value>的key,value对,进入reduce阶段。

4、最终把a文件,把Stage3 reduce阶段输出的文件合并起写到hdfs。

 

如果确认业务需要这样倾斜的逻辑,考虑以下的优化方案:

1、对于join,在判断小表不大于1G的情况下,使用map join

2、对于group by或distinct,设定 hive.groupby.skewindata=true

3、尽量使用上述的SQL语句调节进行优化

posted @ 2017-07-31 19:57 鸿雁 阅读(108) | 评论 (0)编辑 收藏

Hive性能调优

Hive的一般学习者和培训者在谈性能优化的时候一般都会从语法和参数这些雕虫小技的角度谈优化,而不会革命性的优化Hive的性能,产生这种现象的原因有:
1,历史原因和思维定势:大家学习SQL的时候一般都是就单机DB,这个时候你的性能优化技巧确实主要是SQL语法和参数调优;
2,Hive的核心的性能问题往往是产生在超过规模数据集,例如说100亿条级别的数据集,以及每天处理上千上万个Hive作业的情况下产生的;

上面的第二点是我们现在Hive性能调优部分要彻底解决的内容;

要从根本上解决和显著的解决实际企业中Hive真正的性能优化问题,必须考虑到底什么是Hive性能的限制,我们按照优先级来说:
第一重要的是:战略性架构
 解决海量数据下大量Job过于频繁的IO问题,而这个问题实质上涉及了架构方面的分表 数据复用 以及分区表等调优的方式;
    补充:1,海量的数据中有些数据是高频使用的数据,而有些是很少使用的,如果能够分离成为不同的表,会极大的提升效率;很多的作业可能会有共同点,抽离出来先进行计算并保留计算结果,后面的作业都可以复用;同时,底层的基础功能
也可以先计算,在上层应用的时候直接拿数据结果,而不是每次都重复计算;
    2,合理从用静态分区表和动态分区表,可以避免数据全局扫描及计算资源更合理的利用;

    3,数据倾斜的一站式解决方案;
第二重要的是:引擎和物理层面,很多内容都是普通Hive使用这不知道的!
    从Hive语法和Job内部的角度去进行优化,这要求MapReduce以及Hive如何被翻译成为MapReduce要非常精通;

第三重要的是:一些关键的参数;



归根到底,Hive的性能优化主要考虑的是如何最大化和最有效的使用CPU Memory IO;



Hive背后的Mapper调优:
1,Mapper数过大,会产生大量小文件,由于Mapper是基于虚拟机的,过多的Mapper创建和初始化及关闭虚拟机都会消耗大量的硬件资源;
    Mapper数太小,并发度过小,Job执行时间过长,无法充分利用分布式硬件资源;
2,Mapper数据由什么决定呢?
    输入文件数目;
    输入文件的大小;
    配置参数;
 默认情况下:例如一个文件800M,BLock大小是128M,那么Mapper数目就是7个,6个Mapper处理的数据是 128M, 1个Mapper处理的数据是32M;再例如,一个目录下有三个文件分别大小问5M 10M 150M
 此时会产生4个Mapper,处理的数据分别是5M 10M 128M 22M;
  
 减少Mapper的个数,就要合并小文件,这种小文件有可能是直接来自于数据源的小文件,也可能是Reducer产生的小文件;
    set hive.input.format=org.apache.Hadoop.hive.ql.io.CombineHiveInputFormat;
    set hive.merge.mapFiles=true;
    set hive.merge.mapredFiles=true;
    set hive.merge.size.per.task=256000000
    set mapred.max.split.size=256000000
    set mapred.min.split.size.per.node=128000000
    
    
 增加Mapper的个数,一般是通过控制Hive SQL中上一个Job的Reducer个数来控制的,例如在Join操作的时候会把多个表分解为多个Job;


 set mapred.map.tasks=2;
 set hive.merge.mapFiles=true;
 set hive.merge.mapredFiles=true;
 set hive.merge.size.per.task=256000000

 例如我们有5个300M的文件;按照上面的配置会产生10个Mapper,5个Mapper处理的都是256M的数据,另外5个Mapper处理的都是44M的数据,问题是:大的Mapper会数据倾斜
如何解决,设置set mapred.map.tasks=6,此时根据MapRed的运行机制,会划分6个Mapper,每个Mapper的处理数据的大小是250M, min(1500M/6, 256M) =250M

Hive背后的Reducer调优:
1,Reducer数目过大的话,会产生很多小文件,每个Reducer都会产生一个文件,如果这些小文件是下一个JOB的输入,则会需要对小文件进行合并;同样启动 初始化和销毁Reducer的虚拟机也需要消耗大量的硬件;
  Reducer数据过小的话,Reduce的时间会比较长,也可能会出现数据倾斜;
2,如何控制Reducer的个数呢?
  set hive.exec.reducers.byte.per.reducer=1G
  set hive.exec.reducers.max=999
  Reducer个数=min(999, Reducer的数据输入总量/1G);

  set mapred.reduce.tasks = 10, 默认是1; 如果说当前的Reducer的结果很大,且被接下来多个Job使用其结果,我们该如何设置参数呢?一般都需要调大该参数;

  什么情况下只有一个Reducer?如果不进行Group by但却需要汇总,或者说Order by,当然如果最后Reducer的数据小于默认的1G的话,也会只有一个Reducer;


1,Hive在分布式运行的时候最害怕的是数据倾斜,这是由于分布式系统的特性决定的,因为分布式系统之所以很快是由于作业平均分配给了不同的节点,不同节点同心协力,从而达到更快处理完作业的目的;
顺便说明一下,处理数据倾斜的能力是hadoopSpark工程师最核心的竞争力之一;

2,Hive中数据倾斜的原因:
    数据在分布式节点上分布不平衡;
    join时某些key可能特别大;
    groupBy的时候某个Key可能特别多;
    count(distinct)有可能出现数据倾斜,因为其内部首先会进行groupBy操作;


3,join,我们希望join时候key是分散,如果一个key的数据量特别大,有可能会出现数据倾斜和OOM,一个核心点是:小表join大表,在reduce阶段左侧的小表会加载进内存,减少OOM的风险;
4,大表join大表的情况:数据倾斜,例如null值,解决办法一般是要打散null值,例如说使用随机数等,如果数据倾斜比较严重,采用这种方式可以提升至少一倍的速度;
5,mapJoin:小表join(超)大表的时候,可以采用mapJoin的方式把小表全部加载到Mapper端的内存中/*+MAPJOIN(table_name)*/;
6,小表join(超)大表的时候,是否会自动进行mapJoin,想进行mapJoin,需要设置:set hive.auto.convert.join=true,Hive在进行join的时候会判断左表的大小来决定是否进行mapJoin:
    set hive.mapjoin.smalltable.filesize=128000000;
    set hive.mapjoin.cache.numrows=100000;
    上述参数可以根据实际的硬件机器的内存进行调整,对性能有至关重要的影响,因为没有了Shuffle;
  对于mapJoin我们能够使用Mapper端JVM中多大的内存呢?
    set hive.mapjoin.followby.gby.localtask.max.momery.usage = 0.8
    set hive.mapjoin.localtask.max.memory.uage=0.9
7,groupBy,我们可以设置在Mapper端进行部分聚合,最后在Reducer端进行全局聚合
    set hive.map.aggr=true;
    set hive.groupby.mapaggr.checkinterval=100000

    set hive.groupby.skewindata = true 内部会产生两个Job,第一个Job会通过自己的算法打散倾斜的Key并进行聚合操作且保留结果,第二个Job会完成全部的groupBy操作,会产生Mapper-Reducer-Reducer的结构

8, count(distinct),如果某个字段特别多,容易产生数据倾斜,解决思路:
    在查询语句中例如对null进行过滤,在结果中加1


9, 笛卡尔积:join时候没有on条件,或者on条件无效,这个时候会使用Reducer进行笛卡尔积的操作;

posted @ 2017-07-30 13:52 鸿雁 阅读(96) | 评论 (0)编辑 收藏

HIVE和HBASE区别

1. 两者分别是什么?  

 Apache Hive是一个构建在Hadoop基础设施之上的数据仓库。通过Hive可以使用HQL语言查询存放在HDFS上的数据。HQL是一种类SQL语言,这种语言最终被转化为Map/Reduce. 虽然Hive提供了SQL查询功能,但是Hive不能够进行交互查询--因为它只能够在Haoop上批量的执行Hadoop。

    Apache HBase是一种Key/Value系统,它运行在HDFS之上。和Hive不一样,Hbase的能够在它的数据库上实时运行,而不是运行MapReduce任务。Hive被分区为表格,表格又被进一步分割为列簇。列簇必须使用schema定义,列簇将某一类型列集合起来(列不要求schema定义)。例如,“message”列簇可能包含:“to”, ”from” “date”, “subject”, 和”body”. 每一个 key/value对在Hbase中被定义为一个cell,每一个key由row-key,列簇、列和时间戳。在Hbase中,行是key/value映射的集合,这个映射通过row-key来唯一标识。Hbase利用Hadoop的基础设施,可以利用通用的设备进行水平的扩展。

2. 两者的特点

  Hive帮助熟悉SQL的人运行MapReduce任务。因为它是JDBC兼容的,同时,它也能够和现存的SQL工具整合在一起。运行Hive查询会花费很长时间,因为它会默认遍历表中所有的数据。虽然有这样的缺点,一次遍历的数据量可以通过Hive的分区机制来控制。分区允许在数据集上运行过滤查询,这些数据集存储在不同的文件夹内,查询的时候只遍历指定文件夹(分区)中的数据。这种机制可以用来,例如,只处理在某一个时间范围内的文件,只要这些文件名中包括了时间格式。

    HBase通过存储key/value来工作。它支持四种主要的操作:增加或者更新行,查看一个范围内的cell,获取指定的行,删除指定的行、列或者是列的版本。版本信息用来获取历史数据(每一行的历史数据可以被删除,然后通过Hbase compactions就可以释放出空间)。虽然HBase包括表格,但是schema仅仅被表格和列簇所要求,列不需要schema。Hbase的表格包括增加/计数功能。

3. 限制

  Hive目前不支持更新操作。另外,由于hive在hadoop上运行批量操作,它需要花费很长的时间,通常是几分钟到几个小时才可以获取到查询的结果。Hive必须提供预先定义好的schema将文件和目录映射到列,并且Hive与ACID不兼容。

    HBase查询是通过特定的语言来编写的,这种语言需要重新学习。类SQL的功能可以通过Apache Phonenix实现,但这是以必须提供schema为代价的。另外,Hbase也并不是兼容所有的ACID特性,虽然它支持某些特性。最后但不是最重要的--为了运行Hbase,Zookeeper是必须的,zookeeper是一个用来进行分布式协调的服务,这些服务包括配置服务,维护元信息和命名空间服务。

4. 应用场景

    Hive适合用来对一段时间内的数据进行分析查询,例如,用来计算趋势或者网站的日志。Hive不应该用来进行实时的查询。因为它需要很长时间才可以返回结果。

    Hbase非常适合用来进行大数据的实时查询。Facebook用Hbase进行消息和实时的分析。它也可以用来统计Facebook的连接数。

5. 总结

    Hive和Hbase是两种基于Hadoop的不同技术--Hive是一种类SQL的引擎,并且运行MapReduce任务,Hbase是一种在Hadoop之上的NoSQL 的Key/vale数据库。当然,这两种工具是可以同时使用的。就像用Google来搜索,用FaceBook进行社交一样,Hive可以用来进行统计查询,HBase可以用来进行实时查询,数据也可以从Hive写到Hbase,设置再从Hbase写回Hive。

posted @ 2017-07-21 11:44 鸿雁 阅读(86) | 评论 (0)编辑 收藏

Hive分布式安装配置Hive+Mysql

     摘要: 目录 1,环境准备 2,安装Hive和配置环境变量 3,安装MySQL 4,在mysql上创建hive元数据库,并对hive进行授权 5,安装jar包到hive 6,配置hive-site.xml 7,元数据存储初始化 8,启动验证hive 9,报错及解决方法 1,环境准备: 准备好Hadoop集群,参照...  阅读全文

posted @ 2017-07-20 15:20 鸿雁 阅读(369) | 评论 (0)编辑 收藏

TCP/UDP区别以及UDP如何实现可靠传输

TCP和UDP是OSI模型中的运输层中的协议。TCP提供可靠的通信传输,而UDP则常被用于让广播和细节控制交给应用的通信传输。


UDP(User Datagram Protocol)

        UDP不提供复杂的控制机制,利用IP提供面向无连接的通信服务。并且它是将应用程序发来的数据在收到的那一刻,立刻按照原样发送到网络上的一种机制。

        即使是出现网络拥堵的情况下,UDP也无法进行流量控制等避免网络拥塞的行为。此外,传输途中如果出现了丢包,UDO也不负责重发。甚至当出现包的到达顺序乱掉时也没有纠正的功能。如果需要这些细节控制,那么不得不交给由采用UDO的应用程序去处理。换句话说,UDP将部分控制转移到应用程序去处理,自己却只提供作为传输层协议的最基本功能。UDP有点类似于用户说什么听什么的机制,但是需要用户充分考虑好上层协议类型并制作相应的应用程序。


TCP(Transmission Control Protocol)

        TCP充分实现爱呢了数据传输时各种控制功能,可以进行丢包的重发控制,还可以对次序乱掉的分包进行顺序控制。而这些在UDP中都没有。此外,TCP作为一种面向有连接的协议,只有在确认通信对端存在时才会发送数据,从而可以控制通信流量的浪费。

        TCP通过检验和、序列号、确认应答、重发控制、连接管理以及窗口控制等机制实现可靠性传输。此处不一一叙述。


TCP与UDP如何加以区分使用?

        TCP用于在传输层有必要实现可靠性传输的情况。由于它是面向有连接并具备顺序控制、重发控制等机制的。所以它可以为应用提供可靠传输。

        另一方面,UDP主要用于那些对高速传输和实时性有较高要求的通信或广播通信。举一个IP电话进行通话的例子。如果使用TCP,数据在传送途中如果丢失会被重发,但是这样无法流畅地传输通话人的声音,会导致无法进行正常交流。而采用UDP,它不会进行重发处理。从而也就不会有声音大幅度延迟到达的问题。即使有部分数据丢失,也只是影响某一小部分的通话。此外,在多播与广播通信中也使用UDP而不是UDP。RIP、DHCP等基于广播的协议也要依赖于UDP。


TCP与UDP区别总结:

1、TCP面向连接(如打电话要先拨号建立连接);UDP是无连接的,即发送数据之前不需要建立连接

2、TCP提供可靠的服务。也就是说,通过TCP连接传送的数据,无差错,不丢失,不重复,且按序到达;UDP尽最大努力交付,即不保证可靠交付
3、TCP面向字节流,实际上是TCP把数据看成一连串无结构的字节流;UDP是面向报文的
UDP没有拥塞控制,因此网络出现拥塞不会使源主机的发送速率降低(对实时应用很有用,如IP电话,实时视频会议等)
4、每一条TCP连接只能是点到点的;UDP支持一对一,一对多,多对一和多对多的交互通信
5、TCP首部开销20字节;UDP的首部开销小,只有8个字节

6、TCP的逻辑通信信道是全双工的可靠信道,UDP则是不可靠信道


UDP如何实现可靠传输

由于在传输层UDP已经是不可靠的连接,那就要在应用层自己实现一些保障可靠传输的机制

简单来讲,要使用UDP来构建可靠的面向连接的数据传输,就要实现类似于TCP协议的

超时重传(定时器)

有序接受 (添加包序号)

应答确认 (Seq/Ack应答机制)

滑动窗口流量控制等机制 (滑动窗口协议)

等于说要在传输层的上一层(或者直接在应用层)实现TCP协议的可靠数据传输机制,比如使用UDP数据包+序列号,UDP数据包+时间戳等方法。

目前已经有一些实现UDP可靠传输的机制,比如

UDT(UDP-based Data Transfer Protocol

基于UDP的数据传输协议(UDP-based Data Transfer Protocol,简称UDT)是一种互联网数据传输协议。UDT的主要目的是支持高速广域网上的海量数据传输,而互联网上的标准数据传输协议TCP在高带宽长距离网络上性能很差。 顾名思义,UDT建于UDP之上,并引入新的拥塞控制和数据可靠性控制机制。UDT是面向连接的双向的应用层协议。它同时支持可靠的数据流传输和部分可靠的数据报传输。 由于UDT完全在UDP上实现,它也可以应用在除了高速数据传输之外的其它应用领域,例如点到点技术(P2P),防火墙穿透,多媒体数据传输等等。

posted @ 2017-07-19 22:25 鸿雁 阅读(371) | 评论 (0)编辑 收藏

一文读懂分布式数据库Hbase

一、

1、什么是Hbase

一个高可靠性、高性能、列存储、可伸缩、实时读写的分布式数据库系统。

适合于存储非结构化数据,基于列的而不是基于行的模式

如图:Hadoop生态中hbase与其他部分的关系。


2、关系数据库已经流行很多年,并且hadoop已经有了HDFS和MapReduce,为什么需要HBase?

Hadoop可以很好地解决大规模数据的离线批量处理问题,但是,受限于HadoopMapReduce编程框架的高延迟数据处理机制,使得Hadoop无法满足大规模数据实时处理应用的需求
HDFS面向批量访问模式,不是随机访问模式
传统的通用关系型数据库无法应对在数据规模剧增时导致的系统扩展性和性能问题(分库分表也不能很好解决)
传统关系数据库在数据结构变化时一般需要停机维护;空列浪费存储空间
因此,业界出现了一类面向半结构化数据存储和处理的高可扩展、低写入/查询延迟的系统,例如,键值数据库、文档数据库和列族数据库(如BigTableHBase等)
HBase已经成功应用于互联网服务领域和传统行业的众多在线式数据分析处理系统中
3、HBase与传统的关系数据库的区别

1)数据类型:关系数据库采用关系模型,具有丰富的数据类型和存储方式,HBase则采用了更加简单的数据模型,它把数据存储为未经解释的字符串
2)数据操作:关系数据库中包含了丰富的操作,其中会涉及复杂的多表连接。HBase操作则不存在复杂的表与表之间的关系,只有简单的插入、查询、删除、清空等,因为HBase在设计上就避免了复杂的表和表之间的关系
3)存储模式:关系数据库是基于行模式存储的。HBase是基于列存储的,每个列族都由几个文件保存,不同列族的文件是分离的
4)数据索引:关系数据库通常可以针对不同列构建复杂的多个索引,以提高数据访问性能。HBase只有一个索引——行键,通过巧妙的设计,HBase中的所有访问方法,或者通过行键访问,或者通过行键扫描,从而使得整个系统不会慢下来
5)数据维护:在关系数据库中,更新操作会用最新的当前值去替换记录中原来的旧值,旧值被覆盖后就不会存在。而HBase中执行更新操作时,并不会删除数据旧的版本,而是生成一个新的版本,旧有的版本仍然保留
6)可伸缩性:关系数据库很难实现横向扩展,纵向扩展的空间也比较有限。相反,HBase和BigTable这些分布式数据库就是为了实现灵活的水平扩展而开发的,能够轻易地通过在集群中增加或者减少硬件数量来实现性能的伸缩
二、Hbase数据模型

1、模型概述

HBase是一个稀疏、多维度、排序的映射表,这张表的索引是行键、列族、列限定符和时间戳
每个值是一个未经解释的字符串,没有数据类型
用户在表中存储数据,每一行都有一个可排序的行键和任意多的列
表在水平方向由一个或者多个列族组成,一个列族中可以包含任意多个列,同一个列族里面的数据存储在一起
列族支持动态扩展,可以很轻松地添加一个列族或列,无需预先定义列的数量以及类型,所有列均以字符串形式存储,用户需要自行进行数据类型转换
HBase中执行更新操作时,并不会删除数据旧的版本,而是生成一个新的版本,旧有的版本仍然保留(这是和HDFS只允许追加不允许修改的特性相关的)

2、数据坐标
HBase
中需要根据行键、列族、列限定符和时间戳来确定一个单元格,因此,可以视为一个“四维坐标”,即[行键,列族, 列限定符,时间戳]

[201505003,Info,email, 1174184619081]

xie@qq.com

[201505003,Info,email, 1174184620720]

you@163.com


3、概念视图

4、物理视图


三、HBase实现原理

1、HBase的实现包括三个主要的功能组件:

1)库函数:链接到每个客户端
2)一个Master主服务器
3)许多个Region服务器
主服务器Master负责管理和维护HBase表的分区信息,维护Region服务器列表,分配Region,负载均衡
Region服务器负责存储和维护分配给自己的Region,处理来自客户端的读写请求
客户端并不是直接从Master主服务器上读取数据,而是在获得Region的存储位置信息后,直接从Region服务器上读取数据
客户端并不依赖Master,而是通过Zookeeper来获得Region位置信息,大多数客户端甚至从来不和Master通信,这种设计方式使得Master负载很小
2、Region
开始只有一个
Region,后来不断分裂
Region拆分操作非常快,接近瞬间,因为拆分之后的Region读取的仍然是原存储文件,直到“合并”过程把存储文件异步地写到独立的文件之后,才会读取新文件
同一个Region不会被分拆到多个Region服务器
每个Region服务器存储10-1000Region

元数据表,又名.META.表,存储了RegionRegion服务器的映射关系
HBase表很大时, .META.表也会被分裂成多个Region
根数据表,又名-ROOT-表,记录所有元数据的具体位置
-ROOT-表只有唯一一个Region,名字是在程序中被写死的
Zookeeper文件记录了-ROOT-表的位置


客户端访问数据时的“三级寻址”

为了加速寻址,客户端会缓存位置信息,同时,需要解决缓存失效问题

寻址过程客户端只需要询问Zookeeper服务器,不需要连接Master服务器

3、HBase的三层结构中各层次的名称和作用

层次

名称

作用

第一层

Zookeper文件

记录了-ROOT-表的位置信息

第二层

-ROOT-

记录了.META.表的Region位置信息

-ROOT-表只能有一个Region。通过-ROOT-表,就可以访问.META.表中的数据

第三层

.META.

记录了用户数据表的Region位置信息,.META.表可以有多个Region,保存了HBase中所有用户数据表的Region位置信息

四、HBase运行机制
1、HBase系统架构


(1、客户端包含访问HBase的接口,同时在缓存中维护着已经访问过的Region位置信息,用来加快后续数据访问过程

(2、Zookeeper可以帮助选举出一个Master作为集群的总管,并保证在任何时刻总有唯一一个Master在运行,这就避免了Master的“单点失效”问题
Zookeeper是一个很好的集群管理工具,被大量用于分布式计算,提供配置维护、域名服务、分布式同步、组服务等。

(3. Master
主服务器Master主要负责表和Region的管理工作:
管理用户对表的增加、删除、修改、查询等操作
实现不同Region服务器之间的负载均衡
Region分裂或合并后,负责重新调整Region的分布
对发生故障失效的Region服务器上的Region进行迁移
(4. Region服务器
Region服务器是HBase中最核心的模块,负责维护分配给自己的Region,并响应用户的读写请求
2、Region


(1、用户读写数据过程
用户写入数据时,被分配到相应Region服务器去执行
用户数据首先被写入到MemStoreHlog
只有当操作写入Hlog之后,commit()调用才会将其返回给客户端
当用户读取数据时,Region服务器会首先访问MemStore缓存,如果找不到,再去磁盘上面的StoreFile中寻找
(2、缓存的刷新
系统会周期性地把MemStore缓存里的内容刷写到磁盘的StoreFile文件中,清空缓存,并在Hlog里面写入一个标记、
每次刷写都生成一个新的StoreFile文件,因此,每个Store包含多个StoreFile文件

每个Region服务器都有一个自己的HLog文件,每次启动都检查该文件,确认最近一次执行缓存刷新操作之后是否发生新的写入操作;如果发现更新,则先写入MemStore,再刷写到StoreFile,最后删除旧的Hlog文件,开始为用户提供服务
(3、StroreFile的合并
每次刷写都生成一个新的StoreFile,数量太多,影响查找速度
调用Store.compact()把多个合并成一个
合并操作比较耗费资源,只有数量达到一个阈值才启动合并
3、Store工作原理
StoreRegion服务器的核心
多个StoreFile合并成一个
触发分裂操作,1个父Region被分裂成两个子Region
单个StoreFile过大时,又
4、HLog工作原理
分布式环境必须要考虑系统出错。HBase采用HLog保证系统恢复
HBase系统为每个Region服务器配置了一个HLog文件,它是一种预写式日志(WriteAhead Log
用户更新数据必须首先写入日志后,才能写入MemStore缓存,并且,直到MemStore缓存内容对应的日志已经写入磁盘,该缓存内容才能被刷写到磁盘

Zookeeper会实时监测每个Region服务器的状态,当某个Region服务器发生故障时,Zookeeper会通知Master
Master首先会处理该故障Region服务器上面遗留的HLog文件,这个遗留的HLog文件中包含了来自多个Region对象的日志记录
系统会根据每条日志记录所属的Region对象对HLog数据进行拆分,分别放到相应Region对象的目录下,然后,再将失效的Region重新分配到可用的Region服务器中,并把与该Region对象相关的HLog日志记录也发送给相应的Region服务器
Region服务器领取到分配给自己的Region对象以及与之相关的HLog日志记录以后,会重新做一遍日志记录中的各种操作,把日志记录中的数据写入到MemStore缓存中,然后,刷新到磁盘的StoreFile文件中,完成数据恢复
共用日志优点:提高对表的写操作性能;缺点:恢复时需要分拆日志
五、HBase性能
1、行键(RowKey)

行键是按照字典序存储,因此,设计行键时,要充分利用这个排序特点,将经常一起读取的数据存储到一块,将最近可能会被访问的数据放在一块。

举个例子:如果最近写入HBase表中的数据是最可能被访问的,可以考虑将时间戳作为行键的一部分,由于是字典序排序,所以可以使用Long.MAX_VALUE- timestamp作为行键,这样能保证新写入的数据在读取时可以被快速命中。

InMemory:创建表的时候,可以通过HColumnDescriptor.setInMemory(true)将表放到Region服务器的缓存中,保证在读取的时候被cache命中。

Max Version:创建表的时候,可以通过HColumnDescriptor.setMaxVersions(int maxVersions)设置表中数据的最大版本,如果只需要保存最新版本的数据,那么可以设置setMaxVersions(1)

Time To Live创建表的时候,可以通过HColumnDescriptor.setTimeToLive(inttimeToLive)设置表中数据的存储生命期,过期数据将自动被删除,例如如果只需要存储最近两天的数据,那么可以设置setTimeToLive(2* 24 * 60 * 60)

2、HBaseMaster默认基于WebUI服务端口为60010HBase region服务器默认基于WebUI服务端口为60030.如果master运行在名为master.foo.com的主机中,mater的主页地址就是http://master.foo.com:60010,用户可以通过Web浏览器输入这个地址查看该页面
可以查看HBase集群的当前状态
3、NoSQL区别于关系型数据库的一点就是NoSQL不使用SQL作为查询语言,至于为何在NoSQL数据存储HBase上提供SQL接口

易使用,减少编码

4、HBase只有一个针对行健的索引

访问HBase表中的行,只有三种方式:

通过单个行健访问
通过一个行健的区间来访问
全表扫描

总结:
1、HBase数据库是BigTable的开源实现,和BigTable一样,支持大规模海量数据,分布式并发数据处理效率极高,易于扩展且支持动态伸缩,适用于廉价设备
2、HBase可以支持NativeJava APIHBaseShellThriftGatewayHive等多种访问接口,可以根据具体应用场合选择相应访问方式
3、HBase实际上就是一个稀疏、多维、持久化存储的映射表,它采用行键、列键和时间戳进行索引,每个值都是未经解释的字符串。
4、HBase采用分区存储,一个大的表会被分拆许多个Region,这些Region会被分发到不同的服务器上实现分布式存储
5、HBase的系统架构包括客户端、Zookeeper服务器、Master主服务器、Region服务器。客户端包含访问HBase的接口;Zookeeper服务器负责提供稳定可靠的协同服务;Master主服务器主要负责表和Region的管理工作;Region服务器负责维护分配给自己的Region,并响应用户的读写请求

posted @ 2017-07-19 22:24 鸿雁 阅读(155) | 评论 (0)编辑 收藏

在python中获取mac和ip地址

python 获得本机MAC地址:
import uuid
     def get_mac_address():
     mac=uuid.UUID(int=uuid.getnode()).hex[-12:]
     return ":".join([mac[e:e+2] for e in range(0,11,2)])


python获取IP的方法:使用socket

import socket

myname=socket.getfqdn(socket.gethostname( ))
    myaddr=socket.gethostbyname(myname)
    print(myname)
    print(myaddr)

posted @ 2017-05-15 23:26 鸿雁 阅读(140) | 评论 (0)编辑 收藏

Spring—Quartz定时调度CronTrigger时间配置格式说明与实例

spring中使用Quartz时 时间配置例子:

<!-- 定义调用对象和调用对象的方法 end   -->
<!-- 定义调用时间 begin -->
<bean id="realweatherTime" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail">
<ref bean="realweatherTask" />
</property>
<property name="cronExpression">
<value>0 10/30 * * * ?</value><!-- 表示每小时的10,40时执行任务 -->
</property>
</bean>
<!-- 定义调用时间 end   -->


<!-- 定义调用对象和调用对象的方法 end   -->
<!-- 定义调用时间 begin -->
<bean id="weatherTime" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail">
<ref bean="weatherTask" />
</property>
<property name="cronExpression">
<!--  <value>0 30 8,13 * * ?</value>  --><!-- 表示每天的8:30和13:30时执行任务 -->
<value>0 0,30 0-23 * * ?</value><!---表示每天从0-23时中每时的整点或半点执行任务->
</property>
</bean>

<!-- 定义调用时间 end   -->1、   CronTrigger时间格式配置说明

CronTrigger配置格式:

格式: [秒] [分] [小时] [日] [月] [周] [年]

序号

说明

是否必填

允许填写的值

允许的通配符

1

0-59 

, - * /

2

0-59

, - * /

3

小时

0-23

, - * /

4

1-31

, - * ? / L W

5

1-12 or JAN-DEC

, - * /

6

1-7 or SUN-SAT

, - * ? / L #

7

empty 或 1970-2099

, - * /

通配符说明:

:表示所有值. 例如:在分的字段上设置 "*",表示每一分钟都会触发。
? 
:表示不指定值。使用的场景为不需要关心当前设置这个字段的值。例如:要在每月的10号触发一个操作,但不关心是周几,所以需要周位置的那个字段设置为"?" 具体设置为 0 0 0 10 * ?
- 
:表示区间。例如 在小时上设置 "10-12",表示 10,11,12点都会触发。

, :表示指定多个值,例如在周字段上设置 "MON,WED,FRI" 表示周一,周三和周五触发
:用于递增触发。如在秒上面设置"5/15" 表示从5秒开始,每增15秒触发(5,20,35,50)。 在月字段上设置'1/3'所示每月1号开始,每隔三天触发一次。
L :表示最后的意思。在日字段设置上,表示当月的最后一天(依据当前月份,如果是二月还会依据是否是润年[leap]), 在周字段上表示星期六,相当于"7"或"SAT"。如果在"L"前加上数字,则表示该数据的最后一个。

例如在周字段上设置"6L"这样的格式,则表示“本月最后一个星期五"

W :表示离指定日期的最近那个工作日(周一至周五). 例如在日字段上设置"15W",表示离每月15号最近的那个工作日触发。如果15号正好是周六,则找最近的周五(14号)触发, 如果15号是周未,则找最近的下周一(16号)触发.如果15号正好在工作日(周一至周五),则就在该天触发。如果指定格式为 "1W",它则表示每月1号往后最近的工作日触发。如果1号正是周六,则将在3号下周一触发。(注,"W"前只能设置具体的数字,不允许区间"-").

'L'和 'W'可以一组合使用。如果在日字段上设置"LW",则表示在本月的最后一个工作日触发

 

# :序号(表示每月的第几周星期几),例如在周字段上设置"6#3"表示在每月的第三个周星期六.注意如果指定"6#5",正好第五周没有星期六,则不会触发该配置(用在母亲节和父亲节再合适不过了)

周字段的设置,若使用英文字母是不区分大小写的 MON 与mon相同.

常用示例:

格式: [秒] [分] [小时] [日] [月] [周] [年]

0 0 12 * * ?           每天12点触发 
0 15 10 ? * *          每天10点15分触发
 
0 15 10 * * ?          每天10点15分触发
  
0 15 10 * * ? *        每天10点15分触发
  
0 15 10 * * ? 2005     2005年每天10点15分触发
 
0 * 14 * * ?           每天下午的 2点到2点59分每分触发
 
0 0/5 14 * * ?         每天下午的 2点到2点59分(整点开始,每隔5分触发
)  
0 0/5 14,18 * * ?        每天下午的 18点到18点59分(整点开始,每隔5分触发)

0 0-5 14 * * ?            每天下午的 2点到2点05分每分触发 
0 10,44 14 ? 3 WED        3月分每周三下午的 2点10分和2点44分触发
 
0 15 10 ? * MON-FRI       从周一到周五每天上午的10点15分触发
 
0 15 10 15 * ?            每月15号上午10点15分触发
 
0 15 10 L * ?             每月最后一天的10点15分触发
 
0 15 10 ? * 6L            每月最后一周的星期五的10点15分触发
 
0 15 10 ? * 6L 2002-2005  从2002年到2005年每月最后一周的星期五的10点15分触发

0 15 10 ? * 6#3           每月的第三周的星期五开始触发 
0 0 12 1/5 * ?            每月的第一个中午开始每隔5天触发一次
 
0 11 11 11 11 ?           每年的11月11号 11点11分触发(光棍节)

spring中使用Quartz时 时间配置例子:

<!-- 定义调用对象和调用对象的方法 end   -->
<!-- 定义调用时间 begin -->
<bean id="realweatherTime" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail">
<ref bean="realweatherTask" />
</property>
<property name="cronExpression">
<value>0 10/30 * * * ?</value><!-- 表示每小时的10,40时执行任务 -->
</property>
</bean>
<!-- 定义调用时间 end   -->


<!-- 定义调用对象和调用对象的方法 end   -->
<!-- 定义调用时间 begin -->
<bean id="weatherTime" class="org.springframework.scheduling.quartz.CronTriggerBean">
<property name="jobDetail">
<ref bean="weatherTask" />
</property>
<property name="cronExpression">
<!--  <value>0 30 8,13 * * ?</value>  --><!-- 表示每天的8:30和13:30时执行任务 -->
<value>0 0,30 0-23 * * ?</value><!---表示每天从0-23时中每时的整点或半点执行任务->
</property>
</bean>
<!-- 定义调用时间 end   -->


posted @ 2017-04-29 12:01 鸿雁 阅读(220) | 评论 (0)编辑 收藏

前端内容展示操作

1.有时表格内容太多,只显示部分,其余部分已省略号表示,用css处理如下:
.template td{
    word-break:keep-all;/* 不换行 */
    white-space:nowrap;/* 不换行 */
    overflow:hidden;/* 内容超出宽度时隐藏超出部分的内容 */
    text-overflow:ellipsis;/* 当对象内文本溢出时显示省略标记(...) ;需与overflow:hidden;一起使用。*/
}

template 是该表单所在的table class属性。

posted @ 2016-03-09 11:36 鸿雁 阅读(150) | 评论 (0)编辑 收藏

js 金额 用逗号 隔开数字格式化

代码如下:
引用

function fmoney(s, n)  
{  
   n = n > 0 && n <= 20 ? n : 2;  
   s = parseFloat((s + "").replace(/[^\d\.-]/g, "")).toFixed(n) + "";  
   var l = s.split(".")[0].split("").reverse(),  
   r = s.split(".")[1];  
   t = "";  
   for(i = 0; i < l.length; i ++ )  
   {  
      t += l[i] + ((i + 1) % 3 == 0 && (i + 1) != l.length ? "," : "");  
   }  
   return t.split("").reverse().join("") + "." + r;  
}


调用:fmoney("12345.675910", 3),返回12,345.676

还原函数:
引用

function rmoney(s)  
{  
   return parseFloat(s.replace(/[^\d\.-]/g, ""));  
}


示例(可保存一下代码为html文件,运行查看效果):
引用

<SCRIPT>  
function fmoney(s, n)  
{  
   n = n > 0 && n <= 20 ? n : 2;  
   s = parseFloat((s + "").replace(/[^\d\.-]/g, "")).toFixed(n) + "";  
   var l = s.split(".")[0].split("").reverse(),  
   r = s.split(".")[1];  
   t = "";  
   for(i = 0; i < l.length; i ++ )  
   {  
      t += l[i] + ((i + 1) % 3 == 0 && (i + 1) != l.length ? "," : "");  
   }  
   return t.split("").reverse().join("") + "." + r;  
}  
function rmoney(s)  
{  
   return parseFloat(s.replace(/[^\d\.-]/g, ""));  
}  
function g(id)  
{  
   return document.getElementById(id);  
}  
window.onload = function()  
{  
   var num,  
   txt = g("txt"),  
   txt2 = g("txt2"),  
   btn = g("btn"),  
   btn2 = g("btn2"),  
   span = g("span");  
   btn.onclick = function()  
   {  
      num = parseInt(g("num").value);  
      txt.value = fmoney(txt.value, num);  
      txt2.value = fmoney(txt2.value, num);  
   }  
   ;  
   btn2.onclick = function()  
   {  
      num = parseInt(g("num").value);  
      span.innerHTML = "=" + fmoney(rmoney(txt.value) + rmoney(txt2.value), num);  
   }  
   ;  
}  
;  
</SCRIPT>  
小数点位数:  
<select id="num">  
<option value="2">2</option>  
<option value="3">3</option>  
<option value="4">4</option>  
<option value="5">5</option>  
</select>  
<input type="text" id="txt" value="12345.675910"> +  
<input type="text" id="txt2" value="1223"> <span id="span"></span>  
<br>  
<input type="button" id="btn" value="格式化">  
<input type="button" id="btn2" value="相加">

posted @ 2015-09-27 10:23 鸿雁 阅读(186) | 评论 (0)编辑 收藏

BigDecimal用法详解

一、简介
Java在java.math包中提供的API类BigDecimal,用来对超过16位有效位的数进行精确的运算。双精度浮点型变量double可以处理16位有效数。在实际应用中,需要对更大或者更小的数进行运算和处理。float和double只能用来做科学计算或者是工程计算,在商业计算中要用java.math.BigDecimal。BigDecimal所创建的是对象,我们不能使用传统的+、-、*、/等算术运算符直接对其对象进行数学运算,而必须调用其相对应的方法。方法中的参数也必须是BigDecimal的对象。构造器是类的特殊方法,专门用来创建对象,特别是带有参数的对象。


二、构造器描述 
BigDecimal(int)       创建一个具有参数所指定整数值的对象。 
BigDecimal(double) 创建一个具有参数所指定双精度值的对象。 
BigDecimal(long)    创建一个具有参数所指定长整数值的对象。 
BigDecimal(String) 创建一个具有参数所指定以字符串表示的数值的对象。

三、方法描述 
add(BigDecimal)        BigDecimal对象中的值相加,然后返回这个对象。 
subtract(BigDecimal) BigDecimal对象中的值相减,然后返回这个对象。 
multiply(BigDecimal)  BigDecimal对象中的值相乘,然后返回这个对象。 
divide(BigDecimal)     BigDecimal对象中的值相除,然后返回这个对象。 
toString()                将BigDecimal对象的数值转换成字符串。 
doubleValue()          将BigDecimal对象中的值以双精度数返回。 
floatValue()             将BigDecimal对象中的值以单精度数返回。 
longValue()             将BigDecimal对象中的值以长整数返回。 
intValue()               将BigDecimal对象中的值以整数返回。

四、格式化及例子
由于NumberFormat类的format()方法可以使用BigDecimal对象作为其参数,可以利用BigDecimal对超出16位有效数字的货币值,百分值,以及一般数值进行格式化控制。

以利用BigDecimal对货币和百分比格式化为例。首先,创建BigDecimal对象,进行BigDecimal的算术运算后,分别建立对货币和百分比格式化的引用,最后利用BigDecimal对象作为format()方法的参数,输出其格式化的货币值和百分比。

复制代码
public static void main(String[] args) {     
    NumberFormat currency = NumberFormat.getCurrencyInstance(); //建立货币格式化引用
    NumberFormat percent = NumberFormat.getPercentInstance(); //建立百分比格式化引用
    percent.setMaximumFractionDigits(3); //百分比小数点最多3位
    BigDecimal loanAmount = new BigDecimal("15000.48"); //贷款金额
    BigDecimal interestRate = new BigDecimal("0.008"); //利率
    BigDecimal interest = loanAmount.multiply(interestRate); //相乘
    System.out.println("贷款金额:\t" + currency.format(loanAmount));
    System.out.println("利率:\t" + percent.format(interestRate));
    System.out.println("利息:\t" + currency.format(interest)); }
复制代码

运行结果如下:

贷款金额:    ¥15,000.48 
利率:
0.8%
利息: ¥
120.00

五、BigDecimal比较
BigDecimal是通过使用compareTo(BigDecimal)来比较的,具体比较情况如下:

复制代码
public static void main(String[] args) {     
    BigDecimal a = new BigDecimal("1");
    BigDecimal b = new BigDecimal("2");
    BigDecimal c = new BigDecimal("1");
    int result1 = a.compareTo(b);
    int result2 = a.compareTo(c);
    int result3 = b.compareTo(a);
    System.out.println(result1);
    System.out.println(result2);
    System.out.println(result3);
}
复制代码

打印结果是:-1、0、1,即左边比右边数大,返回1,相等返回0,比右边小返回-1。
注意不能使用equals方法来比较大小。

使用BigDecimal的坏处是性能比double和float差,在处理庞大,复杂的运算时尤为明显,因根据实际需求决定使用哪种类型。

posted @ 2015-08-22 12:56 鸿雁 阅读(284) | 评论 (0)编辑 收藏

oracle列转行

如果你只是寻求多行转换成一列,比如把同一个id的某个字段col变成一行数据库,把多个col用逗号链接起来。下面几个SQL可以立竿见影。
《1》最简短的方式,使用WMSYS.WM_CONCAT:
SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/') str
FROM Table1
GROUP BY id;
《2》使用sys_connect_by_path:
SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM Table1) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;
或者
SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM Table1) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id;
《3》使用MODEL:
SELECT id, substr(str, 2) str FROM Table1
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
MEASURES (CAST(col AS VARCHAR2(20)) AS str)
RULES UPSERT
ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
(str[0] = str[0] || ',' || str[iteration_number+1])
ORDER BY 1;
 
 
下面是原文:
1.
概述
最近论坛很多人提的问题都与行列转换有关系,所以我对行列转换的相关知识做了一个总结,希望对大家有所帮助,同时有何错疏,恳请大家指出,我也是在写作过程中学习,算是一起和大家学习吧!
行列转换包括以下六种情况:
1)
列转行
2)
行转列
3)
多列转换成字符串
4)
多行转换成字符串
5)
字符串转换成多列
6)
字符串转换成多行
下面分别进行举例介绍。
首先声明一点,有些例子需要如下10g及以后才有的知识:
A.
掌握model子句
B.
正则表达式
C.
加强的层次查询
讨论的适用范围只包括8i,9i,10g及以后版本。
2.
列转行
CREATE TABLE t_col_row(
ID INT,
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10));
INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');
INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);
INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');
INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');
INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);
INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');
INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);
COMMIT;
SELECT * FROM t_col_row;
2.1
UNION ALL
适用范围:8i,9i,10g及以后版本
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;
若空行不需要转换,只需加一个where条件,
WHERE COLUMN IS NOT NULL 即可。
2.2
MODEL
适用范围:10g及以后
SELECT id, cn, cv FROM t_col_row
MODEL
RETURN UPDATED ROWS
PARTITION BY (ID)
DIMENSION BY (0 AS n)
MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3)
RULES UPSERT ALL
(
cn[1] = 'c1',
cn[2] = 'c2',
cn[3] = 'c3',
cv[1] = c1[0],
cv[2] = c2[0],
cv[3] = c3[0]
)
ORDER BY ID,cn;
2.3
COLLECTION
适用范围:8i,9i,10g及以后版本
要创建一个对象和一个集合:
CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));
CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair;
SELECT id, t.cn AS cn, t.cv AS cv
FROM t_col_row,
TABLE(cv_varr(cv_pair('c1', t_col_row.c1),
cv_pair('c2', t_col_row.c2),
cv_pair('c3', t_col_row.c3))) t
ORDER BY 1, 2;
3.
行转列
CREATE TABLE t_row_col AS
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;
SELECT * FROM t_row_col ORDER BY 1,2;
3.1
AGGREGATE FUNCTION
适用范围:8i,9i,10g及以后版本
SELECT id,
MAX(decode(cn, 'c1', cv, NULL)) AS c1,
MAX(decode(cn, 'c2', cv, NULL)) AS c2,
MAX(decode(cn, 'c3', cv, NULL)) AS c3
FROM t_row_col
GROUP BY id
ORDER BY 1;
MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。
被指定的转置列只能有一列,但固定的列可以有多列,请看下面的例子:
SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;
SELECT mgr,
deptno,
MAX(decode(empno, '7788', ename, NULL)) "7788",
MAX(decode(empno, '7902', ename, NULL)) "7902",
MAX(decode(empno, '7844', ename, NULL)) "7844",
MAX(decode(empno, '7521', ename, NULL)) "7521",
MAX(decode(empno, '7900', ename, NULL)) "7900",
MAX(decode(empno, '7499', ename, NULL)) "7499",
MAX(decode(empno, '7654', ename, NULL)) "7654"
FROM emp
WHERE mgr IN (7566, 7698)
AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;
这里转置列为empno,固定列为mgr,deptno。
还有一种行转列的方式,就是相同组中的行值变为单个列值,但转置的行值不变为列名:
ID CN_1 CV_1 CN_2 CV_2 CN_3 CV_3
1 c1 v11 c2 v21 c3 v31
2 c1 v12 c2 v22 c3
3 c1 v13 c2 c3 v33
4 c1 c2 v24 c3 v34
5 c1 v15 c2 c3
6 c1 c2 c3 v35
7 c1 c2 c3
这种情况可以用分析函数实现:
SELECT id,
MAX(decode(rn, 1, cn, NULL)) cn_1,
MAX(decode(rn, 1, cv, NULL)) cv_1,
MAX(decode(rn, 2, cn, NULL)) cn_2,
MAX(decode(rn, 2, cv, NULL)) cv_2,
MAX(decode(rn, 3, cn, NULL)) cn_3,
MAX(decode(rn, 3, cv, NULL)) cv_3
FROM (SELECT id,
cn,
cv,
row_number() over(PARTITION BY id ORDER BY cn, cv) rn
FROM t_row_col)
GROUP BY ID;
3.2
PL/SQL
适用范围:8i,9i,10g及以后版本
这种对于行值不固定的情况可以使用。
下面是我写的一个包,包中
p_rows_column_real用于前述的第一种不限定列的转换;
p_rows_column用于前述的第二种不限定列的转换。
CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS
TYPE refc IS REF CURSOR;
PROCEDURE p_print_sql(p_txt VARCHAR2);
FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2;
PROCEDURE p_rows_column(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc);
PROCEDURE p_rows_column_real(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS
PROCEDURE p_print_sql(p_txt VARCHAR2) IS
v_len INT;
BEGIN
v_len := length(p_txt);
FOR i IN 1 .. v_len / 250 + 1 LOOP
dbms_output.put_line(substrb(p_txt, (i - 1) * 250 + 1, 250));
END LOOP;
END;
FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
RETURN VARCHAR2 IS
v_first INT;
v_last INT;
BEGIN
IF p_seq < 1 THEN
RETURN NULL;
END IF;
IF p_seq = 1 THEN
IF instr(p_str, p_division, 1, p_seq) = 0 THEN
RETURN p_str;
ELSE
RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);
END IF;
ELSE
v_first := instr(p_str, p_division, 1, p_seq - 1);
v_last := instr(p_str, p_division, 1, p_seq);
IF (v_last = 0) THEN
IF (v_first > 0) THEN
RETURN substr(p_str, v_first + 1);
ELSE
RETURN NULL;
END IF;
ELSE
RETURN substr(p_str, v_first + 1, v_last - v_first - 1);
END IF;
END IF;
END f_split_str;
PROCEDURE p_rows_column(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_cols IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc) IS
v_sql VARCHAR2(4000);
TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_keep v_keep_ind_by;
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_pivot v_pivot_ind_by;
v_keep_cnt INT;
v_pivot_cnt INT;
v_max_cols INT;
v_partition VARCHAR2(4000);
v_partition1 VARCHAR2(4000);
v_partition2 VARCHAR2(4000);
BEGIN
v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
v_pivot_cnt := length(p_pivot_cols) -
length(REPLACE(p_pivot_cols, ',')) + 1;
FOR i IN 1 .. v_keep_cnt LOOP
v_keep(i) := f_split_str(p_keep_cols, ',', i);
END LOOP;
FOR j IN 1 .. v_pivot_cnt LOOP
v_pivot(j) := f_split_str(p_pivot_cols, ',', j);
END LOOP;
v_sql := 'select max(count(*)) from ' || p_table || ' group by ';
FOR i IN 1 .. v_keep.LAST LOOP
v_sql := v_sql || v_keep(i) || ',';
END LOOP;
v_sql := rtrim(v_sql, ',');
EXECUTE IMMEDIATE v_sql
INTO v_max_cols;
v_partition := 'select ';
FOR x IN 1 .. v_keep.COUNT LOOP
v_partition1 := v_partition1 || v_keep(x) || ',';
END LOOP;
FOR y IN 1 .. v_pivot.COUNT LOOP
v_partition2 := v_partition2 || v_pivot(y) || ',';
END LOOP;
v_partition1 := rtrim(v_partition1, ',');
v_partition2 := rtrim(v_partition2, ',');
v_partition := v_partition || v_partition1 || ',' || v_partition2 ||
', row_number() over (partition by ' || v_partition1 ||
' order by ' || v_partition2 || ') rn from ' || p_table;
v_partition := rtrim(v_partition, ',');
v_sql := 'select ';
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ',';
END LOOP;
FOR i IN 1 .. v_max_cols LOOP
FOR j IN 1 .. v_pivot.COUNT LOOP
v_sql := v_sql || ' max(decode(rn,' || i || ',' || v_pivot(j) ||
',null))' || v_pivot(j) || '_' || i || ',';
END LOOP;
END LOOP;
IF p_where IS NOT NULL THEN
v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||
p_where || ') group by ';
ELSE
v_sql := rtrim(v_sql, ',') || ' from (' || v_partition ||
') group by ';
END IF;
FOR i IN 1 .. v_keep.COUNT LOOP
v_sql := v_sql || v_keep(i) || ',';
END LOOP;
v_sql := rtrim(v_sql, ',');
p_print_sql(v_sql);
OPEN p_refc FOR v_sql;
EXCEPTION
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT 'x' FROM dual WHERE 0 = 1;
END;
PROCEDURE p_rows_column_real(p_table IN VARCHAR2,
p_keep_cols IN VARCHAR2,
p_pivot_col IN VARCHAR2,
p_pivot_val IN VARCHAR2,
p_where IN VARCHAR2 DEFAULT NULL,
p_refc IN OUT refc) IS
v_sql VARCHAR2(4000);
TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_keep v_keep_ind_by;
TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
v_pivot v_pivot_ind_by;
v_keep_cnt INT;
v_group_by VARCHAR2(2000);
BEGIN
v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
FOR i IN 1 .. v_keep_cnt LOOP
v_keep(i) := f_split_str(p_keep_cols, ',', i);
END LOOP;
v_sql := 'select ' || 'cast(' || p_pivot_col ||
' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||
' group by ' || p_pivot_col;
EXECUTE IMMEDIATE v_sql BULK COLLECT
INTO v_pivot;
FOR i IN 1 .. v_keep.COUNT LOOP
v_group_by := v_group_by || v_keep(i) || ',';
END LOOP;
v_group_by := rtrim(v_group_by, ',');
v_sql := 'select ' || v_group_by || ',';
FOR x IN 1 .. v_pivot.COUNT LOOP
v_sql := v_sql || ' max(decode(' || p_pivot_col || ',' || chr(39) ||
v_pivot(x) || chr(39) || ',' || p_pivot_val ||
',null)) as "' || v_pivot(x) || '",';
END LOOP;
v_sql := rtrim(v_sql, ',');
IF p_where IS NOT NULL THEN
v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||
v_group_by;
ELSE
v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_by;
END IF;
p_print_sql(v_sql);
OPEN p_refc FOR v_sql;
EXCEPTION
WHEN OTHERS THEN
OPEN p_refc FOR
SELECT 'x' FROM dual WHERE 0 = 1;
END;
END;
/
4.
多列转换成字符串
CREATE TABLE t_col_str AS
SELECT * FROM t_col_row;
这个比较简单,用||或concat函数可以实现:
SELECT concat('a','b') FROM dual;
4.1
|| OR CONCAT
适用范围:8i,9i,10g及以后版本
SELECT * FROM t_col_str;
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_str;
5.
多行转换成字符串
CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10));
INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(1,'b');
INSERT INTO t_row_str VALUES(1,'c');
INSERT INTO t_row_str VALUES(2,'a');
INSERT INTO t_row_str VALUES(2,'d');
INSERT INTO t_row_str VALUES(2,'e');
INSERT INTO t_row_str VALUES(3,'c');
COMMIT;
SELECT * FROM t_row_str;
5.1
MAX + DECODE
适用范围:8i,9i,10g及以后版本
SELECT id,
MAX(decode(rn, 1, col, NULL)) ||
MAX(decode(rn, 2, ',' || col, NULL)) ||
MAX(decode(rn, 3, ',' || col, NULL)) str
FROM (SELECT id,
col,
row_number() over(PARTITION BY id ORDER BY col) AS rn
FROM t_row_str) t
GROUP BY id
ORDER BY 1;
5.2
ROW_NUMBER + LEAD
适用范围:8i,9i,10g及以后版本
SELECT id, str
FROM (SELECT id,
row_number() over(PARTITION BY id ORDER BY col) AS rn,
col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) ||
lead(',' || col, 2) over(PARTITION BY id ORDER BY col) ||
lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str
FROM t_row_str)
WHERE rn = 1
ORDER BY 1;
5.3
MODEL
适用范围:10g及以后版本
SELECT id, substr(str, 2) str FROM t_row_str
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
MEASURES (CAST(col AS VARCHAR2(20)) AS str)
RULES UPSERT
ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
(str[0] = str[0] || ',' || str[iteration_number+1])
ORDER BY 1;
5.4
SYS_CONNECT_BY_PATH
适用范围:8i,9i,10g及以后版本
SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;
适用范围:10g及以后版本
SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id;
5.5
WMSYS.WM_CONCAT
适用范围:10g及以后版本
这个函数预定义按','分隔字符串,若要用其他符号分隔可以用,replace将','替换。
SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/') str
FROM t_row_str
GROUP BY id;
6.
字符串转换成多列
其实际上就是一个字符串拆分的问题。
CREATE TABLE t_str_col AS
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_str;
SELECT * FROM t_str_col;
6.1
SUBSTR + INSTR
适用范围:8i,9i,10g及以后版本
SELECT id,
c123,
substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,
substr(c123,
instr(c123 || ',', ',', 1, 1) + 1,
instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,
substr(c123,
instr(c123 || ',', ',', 1, 2) + 1,
instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3
FROM t_str_col
ORDER BY 1;
6.2
REGEXP_SUBSTR
适用范围:10g及以后版本
SELECT id,
c123,
rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 1), ',') AS c1,
rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 2), ',') AS c2,
rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 3), ',') AS c3
FROM t_str_col
ORDER BY 1;
7.
字符串转换成多行
CREATE TABLE t_str_row AS
SELECT id,
MAX(decode(rn, 1, col, NULL)) ||
MAX(decode(rn, 2, ',' || col, NULL)) ||
MAX(decode(rn, 3, ',' || col, NULL)) str
FROM (SELECT id,
col,
row_number() over(PARTITION BY id ORDER BY col) AS rn
FROM t_row_str) t
GROUP BY id
ORDER BY 1;
SELECT * FROM t_str_row;
7.1
UNION ALL
适用范围:8i,9i,10g及以后版本
SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv
FROM t_str_row
UNION ALL
SELECT id,
2 AS p,
substr(str,
instr(str || ',', ',', 1, 1) + 1,
instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
FROM t_str_row
UNION ALL
SELECT id,
3 AS p,
substr(str,
instr(str || ',', ',', 1, 1) + 1,
instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
FROM t_str_row
ORDER BY 1, 2;
适用范围:10g及以后版本
SELECT id, 1 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 1), ',') AS cv
FROM t_str_row
UNION ALL
SELECT id, 2 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 2), ',') AS cv
FROM t_str_row
UNION ALL
SELECT id, 3 AS p, rtrim(regexp_substr(str||',', '.*?' || ',',1,3), ',') AS cv
FROM t_str_row
ORDER BY 1, 2;
7.2
VARRAY
适用范围:8i,9i,10g及以后版本
要创建一个可变数组:
CREATE OR REPLACE TYPE ins_seq_type IS VARRAY(8) OF NUMBER;
SELECT * FROM TABLE(ins_seq_type(1, 2, 3, 4, 5));
SELECT t.id,
c.column_value AS p,
substr(t.ca,
instr(t.ca, ',', 1, c.column_value) + 1,
instr(t.ca, ',', 1, c.column_value + 1) -
(instr(t.ca, ',', 1, c.column_value) + 1)) AS cv
FROM (SELECT id,
',' || str || ',' AS ca,
length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
FROM t_str_row) t
INNER JOIN TABLE(ins_seq_type(1, 2, 3)) c ON c.column_value <=
t.cnt
ORDER BY 1, 2;
7.3
SEQUENCE SERIES
这类方法主要是要产生一个连续的整数列,产生连续整数列的方法有很多,主要有:
CONNECT BY,ROWNUM+all_objects,CUBE等。
适用范围:8i,9i,10g及以后版本
SELECT t.id,
c.lv AS p,
substr(t.ca,
instr(t.ca, ',', 1, c.lv) + 1,
instr(t.ca, ',', 1, c.lv + 1) -
(instr(t.ca, ',', 1, c.lv) + 1)) AS cv
FROM (SELECT id,
',' || str || ',' AS ca,
length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
FROM t_str_row) t,
(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c
WHERE c.lv <= t.cnt
ORDER BY 1, 2;
SELECT t.id,
c.rn AS p,
substr(t.ca,
instr(t.ca, ',', 1, c.rn) + 1,
instr(t.ca, ',', 1, c.rn + 1) -
(instr(t.ca, ',', 1, c.rn) + 1)) AS cv
FROM (SELECT id,
',' || str || ',' AS ca,
length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
FROM t_str_row) t,
(SELECT rownum rn FROM all_objects WHERE rownum <= 5) c
WHERE c.rn <= t.cnt
ORDER BY 1, 2;
SELECT t.id,
c.cb AS p,
substr(t.ca,
instr(t.ca, ',', 1, c.cb) + 1,
instr(t.ca, ',', 1, c.cb + 1) -
(instr(t.ca, ',', 1, c.cb) + 1)) AS cv
FROM (SELECT id,
',' || str || ',' AS ca,
length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
FROM t_str_row) t,
(SELECT rownum cb FROM (SELECT 1 FROM dual GROUP BY CUBE(1, 2))) c
WHERE c.cb <= t.cnt
ORDER BY 1, 2;
适用范围:10g及以后版本
SELECT t.id,
c.lv AS p,
rtrim(regexp_substr(t.str || ',', '.*?' || ',', 1, c.lv), ',') AS cv
FROM (SELECT id,
str,
length(regexp_replace(str || ',', '[^' || ',' || ']', NULL)) AS cnt
FROM t_str_row) t
INNER JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c ON c.lv <= t.cnt
ORDER BY 1, 2;
7.4
HIERARCHICAL + DBMS_RANDOM
适用范围:10g及以后版本
SELECT id,
LEVEL AS p,
rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
FROM t_str_row
CONNECT BY id = PRIOR id
AND PRIOR dbms_random.VALUE IS NOT NULL
AND LEVEL <=
length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;
7.5
HIERARCHICAL + CONNECT_BY_ROOT
适用范围:10g及以后版本
SELECT id,
LEVEL AS p,
rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
FROM t_str_row
CONNECT BY id = connect_by_root id
AND LEVEL <=
length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;
7.6
MODEL
适用范围:10g及以后版本
SELECT id, p, cv FROM t_str_row
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY( 0 AS p)
MEASURES( str||',' AS cv)
RULES UPSERT
(cv
[ FOR p
FROM 1 TO length(regexp_replace(cv[0],'[^'||','||']',null))

例子:
SELECT t.dutyname , substr(sys_connect_by_path(t.username, ','), 2) str
FROM (SELECT dutyname, username, row_number() over(PARTITION BY dutyname ORDER BY username) rn
FROM test) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND dutyname = PRIOR dutyname;


posted @ 2015-07-27 23:52 鸿雁 阅读(175) | 评论 (0)编辑 收藏

Https通讯原理

Https是什么? Https是基于安全目的的Http通道,其安全基础由SSL层来保证。最初由netscape公司研发,主要提供了通讯双方的身份认证和加密通信方法。现在广泛应用于互联网上安全敏感通讯。 Https与Http主要区别 协议基础不同:Https在Http下加入了SSL层, 通讯方式不同:Https在数据通信之前需要客户端、服务器进行握手(身份认证),建立连接后,传输数据经过加密,通信端口443。 Http传输数据不加密,明文,通信端口80。 SSL协议基础 SSL协议位于TCP/IP协议与各种应用层协议之间,本身又分为两层: SSL记录协议(SSL Record Protocol):建立在可靠传输层协议(TCP)之上,为上层协议提供数据封装、压缩、加密等基本功能。 SSL握手协议(SSL Handshake Procotol):在SSL记录协议之上,用于实际数据传输前,通讯双方进行身份认证、协商加密算法、交换加密密钥等。 SSL协议通信过程 (1) 浏览器发送一个连接请求给服务器;服务器将自己的证书(包含服务器公钥S_PuKey)、对称加密算法种类及其他相关信息返回客户端; (2) 客户端浏览器检查服务器传送到CA证书是否由自己信赖的CA中心签发。若是,执行4步;否则,给客户一个警告信息:询问是否继续访问。 (3) 客户端浏览器比较证书里的信息,如证书有效期、服务器域名和公钥S_PK,与服务器传回的信息是否一致,如果一致,则浏览器完成对服务器的身份认证。 (4) 服务器要求客户端发送客户端证书(包含客户端公钥C_PuKey)、支持的对称加密方案及其他相关信息。收到后,服务器进行相同的身份认证,若没有通过验证,则拒绝连接; (5) 服务器根据客户端浏览器发送到密码种类,选择一种加密程度最高的方案,用客户端公钥C_PuKey加密后通知到浏览器; (6) 客户端通过私钥C_PrKey解密后,得知服务器选择的加密方案,并选择一个通话密钥key,接着用服务器公钥S_PuKey加密后发送给服务器; (7) 服务器接收到的浏览器传送到消息,用私钥S_PrKey解密,获得通话密钥key。 (8) 接下来的数据传输都使用该对称密钥key进行加密。 上面所述的是双向认证 SSL 协议的具体通讯过程,服务器和用户双方必须都有证书。由此可见,SSL协议是通过非对称密钥机制保证双方身份认证,并完成建立连接,在实际数据通信时通过对称密钥机制保障数据安全性

posted @ 2014-06-13 16:16 鸿雁 阅读(169) | 评论 (0)编辑 收藏

8种Nosql数据库系统对比

1. CouchDB
•所用语言: Erlang
•特点:DB一致性,易于使用
•使用许可: Apache
•协议: HTTP/REST
•双向数据复制,
•持续进行或临时处理,
•处理时带冲突检查,
•因此,采用的是master-master复制(见编注2)
•MVCC – 写操作不阻塞读操作
•可保存文件之前的版本
•Crash-only(可靠的)设计
•需要不时地进行数据压缩
•视图:嵌入式 映射/减少
•格式化视图:列表显示
•支持进行服务器端文档验证
•支持认证
•根据变化实时更新
•支持附件处理
•因此, CouchApps(独立的 js应用程序)
•需要 jQuery程序库
最佳应用场景:适用于数据变化较少,执行预定义查询,进行数据统计的应用程序。适用于需要提供数据版本支持的应用程序。 例如: CRM、CMS系统。
master-master复制对于多站点部署是非常有用的。 (编注2:master-master复制:是一种数据库同步方法,允许数据在一组计算机之间共享数据,
并且可以通过小组中任意成员在组内进行数据更新。)

2. Redis
•所用语言:C/C++
•特点:运行异常快
•使用许可: BSD
•协议:类 Telnet
•有硬盘存储支持的内存数据库,
•但自2.0版本以后可以将数据交换到硬盘(注意, 2.4以后版本不支持该特性!)
•Master-slave复制(见编注3)
•虽然采用简单数据或以键值索引的哈希表,但也支持复杂操作,例如 ZREVRANGEBYSCORE。
•INCR & co (适合计算极限值或统计数据)
•支持 sets(同时也支持 union/diff/inter)
•支持列表(同时也支持队列;阻塞式 pop操作)
•支持哈希表(带有多个域的对象)
•支持排序 sets(高得分表,适用于范围查询)
•Redis支持事务 •支持将数据设置成过期数据(类似快速缓冲区设计)
•Pub/Sub允许用户实现消息机制
最佳应用场景:适用于数据变化快且数据库大小可遇见(适合内存容量)的应用程序。 例如:股票价格、数据分析、实时数据搜集、实时通讯。
(编注3:Master-slave复制:如果同一时刻只有一台服务器处理所有的复制请求,这被称为 Master-slave复制,通常应用在需要提供高可用性的服务器集群。)

 3. MongoDB
•所用语言:C++
•特点:保留了SQL一些友好的特性(查询,索引)。
•使用许可: AGPL(发起者: Apache)
•协议: Custom, binary( BSON)
•Master/slave复制(支持自动错误恢复,使用 sets 复制)
•内建分片机制
•支持 javascript表达式查询
•可在服务器端执行任意的 javascript函数
•update-in-place支持比CouchDB更好
•在数据存储时采用内存到文件映射
•对性能的关注超过对功能的要求
•建议最好打开日志功能(参数 –journal)
•在32位操作系统上,数据库大小限制在约2.5Gb
•空数据库大约占 192Mb
•采用 GridFS存储大数据或元数据(不是真正的文件系统)
最佳应用场景:适用于需要动态查询支持;需要使用索引而不是 map/reduce功能;需要对大数据库有性能要求;需要使用 CouchDB但因为数据改变太频繁而占满内存的应用程序。 例如:你本打算采用 MySQL或 PostgreSQL,但因为它们本身自带的预定义栏让你望而却步。

4. Riak
 •所用语言:Erlang和C,以及一些Javascript
•特点:具备容错能力
•使用许可: Apache
•协议: HTTP/REST或者 custom binary
•可调节的分发及复制(N, R, W)
•用 JavaScript or Erlang在操作前或操作后进行验证和安全支持。
•使用JavaScript或Erlang进行 Map/reduce
•连接及连接遍历:可作为图形数据库使用
•索引:输入元数据进行搜索(1.0版本即将支持)
•大数据对象支持( Luwak)
•提供“开源”和“企业”两个版本
•全文本搜索,索引,通过 Riak搜索服务器查询( beta版)
•支持Masterless多站点复制及商业许可的 SNMP监控
最佳应用场景:适用于想使用类似 Cassandra(类似Dynamo)数据库但无法处理 bloat及复杂性的情况。适用于你打算做多站点复制,但又需要对单个站点的扩展性,可用性及出错处理有要求的情况。 例如:销售数据搜集,工厂控制系统;对宕机时间有严格要求;可以作为易于更新的 web服务器使用。
 
5. Membase
•所用语言: Erlang和C
•特点:兼容 Memcache,但同时兼具持久化和支持集群
•使用许可: Apache 2.0
•协议:分布式缓存及扩展
•非常快速(200k+/秒),通过键值索引数据
•可持久化存储到硬盘
•所有节点都是唯一的( master-master复制)
•在内存中同样支持类似分布式缓存的缓存单元
•写数据时通过去除重复数据来减少 IO
•提供非常好的集群管理 web界面
•更新软件时软无需停止数据库服务
•支持连接池和多路复用的连接代理
最佳应用场景:适用于需要低延迟数据访问,高并发支持以及高可用性的应用程序 例如:低延迟数据访问比如以广告为目标的应用,高并发的 web 应用比如网络游戏(例如 Zynga)

6. Neo4j
•所用语言: Java
•特点:基于关系的图形数据库
•使用许可: GPL,其中一些特性使用 AGPL/商业许可
•协议: HTTP/REST(或嵌入在 Java中)
•可独立使用或嵌入到 Java应用程序
•图形的节点和边都可以带有元数据
•很好的自带web管理功能
•使用多种算法支持路径搜索
•使用键值和关系进行索引
•为读操作进行优化
•支持事务(用 Java api)
•使用 Gremlin图形遍历语言
•支持 Groovy脚本
•支持在线备份,高级监控及高可靠性支持使用 AGPL/商业许可 最佳应用场景:适用于图形一类数据。这是 Neo4j与其他nosql数据库的最显著区别 例如:社会关系,公共交通网络,地图及网络拓谱

7. Cassandra
•所用语言: Java
•特点:对大型表格和 Dynamo支持得最好
•使用许可: Apache
•协议: Custom, binary (节约型)
•可调节的分发及复制(N, R, W)
•支持以某个范围的键值通过列查询
•类似大表格的功能:列,某个特性的列集合
•写操作比读操作更快
•基于 Apache分布式平台尽可能地 Map/reduce
•我承认对 Cassandra有偏见,一部分是因为它本身的臃肿和复杂性,也因为 Java的问题(配置,出现异常,等等)
最佳应用场景:当使用写操作多过读操作(记录日志)如果每个系统组建都必须用 Java编写(没有人因为选用 Apache的软件被解雇) 例如:银行业,金融业(虽然对于金融交易不是必须的,但这些产业对数据库的要求会比它们更大)写比读更快,所以一个自然的特性就是实时数据分析

8. HBase (配合 ghshephard使用)
•所用语言: Java
•特点:支持数十亿行X上百万列
•使用许可: Apache
•协议:HTTP/REST (支持 Thrift,见编注4)
•在 BigTable之后建模
•采用分布式架构 Map/reduce
•对实时查询进行优化
•高性能 Thrift网关
•通过在server端扫描及过滤实现对查询操作预判
•支持 XML, Protobuf, 和binary的HTTP
•Cascading, hive, and pig source and sink modules
•基于 Jruby( JIRB)的shell
•对配置改变和较小的升级都会重新回滚
•不会出现单点故障
•堪比MySQL的随机访问性能 最佳应用场景:适用于偏好BigTable:)并且需要对大数据进行随机、实时访问的场合。 例如: Facebook消息数据库(更多通用的用例即将出现)

posted @ 2014-05-23 11:19 鸿雁 阅读(250) | 评论 (0)编辑 收藏

Oracle 10g内存结构之共享池的相关知识及使用简介

SGA中的第三个组成部分是共享池。共享池是对sql ,pl/sql 程序进行语法分析、编译、执行的内存区域。共享池包括库缓冲区(library cache)、数据字典缓冲区(Data Directory Cache)用户全局区(User Global Area)。其中库缓冲区含有Sql 语句的分析码、执行计划;数据字典缓冲区含有从数据字典中得到的表、列定义、权限。用户全局区包含用户的MTS 会话信息。

共享池主要用于对SQL 、pl/sql 程序语句进行语法分析、编译、执行、所以,如果应用中药运行大量存储过程或包,则要增加共享池的尺寸。共享池的大小由参数SHARE_POOL_SIZE确定。要了解共享池大小,可以用以下方法:

方法一:

 

  1. ………  
  2. Shared_Pool_size = 52428800 
  3. ….. 

 

方法二:

 

  1. SQL> select name,value from v$parameter where name like ‘%size’; 

 

方法三:

 

  1. SQL> show parameter share_pool_size 

 

共享池应计算存储过程、包等的成功率。

可以查询数据字典 v$rowcache 了解数据字典的成功与失败次数。

 

  1. SQL> select sum(gets) “dictionary gets”,  
  2. Sum(getmisses) “dictionary cache getmisses”  
  3. From v$rowcache ; 

 

其中gets 表示读取某一类数据字典的成功次数,getsmisses 表示读取某一类数据字典的失败次数。此外还可以通过查询结果计算共享池中读取数据字典的成功率

 

  1. SQL> select parameter, get, getmisses, getmisses/(getmisses+gets)*100 “miss ratio”,  
  2. (1-    (sum(getmisses)/(sum(getmisses)+sum(gets)) ) ) *100 “hit ratio”  
  3. From v$rowcache  
  4. Where gets+getmisses<>0  
  5. Group by parameter,gets,getmisses; 

 

查询数据字典 v$librarycache 可以计算共享池中库缓存的失败率,结果应该小于1%。

 

  1. SQL>select sum(pins) “ total pins”, sum(reloads) “ total reloads”, sum(reloads)/sum(pins)*100 libarycache  from  v$librarycache; 

 

其中 total pins 表示驻留内存的次数, total reloads 表示重新加载到内存的次数,librarycache 表示失败率。

上面分析了系统全局区的三个组成部分-----数据缓冲区、日志缓冲区及共享池,如果要得到SGA的总大小,可以在SQL*Plus中使用show sga 命令。

SQL>show sga或查询数据字典

SQL> select * from v$sga;

如果要查询某个参数的大小,可以查询数据字典v_$sagstat,通过计算可以知道sga的使用空间与空闲空间的比。

 

  1. SQL>col OBJECT_NAME format a20  
  2. SQL> col 空闲空间百分比(%) format 90.99  
  3. SQL> select name,  
  4. Sgasize/1024/1024  “allocated(M)” ,  
  5. Bytes/1024        “空闲空间(k)” ,  
  6. Round(bytes/sagsize*100,2)  “空闲空间百分比(%)”  
  7. From ( select sum(bytes) sgasize from sys.v_$sgastat) s ,sys.v_$sgastat f  
  8. Where f.name=’free memory’ ;  

 

关于Oracle 10g内存结构之共享池的相关知识及使用方法就介绍到这里了,希望本次的介绍能够对您有所收获!

posted @ 2014-05-17 23:37 鸿雁 阅读(116) | 评论 (0)编辑 收藏

Oracle 10g内存结构之系统全局区简介

我们知道,内存结构是Oracle体系结构中最重要的部分之一。按照系统对内存使用方法的不同,可以分为系统全局区(SGA)、程序全局区(PGA)、排序区(Sort Area)、大池(Large Pool)、及java池(java Pool),本文我们先介绍一下Oracle 10g内存结构之系统全局区的内容,接下来我们就开始介绍这部分内容。

系统全局区(System Global Area)

它是一组为系统分配的内存共享结构,可以包含一个数据库实例的数据和控制信息。如果多个用户连接到一个实例,在实例的系统全局区中,数据可以被多个用户共享,所以又称共享全局区。系统全局区按其作用不同,可以分为数据缓冲区、日志缓冲区及共享池。

数据缓冲区:

数据缓冲区用于从磁盘读入的数据,供所有用户共享。

修改的数据、插入的数据存储在数据缓冲区中,修改完成或DBWR进程的其他条件引发时,数据被写入数据文件

数据缓冲区工作原理:

LRU (Least recently used):最近最少使用原则的缩写,是一种数据缓冲区的一种管理机制,,只保留最近数据,不保留旧数据。

Dirty:表示脏数据,脏数据是修改后还没有写到数据文件的数据。

Oracle10g 的数据库内存的设置参数不再由DB_BLOCK_BUFFERS确定,而是由oracle的新参数DB_CACHE_SIZE 和DB_nK_CACHE_SIZE确定,不同的数据段可以使用不同的数据块。大表可以存储在大的数据块表空间中,小表可以存储在小的数据块表空间中,以优化i/o性能。对于系统表空间、临时表空间、及其它默认设置的表空间,可以使用标准的数据块DB_BLOCK_SIZE确定。

标准数据块DB_BLOCK_SIZE用于系统表空间及默认表空间,其他表空间可以使用非标准数据块BLOCKSIZE(创建表空间时使用),其值分别为 2k 4k 8k 16k 32k ,非标准数据块的数据缓冲区使用参数DB_Nk_CACHE_SIZE确定。

需要注意的是BLOCKSIZE不得用于标准块。如果设置了DB_BLOCK_SIZE=2048,则不得设置DB_2K_CACHE_SIZE,标准块必须使用参数DB_CACHE_SIZE 来设置。同时可以在线修改数据缓冲区参数:SQL> alter system set db_2k_cache_size = 10M ;如果要查询数据缓冲区大小,可以如下:SQL> show parameter db。

在创建不同数据块表空间时,要使用参数BLOCKSIZE指出数据块的大小,同时在参数文件中要使用DB_Nk_CACHE_SIZE 进行配置,与BLOCKSIZE的个数相对应,否则会出现错误。

设置动态内存时,可以将多个参数全部写入参数文件,格式如下:

  1. # cache and i/o  
  2.  DB_BLOCK_SIZE=4096 
  3.  DB_CACHE_SIZE=20971520 
  4.  DB_2K_CACHE_SIZE=8M 
  5.  DB_8K_CACHE_SIZE=4M 
  6.  …….. 

其中,参数 DB_CACHE_SIZE 只适用于系统表空间、临时表空间、及默认表空间,DB_2K_CACHE_SIZE  适合 BLOCKSIZE 为2K的表空间。8K 也是一样的道理。

数据缓冲区对数据库德存取速度又直接影响。一般的缓冲区命中率应该在90% 以上。例如,使用数据字典 v$sysstat 计算数据缓冲区命中率:

  1. SQL> select a.value+b.value “logical_reads” , c.value “phys_reads”,  
  2.       Round(100* ( ( a.value+b.value)- c.value) /  
  3.       ( a.value+b.value ) ) “buffer hit radio “  
  4.       From v$sysstat a, v$sysstat b,v$sysstat c  
  5.       Where a.statistic#=38 and b.statistic#=39 and c.statistic#=40; 

下面是计算数据缓冲命中率的另一种方法:

  1. SQL> select name, value  
  2.       From v$sysstat  
  3.       Where name in ( ‘session logical reads’,’physical reads’,physical reads direct’, ‘physical reads direct (lob)’); 

其中:Session logical reads 为读的总量。Physical reads为从数据文件读。Physical reads direct 为从缓冲区读(不含lobs)。Physical reads direct (lobs) 为从缓冲区读(含lobs)。Hit Ratio = 1- ( ( physical reads- physical reads direct – physical reads direct(lob) ) /session logical reads) = 95%。

日志缓冲区

日志缓冲区用来存储数据库的修改信息。日志信息首先在日志缓冲区中产生,当日志缓冲区的日志达到一定数量时,由日志写入进程LGWR将日志数据写入日志文件组,再经过切换,由归档进程ARCH将日志数据写入归档介质。

日志缓冲区大小由参数LOG_BUFFER确定,要查询日志缓冲区大小可以用以下方法:

方法一:参数文件中:

  1.  ……  
  2.  Processes = 150 
  3.  Parallel_max_servers = 5 
  4. Log_buffer = 32768 
  5. …….. 

方法二:

  1. SQL> select name,value from v$parameter where name like ‘%buffer’; 

方法三:

  1. SQL> show parameter log_buffer 

对于日志缓冲区而言可以计算失败率,使用数据字典v$latch 计算日志缓冲区的失败率

  1. SQL>select name,gets,misses,immediate_gets,immediate_misses,  
  2.      Decode(gets,0,0,misses/gets*100) ratiol,  
  3.      Decode (immediate_gets+immediate_misses,0,0,  
  4. immediate_misses/(immediate_gets+immediate_misses)*100)   ratio2  
  5.      from v$latch  
  6.      where name in (‘redo allocation’, ‘redo copy’); 

其中

Gets 表示成功等待日志缓冲区的次数。

Immediate gets 表示成功立即得到日志缓冲区的次数。

Immediate misses 表示未成功立即得到日志缓冲区的次数。

等待表示日志在进入日志缓冲区时,因为日志缓冲区过小而没有空闲空间,所以日志缓冲区的失败可以表示日志缓冲区是否足够大,不够大时,用户的日志写将产生等待过程。日志缓冲区的失败率应该小于1%。

此外,可以查询用户进程等待日志缓冲区时的次数,通过数据字典v$sysstat 得到:

  1. SQL> select name,value from v$sysstat  
  2.       Where name = ‘ redo buffer allocation retries’ ; 

关于Oracle 10g内存结构之系统全局区的相关知识就介绍到这里了,希望本次的介绍能够对您有所收获!

posted @ 2014-05-17 23:31 鸿雁 阅读(128) | 评论 (0)编辑 收藏

Oracle数据库命中率优化原理解析

本文是关于Oracle数据库调试与优化方面的文章,主要介绍Oracle数据库中命中率相关的问题,包括不同的算法之间性能的比对。关于Oracle中各个命中率的计算以及相关的调优 1) Library Cache的命中率: 计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pi

本文是关于Oracle数据库调试与优化方面的文章,主要介绍Oracle数据库中命中率相关的问题,包括不同的算法之间性能的比对。关于Oracle中各个命中率的计算以及相关的调优

1)Library Cache的命中率:计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)

1
SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;

通常在98%以上,否则,需要要考虑加大共享池,绑定变量,修改cursor_sharing等参数。


2)计算共享池内存使用率:

1
2
SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'
FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';

其中: &TSP_IN_M是你的总的共享池的SIZE(M)

共享池内存使用率,应该稳定在75%-90%间,太小浪费内存,太大则内存不足。

查询空闲的共享池内存:

1
2
SELECT * FROM V$SGASTAT WHERE
NAME = 'free memory' AND POOL = 'shared pool';

3)db buffer cache命中率:计算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]

1
2
3
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) 
"Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';

通常应在90%以上,否则,需要调整,加大DB_CACHE_SIZE

外一种计算命中率的方法(摘自ORACLE官方文档<<数据库性能优化>>):

命中率的计算公式为:

Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / (db block gets + consistent gets - physical reads direct - physical reads direct (lob))

分别代入上一查询中的结果值,就得出了Buffer cache的命中率

1
2
3
4
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN(
'session logical reads', 'physical reads'
'physical reads direct', 'physical reads direct (lob)'
'db block gets', 'consistent gets');

4)数据缓冲区命中率

1
2
3
4
5
SQL> select value from v$sysstat where name ='physical reads'
SQL> select value from v$sysstat where name ='physical reads direct'
SQL> select value from v$sysstat where name ='physical reads direct (lob)'
SQL> select value from v$sysstat where name ='consistent gets'
SQL> select value from v$sysstat where name = 'db block gets';

这里命中率的计算应该是令 x = physical reads direct + physical reads direct (lob),命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100,通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区

5)共享池的命中率:

1
2
select sum(pinhits-reloads)/sum(pins)*100 "hit radio"
from v$librarycache;

假如共享池的命中率低于95%,就要考虑调整应用(通常是没使用bind var )或者增加内存

6)计算在内存中排序的比率:

1
SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)';

查询内存排序数 

1
SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)';

查询磁盘排序数caculate sort in memory ratio 

1
SELECT round(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM dual;

此比率越大越好,太小整要考虑调整,加大PGA

7)PGA的命中率:

计算公式:BP x 100 / (BP + EBP)

BP: bytes processed

EBP: extra bytes read/written

1
SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage';

或者从OEM的图形界面中查看

我们可以查看一个视图以获取Oracle的建议值:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT 
FROM V$PGA_TARGET_ADVICE; 
The output of this query might look like the following: 
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
 ---------- -------------- -------------------- 
63 23 367 
125 24 30 
250 30 3 
375 39 0 
500 58 0 
600 59 0 
700 59 0 
800 60 0 
900 60 0

在此例中:PGA至少要分配375M

我个人认为PGA命中率不应该低于50%

以下的SQL统计sql语句执行在三种模式的次数: optimal memory size, one-pass memory size, multi-pass memory size:

1
2
3
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total,4)) 
percentage FROM (SELECT name, value cnt, (sum(value) over ()) 
total FROM V$SYSSTAT WHERE name like 'workarea exec%');

8)共享区字典缓存区命中率

计算公式:SUM(gets - getmisses - usage -fixed) / SUM(gets)

命中率应大于0.85

1
select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;

9)数据高速缓存区命中率

计算公式:1-(physical reads / (db block gets + consistent gets))

命中率应大于0.90最好

1
select name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets');

10)共享区库缓存区命中率

计算公式:SUM(pins - reloads) / SUM(pins)

命中率应大于0.99

1
select sum(pins-reloads)/sum(pins) from v$librarycache;

11)检测回滚段的争用

SUM(waits)值应小于SUM(gets)值的1%

1
select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat;

12)检测回滚段收缩次数

1
2
select name,shrinks from v$rollstat, v$rollname 
where v$rollstat.usn = v$rollname.usn;

 

几个常用的检查语句

1. 查找排序最多的SQL:

1
2
SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS 
FROM V$SQLAREA ORDER BY SORTS DESC;


2.查找磁盘读写最多的SQL:

1
2
3
4
SELECT * FROM (SELECT sql_text,disk_reads "total disk"
executions "total exec",disk_reads/executions "disk/exec"
FROM v$sql WHERE executions>0 and is_obsolete='N' ORDER BY
4 desc) WHERE ROWNUM<11 ;


3.查找工作量最大的SQL(实际上也是按磁盘读写来排序的):

1
2
3
4
5
6
7
8
select substr(to_char(s.pct, '99.00'), 2) || '%' load,
s.executions executes,p.sql_text from(select address,disk_reads,executions,pct,rank() over 
(order by disk_reads desc) ranking from (select
address,disk_reads,executions,100 * ratio_to_report
(disk_reads) over () pct from sys.v_$sql where
command_type != 47) where disk_reads > 50 * executions) s,
ys.v_$sqltext p where s.ranking <= 5 and p.address = s.address 
order by 1, s.address, p.piece;

  

4. 用下列SQL工具找出低效SQL:

1
2
3
4
5
select executions,disk_reads,buffer_gets,round((buffer_gets-
disk_reads)/buffer_gets,2) Hit_radio,round(disk_reads/executions,2)
reads_per_run,sql_text From v$sqlarea Where executions>0 and
buffer_gets >0 and (buffer_gets-disk_reads)/buffer_gets<0.8 
Order by 4 desc;

  

5、根据sid查看对应连接正在运行的sql

1
2
3
4
5
6
7
select /*+ push_subq */command_type,sql_text,sharable_mem,persistent_mem,
runtime_mem,sorts,version_count,loaded_versions,open_versions,
users_opening,executions,users_executing,loads,first_load_time,
invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,
sysdate start_time,sysdate finish_time,’>’||address 
sql_address,’N’status From v$sqlarea Where address=
(select sql_address from v$session where sid=&sid);


***************Oracle 缓冲区命中率低的分析及解决办法******************

首先确定下面的查询结果:

1,缓冲区命中率的查询(是否低于90%):

1
2
3
select round((1 - sum(decode(name,'physical reads',value,0)) / 
(sum(decode(name,'db block gets',value,0)) + sum(decode(name,'
consistent gets',value,0))) ),4) *100 || '%' chitrati from v$sysstat;

  

2,使用率的查询(有无free状态的数据快.):

1
select count(*), status from v$bh group by status ;

  

3,相关等待事件的查询(是否有相关等待事件)

1
2
select event,total_waits from v$system_event where event 
in ('free buffer waits');

  

4,当前大小(是否已经很大)

1
2
select value/1024/1024 cache_size from v$parameter 
where name='db_cache_size'


5,top等待事件分析(Db file scatered read的比率是否大)

1
2
3
4
5
6
7
select event ,total_waits,suml from (select
event,total_waits,round(total_waits/sumt*100,2)||'%' suml 
from (select event,total_waits from v$system_event ), 
(select sum(total_waits) sumt from v$system_event) 
order by total_waits desc) where rownum<6 and event 
not like 'rdbms%' and event not like 'pmon%' and event 
not like 'SQL*Net%' and event not like 'smon%';

  

6,db_cache_advice建议值(9i后的新特性,可以根据他更好的调整cache_size)

1
2
select block_size,size_for_estimate,size_factor,
estd_physical_reads from v$db_cache_advice;


说明分析:

缓冲区命中率(低于90的命中率就算比较低的).

没有free不一定说明需要增加,还要结合当前cache_size的大小(我们是否还可以再增大,是否有需要增加硬件,增加开销),

空闲缓冲区等待说明进程找不到空闲缓冲区,并通过写出灰缓冲区,来加速数据库写入器生成空闲缓冲区,当DBWn将块写入磁盘后,灰数据缓冲区将被释放,以便重新使用.产生这种原因主要是:

1,DBWn可能跟不上写入灰缓冲区:i/0系统较慢,尽量将文件均匀的分布于所有设备,

2,缓冲区过小或过大。

3,可以增加db_writer_processes数量。

4,可能有很大的一个事物,或者连续的大事物

我们需要长期观察这个事件是否长期存在并数值一直在增大,如果一直在增大,则说明需要增大db_cache大小.或优化sql.

数据分散读等待,通常表现存在着与全表扫描相关的等待,逻辑读时,在内存中进行的全表扫描一般是零散地,而并非连续的被分散到缓冲区的各个部分,可能有索引丢失,或被仰制索引的存在。该等待时间在数据库会话等待多块io读取结束的时候产生,并把指定的块数离散的分布在数据缓冲区。这意味这全表扫描过多,或者io不足或争用,

存在这个事件,多数都是问题的,这说明大量的全部扫描而未采用索引.

db_cache_advice对我们调整db_cache_size大小有一定的帮助,但这只是一个参考,不一定很精确。

通过上面6种情况的综合分析,判断是否需要增加大cache_size. 或者把常用的(小)表放到keep区。

但多数的时候做这些不会解决质的问题,

而真正的问题主要是对sql语句的优化(如:是否存在大量的全表扫描等)

索引是在不需要改变程序的情况下,对数据库性能,sql语句提高的最实用的方法.

我在生产中遇到过类似的问题,200M的cache_size,命中率很低21%,但通过对sql语句的优化(添加索引,避免全表扫描),命中率增加到96%,程序运行时间由原来的2小时减少到不到10分钟.

这就提到了怎么定位高消耗的sql问题.全表扫描的问题,在这里不做细致的解说,这里只说明方法,我会在相关的章节专门介绍怎么使用这些工具

1,sql_trace跟踪session.用tkprof 分别输出磁盘读,逻辑读,运行时间长的sql进行优化.这些高消耗的sql一般都伴随着全表扫描.

2,statspack分析.在系统繁忙时期进行时间点的统计分析,产看TOP事件是否有Db file scatered read.并查看TOP sql语句是否存在问题等.

还要说一句:当然在硬件允许的情况下,尽量增大db_cache_size 减少磁盘读,但并不是越大越好,一定要根据自己的库数据量的程度来调节,因为大的db_cache_size同样会增大数据库管理的开销,当然可能开销并不会明显的影响数据库的性能,硬件价格也越来越低,这就需要我们具体问题具体分析了,在我看来物尽其用就最好了,尽量不要浪费,找到问题的本质。调优是一件很艺术的事。

***********************Oracle数据库缓冲区命中率*****************

1、查看Oracle数据库缓冲区命中率

1
2
3
4
select a.value + b.value "logical_reads", c.value "phys_reads"
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) 
"BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c 
where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;


2、Tags: oracle

数据库缓冲区命中率:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select value from v$sysstat where name ='physical reads'
value 3714179 
   
select value from v$sysstat where name ='physical reads direct'
value 0 
   
select value from v$sysstat where name ='physical reads direct(lob)';
value 0 
   
select value from v$sysstat where name ='consistent gets'
value 856309623 
   
select value from v$sysstat where name ='db block gets'
value 19847790

这里命中率的计算应该是

令x=physical reads direct + physical reads direct(lob)

命中率=100-(physical reads -x)/(consistent gets +db block gets -x)*100

通常如果发现命中率低于90%,则应该调整应用可以考虑是否增大数据加


共享池的命中率

1
select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;

如果共享池的命中率低于95%就要考虑调整应用(通常是没应用bind var)或者增加内存。

关于排序部分

1
select name,value from v$sysstat where name like '%sort%';

如果我们发现sorts(disk)/(sorts(memory)+sorts(disk))的比例过高,则通常意味着sort_area_size部分内存教较小,可考虑调整相应的参数。

  

关于log_buffer

1
2
select name,value from v$sysstat where name
in ('redo entries','redo buffer allocation retries');

假如redo buffer allocation retries/redo entries的比例超过1%我们就可以考虑增加log_buffer.

posted @ 2014-05-16 21:14 鸿雁 阅读(174) | 评论 (0)编辑 收藏

深入理解Oracle Statistic统计信息

1.统计信息简介 统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息。CBO正是根据这些统计信息数据,计算出不同访问路

1.统计信息简介

统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息。CBO正是根据这些统计信息数据,计算出不同访问路径下,不同join 方式下,各种计划的成本,最后选择出成本最小的计划。

在CBO(基于代价的优化器模式)条件下,SQL语句的执行计划由统计信息来决定,若没有统计信息则会采取动态采样的方式决定执行计划!可以说统计信息关乎sql的执行计划是否正确,属于sql执行的指导思想,oracle的初始化参数statistics_level控制收集统计信息的级别,有三个参数值:

BASIC :收集基本的统计信息

TYPICAL:收集大部分统计信息(数据库的默认设置)

ALL:收集全部统计信息

Oracle 10g之后,Query Optimizer就已经将CBO作为默认优化器,并且Oracle官方不再支持RBO服务。但是,通过优化器参数optimizer_mode,我们可以控制Oracle优化器生成不同模式下的执行计划。

关于优化器的请参考:《SQL性能优化之optimizer_mode参数原理渗透解析

2.如何收集统计信息

2.1 统计信息的内容:

1)Table statistics

Number of rows --行数量

Number of blocks --block数量

Average row length --平均行的长度.

2)Column statistics

Number of distinct values (NDV) in column --列中distinct的值

Number of nulls in column --列中null的值

Data distribution (histogram)  --数据分布

3)Index statistics

Number of leaf blocks --子节点的块数量

Levels --子节点数量

Clustering factor --集群因子

4)System statistics

I/O performance and utilization --IO性能和利用率

CPU performance and utilization --CPU的性能和利用率

2.2 收集统计信息

Oracle Statistic 的收集,可以使用analyze 命令,也可以使用DBMS_STATS 包来收集,Oracle 建议使用DBMS_STATS包来收集统计信息,因为DBMS_STATS包收集的更广,并且更准确。analyze 在以后的版本中可能会被移除。

DBMS_STATS常用的几个过程如下:

1
2
3
4
5
6
7
8
9
10
11
12
dbms_stats.gather_table_stats 收集表、列和索引的统计信息;
dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息;
dbms_stats.gather_index_stats 收集索引的统计信息;
dbms_stats.gather_system_stats 收集系统统计信息
dbms_stats.GATHER_DICTIONARY_STATS:所有字典对象的统计;
DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计
dbms_stats.delete_table_stats 删除表的统计信息
dbms_stats.delete_index_stats 删除索引的统计信息
dbms_stats.export_table_stats 输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 设置表的统计
dbms_stats.auto_sample_size

analyze 命令的语法如下:

1
2
3
SQL>analyze table tablename compute statistics;
SQL>analyze table tablename compute statistics for all indexes;
SQL>analyze table tablename delete statistics
2.3 统计信息的分类

Oracle 的Statistic 信息的收集分两种:自动收集和手工收集。

Oracle 的Automatic Statistics Gathering 是通过Scheduler 来实现收集和维护的。Job 名称是GATHER_STATS_JOB, 该Job收集数据库所有对象的2种统计信息:

(1)Missing statistics(统计信息缺失)

(2)Stale statistics(统计信息陈旧)

该Job 是在数据库创建的时候自动创建,并由Scheduler来管理。Scheduler 在maintenance windows open时运行gather job。 默认情况下,job 会在每天晚上10到早上6点和周末全天开启。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级,再开始进行统计信息。

Scheduler Job的stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False,那么GATHER_STATS_JOB 会中断, 而没有收集完的对象将在下次启动时继续收集。

Gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。 该过程收集对象statistics的条件如下:

(1)对象的统计信息之前没有收集过。

(2)当对象有超过10%的rows 被修改,此时对象的统计信息也称为stale statistics。

但是对于高度变化的表在白天的活动期间被TRUNCATE/DROP并重建或者块加载超过本身总大小10%的对象;我们可以将这些表上的统计设置为NULL

可以通过以下SQL来查看:

1
2
3
select job_name, program_name, enabled, stop_on_window_close
  from dba_scheduler_jobs
 where job_name = 'gather_stats_job';

为了决定是否对对象进行监控,Oracle 提供了一个参数STATISTICS_LEVEL。通过设置初始化参数STATISTIC_LEVEL 为TYPICAL 或ALL,就可以自动收集统计信息(默认值为TYPICAL,因此可以随即启用自动收集统计信息的功能)。STATISTIC_LEVEL 参数的值可以激活GATHER_STATS_JOB。

在10g中表监控默认是激活的,如果STATISTICS_LEVEL设置为basic,不仅不能监控表,而且将禁掉如下一些10g的新功能:

(1)ASH(Active Session History)

(2)ASSM(Automatic Shared Memory Management)

(3)AWR(Automatic Workload Repository)

(4)ADDM(Automatic Database Diagnostic Monitor)

1
2
3
4
5
sys@ORCL> show parameter statistics_level;
       
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
statistics_level                     string      TYPICAL

当启动对象的监控后,从上次统计信息收集之后的的信息,如inserts,updates,deletes 等,这些改变的信息会记录到user_tab_modifications 视图。

当对象的数据发生改变之后, 经过几分钟的延时,这些信息写入到user_tab_modifications视图,然后dbms_stats.flush_database_monitoring_info过程就会发现这些信息,并讲这些信息保存在内存中。

当监控的对象被修改的部分超过10%时,gather_database_stats 或者gather_schema_stats 过程就会去收集这些stale statistics

3.统计信息的存储位置以及常用数据字典

3.1 统计信息常用数据字典

统计信息收集如下数据:

(1)表自身的分析: 包括表中的行数,数据块数,行长等信息。

(2)列的分析:包括列值的重复数,列上的空值,数据在列上的分布情况。

(3)索引的分析: 包括索引叶块的数量,索引的深度,索引的聚合因子等。

这些统计信息存放在以下的数据字典里:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS
3.2 表的统计信息

包含表行数,使用的块数,空的块数,块的使用率,行迁移和链接的数量,pctfree,pctused的数据,行的平均大小:

1
2
3
4
5
6
7
SELECT NUM_ROWS, --表中的记录数
BLOCKS, --表中数据所占的数据块数
EMPTY_BLOCKS, --表中的空块数
AVG_SPACE, --数据块中平均的使用空间
CHAIN_CNT, --表中行连接和行迁移的数量
AVG_ROW_LEN --每条记录的平均长度
FROM USER_TABLES
3.3索引列的统计信息

包含索引的深度(B-Tree的级别),索引叶级的块数量,集群因子(clustering_factor), 唯一值的个数。

1
2
3
4
5
6
7
SELECT BLEVEL, --索引的层数
LEAF_BLOCKS, --叶子结点的个数
DISTINCT_KEYS, --唯一值的个数
AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数 
AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
CLUSTERING_FACTOR --群集因子
FROM USER_INDEXES
3.4 列的统计信息

包含唯一的值个数,列最大小值,密度(选择率),数据分布(直方图信息),NUll值个数

1
2
3
4
5
6
7
8
SELECT NUM_DISTINCT, --唯一值的个数
LOW_VALUE, --列上的最小值
HIGH_VALUE, --列上的最大值
DENSITY, --选择率因子(密度)
NUM_NULLS, --空值的个数
NUM_BUCKETS, --直方图的BUCKET个数
HISTOGRAM --直方图的类型
FROM USER_TAB_COLUMNS
对于统计信息的搜集,谈谈个人的几点理解:
1.统计信息默认是存放在数据字典表中的,也只有数据字典中的统计信息,才会影响到CBO。
2.DBMS_STATS 提供的CREATE_STAT_TABLE 过程,只是生成一个用户自定义的特定格式的表,用来存放统计信息罢了,这个表中的统计信息是不会影响到统计信息的。
3.GATHER 系列过程中,如果指定stattab,statid,statown 参数(也可以不指定),则是搜集的统计信息除了更新到数据字典外,还在statown 用户下的stattab 表中存放一份,标示为 statid;
4.EXPORT和IMPORT 系列的过程中,stattab,statid,statown 参数不能为空,分别表示把数据字典中的当前统计信息导出到用户自定义的表中,以及把用户表中的统计信息导入到数据字典中,很明显可以看出,这里的导入操作和上面GATHER 操作会改变统计信息,可能会引起执行执行计划的改变,因此要慎重操作。
5.每次统计信息搜集前,将旧的统计信息备份起来是很有必要的;特别是保留一份或多份系统在稳定时期的统计信息也是很有必要的。
6.多长时间搜集一次统计信息,对于统计信息如何备份和保留,搜集统计信息时如何选择合适的采样,并行,直方图设置等都比较重要,需要设计一个较好的统计信息搜集策略。

在OCP 10g考试中会有个跟统计信息相关的考试题:

1. In your Oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00 pm

every Friday. The job has successfully completed. Which three pieces of information would you check to

confirm that the statistics have been collected? (Choose three.)

A. average row size

B. last analyzed date

C. size of table in bytes

D. size of table in database blocks

E. number of free blocks in the free list

F. number of extents present in the table

Answer: ABD

posted @ 2014-05-16 19:08 鸿雁 阅读(202) | 评论 (0)编辑 收藏

Oracle统计信息的收集、管理与清除

     摘要: 以下测试环境为Oracle 10g 10.2.0.4版本,测试对Oracle的统计信息的收集与管理。首先依据dba_objects创建一张测试表: SQL> create table eygle as select * from dba_objects; Table created对该表进行统计信息收集,这里未指定method_opt,则Oracle将采用 FOR ALL COL...  阅读全文

posted @ 2014-05-16 18:21 鸿雁 阅读(122) | 评论 (0)编辑 收藏

oracle sql日期相关

     摘要: 获取昨天: select trunc(SYSDATE-1)  from dual; 检查一下: select to_char (trunc(SYSDATE-1),'yyyy-mm-dd HH24:MI:SS') from dual; 获取上个月第一天00:00:00:  select add_months(trunc(sysdate,'MON'),-1) from d...  阅读全文

posted @ 2014-05-15 17:36 鸿雁 阅读(297) | 评论 (0)编辑 收藏

Oracle 数据库启动的不同状态

Oracle数据库启动分为三个步骤:

nomount,找到初始化文件pfile或者spfile,创建SGA并启动后台进程但不允许访问数据库。

mount,根据初始化文件找到控制文件(Control File),为某些DBA活动装载数据库但不允许用户访问数据库。

open,根据控制文件找到数据文件(Data File),重做日志文件(Redo File),使用户可以访问数据库。

 

关闭数据库的4个不同命令

shutdown normal(等于shutdown)

正常是关闭的缺省方式正常的数据库关闭在下列情况下进行

• 不允许新的连接

• 等待会话结束

• 等待事务结束

• 做一个检查点并关闭数据文件

• 下一次启动时将不要求实例恢复

shutdown transactional

事务处理关闭防止客户丢失工作事务处理数据库关闭在下列情况下进行

• 不允许新的连接

• 不等待会话结束

• 等待事务结束

• 做一个检查点并关闭数据文件

• 下一次启动将不要求实例恢复

shutdown immediate

立即关闭数据库在下列情况下进行

• 不允许新的连接

• 不等待会话结束

• 不等待事务结束

• 未结束的事务自动回滚(rollback)

• 做一个检查点并关闭数据文件

• 下一次启动将不要求例程恢复

shutdown abort

如果正常和立即关闭选项不起作用可以中止当前数据库例程中止例程可以在下列情况下进行

• 不允许新的连接

• 不等待会话结束

• 不等待事务结束

• 不做检查点且没有关闭数据文件

• 下一次启动将要求实例恢复

 

 

启动数据库相关命令

startup(默认启动到open状态)

startup nomount

startup mount

alter database mount;

alter database open;

 

初始化文件

pfile,文本文件,可以手工编辑该文件

spfile,二进制文件,不能手工修改里面的参数,只能使用数据库命令进行修改,数据库启动默认使用spfile

 

查看初始化文件路径

show parameter spfile;

 

根据spfile创建pfile文件

create pfile from spfile;

create pfile='/u01/app/pfile.ora' fromspfile;

create spfile from pfile='/u01/app/pfile.ora';

 

查看控制文件的路径

select name from v$controlfile;

 

查看控制文件的内容,可以根据ctl.trc重建控制文件

alter database backup controlfile to trace as '/u01/app/ctl.trc';

 

查看数据文件的位置

select name from v$datafile;

 

查看重做日志文件的位置

select member from v$logfile;

 

数据库的密码文件

Windows,$ORACLE_HOME/database/PWDorcl.ora

Linux/Unix,$ORACLE_HOME/dbs/orapworcl

密码文件的作用是允许数据库的sysdba、sysoper用户通过口令来远登陆

 

查看数据库是否允许用户远程登陆数据库

show parameter remote_login_passwordfile;

如果remote_login_passwordfile为EXCLUSIVE则表示允许远程连接数据库

如果remote_login_passwordfile为NONE则表示不允许远程连接数据库

 

禁止用户远程登陆数据库(需要重启数据库生效)

alter system set remote_login_passwordfile=none scope=spfile;

 

表空间

select * from dba_tablespaces;

 

数据文件

select * from dba_data_files;

 

重做日志文件

select * from v$logfile;

 

重做日志文件的状态

STALE表示数据已经提交到数据库中,空白状态表示正在使用该文件

 

posted @ 2014-05-11 19:54 鸿雁 阅读(103) | 评论 (0)编辑 收藏

如何查看及解决最耗cpu的sql语句

1.      如何查看及解决最耗CPUSQL语句1.1.  top监控服务器负载

[root@node1 ~]# top

top - 22:51:02 up 56 min,  1 user,  load average: 0.00, 0.00, 0.00

Tasks:  96 total,   1 running,  95 sleeping,   0 stopped,   0 zombie

Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:   1035096k total,   351488k used,   683608k free,    24140k buffers

Swap:  2096472k total,        0k used,  2096472k free,   270360k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND               

    1 root      15   0  2084  660  560 S  0.0  0.1   0:00.22 init                    

    2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/0   

         如果发现user中的CPU过高,比如oracle中最高的进程pid1138782,占CPU27%,则执行下一步。

1.2.  查询数据库会话的sidserial#

进入数据库,根据oracle进程的pid查出对应数据库会话的sidserial#

select s.sid,s.serial#

from v$session s,v$process p

where s.paddr=p.addr and p.spid='1138782';

         查询出来的结果sidserial#分别为48256767

1.3.  查询SQL语句

根据数据库会话的sid查出具体的SQL语句:

desc v$sqltext

名称                                      是否为空? 类型

----------------------------------------- -------- --------------

ADDRESS                                            RAW(4)

HASH_VALUE                                         NUMBER

COMMAND_TYPE                                      NUMBER

PIECE                                               NUMBER

SQL_TEXT                                           VARCHAR2(64)

select sql_text

from v$sqltext a

where a.hash_value=(select sql_hash_value from v$session b

                   where b.sid='&sid')

order by piece;

输入 sid 的值:

1.4.  处理SQL语句

如果SQL语句影响了数据库的运行,可以killSQL语句的会话:

①在数据库中杀死SQL语句的会话:

alter system kill session '482,56767';

         如果不能在数据库中杀死SQL语句,可在LINUX系统中强制杀死Oracle进程

         ②在linux系统中强制杀死oracle进程

#kill -9 1138782

1.5.  SQL语句优化

最后可以根据步骤(3)查询出来的SQL语句进行优化,以避免再次出现上述消耗CPU的情况。

posted @ 2014-05-11 19:44 鸿雁 阅读(144) | 评论 (0)编辑 收藏

Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法

     摘要: 一. 分区表理论知识 Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。 Oracle的分区表可以包括多个分区,每个分区都是一个独立的段(SEGMENT),可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据,也可以通过在查询时直接指定分区的方法来进行查询。 ...  阅读全文

posted @ 2014-05-10 12:19 鸿雁 阅读(7657) | 评论 (0)编辑 收藏

Oracle分区表

1.分区表的维护注意事项
  1. 若分区表跨不同表空间,做导出、导入时目标数据库必须预建这些表空间。分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间!这些表空间不一定是用户的默认表空间,只要存在即可。如果有一个不存在,就会报错!
  2. 默认时,对分区表的许多表维护操作会使全局索引不可用,标记成UNUSABLE。 那么就必须重建整个全局索引或其全部分区。如果已被分区,Oracle 允许在用于维护操作的ALTER TABLE 语句中指定UPDATE GLOBAL INDEXES 来重载这个默认特性,指定这个子句也就告诉Oracle 当它执行维护操作的DDL 语句时更新全局索引,这提供了如下好处:
    1. 在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引;
    2. 因为没有被标记成UNUSABLE, 所以全局索引的可用性更高了,甚至正在执行分区的DDL 语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建它们;

另外在指定UPDATE GLOBAL INDEXES 之前还要考虑如下性能因素:

  1. 因为要更新事先被标记成UNUSABLE 的索引,所以分区的DDL 语句要执行更长时间,当然这要与先不更新索引而执行DDL 然后再重建索引所花的时间做个比较,一个适用的规则是如果分区的大小小于表的大小的5% ,则更新索引更快一点;
  2. DROP TRUNCATE 和EXCHANGE 操作也不那么快了,同样这必须与先执行DDL 然后再重建所有全局索引所花的时间做个比较;
  3. 要登记对索引的更新并产生重做记录和撤消记录,重建整个索引时可选择NOLOGGING;
  4. 重建整个索引产生一个更有效的索引,因为这更利于使用空间,再者重建索引时允许修改存储选项;
  5. 分区索引结构表不支持UPDATE GLOBAL INDEXES 子句。
2.普通表变为分区表

将已存在数据的普通表转变为分区表,没有办法通过修改属性的方式直接转化为分区表,必须通过重建的方式进行转变,一般可以有三种方法,视不同场景使用:

2.1方法一:利用原表重建分区表。

CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 

INSERT INTO t
  SELECT Rownum, SYSDATE - Rownum FROM Dba_Objects WHERE Rownum <= 5000;
COMMIT;

CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME) 
 (PARTITION P1 VALUES LESS THAN (TO_DATE('2000-1-1', 'YYYY-MM-DD')), 
 PARTITION P2 VALUES LESS THAN (TO_DATE('2002-1-1', 'YYYY-MM-DD')), 
 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 
 PARTITION P4 VALUES LESS THAN (MAXVALUE))
 AS SELECT ID, TIME FROM T; 

RENAME T TO T_OLD; 

RENAME T_NEW TO T; 

 

 

优点:方法简单易用,由于采用DDL语句,不会产生UNDO,且只产生少量REDO,效率相对较高,而且建表完成后数据已经在分布到各个分区中了。

不足:对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用性:适用于修改不频繁的表,在闲时进行操作,表的数据量不宜太大。

2.2方法二:使用交换分区的方法

Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 
INSERT INTO t
  SELECT Rownum, SYSDATE - Rownum FROM Dba_Objects WHERE Rownum <= 5000;
COMMIT;

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 
 (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-9-1', 'YYYY-MM-DD')), 
 PARTITION P2 VALUES LESS THAN (MAXVALUE)); 

ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T; 

RENAME T TO T_OLD; 

RENAME T_NEW TO T; 

 

 

优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行完RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。

不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行分区的SPLIT操作,会增加操作的复杂度,效率也会降低。

适用性:适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

2.3方法三:Oracle9i以上版本,利用在线重定义功能

Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 
INSERT INTO T
 SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T'); 

PL/SQL 过程已成功完成。 

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 
 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 
 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 
 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 
 PARTITION P4 VALUES LESS THAN (MAXVALUE)); 

表已创建。 

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW'); 

PL/SQL 过程已成功完成。 

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_NEW'); 

PL/SQL 过程已成功完成。 

 

 

优点:保证数据的一致性,在大部分时间内,表T都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。

不足:实现上比上面两种略显复杂。

适用性:适用于各种情况。

这里只给出了在线重定义表的一个最简单的例子,详细的描述和例子可以参考下面两篇文章。

Oracle的在线重定义表功能:http://blog.itpub.net/post/468/12855

Oracle的在线重定义表功能(二):http://blog.itpub.net/post/468/12962

2.4把一个已存在数据的大表改成分区表:

第一种(表不是太大):

--1.把原表改名:
rename xsb1 to xsb2;
--2.创建分区表:
CREATE TABLE xsb1
 PARTITION BY LIST (c_test) 
 (PARTITION xsb1_p1 VALUES (1),
 PARTITION xsb1_p2 VALUES (2),
 PARTITION xsb1_p0 VALUES (default))
 nologging AS SELECT * FROM xsb2;
--3.将原表上的触发器、主键、索引等应用到分区表上;
--4.删除原表:
drop table xsb2;

 

 

第二种(表很大):

--1. 创建分区表:
CREATE TABLE x PARTITION BY LIST (c_test) [range ()]
 (PARTITION p0 VALUES [less than ](1) tablespace tbs1,
 PARTITION p2 VALUES (2) tablespace tbs1,
 PARTITION xsb1_p0 VALUES ([maxvalue]default))
 AS SELECT * FROM xsb2 [where 1=2];
--2. 交换分区 
alter table x exchange partition p0 with table bsvcbusrundatald ;
--3. 原表改名
alter table bsvcbusrundatald rename to x0;
--4. 新表改名
alter table x rename to bsvcbusrundatald ;
--5. 删除原表
drop table x0;
--6. 创建新表触发器和索引
create index ind_busrundata_lp on bsvcbusrundatald(。。。) local tablespace tbs_brd_ind ;

 

 

或者:

1. 规划原大表中数据分区的界限,原则上将原表中近期少量数据复制至另一表;

2. 暂停原大表中的相关触发器;

3. 删除原大表中近期数据;

4. 改名原大表名称;

5. 创建分区表;

6. 交换分区;

7. 重建相关索引及触发器(先删除之再重建).

参考脚本:

select count(*) from t1 where recdate>sysdate-2;

create table x2 nologging as select * from t1 where recdate>trunc(sysdate-2);

alter triger trg_t1 disable;

delete t1 where recdate>sysdate-2;

commit;

rename t1 to x1;

create table t1 [nologging] partition by range(recdate)

(partition pbefore values less than (trunc(sysdate-2)),

partition pmax values less than (maxvalue))

as select * from x1 where 1=2;

alter table t1 exchange partition pbefore with table x1;

alter table t1 exchange partition pmax with table x2;

drop table x2;

--重建触发器

drop table x1;

 

 

3.分区的方法:
  1. 范围分区Range
  2. 散列分区Hash
  3. 列表分区List
  4. 组合范围-散列分区Range-Hash
  5. 组合范围-列表分区Range-List

可对索引和表分区。全局索引只能按范围分区,但可以将其定义在任何类型的分区或非分区表上。通常全局索引比局部索引需要更多的维护。

一般组建局部索引,以便反映其基础表的结构。它与基础表是等同分区的,即它与基础表在同样的列上分区,创建同样数量的分区或子分区,设置与基础表相对应的同样的分区边界。对局部索引而言,当维护活动影响分区时,会自动维护索引分区。这保证了索引与基础表之间的等同分区。

关于范围分区Range:

要想将行映射到基于列值范围的分区,就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份,这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时,就需要考虑其他的分区方法。

关于散列分区Hash:

如果数据不那么容易进行范围分区,但为了性能和管理的原因又想分区时,就使用散列分区方法。散列分区提供了一种在指定数量的分区中均等地划分数据的方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法,因为你可以通过在I/O 驱动器之间播撒(摘掉)这些均等定量的分区,来影响可用性和性能。

关于列表分区List:

当你需要明确地控制如何将行映射到分区时,就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值,这不同于范围分区,在那里一个范围与一个分区相关,这也不同于散列分区,在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。

与范围分区和散列分区所不同,列表分区不支持多列分区。如果要将表按列分区,那么分区键就只能由表的一个单独的列组成,然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。

关于组合范围-散列分区:

范围和散列技术的组合,首先对表进行范围分区,然后用散列技术对每个范围分区再次分区。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。

关于组合范围-列表分区:

范围和列表技术的组合,首先对表进行范围分区,然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是,每个子分区的所有内容表示数据的逻辑子集,由适当的范围和列表分区设置来描述。

注意:创建或更改分区表时可以指定行移动子句,即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT ,当其键被更改时,该子句启用或停用将行迁移到一个新的分区。默认值为DISABLE ROW MOVEMENT。

如果表中预期的数据量较大,通常都需要考虑使用分区表,确定使用分区表后,还要确定什么类型的分区(range partition、hash partition、list partition等)、分区区间大小等。分区的创建最好与程序有某种默契,比如创建一个分区表,按自然月份定义分区的,但程序却在查询时默认的开始时间与结束时间是:当前日期-30至当前日期,比如当天是9.18号,那查询条件被产生为8.18-9.18,结果分区后并不没有大幅提高性能。为此将程序的查询日期做了调整,按自然月查询,系统的负载小了很多。

4.补充资料

从Oracle8.0开始支持表分区(MSSQL2005开始支持表分区)。

Oracle9i 分区能够提高许多应用程序的可管理性、性能与可用性。分区可以将表、索引及索引编排表进一步划分,从而可以更精细地对这些数据库对象进行管理和访问。Oracle 提供了种类繁多的分区方案以满足所有的业务需要。另外,由于在 SQL 语句中是完全透明的,所以分区可以用于几乎所有的应用程序。

分区表允许将数据分成被称为分区甚至子分区的更小的更好管理的块。索引也可以这么分区。每个分区可以被单独管理,可以不依赖于其他分区而单独发挥作用,因此提供了一个更有利于可用性和性能的结构。

分区可以提高可管理性、性能与可用性,从而给各种各样的应用程序带来极大的好处。通常,分区可以使某些查询以及维护操作的性能大大提高。此外,分区还能够在很大程度上简化日常管理任务。分区还使数据库设计人员和管理员能够解决尖端应用程序带来的最难的问题。分区是建立上亿万字节数据系统或需要极高可用性系统的关键工具。

在多CPU配置环境下,如果打算使用并行执行,则分区提供了另一种并行的方法。通过给表或索引的不同分区分配不同的并行执行服务器,就可以并行执行对分区表和分区索引的操作。

表或索引的分区和子分区都共享相同的逻辑属性。例如表的所有分区或子分区共享相同的列和约束定义,一个索引的分区或子分区共享相同的索引选项。然而它们可以具有不同的物理属性如表空间。

尽管不需要将表或索引的每个分区或子分区放在不同的表空间,但这样做更好。将分区存储到不同的表空间能够:

  1. 减少数据在多个分区中冲突的可能性
  2. 可以单独备份和恢复每个分区
  3. 控制分区与磁盘驱动器之间的映射对平衡I/O 负载是重要的
  4. 改善可管理性可用性和性能

分区操作对现存的应用和运行在分区表上的标准DML 语句来说是透明的。但是可以通过在DML 中使用分区扩展表或索引的名字来对应用编程,使其利用分区的优点。

可以使用SQL*Loader、Import 和Export 工具来装载或卸载分区表中的数据。这些工具都是支持分区和子分区的。

4.1分区技术能够提高数据库的可管理性:

使用分区技术,维护操作可集中于表的特定部分。例如,数据库管理员可以只对表的一部分做备份,而不必对整个表做备份。对整个数据库对象的维护操作,可以在每个分区的基础上进行,从而将维护工作分解成更容易管理的小块。

分区技术提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。该表可以是范围分区,以便每个分区包含一周的数据。加载进程只是简单地添加新的分区。添加一个新分区的操作比修改整个表效率高很多,因为数据库管理员不需要修改任何其他分区。从分区后的表中去除数据也是一样。你只要用一个很简便快捷的数据字典操作删掉一个分区,而不必发出使用大量资源和调动所有要删除的数据的 ‘DELETE’ 命令。

4.2分区技术能够提高数据库的性能:

由于减少了所检查或操作的数据数量,同时允许并行执行,Oracle9i 的分区功能提供了性能上的优势。这些性能包括:

  1. 分区修整:分区修整是用分区技术提高性能的最简单最有价值的手段。分区修整常常能够将查询性能提高几个数量级。例如,假定应用程序中有包含定单历史记录的定单表,该表用周进行了分区。查询一周的定单只需访问该定单表的一个分区。如果该定单表包含两年的历史记录,这个查询只需要访问一个而不是一百零四个分区。该查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。Oracle 公司将把分区修整技术与索引技术、连结技术和并行访问方法一起联合使用。
  2. 分区智能联接:分区功能可以通过称为分区智能联接的技术提高多表联接的性能。当两个表要联接在一起,而且每个表都用联接关键字来分区时,就可以使用分区智能联接。分区智能联接将大型联接分解成较小的发生在各个分区间的联接,从而用较少的时间完成全部联接。这就给串行和并行的执行都能带来显著的性能改善。
  3. 更新和删除的并行执行:分区功能能够无限地并行执行 UPDATE、DELETE 与 MERGE 语句。当访问分区或未分区的数据库对象时Oracle 将并行处理 SELECT 与 INSERT 语句。当不使用位图索引时,也可以对分区或未分区的数据库对象并行处理 UPDATE、DELETE 和 MERGE 语句。为了对有位图索引的对象并行处理那些操作,目标表必须先分区。这些 SQL 语句的并行执行可以大大提高性能,特别是提高 UPDATE 与 DELETE 或 MERGE 操作涉及大量数据时的性能。
4.3分区技术提高可用性:

分区的数据库对象具有分区独立性。该分区独立性特点可能是高可用性战略的一个重要部分,例如,如果分区表的分区不能用,但该表的所有其他分区仍然保持在线并可用。那么这个应用程序可以继续针对该分区表执行查询和事务处理,只要不是访问那个不可用的分区,数据库操作仍然能够成功运行。 数据库管理员可以指定各分区存放在不同的表空间里,从而让管理员独立于其它表分区针对每个分区进行备份与恢复操作。 还有,分区功能可以减少计划停机时间。性能由于分区功能得到了改善,使数据库管理员在相对较小的批处理窗口完成大型数据库对象的维护工作。

posted @ 2014-05-07 22:53 鸿雁 阅读(159) | 评论 (0)编辑 收藏

普通表转分区表和交换分区(oracle)

将普通表转换成分区表有4种方法:  

       1. Export/import method  

       2. Insert with a subquery method  

       3. Partition exchange method  

       4.
DBMS_REDEFINITION           


    select
 * from t_user_info_test;  


    --方法一
  

    drop table t_phone_test purge;  

create table t_phone_test(phone,part) nologging  partition by list(part)  

(  

partition p0 values('0'),  

partition p1 values('1'),  

partition p2 values('2'),  

partition p3 values('3'),  

partition p4 values('4'),  

partition p5 values('5'),  

partition p6 values('6'),  

partition p7 values('7'),  

partition p8 values('8'),  

partition p9 values('9')  

)   

as   

select user_mobile phone,substr(user_mobile,-1,1) part  

from t_user_info_test;  

  

  

select * from t_phone_test partition(p0);  

  

select * from t_phone_test where part='0';  

  

--方法二 交换分区   

     这种方法只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。  

  

交换分区的操作步骤如下:  

     1. 创建分区表,假设有2个分区,P1,P2.  

     2. 创建表A存放P1规则的数据。  

     3. 创建表B 存放P2规则的数据。  

     4. 用表A 和P1 分区交换。 把表A的数据放到到P1分区  

     5. 用表B 和p2 分区交换。 把表B的数据存放到P2分区。  

  

  

  

create table t_phone_test_0 nologging  

as   

select user_mobile phone,substr(user_mobile,-1,1) part  

from t_user_info_test where substr(user_mobile,-1,1)='0';  

  

select count(*) from t_phone_test where part='0';  

--4410   

select count(*) from t_user_info_test where substr(user_mobile,-1,1)='0';  

--4410   

  

alter table t_phone_test exchange partition p0 with table t_phone_test_0;  

  

  

delete from   t_phone_test_0;  

  

select count(*) from t_phone_test where part='0';  

select count(*) from t_phone_test_0;  

  

insert into t_phone_test(phone,part) values('15267046070','0');  

  

--p0一条数据,t_phone_test_0里4410条数据,交换之后p0是4410,t_phone_test_0是1,再执行一次数据又换回来了。   

  

insert into t_phone_test_0(phone,part) values('15267046070','1');  

alter table t_phone_test exchange partition p0 with table t_phone_test_0;  

delete from t_phone_test_0 where part='1';  

  

  

--合并分区   

----alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3;   

  

alter table t_phone_test merge partitions p0,p1 into partition p0;  

  

  

select count(*) from t_phone_test where part='0';  

select count(*) from t_phone_test where part='1';  

  

select count(*)  from t_phone_test partition(p0);  

select count(*)  from t_phone_test partition(p1);  

  

  

  

 alter table t_phone_test  add partition p10 values(default);  

  

insert into t_phone_test(phone,part) values('15267046010','10');  

insert into t_phone_test(phone,part) values('15267046020','20');  

  

select * from   

  

--   

alter table t_phone_test drop partition p10;  

 alter table t_phone_test  add partition p10 values'10');  

   

alter table t_phone_test exchange partition p10 with table t_phone_test_10;  

--ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION   

alter table T_PHONE_TEST_10 modify PART VARCHAR2(2);  

alter table t_phone_test merge partitions p0,p10 into partition p0;  

  

--此时p0中有p0和p10的数据,但是p0的list不再是0而是0和10   

  partition P0 values ('10''0')  

    tablespace APP_DATAN  

    pctfree 10  

    initrans 1  

    maxtrans 255  

    storage  

    (  

      initial 1M  

      next 1M  

      minextents 1  

      maxextents unlimited  

      pctincrease 0  

    ),  

      

alter table t_phone_test exchange partition p0 with table t_phone_test_10;     

alter table t_phone_test drop partition p0;  

alter table t_phone_test  add partition p0 values'0');      

  

alter table t_phone_test exchange partition p0 with table t_phone_test_10;     

  

  

drop table t_phone_test_10 purge;  

create table t_phone_test_10 nologging  

as   

select user_mobile phone,substr(user_mobile,-2,2) part  

from t_user_info_test where substr(user_mobile,-2,2)='10';  

  

drop table t_phone_test_0 purge;  

create table t_phone_test_0 nologging   

as  

select  phone,substr(phone,-1,1) part  

from t_phone_test_10;  

  

alter table t_phone_test exchange partition p0 with table t_phone_test_0;  

  

  

select * from t_phone_test_10;  

  

  

  

select count(*)  from t_phone_test partition(p0);  

select count(*)  from t_phone_test partition(p10);  

select count(*) from t_phone_test_10;  

select count(*) from t_phone_test_0;  

  

  

  

select substr('123456',-1,1),substr('123456',-2,2),substr('123456',-3,2) from dual;  

  

  

---------------------------------------------------------   

1.创建分区表  

drop table t_phone_test purge;  

create table t_phone_test(phone,part) nologging  partition by list(part)  

(  

partition p0 values('0'),  

partition p1 values('1'),  

partition p2 values('2'),  

partition p3 values('3'),  

partition p4 values('4'),  

partition p5 values('5'),  

partition p6 values('6'),  

partition p7 values('7'),  

partition p8 values('8'),  

partition p9 values('9')  

)   

as   

select user_mobile phone,substr(user_mobile,-1,1) part  

from t_user_info_test;  

  

select count(*)  from t_phone_test partition(p0);--4410   

select count(*)  from t_phone_test partition(p10);  

select count(*) from t_phone_test_10;  

select count(*) from t_phone_test_0;  

  

2.创建基表  

drop table t_phone_test_10 purge;  

create table t_phone_test_10 nologging  

as  

select  phone,substr(phone,-2,2) part  

from t_phone_test where substr(phone,-2,2)='10';  

  

select count(*) from t_phone_test_10;--406   

  

--ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION   

alter table T_PHONE_TEST_10 modify PART VARCHAR2(2);  

  

3.添加分区  

alter table t_phone_test  add partition p10 values'10');      

select count(*)  from t_phone_test partition(p10);--0   

4.交换分区  

alter table t_phone_test exchange partition p10 with table t_phone_test_10;     

select count(*)  from t_phone_test partition(p10);--406   

5.合并分区  

alter table t_phone_test merge partitions p0,p10 into partition p0;  

select count(*)  from t_phone_test partition(p0);--4816   

--此时p0中有p0和p10的数据,但是p0的list不再是0而是0和10   

  partition P0 values ('10''0')  

    tablespace APP_DATAN  

    pctfree 10  

    initrans 1  

    maxtrans 255  

    storage  

    (  

      initial 1M  

      next 1M  

      minextents 1  

      maxextents unlimited  

      pctincrease 0  

    ),  

      

6.交换分区  

alter table t_phone_test exchange partition p0 with table t_phone_test_10;    

  

select count(*)  from t_phone_test partition(p0);--0   

select count(*) from t_phone_test_10;--4816   

  

  

6.删除分区 和添加分区  

alter table t_phone_test  drop partition p0;  

alter table t_phone_test  add partition p0 values('0');  

  

7.筛选数据  

drop table t_phone_test_0 purge;  

create table t_phone_test_0 nologging  

as  

select  phone,substr(phone,-1,1) part  

from t_phone_test_10 where substr(phone,-1,1)='0';  

  

select count(*) from t_phone_test_0;--4816   

  

8.交换分区  

alter table t_phone_test exchange partition p0 with table t_phone_test_0;    

  

select count(*)  from t_phone_test partition(p0);--4816   
select count(*) from t_phone_test_0;--0  

posted @ 2014-05-07 22:31 鸿雁 阅读(232) | 评论 (0)编辑 收藏

Oracle全局索引和本地索引

      Oracle数据库中,有两种类型的分区索引,全局索引和本地索引,其中本地索引又可以分为本地前缀索引和本地非前缀索引。下面就分别看看每种类型的索引各自的特点。

全局索引以整个表的数据为对象建立索引,索引分区中的索引条目既可能是基于相同的键值但是来自不同的分区,也可能是多个不同键值的组合。

全局索引既允许索引分区的键值和表分区键值相同,也可以不相同。全局索引和表之间没有直接的联系,这一点和本地索引不同。

SQL> create table orders (
     order_no      number,
     part_no       varchar2(40),
     ord_date      date
     )
     partition by range (ord_date)
      (partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
       partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
       partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
       partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
      )
     ;

Table created.

SQL> create index orders_global_1_idx
     on orders(ord_date)
      global partition by range (ord_date)
       (partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
        partition GLOBAL2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
        partition GLOBAL3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
        partition GLOBAL4 values less than (MAXVALUE)
       )
     ;

Index created.

SQL> create index orders_global_2_idx
     on orders(part_no)
      global partition by range (part_no)
       (partition IND1 values less than (555555),
        partition IND2 values less than (MAXVALUE)
       )
     ;

Index created.

从上面的语句可以看出,全局索引和表没有直接的关联,必须显式的指定maxvalue值。假如表中新加了分区,不会在全局索引中自动增加新的分区,必须手工添加相应的分区。

SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));

Table altered.

SQL> select TABLE_NAME, PARTITION_NAME from dba_tab_partitions where table_name='ORDERS';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS                         Q1
ORDERS                         Q2
ORDERS                         Q3
ORDERS                         Q4
ORDERS                         Q5

SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_global_1_idx');

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS_GLOBAL_1_IDX            GLOBAL1
ORDERS_GLOBAL_1_IDX            GLOBAL2
ORDERS_GLOBAL_1_IDX            GLOBAL3
ORDERS_GLOBAL_1_IDX            GLOBAL4

使用全局索引,索引键值必须和分区键值相同,这就是所谓的前缀索引。Oracle不支持非前缀的全局分区索引,如果需要建立非前缀分区索引,索引必须建成本地索引。

SQL> create index orders_global_2_idx
  2  on orders(part_no)
  3   global partition by range (order_no)
  4    (partition IND1 values less than (555555),
  5     partition IND2 values less than (MAXVALUE)
  6    )
  7  ;
 global partition by range (order_no)
                                    *
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed

接下来再来看看本地分区。

本地索引的分区和其对应的表分区数量相等,因此每个表分区都对应着相应的索引分区。使用本地索引,不需要指定分区范围因为索引对于表而言是本地的,当本地索引创建时,Oracle会自动为表中的每个分区创建独立的索引分区。

创建本地索引不必显式的指定maxvalue值,因为为表新添加表分区时,会自动添加相应的索引分区。

create index orders_local_1_idx
on orders(ord_date)
 local
  (partition LOCAL1,
   partition LOCAL2,
   partition LOCAL3,
   partition LOCAL4
  )
;

Index created.

SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX             LOCAL1
ORDERS_LOCAL_1_IDX             LOCAL2
ORDERS_LOCAL_1_IDX             LOCAL3
ORDERS_LOCAL_1_IDX             LOCAL4

SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));

Table altered.

SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX             LOCAL1
ORDERS_LOCAL_1_IDX             LOCAL2
ORDERS_LOCAL_1_IDX             LOCAL3
ORDERS_LOCAL_1_IDX             LOCAL4
ORDERS_LOCAL_1_IDX             Q5

这里系统已经自动以和表分区相同的名字自动创建了一个索引分区。同理,删除表分区时相对应的索引分区也自动被删除。

本地索引和全局索引还有一个显著的差别,就是上面提到的,本地索引可以创建成本地非前缀型,而全局索引只能是前缀型。

SQL> create index orders_local_2_idx
     on orders(part_no)
      local
       (partition LOCAL1,
        partition LOCAL2,
        partition LOCAL3,
        partition LOCAL4)
     ;

Index created.

SQL> select INDEX_NAME, PARTITION_NAME, HIGH_VALUE from dba_ind_partitions
     where index_name=upper('orders_local_2_idx');

INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ---------------------------------------------------------
ORDERS_LOCAL_2_IDX             LOCAL1                         TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                              'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX             LOCAL2                         TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                              'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX             LOCAL3                         TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                              'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX             LOCAL4                         TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                              'NLS_CALENDAR=GREGORIA'

从上面的输出可以看出,虽然索引的键值是part_no,但索引分区的键值仍然和表的分区键值相同,即ord_date,也即是所谓的非前缀型索引。

最后,再引用一个例子说明前缀索引和非前缀索引的应用。

假设有一个使用DATE列分区的大表。我们经常使用一个VARCHAR2列(VCOL)进行查询,但这个列并不是表的分区键值。

有两种可能的方法来访问VCOL列的数据,一是建立基于VCOL列的本地非前缀索引,

               |                                         |
             -------                                   -------
            |       |         (10 more                |        |
Values:     A..     Z..   partitions here)            A..      Z..

另一种是建立基于VCOL列的全局索引,

                |                                         |
              -------                                   -------
             |       |         (10 more                |        |
Values:      A..     D..   partitions here)            T..      Z..

可以看出,如果能够保证VCOL列值的唯一性,全局索引将会是最好的选择。如果VCOL列值不唯一,就需要在本地非前缀索引的并行查询和全局索引顺序查询以及高昂的维护代价之间做出选择。

posted @ 2014-05-06 17:29 鸿雁 阅读(140) | 评论 (0)编辑 收藏

解决ORA-12560: TNS: 协议适配器错误

造成ORA-12560: TNS: 协议适配器错误的问题的原因可能有如下三个:
1.监听服务没有起起来。windows平台个一如下操作:开始---程序---管理工具---服务,打开服务面板,

启动oraclehome92TNSlistener服务。
2.database instance没有起起来。windows平台如下操作:开始---程序---管理工具---服务,打开服务

面板,启动oracleserviceXXXX,XXXX就是你的database SID.
3.注册表问题。regedit,然后进入HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0将该环境变量ORACLE_SI

D设置为XXXX,XXXX就是你的database SID.或者右几我的电脑,属性--高级--环境变量---系统变量--新建

,变量名=oracle_sid,变量值=XXXX,XXXX就是你的database SID.或者进入sqlplus前,在command line下

输set oracle_sid=XXXX,XXXX就是你的database SID.
经过以上步骤,就可以解决问题。

posted @ 2014-05-05 10:46 鸿雁 阅读(113) | 评论 (0)编辑 收藏

Oracle优化全攻略一(Oracle SQL Hint)

其实Oracle的优化器有两种优化方式,
基于规则的优化方式(Rule-Based Optimization,简称为RBO)
基于代价的优化方式(Cost-Based Optimization,简称为CBO)
所以hint也不例外,除了/*+rule*/其他的都是CBO优化方式
RBO方式:
  优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。
CBO方式:
   它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。

优化模式包括Rule、Choose、First rows、All rows四种方式:

    Rule:基于规则的方式。

    Choolse:默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。

    First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。

    All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走RBO的方式

Oracle在那配置默认的优化规则
    A、Instance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。
    B、Sessions级别通过ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。
    C、语句级别用Hint(/*+ ... */)来设定
为什么表的某个字段明明有索引,但执行计划却不走索引?
    1、优化模式是all_rows的方式
    2、表作过analyze,有统计信息
    3、表很小,Oracle的优化器认为不值得走索引。
提示
   不区分大小写, 多个提示用空格分开
  如:select /*+ hint1(tab1) hint2(TAB1 idx1) */ col1, col2 from tab1 where col1='xxx';
  如果表使用了别名, 那么提示里也必须使用别名
如:select /*+ hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';
如果使用同一个表的多个用,号分开
如: select /*+ index(t1.A,t1.B) */ col1, col2
    from   tab1 t1
    where  col1='xxx';

oracle 10g hints知识,
    10g数据库可以使用更多新的optimizer hints来控制优化行为。现在让我们快速解析一下这些强大的新hints:

1、spread_min_analysis

   使用这一hint,你可以忽略一些关于如详细的关系依赖图分析等电子表格的编译时间优化规则。其他的一些优化,如创建过滤以有选择性的定位电子表格访问结构并限制修订规则等,得到了继续使用。

   由于在规则数非常大的情况下,电子表格分析会很长。这一提示可以帮助我们减少由此产生的数以百小时计的编译时间。

例:
    SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

2、spread_no_analysis

   通过这一hint,可以使无电子表格分析成为可能。同样,使用这一hint可以忽略修订规则和过滤产生。如果存在一电子表格分析,编译时间可以被减少到最低程度。

例:
    SELECT /*+ SPREAD_NO_ANALYSIS */ ...

3、use_nl_with_index

   这项hint使CBO通过嵌套循环把特定的表格加入到另一原始行。只有在以下情况中,它才使用特定表格作为内部表格:如果没有指定标签,CBO必须可以使用一些标签,且这些标签至少有一个作为索引键值加入判断;反之,CBO必须能够使用至少有一个作为索引键值加入判断的标签。

例:
  SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...

4、CARDINALITY

  此hint定义了对由查询或查询部分返回的基数的评价。注意如果没有定义表格,基数是由整个查询所返回的总行数。

例:
  SELECT /*+ CARDINALITY ( [tablespec] card ) */

5、SELECTIVITY

  此hint定义了对查询或查询部分选择性的评价。如果只定义了一个表格,选择性是在所定义表格里满足所有单一表格判断的行部分。如果定义了一系列表格,选择性是指在合并以任何顺序满足所有可用判断的全部表格后,所得结果中的行部分。

例:
   SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

然而,注意如果hints CARDINALITY 和 SELECTIVITY都定义在同样的一批表格,二者都会被忽略。

6、no_use_nl

  Hint no_use_nl使CBO执行循环嵌套,通过把指定表格作为内部表格,把每个指定表格连接到另一原始行。通过这一hint,只有hash join和sort-merge joins会为指定表格所考虑。

例:
   SELECT /*+ NO_USE_NL ( employees ) */ ...

7、no_use_merge

  此hint使CBO通过把指定表格作为内部表格的方式,拒绝sort-merge把每个指定表格加入到另一原始行。

例:
  SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

8、no_use_hash

  此hint使CBO通过把指定表格作为内部表格的方式,拒绝hash joins把每个指定表格加入到另一原始行。

例:
  SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

9、no_index_ffs

  此hint使CBO拒绝对指定表格的指定标签进行fast full-index scan。
Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */


在SQL优化过程中常见HINT的用法(前10个比较常用, 前3个最常用):

1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(tab1.col1 tab2.col2) */ 和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */

表明对表选择索引的扫描方法. 第一种不指定索引名是让oracle对表中可用索引比较并选择某个最佳索引; 第二种是指定索引名且可指定多个索引; 第三种是10g开始有的, 指定列名, 且表名可不用别名; 第四种即全表扫描; 第五种表示禁用某个索引, 特别适合于准备删除某个索引前的评估操作. 如果同时使用了INDEX和NO_INDEX则两个提示都会被忽略掉.
例如:SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

2. /*+ ORDERED */
FROM子句中默认最后一个表是驱动表,ORDERED将from子句中第一个表作为驱动表. 特别适合于多表连接非常慢时尝试.
例如:SELECT /*+ ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

3. /*+ PARALLEL(table1,DEGREE) */ 和 /*+ NO_PARALLEL(table1) */
该提示会将需要执行全表扫描的查询分成多个部分(并行度)执行, 然后在不同的操作系统进程中处理每个部分. 该提示还可用于DML语句. 如果SQL里还有排序操作, 进程数会翻倍,此外还有一个一个负责组合这些部分的进程,如下面的例子会产生9个进程. 如果在提示中没有指定DEGREE, 那么就会使用创建表时的默认值. 该提示在默认情况下会使用APPEND提示. NO_PARALLEL是禁止并行操作,否则语句会使用由于定义了并行对象而产生的并行处理.
例如:select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;

4. /*+ FIRST_ROWS */ 和 /*+ FIRST_ROWS(n) */
表示用最快速度获得第1/n行, 获得最佳响应时间, 使资源消耗最小化.
在update和delete语句里会被忽略, 使用分组语句如group by/distinct/intersect/minus/union时也会被忽略.
例如:SELECT /*+ FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+ RULE */
表明对语句块选择基于规则的优化方法.
例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

6. /*+ FULL(TABLE) */
表明对表选择全局扫描的方法.
例如:SELECT /*+ FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

7. /*+ LEADING(TABLE) */
类似于ORDERED提示, 将指定的表作为连接次序中的驱动表.

8. /*+ USE_NL(TABLE1,TABLE2) */
将指定表与嵌套的连接的行源进行连接,以最快速度返回第一行再连接,与USE_MERGE刚好相反.
例如:SELECT /*+ ORDERED USE_NL(BSEMPMS) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

9. /*+ APPEND */ 和 /*+ NOAPPEND */
直接插入到表的最后,该提示不会检查当前是否有插入操作所需的块空间而是直接添加到新块中, 所以可以提高速度. 当然也会浪费些空间, 因为它不会使用那些做了delete操作的块空间. NOAPPEND提示则相反,所以会取消PARALLEL提示的默认APPEND提示.
例如:insert /*+ append */ into test1 select * from test4;
insert /*+ parallel(test1) noappend */ into test1 select * from test4;

10. /*+ USE_HASH(TABLE1,table2) */
将指定的表与其它行源通过哈希连接方式连接起来.为较大的结果集提供最佳响应时间. 类似于在连接表的结果中遍历每个表上每个结果的嵌套循环, 指定的hash表将被放入内存, 所以需要有足够的内存(hash_area_size或pga_aggregate_target)才能保证语句正确执行, 否则将在磁盘里进行.
例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

---------------------------------------------------------------------

11. /*+ USE_MERGE(TABLE) */
将指定的表与其它行源通过合并排序连接方式连接起来.特别适合于那种在多个表大量行上进行集合操作的查询, 它会将指定表检索到的的所有行排序后再被合并, 与USE_NL刚好相反.
例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

12. /*+ ALL_ROWS */
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 可能会限制某些索引的使用.
例如:SELECT /*+ ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

13. /*+ CLUSTER(TABLE) */
提示明确表明对指定表选择簇扫描的访问方法. 如果经常访问连接表但很少修改它, 那就使用集群提示.
例如:SELECT /*+ CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

14. /*+ INDEX_ASC(TABLE INDEX1, INDEX2) */
表明对表选择索引升序的扫描方法. 从8i开始, 这个提示和INDEX提示功能一样, 因为默认oracle就是按照升序扫描索引的, 除非未来oracle还推出降序扫描索引.
例如:SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

15. /*+ INDEX_COMBINE(TABLE INDEX1, INDEX2) */
指定多个位图索引, 对于B树索引则使用INDEX这个提示,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
例如:SELECT /*+ INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE<SYSDATE;

16. /*+ INDEX_JOIN(TABLE INDEX1, INDEX2) */
合并索引, 所有数据都已经包含在这两个索引里, 不会再去访问表, 比使用索引并通过rowid去扫描表要快5倍.
例如:SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;

17. /*+ INDEX_DESC(TABLE INDEX1, INDEX2) */
表明对表选择索引降序的扫描方法.
例如:SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

18. /*+ INDEX_FFS(TABLE INDEX_NAME) */
对指定的表执行快速全索引扫描,而不是全表扫描的办法.要求要检索的列都在索引里, 如果表有很多列时特别适用该提示.
例如:SELECT /*+ INDEX_FFS(BSEMPMS IN_EMPNAM) */ * FROM BSEMPMS WHERE DPT_NO='TEC305';

19. /*+ NO_EXPAND */
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展, 缩短解析时间.
例如:SELECT /*+ NO_EXPAND */ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

20. /*+ DRIVING_SITE(TABLE) */
强制与ORACLE所选择的位置不同的表进行查询执行.特别适用于通过dblink连接的远程表.
例如:SELECT /*+ DRIVING_SITE(DEPT) */ * FROM BSEMPMS,DEPT@BSDPTMS DEPT WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

21. /*+ CACHE(TABLE) */ 和 /*+ NOCACHE(TABLE) */
当进行全表扫描时,CACHE提示能够将表全部缓存到内存中,这样访问同一个表的用户可直接在内存中查找数据. 比较适合数据量小但常被访问的表, 也可以建表时指定cache选项这样在第一次访问时就可以对其缓存. NOCACHE则表示对已经指定了CACHE选项的表不进行缓存.
例如:SELECT /*+ FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

22. /*+ PUSH_SUBQ */
当SQL里用到了子查询且返回相对少的行时, 该提示可以尽可能早对子查询进行评估从而改善性能, 不适用于合并连接或带远程表的连接.
例如:select /*+ PUSH_SUBQ */ emp.empno, emp.ename, itemno from emp, orders where emp.empno = orders.empno and emp.deptno = (select deptno from dept where loc='XXX');
远程连接其他数据库,注意判断数据库是否启动,或者是否有需要的表,否则会出错

23. /*+ INDEX_SS(TABLE INDEX1,INDEX2) */
指示对特定表的索引使用跳跃扫描, 即当组合索引的第一列不在where子句中时, 让其使用该索引
参考资料
Oracle SQL hints
  /*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted. There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:
select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
better:
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias
Why using hints
It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all. Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help. It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.
Hint categories
Hints can be categorized as follows:
Hints for Optimization Approaches and Goals,
Hints for Access Paths, Hints for Query Transformations,
Hints for Join Orders,
Hints for Join Operations,
Hints for Parallel Execution,
Additional Hints

Documented Hints
Hints for Optimization Approaches and Goals
ALL_ROWS
One of the hints that 'invokes' the Cost based optimizer
ALL_ROWS is usually used for batch processing or data warehousing systems.
FIRST_ROWS
One of the hints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTP systems.
CHOOSE
One of the hints that 'invokes' the Cost based optimizer
This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
RULE
The RULE hint should be considered deprecated as it is dropped from Oracle9i2.
See also the following initialization parameters: optimizer_mode, optimizer_max_permutations, optimizer_index_cost_adj, optimizer_index_caching and
Hints for Access Paths
CLUSTER
Performs a nested loop by the cluster index of one of the tables.
FULL
Performs full table scan.
HASH
Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.
ROWID
Retrieves the row by rowid
INDEX
Specifying that index index_name should be used on table tab_name: /*+ index (tab_name index_name) */
Specifying that the index should be used the the CBO thinks is most suitable. (Not always a good choice).
Starting with Oracle 10g, the index hint can be described: /*+ index(my_tab my_tab(col_1, col_2)) */. Using the index on my_tab that starts with the columns col_1 and col_2.
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
NO_INDEX
AND_EQUAL
The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes

Hints for Query Transformations
FACT
The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
MERGE
NO_EXPAND
NO_EXPAND_GSET_TO_UNION
NO_FACT
NO_MERGE
NOREWRITE
REWRITE
STAR_TRANSFORMATION
USE_CONCAT

Hints for Join Operations
DRIVING_SITE
HASH_AJ
HASH_SJ
LEADING
MERGE_AJ
MERGE_SJ
NL_AJ
NL_SJ
USE_HASH
USE_MERGE
USE_NL

Hints for Parallel Execution
NOPARALLEL
PARALLEL
NOPARALLEL_INDEX
PARALLEL_INDEX
PQ_DISTRIBUTE

Additional Hints
ANTIJOIN
APPEND
If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
BITMAP
BUFFER
CACHE
CARDINALITY
CPU_COSTING
DYNAMIC_SAMPLING
INLINE
MATERIALIZE
NO_ACCESS
NO_BUFFER
NO_MONITORING
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_QKN_BUFF
NO_SEMIJOIN
NOAPPEND
NOCACHE
OR_EXPAND
ORDERED
ORDERED_PREDICATES
PUSH_PRED
PUSH_SUBQ
QB_NAME
RESULT_CACHE (Oracle 11g)
SELECTIVITY
SEMIJOIN
SEMIJOIN_DRIVER
STAR
The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
SWAP_JOIN_INPUTS
USE_ANTI
USE_SEMI

Undocumented hints:
BYPASS_RECURSIVE_CHECK
Workaraound for bug 1816154
BYPASS_UJVC
CACHE_CB
CACHE_TEMP_TABLE
CIV_GB
COLLECTIONS_GET_REFS
CUBE_GB
CURSOR_SHARING_EXACT
DEREF_NO_REWRITE
DML_UPDATE
DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN
EXPAND_GSET_TO_UNION
FORCE_SAMPLE_BLOCK
GBY_CONC_ROLLUP
GLOBAL_TABLE_HINTS
HWM_BROKERED
IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE
INDEX_RRS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
LIKE_EXPAND
LOCAL_INDEXES
MV_MERGE
NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID
NO_FILTERING
NO_ORDER_ROLLUPS
NO_PRUNE_GSETS
NO_STATS_GSETS
NO_UNNEST
NOCPU_COSTING
OVERFLOW_NOMOVE
PIV_GB
PIV_SSF
PQ_MAP
PQ_NOMAP
REMOTE_MAPPED
RESTORE_AS_INTERVALS
SAVE_AS_INTERVALS
SCN_ASCENDING
SKIP_EXT_OPTIMIZER
SQLLDR
SYS_DL_CURSOR
SYS_PARALLEL_TXN
SYS_RID_ORDER
TIV_GB
TIV_SSF
UNNEST
USE_TTT_FOR_GSETS

posted @ 2014-05-04 21:21 鸿雁 阅读(134) | 评论 (0)编辑 收藏

Oracle 执行计划(Explain Plan) 说明

     摘要:       如果要分析某条SQL的性能问题,通常我们要先看SQL的执行计划,看看SQL的每一步执行是否存在问题。 如果一条SQL平时执行的好好的,却有一天突然性能很差,如果排除了系统资源和阻塞的原因,那么基本可以断定是执行计划出了问题。          &nb...  阅读全文

posted @ 2014-05-03 11:52 鸿雁 阅读(164) | 评论 (0)编辑 收藏

使用EXPLAIN PLAN获取SQL语句执行计划

     摘要: SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端,本文描述的将是如何通过EXPLAIN PLAN 获取SQL语句执行计划来获取SQL语句的执行计划。一、获取SQL语句执行计划的方式     1. 使用expl...  阅读全文

posted @ 2014-05-03 11:44 鸿雁 阅读(105) | 评论 (0)编辑 收藏

执行计划的使用(EXPLAIN)

对于sql执行的小量高低。我们可以通过执行计划的信息基本上可以进行分析查看该SQL语句执行的时间。连接顺序及浪费的数据库资源等信息,从而判断该SQL语句执行的效率如何,下面就简单的介绍一下执行计划的使用

  2.        Explain使用

  Oracle RDBMS执行每一条SQL语句,都必须经过Oracle优化器的评估。所 以,了解优化器是如何选择(搜索)路径以及索引是如何被使用的,对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。

  2.1.        安装

  要使用执行计划首先需要执行相应的脚本。

  使用Explain工具需要创建Explain_plan表,这必须先进入相关应用表、视图和索引的所有者的帐户内。Oracle的介质中包含有执行此项工作的SQL源程序,例如:

  ORA_RDBMS: XPLAINPL.SQL (VMS)

  $ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)

  该脚本后会生成一个表这个程序会创建一个名为plan_table的表,表结构如下:

  我们简单的介绍一下主要的字段含义:

字段名              字段类型            含义
STATEMENT_ID    VARCHAR2(30)        explain PLAN 语句中所指定的最优STATEMENT_ID 参数值, 如果在EXPLAN PLAN语句中没有使用SET STATEMENT_ID,那么此值会被设为NULL。
REMARKS         VARCHAR2(80)        与被解释规划的各步骤相关联的注释最长可达80 字节
OPERATION       VARCHAR2(30)        各步骤所执行内部操作的名称在某条语句所产生的第一行中该列的可能取值如下DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT
OPTIONS         VARCHAR2(30)        对OPERATION 列中所描述操作的变种
OBJECT_NODE     VARCHAR2(128)       用于访问对象的数据库链接database link 的名称对于使用并行执行的本地查询该列能够描述操作中输出的次序
OBJECT_OWNER    VARCHAR2(30)        对于包含有表或索引的架构schema 给出其所有者的名称
OBJECT_NAME     VARCHAR2(30)        表或索引的名称
OBJECT_INSTANCE  INTEGER            根据对象出现在原始original 语句中的次序所给出的相应次序编号就原始的语句文本而论其处理顺序为自左至右自外向内景象扩张view
OBJECT_TYPE     VARCHAR2(30)        用于提供对象描述性信息的修饰符例如索引的NON-UNIQUE
OPTIMIZER       VARCHAR2(255)       当前优化程序的模式
ID              INTEGER             分配给执行规划各步骤的编号
PARENT_ID       INTEGER             对ID 步骤的输出进行操作的下一个执行步骤的ID
POSITION        INTEGER             对于具有相同PARENT_ID 的步骤其相应的处理次序
COST            INTEGER             根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值
CARDINALITY     INTEGER             根据基于开销的方法对操作所访问行数的估计值
BYTES           INTEGER             根据基于开销的方法对操作所访问字节的估计

  2.2.        使用

  2.2.1.        常规使用

  常规使用语法:

explain PLAN [ SET STATEMENT_ID [=] < string literal > ]
[ INTO < table_name > ]
FOR < sql_statement >
其中:
STATEMENT_ID是一个唯一的字符串,把当前执行计划与存储在同一PLAN表中的其它执行计划区别开来。
TABLE_NAME是plan表名,它结构如前所示,你可以任意设定这个名称。
SQL_STATEMENT是真正的SQL语句。
如:
SQL> explain plan set statement_id='test1' for
  2     SELECT a.soctermbegin,
  3            a.soctermend,
  4            a.dealserialno,
  5            a.levydataid,
  6            a.dealtotal,
  7            e.categoryitemcode,
  8            row_number() over(PARTITION BY a.levydataid ORDER BY 1) AS theRow
  9       FROM tb_soc_packdealdata   a,
10            tb_Lvy_TaxDataBillMap c,
11            Tb_lvy_BillData       d,
12            tb_soc_levydetaildata e
13      WHERE a.levydataid = c.datafrompointer(+)
14        AND c.billdataid = d.billdataid(+)
15        AND a.levydataid = e.levydataid
16        AND a.packdealstatuscode = '10'
17        AND (a.datastatus <> '9' OR a.datastatus is NULL)
18        AND (d.billstatus IS NULL OR
19            (d.billstatus <> '2' AND d.billstatus <> '8'))
20        AND a.Insurcode = '6010952'
21  ;
Explained

  执行下面语句就可以查看该语句执行的执行计划:

SQL> SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
  2  FROM PLAN_TABLE  a
  3  WHERE STATEMENT_ID='test1'
  4  ORDER BY Id;
OPERATION        OPTIONS        OBJECT_NAME                    OBJECT_TYPEID  PARENT_ID
---------------- --------------------------------------------- ------------- ----------
SELECT STATEMENT                                                           0
WINDOW           SORT                                                      1          0
FILTER                                                                     2          1
NESTED LOOPS     OUTER                                                     3          2
NESTED LOOPS     OUTER                                                     4          3
NESTED LOOPS                                                               5          4
TABLE ACCESS     FULL           TB_SOC_PACKDEALDATA                        6          5
TABLE ACCESS     BY INDEX ROWID TB_SOC_LEVYDETAILDATA                      7          5
INDEX            RANGE SCAN     IND_DATAID_LEVSOC              NON-UNIQUE  8          7
TABLE ACCESS     BY INDEX ROWID TB_LVY_TAXDATABILLMAP                      9          4
INDEX            RANGE SCAN     TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10          9
TABLE ACCESS     BY INDEX ROWID TB_LVY_BILLDATA                           11          3
INDEX            UNIQUE SCAN    TBLVYBILLDATA_BILLDATAID       UNIQUE

  2.2.2.        自动显示使用

  在SQLPLUS中自动跟踪显示执行计划及相关信息
  SQL>set timing on  --显示执行时间
  SQL>set autorace on ?C显示执行计划
  SQL>set autorace on ?C显示执行计划
  SQL>set autotrace traceonly ?C只显示执行计划即不显示查询出来的数据

  设置完毕后执行SQL语句就会显示执行计划信息及相应的统计信息(需要设置显示该选项)

SQL> select nvl(sum(t.taxdue), 0)
  2             from tb_lvy_sbzs100 t, tb_lvy_declaredoc a, tb_lvy_declaredoc b
  3            where a.dossiercode = 'SB02041108'
  4              and a.pages = 123
  5              and a.remarkid = b.remarkid
  6              AND A.REMARKID IS NOT NULL
  7              and b.declaredocid = t.declaredocid;
NVL(SUM(T.TAXDUE),0)
--------------------
                   0
                  
  已用时间:  00: 00: 04.07
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=110)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=6 Card=1 Bytes=110)
   3    2       MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=74)
   4    3         TABLE ACCESS (FULL) OF 'TB_LVY_SBZS100' (Cost=2 Card =1 Bytes=31)
   5    3         BUFFER (SORT) (Cost=2 Card=1 Bytes=43)
   6    5           TABLE ACCESS (FULL) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=43)
   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=36)
   8    7         INDEX (UNIQUE SCAN) OF 'TBLVYDECLAREDOC_DECLAREDOCID' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls --循环递归次数
          0  db block gets―请求的数据块在buffer能满足的个数
       6675  consistent gets --逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中
         45  physical reads ?C从磁盘读到Buffer Cache数据块数量
          0  redo size ?C产生的redo日志大小
        217  bytes sent via SQL*Net to client
        276  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>

  如果6675  consistent gets --逻辑IO用于读表并计算行数, 数据请求总数在回滚段Buffer中

  45        physical reads ?C从磁盘读到Buffer Cache数据块数量的数值比较小则该语句对对数据库的性能比较高。

  2.2.3.        PL/SQL和TOAD中使用

  如果在PL/SQL中使用选择要查询语句显示执行计划,则只需要SQL WINDOWS 窗口里面输入要查询的SQL语句,然后选择按键F5或者在菜单TOOLS?D?D>Explain Plan 菜单按键就可以在执行计划窗口查看该语句的执行计划。

  在TOAD语句中在执行当前的SQL窗口中选择下方的Explain PlanTAB页即可以查看要执行语句的执行计划信息。

  2.3.        限制

  虽然任何SQL语句都可以用explain解释,但对于没有查询的INSERT,UPDATE,DELETE操作来说,这个工具并没有太大的用处。没有子查询的INSERT操作不会创建执行计划,但没有WHERE子句或子查询的UPDATE和DELETE操作会创建执行计划,因为这些操作必须先找出所要的记录。

  另外,如果你在SQL语句中使用其它类型如sequence等,explain也能揭示它的用法。

  explain真正的唯一的限制是用户不能去解释其它用户的表,视图,索引或其它类型,用户必须是所有被解释事物的所有者,如果不是所有者而只有select权限,explain会返回一个错误。

posted @ 2014-05-03 11:36 鸿雁 阅读(95) | 评论 (0)编辑 收藏