DBA Notes: 2011/10/13

Cheng Li

AWR Reports: SQL Order By

[oracle@ctmcmsdb dbalog]$ sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 11 09:18:35 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @awr.sql

From AWR reports, we found those top SQL depends on different conditions such SQL order by Get. That is top SQL order by buffer gets.

Part of AWR Reports:

SQL ordered by Gets                DB/Inst: CMSPRD/CMSPRD  Snaps: 20156-20181

-> Resources reported for PL/SQL code includes the resources used by all SQL

   statements called by the code.

-> Total Buffer Gets:   1,399,706,007

-> Captured SQL account for      99.1% of Total

                                Gets              CPU     Elapsed

  Buffer Gets   Executions    per Exec   %Total Time (s)  Time (s)    SQL Id

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

   235,035,085        1,025    229,302.5   16.8  1361.45   1384.94 9qz0udgq7qwj0

Module: JDBC Thin Client

 select PRD.PRD_INST_ID prdInstId, PRD.PRD_CODE prdCode, PRD.PRD_DESC prd

Desc, PRD.PRD_DUR prdDur, PRD.PRD_ACTIVE_DATE prdActiveDate, PRD.PRD_INACT

IVE_DATE prdInactiveDate, PRD.PRD_EXPIRY_DATE prdExpiryDate, PRD.PRD_RATE pr

dRate, PRD.PRD_STATUS prdStatus, CAT.DESC_TXT prdCategory,

Reference:

http://www.eygle.com/archives/2011/01/awrsql_ordered_userio.html