Home » RDBMS Server » Performance Tuning » Bucketing Query Performance
| Bucketing Query Performance [message #309777] |
Fri, 28 March 2008 14:32  |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Dear All,
I need your help with speeding up this bucketing query for Oracle 10.2.0.3.0
SELECT i,FNAME,SNAME,DOB
FROM (
SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
FNAME,
SNAME,
DOB,
rownum rnum
FROM CUSTOMER
WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
ORDER BY FNAME,SNAME,ID
)
WHERE mod(rnum,:RPB)<3;
The purpose of this query is to divide all the non-null rows of the CUSTOMER table sorted by FNAME,SNAME,ID into 100 evenly-sized buckets and then to return only the first 3 rows from each bucket.
The "i" column is a disambiguation column that consecutively numbers all the rows with the same FNAME and SNAME.
The :RPB variable is known ahead of time.
Please do not concern yourself with cases where the count of table rows is not divisible by 100.
Regards,
George Robinson
Here is a testcase. Table & Data are the same as with the huge query before, but any data could do for this test.
SQL> DROP INDEX IDX_USE_ME;
Index dropped.
SQL> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (FNAME, SNAME , ID);
Index created.
SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'ORAUSER', indname => 'IDX_USE_ME');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ORAUSER', tabname => 'CUSTOMER');
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT i,FNAME,SNAME,DOB
2 FROM (
3 SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
4 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
5 SNAME,FNAME,DOB,rownum rnum
6 FROM CUSTOMER
7 WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
8 ORDER BY FNAME,SNAME,ID
9 )
10 WHERE mod(rnum,506)<3;
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 @2-07-18:08:04: SI_COLORHISTOGRAM 19-NOV-06
1 @2-10-23:15:20: AQ$_JMS_MAP_MESSAGES 19-NOV-06
2 @2-10-23:15:20: AQ$_JMS_MAP_MESSAGES 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 @6-11-19:10:54: /7a628fb8_DefaultHSBChooserPan 19-NOV-06
1 @6-11-19:10:54: /7aa898c9_BasicFileChooserUIGo 19-NOV-06
1 @6-11-19:10:56: java/awt/TextArea 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 @6-11-19:10:56: java/awt/Window$1DisposeAction 19-NOV-06
1 @6-11-19:10:56: java/awt/event/FocusAdapter 19-NOV-06
1 @6-11-19:11:03: oracle/ODCI/ODCIIndexCtx 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 @6-11-19:11:03: oracle/ODCI/ODCIIndexInfo 19-NOV-06
1 @6-11-19:11:03: oracle/expfil/ParamsParser 19-NOV-06
1 A6-11-19:10:29: DBA_OPBINDINGS 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 A6-11-19:10:29: DBA_SECONDARY_OBJECTS 19-NOV-06
1 A6-11-19:10:29: DBA_UNUSED_COL_TABS 19-NOV-06
1 A6-11-19:10:54: sqlj/codegen/ExecCodegen 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 A6-11-19:10:54: sqlj/codegen/ParseletFactory 19-NOV-06
1 A6-11-19:10:54: sqlj/codegen/TempDecl 19-NOV-06
1 A6-11-19:10:57: /5a41f72c_UTF8ValidationFilter 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 A6-11-19:10:57: /5a696e48_PagesPerMinute 19-NOV-06
1 A6-11-19:10:57: /5bd3aa59_ExtensionInstallatio 19-NOV-06
1 A6-11-19:11:11: /cdd2f2c1_BMPDescriptor 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 A6-11-19:11:11: /de10413_AndOpImage 19-NOV-06
1 A6-11-19:11:11: /e4479ba2_TiffTagAdapter 19-NOV-06
1 B6-11-19:10:33: AQ$_SCHEDULER$_JOBQTAB_I 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 B6-11-19:10:33: DBA_SCHEDULER_RUNNING_CHAINS 19-NOV-06
1 B6-11-19:10:33: DBA_SCHEDULER_WINDOWS 19-NOV-06
1 B6-11-19:10:55: /6f4b0289_AttributeGen 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 B6-11-19:10:55: /71461172_ModuleEntry 19-NOV-06
1 B6-11-19:10:55: /75721cff_TypeErrorType 19-NOV-06
1 B6-11-19:10:57: java/io/PrintWriter 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 B6-11-19:10:57: java/io/StringReader 19-NOV-06
1 B6-11-19:10:57: java/io/Win32FileSystem 19-NOV-06
1 B6-11-19:11:26: GENCURSORBLOCKSEQUENCE 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 B6-11-19:11:26: GENCURSORSPECIFIERSTRUCT 19-NOV-06
1 B6-11-19:11:26: GENDEFINITIONPOST92UNION 19-NOV-06
1 C6-11-19:10:41: TEMPLATE$_TARGETS_PK 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 C6-11-19:10:41: USER_REPCATLOG 19-NOV-06
1 C6-11-19:10:41: USER_REPCOLUMN 19-NOV-06
1 C6-11-19:10:55: /eff941fe_RSAKey 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 C6-11-19:10:55: /f1074797_InterceptorInvoker 19-NOV-06
1 C6-11-19:10:55: /f27212bf_GenericURLDirContext 19-NOV-06
1 C6-11-19:10:58: /48429d3_SerProfileToClassErro 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 C6-11-19:10:58: /5a4138b2_jndiproviderproperti 19-NOV-06
1 C6-11-19:10:58: /5e951bce_LocaleElements_th 19-NOV-06
1 C6-11-19:11:36: /554b5878_TimeZone 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
2 C6-11-19:11:36: /554b5878_TimeZone 19-NOV-06
1 C6-11-19:11:36: /5ec5f173_JGeoRasterSRS 19-NOV-06
1 D6-11-19:10:53: /540bc759_MutableAttrListImpl 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 D6-11-19:10:53: /57000ead_FactoryFinder 19-NOV-06
1 D6-11-19:10:53: /5b10dc5_XNodeSet 19-NOV-06
1 D6-11-19:10:56: /383f88fa_HTMLDocumentHTMLRead 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 D6-11-19:10:56: /38e6f422_ContainerEventTarget 19-NOV-06
1 D6-11-19:10:56: /413926ef_HiddenTagViewEndTagB 19-NOV-06
1 D6-11-19:10:58: sun/security/util/Resources_sv 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 D6-11-19:10:58: sun/tools/jar/SignatureFile 19-NOV-06
1 D6-11-19:10:59: LocaleData_coll_ja_JP 19-NOV-06
1 D7-11-07:03:41: SALES 07-NOV-07
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 D7-11-07:03:41: SALES_CHANNEL_BIX 07-NOV-07
1 D7-11-07:03:41: SALES_CUST_BIX 07-NOV-07
1 E6-11-19:10:54: /7125a7ad_ObjectStatement 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 E6-11-19:10:54: /71e9a4f9_CharExpression 19-NOV-06
1 E6-11-19:10:54: /78209d9_MetalTitlePaneTitlePa 19-NOV-06
1 E6-11-19:10:56: COM/rsa/jsafe/SunJSSE_fe 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 E6-11-19:10:56: COM/rsa/jsafe/SunJSSE_q 19-NOV-06
1 E6-11-19:10:56: HTTPClient/BasicCookieBox$1 19-NOV-06
1 E6-11-19:11:02: oracle/qsma/TokenMgrError 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 E6-11-19:11:02: oracle/xquery/XQException 19-NOV-06
1 E6-11-19:11:02: oracle/xquery/comp/QueryMgr 19-NOV-06
1 F6-11-19:10:28: USER_IND_EXPRESSIONS 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 F6-11-19:10:28: USER_PASSWORD_LIMITS 19-NOV-06
1 F6-11-19:10:28: USER_TABLES 19-NOV-06
1 F6-11-19:10:54: /f05e77f8_NavSchemaObjectFacto 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 F6-11-19:10:54: /f0c95e76_T4C8TTIBfile 19-NOV-06
1 F6-11-19:10:54: /f143cb63_BasicProgressBarUICh 19-NOV-06
1 F6-11-19:10:57: /15795111_basic 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 F6-11-19:10:57: /15c1592c_ParserAdapter 19-NOV-06
1 F6-11-19:10:57: /167106dd_CertPathBuilder 19-NOV-06
1 F6-11-19:11:11: /386133af_DescriptorFactory 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 F6-11-19:11:11: /3fa9ba61_ExpDescriptor 19-NOV-06
1 F6-11-19:11:11: /403acb0c_OrdBlobSeekableSourc 19-NOV-06
1 G6-11-19:10:31: UTL_HTTP 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 G6-11-19:10:31: UTL_I18N 19-NOV-06
1 G6-11-19:10:31: UTL_NLA_ARRAY_INT 19-NOV-06
1 G6-11-19:10:55: /517cc1bc_AlreadyBoundHelper 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 G6-11-19:10:55: /51e48399_ServerHeldDown 19-NOV-06
1 G6-11-19:10:55: /5321a108_Activator 19-NOV-06
1 G6-11-19:10:57: /c4d983a3_ClassDeclarationStac 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 G6-11-19:10:57: /c73108d1_CharacterConverter2B 19-NOV-06
1 G6-11-19:10:57: /c860a039_ConsoleHandler 19-NOV-06
1 G6-11-19:11:14: /4b0441f6_CodecUtils 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 G6-11-19:11:14: /6460eb01_TIFFFaxDecoder 19-NOV-06
1 G6-11-19:11:14: /6945eb77_TIFFHandler1 19-NOV-06
1 H6-11-19:10:39: V_$DATAPUMP_JOB 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 H6-11-19:10:40: AQ$KUPC$DATAPUMP_QUETAB_S 19-NOV-06
1 H6-11-19:10:40: DBA_LOGSTDBY_LOG 19-NOV-06
1 H6-11-19:10:55: /ca4179be_XConstants 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 H6-11-19:10:55: /cad56a18_GIOPImpl1 19-NOV-06
1 H6-11-19:10:55: /cebc8509_InvalidResolverPatte 19-NOV-06
1 H6-11-19:10:57: sun/misc/FileURLMapper 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 H6-11-19:10:57: sun/misc/URLClassPath$3 19-NOV-06
1 H6-11-19:10:57: sun/net/www/MimeTable$1 19-NOV-06
1 H6-11-19:11:28: MRV_OLAP_CWM1_AGGOP 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 H6-11-19:11:28: XML_LOAD_RECORDS 19-NOV-06
1 H6-11-19:11:29: CWM2_OLAP_AW_AWUTIL 19-NOV-06
1 I6-11-19:10:52: WM$CURRENT_WORKSPACE_VIEW 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 I6-11-19:10:52: WM$PARCONFLICT_HIERARCHY_VIEW 19-NOV-06
1 I6-11-19:10:52: WM$TABLE_VERSIONS_IN_LIVE_VIEW 19-NOV-06
1 I6-11-19:10:55: sun/tools/tree/LessExpression 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 I6-11-19:10:55: sun/tools/tree/OrExpression 19-NOV-06
1 I6-11-19:10:56: /1169ca20_AdapterActivator 19-NOV-06
1 I6-11-19:10:58: sun/io/ByteToCharCp875 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 I6-11-19:10:58: sun/io/ByteToCharCp948 19-NOV-06
1 I6-11-19:10:58: sun/io/ByteToCharGBK 19-NOV-06
1 I6-11-19:11:39: SMP_EMD_DBSITEMAP_OBJ 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 I6-11-19:11:39: SYS_C005080 19-NOV-06
1 I6-11-19:11:40: MGMT$CLUSTER_INTERCONNECTS 19-NOV-06
1 J6-11-19:10:54: /40c3b2ff_BasicInternalFrameTi 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 J6-11-19:10:54: /4173215_DynamicProfile 19-NOV-06
1 J6-11-19:10:54: /4262d3ab_OracleClobWriter 19-NOV-06
1 J6-11-19:10:56: /d2d6fe5c_PolicyFile3 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 J6-11-19:10:56: /d343fdf4_RMIHttpToPortSocketF 19-NOV-06
1 J6-11-19:10:56: /d54e8448_StyledEditorKitStyle 19-NOV-06
1 J6-11-19:11:01: oracle/xml/xslt/XSLCondition 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 J6-11-19:11:01: oracle/xml/xslt/XSLConstants 19-NOV-06
1 J6-11-19:11:01: oracle/xml/xslt/XSLOutput 19-NOV-06
1 K6-11-19:10:28: GV$AW_SESSION_INFO 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 K6-11-19:10:28: GV$BACKUP_ASYNC_IO 19-NOV-06
1 K6-11-19:10:28: GV$HS_PARAMETER 19-NOV-06
1 K6-11-19:10:54: /de186e24_BasicPopupMenuUIBasi 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 K6-11-19:10:54: /deeccaa2_OraClosureFactory16 19-NOV-06
1 K6-11-19:10:54: /e1bcd77c_MetalIconFactoryFile 19-NOV-06
1 K6-11-19:10:56: javax/swing/text/SegmentCache 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 K6-11-19:10:56: javax/swing/text/StyleContext 19-NOV-06
1 K6-11-19:10:56: javax/swing/text/View 19-NOV-06
1 K6-11-19:11:07: SYS_C003142 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 K6-11-19:11:07: USER_XML_COLUMN_NAMES 19-NOV-06
1 K6-11-19:11:07: USER_XML_TAB_COLS 19-NOV-06
1 L6-11-19:10:30: LOADER_PARAM_INFO 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 L6-11-19:10:30: MVIEW$_ADV_CLIQUE_PK 19-NOV-06
1 L6-11-19:10:30: MVIEW$_ADV_WORKLOAD_PK 19-NOV-06
1 L6-11-19:10:55: /2b64edd1_InvalidName 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 L6-11-19:10:55: /2f5e2b7a_TreeClassDef 19-NOV-06
1 L6-11-19:10:55: /3034e0cd_AbstractSubWriter 19-NOV-06
1 L6-11-19:10:57: /98b64cec_OracleErrorsText_zh_ 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 L6-11-19:10:57: /9966bd63_Oracle8JdbcChecker 19-NOV-06
1 L6-11-19:10:57: /9abe3626_StreamDecoderConvert 19-NOV-06
1 L6-11-19:11:12: /ef54c4c1_NoMarkStream 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 L6-11-19:11:12: javax/media/jai/AreaOpImage 19-NOV-06
1 L6-11-19:11:12: javax/media/jai/KernelJAI 19-NOV-06
1 M6-11-19:10:36: SYS_YOID0000006391$ 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 M6-11-19:10:37: KU$_OBJGRANT_VIEW 19-NOV-06
1 M6-11-19:10:37: KU$_OPARG_T 19-NOV-06
1 M6-11-19:10:55: /a7f33a81_KeyManagerFactorySpi 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 M6-11-19:10:55: /a843c5bc_TreeCatch 19-NOV-06
1 M6-11-19:10:55: /ac692a44_ConnectorImplStringA 19-NOV-06
1 M6-11-19:10:57: oracle/i18n/text/OraCharset 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 M6-11-19:10:57: oracle/jdbc/driver/DMSFactory 19-NOV-06
1 M6-11-19:10:57: oracle/jdbc/util/Login 19-NOV-06
1 M6-11-19:11:28: ALL$OLAPMR_DIMENSIONS 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 M6-11-19:11:28: ALL_OLAP2_AWVIEWCOLS 19-NOV-06
1 M6-11-19:11:28: ALL_OLAP2_CUBE_MEASURE_MAPS 19-NOV-06
1 N6-11-19:10:45: DBA_HIST_METRIC_NAME 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 N6-11-19:10:45: DBA_HIST_MTTR_TARGET_ADVICE 19-NOV-06
1 N6-11-19:10:45: DBA_HIST_SNAP_ERROR 19-NOV-06
1 N6-11-19:10:55: javax/crypto/SunJCE_j 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 N6-11-19:10:55: javax/crypto/SunJCE_v 19-NOV-06
1 N6-11-19:10:55: javax/naming/RefAddr 19-NOV-06
1 N6-11-19:10:58: LocaleData_coll_fr_BE_EURO 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 N6-11-19:10:58: LocaleData_coll_is_IS 19-NOV-06
1 N6-11-19:10:58: oracle/i18n/data/lx0001a.glb 19-NOV-06
1 N6-11-19:11:38: EM$CURRENT_METRICS 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 N6-11-19:11:38: EMD_SCHEMA 19-NOV-06
1 N6-11-19:11:38: HOST_CONFIG_COMPARISON 19-NOV-06
1 O6-11-19:10:54: /1d02b734_RTFReaderInfoDestina 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 O6-11-19:10:54: /1fae271a_IterConvStatement 19-NOV-06
1 O6-11-19:10:54: /22c33ee5_MediaSizeEngineering 19-NOV-06
1 O6-11-19:10:56: /b040faea_AbstractDocumentElem 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 O6-11-19:10:56: /b15e932f_CollectionsSynchroni 19-NOV-06
1 O6-11-19:10:56: /b28cef24_JComponentEnableSeri 19-NOV-06
1 O6-11-19:11:01: /c85b188a_FNContexts 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 O6-11-19:11:01: /ce549c0_Sum 19-NOV-06
1 O6-11-19:11:01: /d70fe118_EventException 19-NOV-06
1 P6-11-19:10:27: I_REG_SNAP2 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 P6-11-19:10:27: I_RLS_GRP 19-NOV-06
1 P6-11-19:10:27: I_SETTINGS1 19-NOV-06
1 P6-11-19:10:54: /b2f165b3_T2SStringBinder 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 P6-11-19:10:54: /b360fa72_ParserExtension 19-NOV-06
1 P6-11-19:10:54: /b37b129b_CopiedNullBinder 19-NOV-06
1 P6-11-19:10:56: java/rmi/ConnectIOException 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 P6-11-19:10:56: java/rmi/server/LogStream 19-NOV-06
1 P6-11-19:10:56: java/rmi/server/RemoteStub 19-NOV-06
1 P6-11-19:11:04: DM_ABN_DETAIL 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 P6-11-19:11:04: DM_CENTROIDS 19-NOV-06
1 P6-11-19:11:04: DM_ITEMSET 19-NOV-06
1 Q6-11-19:10:29: PRODUCT_COMPONENT_VERSION 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 Q6-11-19:10:29: TAB 19-NOV-06
1 Q6-11-19:10:29: USER_USTATS 19-NOV-06
1 Q6-11-19:10:54: sqlj/util/SQLJParser 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 Q6-11-19:10:54: sun/print/ServiceDialog$2 19-NOV-06
1 Q6-11-19:10:55: /10744837_ObjectStreamClass2 19-NOV-06
1 Q6-11-19:10:57: /6989c0ac_UnsafeFieldAccessorI 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 Q6-11-19:10:57: /6c002de3_EchoInputStream 19-NOV-06
1 Q6-11-19:10:57: /6c2f7d50_StreamEncoderCharset 19-NOV-06
1 Q6-11-19:11:11: javax/media/jai/PixelAccessor 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 Q6-11-19:11:12: /1013c29d_PlanarImageServerPro 19-NOV-06
1 Q6-11-19:11:12: /1acd913a_IIPDescriptor 19-NOV-06
1 R6-11-19:10:34: LOGMNR_KRVRDA_TEST_APPLY 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 R6-11-19:10:34: OL$HINTS 19-NOV-06
1 R6-11-19:10:34: V$BACKUP_ARCHIVELOG_DETAILS 19-NOV-06
1 R6-11-19:10:55: /7e87e147_DocletInvoker1 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 R6-11-19:10:55: /8218bf01_ClientRequestInterce 19-NOV-06
1 R6-11-19:10:55: /8236e38c_SunJCE_p 19-NOV-06
1 R6-11-19:10:57: java/nio/InvalidMarkException 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 R6-11-19:10:57: java/nio/channels/FileLock 19-NOV-06
1 R6-11-19:10:57: java/security/GuardedObject 19-NOV-06
1 R6-11-19:11:27: /9abd5a42_AWExceptionMessageRe 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 R6-11-19:11:27: /b0bc945a_GenErrorDescriptionS 19-NOV-06
1 R6-11-19:11:27: /c6852306_ExternalSourceExpres 19-NOV-06
1 S6-11-19:10:44: DBMS_CDC_SYS_IPUBLISH 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 S6-11-19:10:44: DBMS_PROPAGATION_ADM 19-NOV-06
1 S6-11-19:10:44: DBMS_TSM_PRVT 19-NOV-06
1 S6-11-19:10:55: /fd763802_IIOPConnectionDelete 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 S6-11-19:10:55: /fe116fe4_InvalidValue 19-NOV-06
1 S6-11-19:10:55: /fefe853f_WouldReplaceExceptio 19-NOV-06
1 S6-11-19:10:58: /be97e72a_DateFormatZoneData_a 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 S6-11-19:10:58: /c0993064_StatementCacheCustom 19-NOV-06
1 S6-11-19:10:58: /c1f701a8_rmiregistry_japroper 19-NOV-06
1 S6-11-19:11:37: MGMT_E2E_JDBC_1HOUR 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 S6-11-19:11:37: MGMT_JOB_STEP_LIST 19-NOV-06
1 S6-11-19:11:37: MGMT_METADATA_SETS 19-NOV-06
1 T6-11-19:10:53: /fcf9a3d6_AuditedProfile12Dyna 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 T6-11-19:10:53: /fd945239_DTMDefaultBaseTraver 19-NOV-06
1 T6-11-19:10:53: ALL_WM_CONSTRAINTS 19-NOV-06
1 T6-11-19:10:56: /5f5f94c1_JPopupMenuAccessible 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 T6-11-19:10:56: /62503596_MemoryCacheImageInpu 19-NOV-06
1 T6-11-19:10:56: /628b38ea_StyledEditorKitUnder 19-NOV-06
1 T6-11-19:11:00: javax/mail/search/HeaderTerm 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 T6-11-19:11:00: javax/mail/search/OrTerm 19-NOV-06
1 T6-11-19:11:00: javax/servlet/GenericServlet 19-NOV-06
1 T8-03-18:22:13: WRH$_SGASTAT_U 18-MAR-08
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 T8-03-19:19:44: BIN$Jf8o17FvTTyRtIEM+p/JTA==$0 19-MAR-08
1 U6-11-19:10:27: APPLY$_CONF_HDLR_COLUMNS 19-NOV-06
1 U6-11-19:10:54: /89925082_BasicComboBoxUIItemH 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 U6-11-19:10:54: /8ace5a9d_BasicComboPopupInvoc 19-NOV-06
1 U6-11-19:10:54: /8c03fffd_ConvertExpression 19-NOV-06
1 U6-11-19:10:56: java/awt/Robot$1 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 U6-11-19:10:56: java/awt/Scrollbar 19-NOV-06
1 U6-11-19:10:56: java/awt/Transparency 19-NOV-06
1 U6-11-19:11:03: oracle/jms/AQjmsDBConnMgr 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 U6-11-19:11:03: oracle/jms/AQjmsFactory 19-NOV-06
1 U6-11-19:11:03: oracle/jms/AQjmsMessages_es 19-NOV-06
1 V6-11-19:10:29: DBA_UPDATABLE_COLUMNS 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 V6-11-19:10:29: DBA_VIEWS 19-NOV-06
1 V6-11-19:10:29: EXU81SCMU 19-NOV-06
1 V6-11-19:10:54: oracle/net/ano/AnoComm 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 V6-11-19:10:54: oracle/net/aso/C04 19-NOV-06
1 V6-11-19:10:54: oracle/net/nl/RepConversion 19-NOV-06
1 V6-11-19:10:57: /468abe72_ExProperties 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 V6-11-19:10:57: /4b20d243_SQLCheckerCustomizer 19-NOV-06
1 V6-11-19:10:57: /4ed3030_SQLCheckerCustomizerE 19-NOV-06
1 V6-11-19:11:11: /cd0ad5d2_SeekableInputStream 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 V6-11-19:11:11: /d7ae6e21_ComplexArithmeticOpI 19-NOV-06
1 V6-11-19:11:11: /dc740995_ImageCanvasPaintList 19-NOV-06
1 W6-11-19:10:33: DBMS_SCHED_JOB_EXPORT 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 W6-11-19:10:33: I_SCHEDULER_STEP1 19-NOV-06
1 W6-11-19:10:33: I_SCHEDULER_WINDOW1 19-NOV-06
1 W6-11-19:10:55: /70be5192_ManagerFactoryParame 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 W6-11-19:10:55: /75197509_RemainderExpression 19-NOV-06
1 W6-11-19:10:55: /7573ba94_EventContext 19-NOV-06
1 W6-11-19:10:57: java/net/URLConnection$1 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 W6-11-19:10:57: java/nio/CharBuffer 19-NOV-06
1 W6-11-19:10:57: java/nio/DirectByteBuffer$1 19-NOV-06
1 W6-11-19:11:26: SQLREMOTESPECIFICATIONUPDATENE 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 W6-11-19:11:26: SQLUNMATCHEDINPUTSEXCEPTION 19-NOV-06
1 W6-11-19:11:26: SQLWSTRINGSEQUENCE 19-NOV-06
1 X6-11-19:10:41: _ALL_REPCOLUMN_GROUP 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 X6-11-19:10:41: _USER_REPL_NESTED_TABLE_NAMES 19-NOV-06
1 X6-11-19:10:42: DBMS_DEFER_ENQ_UTL_LIB 19-NOV-06
1 X6-11-19:10:55: /df03598d_NoContextHelper 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 X6-11-19:10:55: /e0cd4f75_ComponentFactoryBase 19-NOV-06
1 X6-11-19:10:55: /e34a44ec_DelegationPermission 19-NOV-06
1 X6-11-19:10:58: /4376bfbb_ProfileErrorsText_sv 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 X6-11-19:10:58: /43b612aa_RuntimeRefErrorsText 19-NOV-06
1 X6-11-19:10:58: /4dcd2f2c_LocaleElements_sv 19-NOV-06
1 X6-11-19:11:36: /e4e2d7af_GeoRasterException 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 X6-11-19:11:36: /eba1cb38_GRImageWriteParam 19-NOV-06
1 X6-11-19:11:36: JAVA$POLICY$SHARED$0000000a 19-NOV-06
1 Y6-11-19:10:53: /bd7edaad_TransformerIdentityI 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 Y6-11-19:10:53: /be699917_NodeIterator 19-NOV-06
1 Y6-11-19:10:53: /c27977a2__DynValueStub 19-NOV-06
1 Y6-11-19:10:56: /4adc2f24_ThreadPolicyOperatio 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 Y6-11-19:10:56: /4b3869f4_RuleBasedBreakIterat 19-NOV-06
1 Y6-11-19:10:56: /4b8ed38e_PixelConverterUshort 19-NOV-06
1 Y6-11-19:11:00: /7fdf8865_DataContentHandler 19-NOV-06
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 Y6-11-19:11:00: /a67381d0_IMAPStoreConnectionP 19-NOV-06
1 Y6-11-19:11:00: /a776837d_FolderListener 19-NOV-06
1 Y7-11-07:03:42: COMPOSITE_CATEGORY_TYP 07-NOV-07
I FNAME SNAME DOB
---------- -------------------------------------------------- -------------------------------------------------------------------------------- ---------
1 Y7-11-07:03:42: LEAF_CATEGORY_TYP 07-NOV-07
1 Y7-11-07:03:42: LINEITEM_V 07-NOV-07
299 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3241706543
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50116 | 5089K| 41585 (1)| 00:08:20 |
|* 1 | VIEW | | 50116 | 5089K| 41585 (1)| 00:08:20 |
| 2 | WINDOW NOSORT | | 50116 | 2544K| 41585 (1)| 00:08:20 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 50116 | 2544K| 40930 (1)| 00:08:12 |
|* 5 | INDEX FULL SCAN | IDX_USE_ME | 50116 | | 389 (1)| 00:00:05 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MOD("RNUM",506)<3)
5 - filter("FNAME" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
40897 consistent gets
0 physical reads
0 redo size
15840 bytes sent via SQL*Net to client
605 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
299 rows processed
SQL> [Updated on: Fri, 28 March 2008 16:52] Report message to a moderator |
|
|
|
| Re: Bucketing Query Performance [message #309860 is a reply to message #309777] |
Sat, 29 March 2008 07:22  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
How fast is it now?
How fast do you hope to make it?
As with your other thread (you haven't replied to my latest offering there) you have used an index to eliminate a sort, and reduced the SQL to a single FULL SCAN of the index.
If you think Oracle can do better, I think you're being a bit unreasonable.
The only things I can think of to consider are:
- INDEX FULL SCAN reads data block-by-block. This is ineffiecient. FULL TABLE SCAN and FAST-FULL INDEX SCAN read multi-blocks making I/O much faster. But both of these would require a sort; this would steal back any performance savings.
- You are not keeping every row, so there is some redundant I/O. Can it be eliminated? I think not. I know of know way to skip-forward n rows in an index. You can write clever code to skip to the next distinct value, but the only way to count those rows is to read them.
- Once we have eliminated redundant I/O and sorts, the next option in tuning is to parallelise. Not much opportunity here. If you range-partitioned the table on FNAME you might have a shot. But the trick would be to get partitions of equal size. That would be easier with HASH partitions, but that would upset your sorting.
I think you're at the end of the road.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Bucketing Query Performance [message #309905 is a reply to message #309860] |
Sat, 29 March 2008 13:45 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Ross,
The query takes 2 seconds to execute now, and I'd like to speed it up to at least 0.5sec.
I do not know Oracle well enough to see if I am unreasonable, however I am a seasoned Real Programmer who implemented many trees (Red&Black, B+Tree, PK-Tree, XY-AVL, Trie) in C and assembler, and it is hard for me to accept the unnecessary scans and I/O because I've done better myself with indices of all sorts (albeit not in Oracle).
I'm down to the bare metal guy, who thinks in bytes & bits and is disgusted by monstrosities like HTML, etc...
I do not understand how Oracle organizes its indices, but if there is some kind if In-Order sequencing of the index file, maybe jumping X rows ahead can be done without scanning all of the intermediate rows. The LEAD windowing function comes to mind here...
If I am at the end of the road with this problem then it is a valid answer, but it would mean that RDBMS architecture is architecturally flawed if it cannot deal with such class of problems efficiently.
Regards,
George
|
|
|
|
|
|
| Re: Bucketing Query Performance [message #309907 is a reply to message #309906] |
Sat, 29 March 2008 14:09 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Dear All,
Experimenting with the ROW_NUMBER analytical function and a 2 level nesting of the query has brought me to an interesting observation in the query plan.
Namely to the WINDOW NOSORT STOPKEY
Intersting from perfomance point of view...
Just sharing,
George
SELECT i,FNAME,SNAME,DOB
FROM (
SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
--LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
FNAME,SNAME,DOB
FROM CUSTOMER
WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
ORDER BY FNAME,SNAME,ID
)
WHERE rnum=10000 |
|
|
|
| Re: Bucketing Query Performance [message #309921 is a reply to message #309907] |
Sat, 29 March 2008 15:55 |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
On 11g I get window sort pushed rank and fast execution.
SCOTT@orcl_11g> SELECT i,FNAME,SNAME,DOB
2 FROM (
3 SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
4 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
5 row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
6 --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
7 FNAME,SNAME,DOB
8 FROM CUSTOMER
9 WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
10 ORDER BY FNAME,SNAME,ID
11 )
12 WHERE rnum=10000
13 /
I FNAME SNAME DOB
---------- --------------- ------------------------------ ---------
1 C7-10-15:10:41: /4585bb77_MatchCDFDescriptor 15-OCT-07
Elapsed: 00:00:00.15
Execution Plan
----------------------------------------------------------
Plan hash value: 2990140013
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68419 | 6948K| 171 (1)| 00:00:03 |
|* 1 | VIEW | | 68419 | 6948K| 171 (1)| 00:00:03 |
|* 2 | WINDOW SORT PUSHED RANK| | 68419 | 3541K| 171 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | CUSTOMER | 68419 | 3541K| 171 (1)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM"=10000)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "FNAME","SNAME","ID")<=10000)
3 - filter("FNAME" IS NOT NULL)
SCOTT@orcl_11g>
From the documentation:
"Predicate Pushing
For those views that are not merged, the query transformer can push the relevant predicates from the containing query block into the view query block. This technique improves the subplan of the non-merged view, because the pushed-in predicates can be used either to access indexes or to act as filters."
|
|
|
|
| Re: Bucketing Query Performance [message #309922 is a reply to message #309921] |
Sat, 29 March 2008 16:35 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Barbara,
Very interesting! 
..I wonder how much better it still would be if it used an index?
Could you try it with the mod(rnum,684)<=3 in the WHERE clause on 11g, and see how much it slows down?
I have only 10g to experiment with.
Regards,
George
P.S.
Of course, change the literal 684 to 1/100th of your row count.
[Updated on: Sat, 29 March 2008 16:41] Report message to a moderator |
|
|
|
| Re: Bucketing Query Performance [message #309923 is a reply to message #309922] |
Sat, 29 March 2008 16:53 |
|
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It loses the plan.
SCOTT@orcl_11g> SELECT i,FNAME,SNAME,DOB
2 FROM (
3 SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
4 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
5 row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
6 --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
7 FNAME,SNAME,DOB
8 FROM CUSTOMER
9 WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
10 ORDER BY FNAME,SNAME,ID
11 )
12 WHERE MOD (rnum, 684) <= 3
13 /
I FNAME SNAME DOB
---------- --------------- ------------------------------ ---------
1 @2-08-01:09:48: SDO_LIST_TYPE 15-OCT-07
1 @7-04-09:14:17: ST_ANNOTATION_TEXT 15-OCT-07
1 @7-10-15:10:09: ALL_IND_COLUMNS 15-OCT-07
1 @7-10-15:10:28: /9330a682_BasicToggleButtonUI 15-OCT-07
1 @7-10-15:10:28: /94254c62_VMMethodVMMethodImpl 15-OCT-07
1 @7-10-15:10:28: /9512e7a_AMD64AbstractMIR2LIRC 15-OCT-07
1 @7-10-15:10:28: /9678561d_ConditionalExpressio 15-OCT-07
1 @7-10-15:10:30: /a106cfc4_WritableRenderedImag 15-OCT-07
1 @7-10-15:10:30: /a15b376c_NVListImpl 15-OCT-07
1 @7-10-15:10:30: /a210f279_CopierManager 15-OCT-07
1 @7-10-15:10:30: /a2af1500_BasicSplitPaneUIKeyb 15-OCT-07
1 @7-10-15:10:36: SYS_C003684 15-OCT-07
1 @7-10-15:10:36: XDB$ELEMENT_T 15-OCT-07
1 @7-10-15:10:36: XDB$INCLUDE_LIST_T 15-OCT-07
1 @7-10-15:10:36: XDB$XMLTYPE_REF_LIST_T 15-OCT-07
1 A7-10-15:10:09: V_$SQLAREA 15-OCT-07
1 A7-10-15:10:09: V_$SQL_FEATURE_DEPENDENCY 15-OCT-07
1 A7-10-15:10:09: V_$SQL_PLAN 15-OCT-07
1 A7-10-15:10:09: V_$THREAD 15-OCT-07
1 A7-10-15:10:28: /ef9b7e35_ConditionalBitPatter 15-OCT-07
1 A7-10-15:10:28: /f143cb63_BasicProgressBarUICh 15-OCT-07
1 A7-10-15:10:28: /f15851f5_ExecutePermissionExc 15-OCT-07
1 A7-10-15:10:28: /f1eac0bd_IA64AbstractMIR2LIRC 15-OCT-07
1 A7-10-15:10:30: java/awt/geom/QuadIterator 15-OCT-07
1 A7-10-15:10:30: java/awt/image/BufferStrategy 15-OCT-07
1 A7-10-15:10:30: java/awt/image/ConvolveOp 15-OCT-07
1 A7-10-15:10:30: java/beans/BeanDescriptor 15-OCT-07
1 A7-10-15:10:41: SI_SCOREBYAVGCLR 15-OCT-07
1 A7-10-15:10:41: SI_SETTEXTUREFTR 15-OCT-07
1 A7-10-15:10:41: SL_ATTR_T547_T 15-OCT-07
1 A7-10-15:10:41: XDj7qm+JEZR2uI0wGKnu+qQg== 15-OCT-07
1 B7-10-15:10:11: REPCAT_GENERATED 15-OCT-07
1 B7-10-15:10:11: RULE$ 15-OCT-07
1 B7-10-15:10:11: SCHEDULER$_ATTRIB_PK 15-OCT-07
1 B7-10-15:10:11: SCHEDULER$_EVENT_LOG 15-OCT-07
1 B7-10-15:10:29: /25ef00e8_SecuritySupport125 15-OCT-07
1 B7-10-15:10:29: /27bbee3d_JDWPStackFrameGetVal 15-OCT-07
1 B7-10-15:10:29: /28b67e03_Operation 15-OCT-07
1 B7-10-15:10:29: /28cdf563_ThreadPolicyImpl 15-OCT-07
1 B7-10-15:10:30: sun/misc/PerformanceLogger 15-OCT-07
1 B7-10-15:10:30: sun/misc/ProxyGenerator 15-OCT-07
1 B7-10-15:10:30: sun/nio/ch/DatagramDispatcher 15-OCT-07
1 B7-10-15:10:30: sun/nio/ch/SocketAdaptor$1 15-OCT-07
1 B7-10-15:10:48: GV_OLAPI_IFACE_OP_HISTORY 15-OCT-07
1 B7-10-15:10:48: OLAPIHANDSHAKE2 15-OCT-07
1 B7-10-15:10:48: oracle/spatial/network/Kruskal 15-OCT-07
1 B7-10-15:10:49: /5111681e_OraOlapConstants 15-OCT-07
1 C7-10-15:10:13: USER_CUBE_HIER_LEVELS 15-OCT-07
1 C7-10-15:10:13: USER_MEASURE_FOLDER_CONTENTS 15-OCT-07
1 C7-10-15:10:13: USER_QUEUE_SCHEDULES 15-OCT-07
1 C7-10-15:10:13: USER_SCHEDULER_CHAIN_STEPS 15-OCT-07
1 C7-10-15:10:29: /718f419e_Constants 15-OCT-07
1 C7-10-15:10:29: /72285d3a_DatatypeException 15-OCT-07
1 C7-10-15:10:29: /72b13abc_BasicParserConfigura 15-OCT-07
1 C7-10-15:10:29: /75db13f6_JDWPEventRequestSetM 15-OCT-07
1 C7-10-15:10:31: /96ca5e42_Messages_arpropertie 15-OCT-07
1 C7-10-15:10:31: /98b64cec_OracleErrorsText_zh_ 15-OCT-07
1 C7-10-15:10:31: /9966bd63_Oracle8JdbcChecker 15-OCT-07
1 C7-10-15:10:31: /9a3789ab_InvalidRelationIdExc 15-OCT-07
1 C7-10-15:10:56: WK$INST_ADMIN_LIST 15-OCT-07
1 C7-10-15:10:56: WK$PT_ID_SEQ 15-OCT-07
1 C7-10-15:10:56: WK$SCHED_ID_SEQ 15-OCT-07
1 C7-10-15:10:56: WK$TRACE_PK 15-OCT-07
1 D7-10-15:10:23: OWA_OPT_LOCK 15-OCT-07
1 D7-10-15:10:23: SYS_IOT_OVER_11848 15-OCT-07
1 D7-10-15:10:26: ALL_WORKSPACES 15-OCT-07
1 D7-10-15:10:26: OWM_ASSERT_PKG 15-OCT-07
1 D7-10-15:10:29: /cba799a5_TransportServiceList 15-OCT-07
1 D7-10-15:10:29: /cc33bb15_ResourceManager 15-OCT-07
1 D7-10-15:10:29: /cc620e87_IAND 15-OCT-07
1 D7-10-15:10:29: /ccce3e04_RMIConnectorRMIClien 15-OCT-07
1 D7-10-15:10:31: lib/security/java.security 15-OCT-07
1 D7-10-15:10:31: oracle/aurora/rdbms/JMXAgent 15-OCT-07
1 D7-10-15:10:31: oracle/aurora/util/JRIExt 15-OCT-07
1 D7-10-15:10:31: oracle/aurora/util/TableReader 15-OCT-07
1 D7-10-15:10:59: MGMT_ECM_SNAPSHOT_PKG 15-OCT-07
1 D7-10-15:11:00: AQ$_MGMT_PAF_MSG_QTABLE_1_I 15-OCT-07
1 D7-10-15:11:00: CHECK_DUPLICATE_TARGETS 15-OCT-07
1 D7-10-15:11:00: EM_LICENSE 15-OCT-07
1 E7-10-15:10:28: /24c93e10_IA64AbstractMIR2LIRC 15-OCT-07
1 E7-10-15:10:28: /255eb681_AnnotationValueImplT 15-OCT-07
1 E7-10-15:10:28: /25bcb906_ConditionalBitPatter 15-OCT-07
1 E7-10-15:10:28: /25ce416e_OracleClassLoader 15-OCT-07
1 E7-10-15:10:29: sun/misc/CacheEntry 15-OCT-07
1 E7-10-15:10:29: sun/net/www/HeaderParser 15-OCT-07
1 E7-10-15:10:29: sun/security/jgss/ProviderList 15-OCT-07
1 E7-10-15:10:29: sun/security/krb5/Checksum 15-OCT-07
1 E7-10-15:10:33: /1eb71508_XSLJDWPSocketConn 15-OCT-07
1 E7-10-15:10:33: /2293736_XSLTransformerErrorLi 15-OCT-07
1 E7-10-15:10:33: /29988fbd_MailcapCommandMap 15-OCT-07
1 E7-10-15:10:33: /2dfbd72c_ComparisonTerm 15-OCT-07
1 E7-11-08:05:58: AQ$ORDERS_QUEUETABLE_R 08-NOV-07
1 E7-11-08:05:58: AQ$_STREAMS_QUEUE_TABLE_H 08-NOV-07
1 E7-11-08:05:58: COSTS 08-NOV-07
1 E7-11-08:05:58: COSTS_PROD_BIX 08-NOV-07
1 F7-10-15:10:28: /7df2be4b_BasicComboBoxUI1 15-OCT-07
1 F7-10-15:10:28: /7ef71be3_VMWellKnownMethodReg 15-OCT-07
1 F7-10-15:10:28: /7f9d6b06_PowerPCCodeGenerator 15-OCT-07
1 F7-10-15:10:28: /8136aaa1_ClassReader1 15-OCT-07
1 F7-10-15:10:30: /846552c0_BaseMetaDataImpl 15-OCT-07
1 F7-10-15:10:30: /8573778c_WriteContents 15-OCT-07
1 F7-10-15:10:30: /85db04e1_AttributeNotFoundExc 15-OCT-07
1 F7-10-15:10:30: /87f55b2d_ImageViewImageLabelV 15-OCT-07
1 F7-10-15:10:34: oracle/xquery/exec/LogicalOp 15-OCT-07
1 F7-10-15:10:34: oracle/xquery/exec/PathAxis 15-OCT-07
1 F7-10-15:10:34: oracle/xquery/func/OraVersion 15-OCT-07
1 F7-10-15:10:34: oracle/xquery/parser/Qname 15-OCT-07
1 G7-10-15:10:09: PROXY_ROLE_DATA$ 15-OCT-07
1 G7-10-15:10:09: RESOURCE_MAPPING_PRIORITY$ 15-OCT-07
1 G7-10-15:10:09: RLS_SC$ 15-OCT-07
1 G7-10-15:10:09: RULESET$ 15-OCT-07
1 G7-10-15:10:28: /e2cd7a5d_Dominators 15-OCT-07
1 G7-10-15:10:28: /e2d98d5_IA64BaseLIRInstrFSABu 15-OCT-07
1 G7-10-15:10:28: /e2ffd5f2_BasicTreeUITreeCance 15-OCT-07
1 G7-10-15:10:28: /e505feef_AdaptorBootstrapDefa 15-OCT-07
1 G7-10-15:10:30: /e6ed02b7_VetoableChangeSuppor 15-OCT-07
1 G7-10-15:10:30: /e7f02dc5__CodeBaseStub 15-OCT-07
1 G7-10-15:10:30: /e8c9db7b_CDRInputStream_1_0 15-OCT-07
1 G7-10-15:10:30: /e99d1147_DocumentName 15-OCT-07
1 G7-10-15:10:41: /a0d62d0d_FileLoadDescriptor 15-OCT-07
1 G7-10-15:10:41: /a28b5b32_strhChunk 15-OCT-07
1 G7-10-15:10:41: /a500e316_JaiI18N 15-OCT-07
1 G7-10-15:10:41: /a8cafc03_MlibTranslateRIF 15-OCT-07
1 H7-10-15:10:11: ALL_REPGENOBJECTS 15-OCT-07
1 H7-10-15:10:11: ALL_REPGROUP 15-OCT-07
1 H7-10-15:10:11: ALL_RULE_SETS 15-OCT-07
1 H7-10-15:10:11: AQ$_SUBSCRIBER_TABLE_PRIMARY 15-OCT-07
1 H7-10-15:10:28: sqlj/util/Parselet 15-OCT-07
1 H7-10-15:10:28: sun/swing/FilePane$3 15-OCT-07
1 H7-10-15:10:28: sun/text/IntHashtable 15-OCT-07
1 H7-10-15:10:28: sun/tools/asm/CatchData 15-OCT-07
1 H7-10-15:10:30: javax/swing/text/html/HTML 15-OCT-07
1 H7-10-15:10:30: javax/swing/tree/RowMapper 15-OCT-07
1 H7-10-15:10:30: javax/swing/undo/CompoundEdit 15-OCT-07
1 H7-10-15:10:30: oracle/aurora/vm/IUHandle 15-OCT-07
1 H7-10-15:10:44: ST_GEOMCOLLECTION 15-OCT-07
1 H7-10-15:10:45: COORD_REF_SYSTEM_PRIM 15-OCT-07
1 H7-10-15:10:45: SDO_ADMIN 15-OCT-07
1 H7-10-15:10:45: SDO_COORD_OPS 15-OCT-07
1 I7-10-15:10:12: LOGMNR_TAB_COLS_SUPPORT 15-OCT-07
1 I7-10-15:10:12: ODCIINDEXINFODUMP 15-OCT-07
1 I7-10-15:10:12: ODCIOBJECTLIST 15-OCT-07
1 I7-10-15:10:12: REPCAT$_SITES_NEW_FK2_IDX 15-OCT-07
1 I7-10-15:10:29: /5cd64240_MessageTokenMessageT 15-OCT-07
1 I7-10-15:10:29: /5f1659ed_RealType 15-OCT-07
1 I7-10-15:10:29: /60f1dbe_XMLEntityManagerEntit 15-OCT-07
1 I7-10-15:10:29: /61027b31_VoidType 15-OCT-07
1 I7-10-15:10:31: /561f4976_AbstractPreferencesN 15-OCT-07
1 I7-10-15:10:31: /564607d_HTMLAnchorElement 15-OCT-07
1 I7-10-15:10:31: /5aaea7ab_OraCustomizerErrorsT 15-OCT-07
1 I7-10-15:10:31: /5ae161b6_DefaultProxySelector 15-OCT-07
1 I7-10-15:10:54: USER_SDO_NETWORK_USER_DATA 15-OCT-07
1 I7-10-15:10:55: ORDMD_TNPC_LIBS 15-OCT-07
1 I7-10-15:10:55: R_TABLE 15-OCT-07
1 I7-10-15:10:55: SDO_WFS_LOCK 15-OCT-07
1 J7-10-15:10:17: DBMS_EXTENDED_TTS_CHECKS 15-OCT-07
1 J7-10-15:10:17: DBMS_METADATA_UTIL 15-OCT-07
1 J7-10-15:10:17: DBMS_SUMVDM 15-OCT-07
1 J7-10-15:10:17: WRI$_ADV_OBJSPACE_TREND_T 15-OCT-07
1 J7-10-15:10:29: /b7fde19a_DefaultCallbackHandl 15-OCT-07
1 J7-10-15:10:29: /b86f7d4a_AptAptTreeScanner 15-OCT-07
1 J7-10-15:10:29: /b93aef44_JDWPStackFrameSetVal 15-OCT-07
1 J7-10-15:10:29: /b960f153_LdapSchemaCtx 15-OCT-07
1 J7-10-15:10:31: /f8866ac7_SslRMIServerSocketFa 15-OCT-07
1 J7-10-15:10:31: /fa7e56d4_BasicToolBarSeparato 15-OCT-07
1 J7-10-15:10:31: /fab81813_MetalComboBoxUI 15-OCT-07
1 J7-10-15:10:31: /fc374dcc_ProviderList3 15-OCT-07
1 J7-10-15:10:57: MGMT_USER_TARGETS_PK 15-OCT-07
1 J7-10-15:10:57: MGMT_VIOLATION_CONTEXT_PK 15-OCT-07
1 J7-10-15:10:57: PARAM_VALUES_TAB 15-OCT-07
1 J7-10-15:10:57: PK_MGMT_HTTP_SESS_CBS 15-OCT-07
1 K7-10-15:10:27: sun/nio/cs/ext/ISCII91 15-OCT-07
1 K7-10-15:10:27: sun/nio/cs/ext/JISAutoDetect 15-OCT-07
1 K7-10-15:10:27: sun/nio/cs/ext/MS1255$Encoder 15-OCT-07
1 K7-10-15:10:27: sun/nio/cs/ext/MS874$Encoder 15-OCT-07
1 K7-10-15:10:29: /f8d8eba_LocalClientRequestDis 15-OCT-07
1 K7-10-15:10:29: /fc898616_XMLEntityManagerScan 15-OCT-07
1 K7-10-15:10:29: /fdb344c_AttributeSetMethodGen 15-OCT-07
1 K7-10-15:10:29: /fe43c70e_InvalidName 15-OCT-07
1 K7-10-15:10:31: sun/io/CharToByteCp922 15-OCT-07
1 K7-10-15:10:31: sun/io/CharToByteCp950 15-OCT-07
1 K7-10-15:10:31: sun/io/CharToByteISO2022 15-OCT-07
1 K7-10-15:10:31: sun/io/CharToByteISO8859_9 15-OCT-07
1 K7-10-15:11:07: WWV_FLOW_TRANSLATABLE_COLS$ 15-OCT-07
1 K7-10-15:11:07: WWV_FLOW_WS_MAP_IDX2 15-OCT-07
1 K7-10-15:11:07: WWV_MIG_ACC_FORM_MDL_IDX2 15-OCT-07
1 K7-10-15:11:07: WWV_MIG_ACC_GROUPS 15-OCT-07
1 L7-10-15:10:28: /63a4e300_BinaryConstantPool 15-OCT-07
1 L7-10-15:10:28: /63a8cb00_BoundsCheckRemovalSu 15-OCT-07
1 L7-10-15:10:28: /6526faab_StructMetaData 15-OCT-07
1 L7-10-15:10:28: /6653a3ee_BasicInternalFrameTi 15-OCT-07
1 L7-10-15:10:30: /67174ddd_HashtableEmptyEnumer 15-OCT-07
1 L7-10-15:10:30: /68c8d9b9_WeakHashMapValueIter 15-OCT-07
1 L7-10-15:10:30: /6989c0ac_UnsafeFieldAccessorI 15-OCT-07
1 L7-10-15:10:30: /6aa6faf_StringCodingConverter 15-OCT-07
1 L7-10-15:10:34: /e4907c47_OraURIException 15-OCT-07
1 L7-10-15:10:34: /e6092e7b_XQueryXUtils 15-OCT-07
1 L7-10-15:10:34: /f0c06870_XSLJDWPSockReader 15-OCT-07
1 L7-10-15:10:34: /f29b75a6_ExpectedSequenceType 15-OCT-07
1 M7-10-15:10:09: GV$TEMP_CACHE_TRANSFER 15-OCT-07
1 M7-10-15:10:09: GV_$ASM_FILE 15-OCT-07
1 M7-10-15:10:09: GV_$AW_OLAP 15-OCT-07
1 M7-10-15:10:09: GV_$BUFFERED_QUEUES 15-OCT-07
1 M7-10-15:10:28: /c3159ec_OracleResultSetMetaDa 15-OCT-07
1 M7-10-15:10:28: /c4cda70e_CommonSourceObject 15-OCT-07
1 M7-10-15:10:28: /c5790956_VerifierClassLoader 15-OCT-07
1 M7-10-15:10:28: /c591b98e_ByteCodeToMIRInstrMI 15-OCT-07
1 M7-10-15:10:30: /c6a2699c_AccessibleKeyBinding 15-OCT-07
1 M7-10-15:10:30: /c7e340d5_FontRenderContext 15-OCT-07
1 M7-10-15:10:30: /c80d76bd_MissingFormatArgumen 15-OCT-07
1 M7-10-15:10:30: /c8dc965d_JFormattedTextFieldA 15-OCT-07
1 M7-10-15:10:40: TiffIfd318_T 15-OCT-07
1 M7-10-15:10:40: XS$CACHE_ACTIONS 15-OCT-07
1 M7-10-15:10:40: gpsStatusType265_T 15-OCT-07
1 M7-10-15:10:41: /1106a6c1_MultiplyOpImage 15-OCT-07
1 N7-10-15:10:10: DICTIONARY 15-OCT-07
1 N7-10-15:10:10: EXU81INDC 15-OCT-07
1 N7-10-15:10:10: EXU81IXCP 15-OCT-07
1 N7-10-15:10:10: EXU81SRTU 15-OCT-07
1 N7-10-15:10:28: javax/sql/RowSetEvent 15-OCT-07
1 N7-10-15:10:28: javax/swing/Spring$1 15-OCT-07
1 N7-10-15:10:28: oracle/aurora/ncomp/tree/Node 15-OCT-07
1 N7-10-15:10:28: oracle/aurora/rdbms/Compiler$2 15-OCT-07
1 N7-10-15:10:30: java/util/regex/Pattern$SliceU 15-OCT-07
1 N7-10-15:10:30: java/util/zip/Checksum 15-OCT-07
1 N7-10-15:10:30: java/util/zip/GZIPInputStream 15-OCT-07
1 N7-10-15:10:30: java/util/zip/ZipFile$1 15-OCT-07
1 N7-10-15:10:42: /68d868b4_TIFFFaxEncoder 15-OCT-07
1 N7-10-15:10:42: /6b1383f9_MlibExpOpImage 15-OCT-07
1 N7-10-15:10:42: /6d546325_InterpolationBicubic 15-OCT-07
1 N7-10-15:10:42: /75b6e045_DicomXSLTTreeLeaf 15-OCT-07
1 O7-10-15:10:11: WRI$_SQLSET_REFERENCES 15-OCT-07
1 O7-10-15:10:11: WRI$_TRACING_IND1 15-OCT-07
1 O7-10-15:10:11: WRM$_DATABASE_INSTANCE_PK 15-OCT-07
1 O7-10-15:10:11: WRR$_CONNECTION_MAP_PK 15-OCT-07
1 O7-10-15:10:29: /374ed299_ParserTable9 15-OCT-07
1 O7-10-15:10:29: /37eb2758_SWAP 15-OCT-07
1 O7-10-15:10:29: /383696a5_Choose 15-OCT-07
1 O7-10-15:10:29: /394708f4_SortResponseControl 15-OCT-07
1 O7-10-15:10:31: /3571c81c_DateFormatZoneData_s 15-OCT-07
1 O7-10-15:10:31: /39c45e79_ReturnType 15-OCT-07
1 O7-10-15:10:31: /3b25c3cc_Java 15-OCT-07
1 O7-10-15:10:31: /3cb23c9e_ClassResolver 15-OCT-07
1 O7-10-15:10:49: CWM2$LEVELATTRIBUTEMAP 15-OCT-07
1 O7-10-15:10:49: CWM2$LEVELATTRIBUTE_PK 15-OCT-07
1 O7-10-15:10:49: CWM2$MEASUREUPD 15-OCT-07
1 O7-10-15:10:49: CWM2$MRALL_DIM_LEVEL_ATTR_MAPS 15-OCT-07
1 P7-10-15:10:15: KUPC$_MASTER_MSG 15-OCT-07
1 P7-10-15:10:15: KUPC$_PAR_CON 15-OCT-07
1 P7-10-15:10:15: LOGSTDBY$EVENTS 15-OCT-07
1 P7-10-15:10:15: LOGSTDBY_UNSUPPORT_TAB_11_1 15-OCT-07
1 P7-10-15:10:29: /86a42226_ArrayNotificationBuf 15-OCT-07
1 P7-10-15:10:29: /86c5292e_TypesHashCodeFcn 15-OCT-07
1 P7-10-15:10:29: /887375b1_ClientNotifForwarder 15-OCT-07
1 P7-10-15:10:29: /88aa9821_ConstFold 15-OCT-07
1 P7-10-15:10:31: /d8a72f85_MultiMenuBarUI 15-OCT-07
1 P7-10-15:10:31: /d8ae555b_KeyGeneratorCoreARCF 15-OCT-07
1 P7-10-15:10:31: /d9bc5577_LocaleElements_ar_TN 15-OCT-07
1 P7-10-15:10:31: /db58ada5_RowSetResourceBundle 15-OCT-07
1 P7-10-15:10:57: MGMT_HC_CPU_DETAILS_IDX 15-OCT-07
1 P7-10-15:10:57: MGMT_HC_VENDOR_SW_SUMMARY 15-OCT-07
1 P7-10-15:10:57: MGMT_INV_PATCHSET 15-OCT-07
1 P7-10-15:10:57: MGMT_INV_SUMMARY_IDX 15-OCT-07
1 Q7-10-15:10:27: /e8494560_PermissionNameMenuLi 15-OCT-07
1 Q7-10-15:10:27: /ed4eba72_ReverseState 15-OCT-07
1 Q7-10-15:10:27: /f0698992_RemoteMonitoredVmNot 15-OCT-07
1 Q7-10-15:10:27: JAVA$JVM$STEPS$DONE 15-OCT-07
1 Q7-10-15:10:29: /e3b46633_SunJCE_ag 15-OCT-07
1 Q7-10-15:10:29: /e3c2b1ce_INSURLHandler 15-OCT-07
1 Q7-10-15:10:29: /e4390c99_EventSetImplExceptio 15-OCT-07
1 Q7-10-15:10:29: /e4af2d78_CastExpr 15-OCT-07
1 Q7-10-15:10:31: org/omg/CORBA/VM_CUSTOM 15-OCT-07
1 Q7-10-15:10:31: org/omg/IOP/TAG_INTERNET_IOP 15-OCT-07
1 Q7-10-15:10:31: org/w3c/dom/TypeInfo 15-OCT-07
1 Q7-10-15:10:31: org/xml/sax/ext/Locator2Impl 15-OCT-07
1 Q7-10-15:11:07: WWV_BD_FLOWPROCESSING 15-OCT-07
1 Q7-10-15:11:07: WWV_BIU_FLOWFLASHCHARTSERIES 15-OCT-07
1 Q7-10-15:11:07: WWV_BIU_FLOW_DB_AUTH 15-OCT-07
1 Q7-10-15:11:07: WWV_BIU_FLOW_PLATFORM_PREFS 15-OCT-07
1 R7-10-15:10:28: /432a0499_ASTVariableInitializ 15-OCT-07
1 R7-10-15:10:28: /43e0e39c_LoopOptimizations5 15-OCT-07
1 R7-10-15:10:28: /4548ec8d_AMD64AbstractMIR2LIR 15-OCT-07
1 R7-10-15:10:28: /45b38554_SnmpCachedData1 15-OCT-07
1 R7-10-15:10:30: /3551b5d0_JLabelAccessibleJLab 15-OCT-07
1 R7-10-15:10:30: /35e884ee_JTableNumberEditor 15-OCT-07
1 R7-10-15:10:30: /370ef07d_ServerSocketChannelI 15-OCT-07
1 R7-10-15:10:30: /37af531e_SystemEventQueueUtil 15-OCT-07
1 R7-10-15:10:33: oracle/xdb/dom/XDBDocumentType 15-OCT-07
1 R7-10-15:10:33: oracle/xdb/servlet/XDBStream 15-OCT-07
1 R7-10-15:10:33: oracle/xml/async/DOMBuilder 15-OCT-07
1 R7-10-15:10:33: oracle/xml/binxml/BinXMLStream 15-OCT-07
1 S2-10-23:15:30: AQ$_JMS_MESSAGE_PROPERTY 15-OCT-07
1 S7-04-12:12:59: AQ$_RECIPIENTS 15-OCT-07
1 S7-04-12:12:59: RE$NV_NODE 15-OCT-07
1 S7-10-15:10:09: ALL_COL_COMMENTS 15-OCT-07
1 S7-10-15:10:28: /a224f89a_MIRThrow 15-OCT-07
1 S7-10-15:10:28: /a262318d_MetalBordersInternal 15-OCT-07
1 S7-10-15:10:28: /a3aa743b_RebindingBatchParser 15-OCT-07
1 S7-10-15:10:28: /a6497024_OracleTimeoutThreadP 15-OCT-07
1 S7-10-15:10:30: /9a52cc97_LayeredHighlighter 15-OCT-07
1 S7-10-15:10:30: /9b14d1c3_BlitBgTraceBlitBg 15-OCT-07
1 S7-10-15:10:30: /9b35b28b_ValueHandlerImpl1 15-OCT-07
1 S7-10-15:10:30: /9b679ecb_ServiceDialogPageSet 15-OCT-07
1 S7-10-15:10:36: XDB$SIMPLECONT_RES_T 15-OCT-07
1 S7-10-15:10:36: XMLCHARACTEROUTPUTSTREAM 15-OCT-07
1 S7-10-15:10:37: ALL_XML_INDEXES 15-OCT-07
1 S7-10-15:10:37: DBA_XML_TAB_COLS 15-OCT-07
1 T7-10-15:10:09: V_$JAVA_LIBRARY_CACHE_MEMORY 15-OCT-07
1 T7-10-15:10:09: V_$LOCK_TYPE 15-OCT-07
1 T7-10-15:10:09: V_$PERSISTENT_QUEUES 15-OCT-07
1 T7-10-15:10:09: V_$PX_PROCESS 15-OCT-07
1 T7-10-15:10:28: /f292305d_OracleConversionRead 15-OCT-07
1 T7-10-15:10:28: /f3d144e_MkMsg 15-OCT-07
1 T7-10-15:10:28: /f461ba6f_ClassDocImpl 15-OCT-07
1 T7-10-15:10:28: /f596ba05_UnpickleContext 15-OCT-07
1 T7-10-15:10:30: java/awt/font/NumericShaper 15-OCT-07
1 T7-10-15:10:30: java/awt/font/TextLine$4 15-OCT-07
1 T7-10-15:10:30: java/awt/geom/Point2D$Double 15-OCT-07
1 T7-10-15:10:30: java/awt/image/BufferedImage 15-OCT-07
1 T7-10-15:10:41: SI_COLOR 15-OCT-07
1 T7-10-15:10:41: SI_COLORHISTOGRAM 15-OCT-07
1 T7-10-15:10:41: SI_GETCONTENTLNGTH 15-OCT-07
1 T7-10-15:10:41: SI_GETPSTNLCLRFTR 15-OCT-07
1 U7-10-15:10:11: REPCAT$_TEMPLATE_OBJECTS_S 15-OCT-07
1 U7-10-15:10:11: REPCAT$_USER_AUTHORIZATIONS_U1 15-OCT-07
1 U7-10-15:10:11: SCHEDULER$_PROGRAM_PK 15-OCT-07
1 U7-10-15:10:11: SCHEDULER$_RULE 15-OCT-07
1 U7-10-15:10:29: /1e0126be_Pattern 15-OCT-07
1 U7-10-15:10:29: /1e721728_MicToken_v2 15-OCT-07
1 U7-10-15:10:29: /1ecde73_Main14 15-OCT-07
1 U7-10-15:10:29: /1f354afc_XSSimpleTypeDeclXSFa 15-OCT-07
1 U7-10-15:10:30: sun/print/PSStreamPrintJob 15-OCT-07
1 U7-10-15:10:30: sun/print/PageableDoc 15-OCT-07
1 U7-10-15:10:30: sun/print/ServiceDialog$4 15-OCT-07
1 U7-10-15:10:30: sun/reflect/UTF8 15-OCT-07
1 U7-10-15:10:48: GENDATAPROVIDERINTERFACE 15-OCT-07
1 U7-10-15:10:48: OLAPIHANDSHAKE2 15-OCT-07
1 U7-10-15:10:48: OLAPISHUTDOWNTRIGGER 15-OCT-07
1 U7-10-15:10:48: OLAPLEVELTUPLES 15-OCT-07
1 V7-10-15:10:13: USER_SCHEDULER_CHAIN_RULES 15-OCT-07
1 V7-10-15:10:13: _DBA_QUEUE_SCHEDULES 15-OCT-07
1 V7-10-15:10:14: KU$_10_1_SYSGRANT_VIEW 15-OCT-07
1 V7-10-15:10:14: KU$_HTSPART_BYTES_ALLOC_VIEW 15-OCT-07
1 V7-10-15:10:29: /763d9f4_StringBufferPool 15-OCT-07
1 V7-10-15:10:29: /765f219d_TreeTypeTest 15-OCT-07
1 V7-10-15:10:29: /76fb91e5_SecuritySupport126 15-OCT-07
1 V7-10-15:10:29: /785e28d4_JPEGImageEncoderImpl 15-OCT-07
1 V7-10-15:10:31: /9130791a_Messages_nl 15-OCT-07
1 V7-10-15:10:31: /94b047ba_ProfileErrorsText_da 15-OCT-07
1 V7-10-15:10:31: /974c0aa9_MirroredTypesExcepti 15-OCT-07
1 V7-10-15:10:31: /977cf88f_SemanticErrorsText_r 15-OCT-07
1 V7-10-15:10:56: WK$_DATA_SOURCE_TYPE 15-OCT-07
1 V7-10-15:10:56: WK$_FSEARCH_ATTR_PK 15-OCT-07
1 V7-10-15:10:56: WKDS_ADM 15-OCT-07
1 V7-10-15:10:57: AQ$_MGMT_TASK_QTABLE_T 15-OCT-07
1 W7-10-15:10:26: DBA_WORKSPACE_SAVEPOINTS 15-OCT-07
1 W7-10-15:10:26: OWM_REPUTIL 15-OCT-07
1 W7-10-15:10:26: SYS_C003350 15-OCT-07
1 W7-10-15:10:26: SYS_C003469 15-OCT-07
1 W7-10-15:10:29: /d6d4f8cf_HmacCoreHmacSHA512 15-OCT-07
1 W7-10-15:10:29: /d6fdcf7e_NamingContextHelper 15-OCT-07
1 W7-10-15:10:29: /d804fd61_B64Encoder 15-OCT-07
1 W7-10-15:10:29: /d8386f82_TreeScanner 15-OCT-07
1 W7-10-15:10:31: oracle/i18n/data/lx00011.glb 15-OCT-07
1 W7-10-15:10:31: oracle/i18n/data/lx0002b.glb 15-OCT-07
1 W7-10-15:10:31: oracle/i18n/data/lx0boot.glb 15-OCT-07
1 W7-10-15:10:31: oracle/i18n/data/lx10018.glb 15-OCT-07
1 W7-10-15:11:00: METRICS_INSERT_TRIGGER 15-OCT-07
1 W7-10-15:11:00: MGMT$DB_INIT_PARAMS_ALL 15-OCT-07
1 W7-10-15:11:00: MGMT$E2E_HOURLY 15-OCT-07
1 W7-10-15:11:00: MGMT$HA_BACKUP 15-OCT-07
1 X7-10-15:10:28: /2cfc8d65_SynthTextAreaUI 15-OCT-07
1 X7-10-15:10:28: /308433c9_IndentPrintWriter 15-OCT-07
1 X7-10-15:10:28: /308fbfa1_BeanContextServices 15-OCT-07
1 X7-10-15:10:28: /32964284_DefaultLoader 15-OCT-07
1 X7-10-15:10:30: /122fb53c_FontConfigurationPro 15-OCT-07
1 X7-10-15:10:30: /12e3f18d_SecureRandom 15-OCT-07
1 X7-10-15:10:30: /13257198_AccessibleHTMLProper 15-OCT-07
1 X7-10-15:10:30: /143bd6b4_AttributeMode 15-OCT-07
1 X7-10-15:10:33: /459fdef3_InstanceOfExpr 15-OCT-07
1 X7-10-15:10:33: /4d25c390_xdk_version_111060_p 15-OCT-07
1 X7-10-15:10:33: /4e5beaa7_JXTransformer1 15-OCT-07
1 X7-10-15:10:33: /4f88f54_XPathFollowingAxis 15-OCT-07
1 X7-11-08:05:58: SALES_CUST_BIX 08-NOV-07
1 X7-11-08:05:58: SALES_PROD_BIX 08-NOV-07
1 X7-11-08:05:58: SALES_PROMO_BIX 08-NOV-07
1 X7-11-08:05:58: SALES_TIME_BIX 08-NOV-07
1 Y7-10-15:10:28: /75730318_ParsedSynthStyleDele 15-OCT-07
1 Y7-10-15:10:28: /75d2b0ba_KnownOptions5 15-OCT-07
1 Y7-10-15:10:28: /75de5dcf_ActivationGroupImplA 15-OCT-07
1 Y7-10-15:10:28: /7647e11d_BinaryClass 15-OCT-07
1 Y7-10-15:10:30: /835eb71b_JDesktopPaneAccessib 15-OCT-07
1 Y7-10-15:10:30: /858cce89_WrappedPlainViewWrap 15-OCT-07
1 Y7-10-15:10:30: /86e7752b_IIOReadProgressListe 15-OCT-07
1 Y7-10-15:10:30: /883fe97c_InputMethodListener 15-OCT-07
1 Y7-10-15:10:35: ALL_EXPFIL_ASET_FUNCTIONS 15-OCT-07
1 Y7-10-15:10:35: ALL_EXPFIL_EXPRSET_STATS 15-OCT-07
1 Y7-10-15:10:35: CATINDEXMETHODS 15-OCT-07
1 Y7-10-15:10:35: CTX_PARAMETERS 15-OCT-07
399 rows selected.
Elapsed: 00:00:00.81
Execution Plan
----------------------------------------------------------
Plan hash value: 659629041
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68319 | 6938K| 171 (1)| 00:00:03 |
|* 1 | VIEW | | 68319 | 6938K| 171 (1)| 00:00:03 |
| 2 | WINDOW SORT | | 68319 | 3536K| 171 (1)| 00:00:03 |
|* 3 | TABLE ACCESS FULL| CUSTOMER | 68319 | 3536K| 171 (1)| 00:00:03 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MOD("RNUM",684)<=3)
3 - filter("FNAME" IS NOT NULL)
SCOTT@orcl_11g>
|
|
|
|
| Re: Bucketing Query Performance [message #309924 is a reply to message #309865] |
Sat, 29 March 2008 18:24 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| coleing wrote on Sat, 29 March 2008 23:43 | You could add DOB to the index to eliminate the table access. That is all I can think of.
|
Oh, man, I must be getting old. I didn't see the table access.
Yes, do this. You will almost certainly get your 4-fold improvement.
Also worth noting: your 2 second response time is on cached data
0 db block gets
40897 consistent gets
0 physical reads
This does not stop the redundant data read on the index (although there is TONS of redundant data that will be no longer read if you use coleing's suggestion. I believe with some confidence that eliminating the redundant scan of discarded index rows is impossible. Oracle stores variable-width index entries. The only way to know how many entires are stored in a block is to read the block - and then it's too late.
For more information on the structure of Indexes in Oracle, and for some explanation of why the TABLE ACCESS must be eliminated, see this article.
Ross Leishman
|
|
|
|
| Re: Bucketing Query Performance [message #309928 is a reply to message #309922] |
Sat, 29 March 2008 23:21 |
|
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I had an invalid test environment. I had an index named customer instead of idx_use_me, so the hint was invalid. Here are the revised results after changing the name of the index to idx_use_me.
SCOTT@orcl_11g> COLUMN fname FORMAT A15 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN sname FORMAT A30 WORD_WRAPPED
SCOTT@orcl_11g> SELECT i,FNAME,SNAME,DOB
2 FROM (
3 SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
4 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
5 row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
6 --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
7 FNAME,SNAME,DOB
8 FROM CUSTOMER
9 WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
10 ORDER BY FNAME,SNAME,ID
11 )
12 WHERE rnum=10000
13 /
I FNAME SNAME DOB
---------- --------------- ------------------------------ ---------
1 C7-10-15:10:41: /902b0133_DescriptorException 15-OCT-07
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 3488441616
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68309 | 6937K| 58561 (1)| 00:11:43 |
|* 1 | VIEW | | 68309 | 6937K| 58561 (1)| 00:11:43 |
|* 2 | WINDOW NOSORT STOPKEY | | 68309 | 3535K| 58561 (1)| 00:11:43 |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 68309 | 3535K| 58561 (1)| 00:11:43 |
|* 4 | INDEX FULL SCAN | IDX_USE_ME | 68309 | | 530 (1)| 00:00:07 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM"=10000)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "FNAME","SNAME","ID")<=10000)
4 - filter("FNAME" IS NOT NULL)
SCOTT@orcl_11g> SELECT i,FNAME,SNAME,DOB
2 FROM (
3 SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
4 row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
5 row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
6 --LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
7 FNAME,SNAME,DOB
8 FROM CUSTOMER
9 WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
10 ORDER BY FNAME,SNAME,ID
11 )
12 WHERE MOD (rnum, 684) <= 3
13 /
I FNAME SNAME DOB
---------- --------------- ------------------------------ ---------
1 @2-08-01:09:48: SDO_LIST_TYPE 15-OCT-07
1 @7-04-09:14:17: ST_ANNOTATION_TEXT 15-OCT-07
1 @7-10-15:10:09: ALL_IND_COLUMNS 15-OCT-07
1 @7-10-15:10:28: /9330a682_BasicToggleButtonUI 15-OCT-07
1 @7-10-15:10:28: /94254c62_VMMethodVMMethodImpl 15-OCT-07
1 @7-10-15:10:28: /9512e7a_AMD64AbstractMIR2LIRC 15-OCT-07
1 @7-10-15:10:28: /9678561d_ConditionalExpressio 15-OCT-07
1 @7-10-15:10:30: /a106cfc4_WritableRenderedImag 15-OCT-07
1 @7-10-15:10:30: /a15b376c_NVListImpl 15-OCT-07
1 @7-10-15:10:30: /a210f279_CopierManager 15-OCT-07
1 @7-10-15:10:30: /a2af1500_BasicSplitPaneUIKeyb 15-OCT-07
1 @7-10-15:10:36: SYS_C003684 15-OCT-07
1 @7-10-15:10:36: XDB$ELEMENT_T 15-OCT-07
1 @7-10-15:10:36: XDB$INCLUDE_LIST_T 15-OCT-07
1 @7-10-15:10:36: XDB$XMLTYPE_REF_LIST_T 15-OCT-07
1 A7-10-15:10:09: V_$SQLAREA 15-OCT-07
1 A7-10-15:10:09: V_$SQL_FEATURE_DEPENDENCY 15-OCT-07
1 A7-10-15:10:09: V_$SQL_PLAN 15-OCT-07
1 A7-10-15:10:09: V_$THREAD 15-OCT-07
1 A7-10-15:10:28: /ef9b7e35_ConditionalBitPatter 15-OCT-07
1 A7-10-15:10:28: /f143cb63_BasicProgressBarUICh 15-OCT-07
1 A7-10-15:10:28: /f15851f5_ExecutePermissionExc 15-OCT-07
1 A7-10-15:10:28: /f1eac0bd_IA64AbstractMIR2LIRC 15-OCT-07
1 A7-10-15:10:30: java/awt/geom/QuadIterator 15-OCT-07
1 A7-10-15:10:30: java/awt/image/BufferStrategy 15-OCT-07
1 A7-10-15:10:30: java/awt/image/ConvolveOp 15-OCT-07
1 A7-10-15:10:30: java/beans/BeanDescriptor 15-OCT-07
1 A7-10-15:10:41: SI_SCOREBYAVGCLR 15-OCT-07
1 A7-10-15:10:41: SI_SETTEXTUREFTR 15-OCT-07
1 A7-10-15:10:41: SL_ATTR_T547_T 15-OCT-07
1 A7-10-15:10:41: XDj7qm+JEZR2uI0wGKnu+qQg== 15-OCT-07
1 B7-10-15:10:11: REPCAT_GENERATED 15-OCT-07
1 B7-10-15:10:11: RULE$ 15-OCT-07
1 B7-10-15:10:11: SCHEDULER$_ATTRIB_PK 15-OCT-07
1 B7-10-15:10:11: SCHEDULER$_EVENT_LOG 15-OCT-07
1 B7-10-15:10:29: /25ef00e8_SecuritySupport125 15-OCT-07
1 B7-10-15:10:29: /27bbee3d_JDWPStackFrameGetVal 15-OCT-07
1 B7-10-15:10:29: /28b67e03_Operation 15-OCT-07
1 B7-10-15:10:29: /28cdf563_ThreadPolicyImpl 15-OCT-07
1 B7-10-15:10:30: sun/misc/PerformanceLogger 15-OCT-07
1 B7-10-15:10:30: sun/misc/ProxyGenerator 15-OCT-07
1 B7-10-15:10:30: sun/nio/ch/DatagramDispatcher 15-OCT-07
1 B7-10-15:10:30: sun/nio/ch/SocketAdaptor$1 15-OCT-07
1 B7-10-15:10:48: GV_OLAPI_IFACE_OP_HISTORY 15-OCT-07
1 B7-10-15:10:48: OLAPIHANDSHAKE2 15-OCT-07
1 B7-10-15:10:48: oracle/spatial/network/Kruskal 15-OCT-07
1 B7-10-15:10:49: /5111681e_OraOlapConstants 15-OCT-07
1 C7-10-15:10:13: USER_CUBE_HIER_LEVELS 15-OCT-07
1 C7-10-15:10:13: USER_MEASURE_FOLDER_CONTENTS 15-OCT-07
1 C7-10-15:10:13: USER_QUEUE_SCHEDULES 15-OCT-07
1 C7-10-15:10:13: USER_SCHEDULER_CHAIN_STEPS 15-OCT-07
1 C7-10-15:10:29: /718f419e_Constants 15-OCT-07
1 C7-10-15:10:29: /72285d3a_DatatypeException 15-OCT-07
1 C7-10-15:10:29: /72b13abc_BasicParserConfigura 15-OCT-07
1 C7-10-15:10:29: /75db13f6_JDWPEventRequestSetM 15-OCT-07
1 C7-10-15:10:31: /96ca5e42_Messages_arpropertie 15-OCT-07
1 C7-10-15:10:31: /98b64cec_OracleErrorsText_zh_ 15-OCT-07
1 C7-10-15:10:31: /9966bd63_Oracle8JdbcChecker 15-OCT-07
1 C7-10-15:10:31: /9a3789ab_InvalidRelationIdExc 15-OCT-07
1 C7-10-15:10:56: WK$INST_ADMIN_LIST 15-OCT-07
1 C7-10-15:10:56: WK$PT_ID_SEQ 15-OCT-07
1 C7-10-15:10:56: WK$SCHED_ID_SEQ 15-OCT-07
1 C7-10-15:10:56: WK$TRACE_PK 15-OCT-07
1 D7-10-15:10:23: OWA_OPT_LOCK 15-OCT-07
1 D7-10-15:10:23: SYS_IOT_OVER_11848 15-OCT-07
1 D7-10-15:10:26: ALL_WORKSPACES 15-OCT-07
1 D7-10-15:10:26: OWM_ASSERT_PKG 15-OCT-07
1 D7-10-15:10:29: /cba799a5_TransportServiceList 15-OCT-07
1 D7-10-15:10:29: /cc33bb15_ResourceManager 15-OCT-07
1 D7-10-15:10:29: /cc620e87_IAND 15-OCT-07
1 D7-10-15:10:29: /ccce3e04_RMIConnectorRMIClien 15-OCT-07
1 D7-10-15:10:31: lib/security/java.security 15-OCT-07
1 D7-10-15:10:31: oracle/aurora/rdbms/JMXAgent 15-OCT-07
1 D7-10-15:10:31: oracle/aurora/util/JRIExt 15-OCT-07
1 D7-10-15:10:31: oracle/aurora/util/TableReader 15-OCT-07
1 D7-10-15:10:59: MGMT_ECM_SNAPSHOT_PKG 15-OCT-07
1 D7-10-15:11:00: AQ$_MGMT_PAF_MSG_QTABLE_1_I 15-OCT-07
1 D7-10-15:11:00: CHECK_DUPLICATE_TARGETS 15-OCT-07
1 D7-10-15:11:00: EM_LICENSE 15-OCT-07
1 E7-10-15:10:28: /24c93e10_IA64AbstractMIR2LIRC 15-OCT-07
1 E7-10-15:10:28: /255eb681_AnnotationValueImplT 15-OCT-07
1 E7-10-15:10:28: /25bcb906_ConditionalBitPatter 15-OCT-07
1 E7-10-15:10:28: /25ce416e_OracleClassLoader 15-OCT-07
1 E7-10-15:10:29: sun/misc/CacheEntry 15-OCT-07
1 E7-10-15:10:29: sun/net/www/HeaderParser 15-OCT-07
1 E7-10-15:10:29: sun/security/jgss/ProviderList 15-OCT-07
1 E7-10-15:10:29: sun/security/krb5/Checksum 15-OCT-07
1 E7-10-15:10:33: /1eb71508_XSLJDWPSocketConn 15-OCT-07
1 E7-10-15:10:33: /2293736_XSLTransformerErrorLi 15-OCT-07
1 E7-10-15:10:33: /29988fbd_MailcapCommandMap 15-OCT-07
1 E7-10-15:10:33: /2dfbd72c_ComparisonTerm 15-OCT-07
1 E7-11-08:05:58: AQ$ORDERS_QUEUETABLE_R 08-NOV-07
1 E7-11-08:05:58: AQ$_STREAMS_QUEUE_TABLE_H 08-NOV-07
1 E7-11-08:05:58: COSTS 08-NOV-07
1 E7-11-08:05:58: COSTS_PROD_BIX 08-NOV-07
1 F7-10-15:10:28: /7df2be4b_BasicComboBoxUI1 15-OCT-07
1 F7-10-15:10:28: /7ef71be3_VMWellKnownMethodReg 15-OCT-07
1 F7-10-15:10:28: /7f9d6b06_PowerPCCodeGenerator 15-OCT-07
1 F7-10-15:10:28: /8136aaa1_ClassReader1 15-OCT-07
1 F7-10-15:10:30: /846552c0_BaseMetaDataImpl 15-OCT-07
1 F7-10-15:10:30: /8573778c_WriteContents 15-OCT-07
1 F7-10-15:10:30: /85db04e1_AttributeNotFoundExc 15-OCT-07
1 F7-10-15:10:30: /87f55b2d_ImageViewImageLabelV 15-OCT-07
1 F7-10-15:10:34: oracle/xquery/exec/LogicalOp 15-OCT-07
1 F7-10-15:10:34: oracle/xquery/exec/PathAxis 15-OCT-07
1 F7-10-15:10:34: oracle/xquery/func/OraVersion 15-OCT-07
1 F7-10-15:10:34: oracle/xquery/parser/Qname 15-OCT-07
1 G7-10-15:10:09: PROXY_ROLE_DATA$ 15-OCT-07
1 G7-10-15:10:09: RESOURCE_MAPPING_PRIORITY$ 15-OCT-07
1 G7-10-15:10:09: RLS_SC$ 15-OCT-07
1 G7-10-15:10:09: RULESET$ 15-OCT-07
1 G7-10-15:10:28: /e2cd7a5d_Dominators 15-OCT-07
1 G7-10-15:10:28: /e2d98d5_IA64BaseLIRInstrFSABu 15-OCT-07
1 G7-10-15:10:28: /e2ffd5f2_BasicTreeUITreeCance 15-OCT-07
1 G7-10-15:10:28: /e505feef_AdaptorBootstrapDefa 15-OCT-07
1 G7-10-15:10:30: /e6ed02b7_VetoableChangeSuppor 15-OCT-07
1 G7-10-15:10:30: /e7f02dc5__CodeBaseStub 15-OCT-07
1 G7-10-15:10:30: /e8c9db7b_CDRInputStream_1_0 15-OCT-07
1 G7-10-15:10:30: /e99d1147_DocumentName 15-OCT-07
1 G7-10-15:10:41: /a0d62d0d_FileLoadDescriptor 15-OCT-07
1 G7-10-15:10:41: /a28b5b32_strhChunk 15-OCT-07
1 G7-10-15:10:41: /a500e316_JaiI18N 15-OCT-07
1 G7-10-15:10:41: /a8cafc03_MlibTranslateRIF 15-OCT-07
1 H7-10-15:10:11: ALL_REPGENOBJECTS 15-OCT-07
1 H7-10-15:10:11: ALL_REPGROUP 15-OCT-07
1 H7-10-15:10:11: ALL_RULE_SETS 15-OCT-07
1 H7-10-15:10:11: AQ$_SUBSCRIBER_TABLE_PRIMARY 15-OCT-07
1 H7-10-15:10:28: sqlj/util/Parselet 15-OCT-07
1 H7-10-15:10:28: sun/swing/FilePane$3 15-OCT-07
1 H7-10-15:10:28: sun/text/IntHashtable 15-OCT-07
1 H7-10-15:10:28: sun/tools/asm/CatchData 15-OCT-07
1 H7-10-15:10:30: javax/swing/text/html/HTML 15-OCT-07
1 H7-10-15:10:30: javax/swing/tree/RowMapper 15-OCT-07
1 H7-10-15:10:30: javax/swing/undo/CompoundEdit 15-OCT-07
1 H7-10-15:10:30: oracle/aurora/vm/IUHandle 15-OCT-07
1 H7-10-15:10:44: ST_GEOMCOLLECTION 15-OCT-07
1 H7-10-15:10:45: COORD_REF_SYSTEM_PRIM 15-OCT-07
1 H7-10-15:10:45: SDO_ADMIN 15-OCT-07
1 H7-10-15:10:45: SDO_COORD_OPS 15-OCT-07
1 I7-10-15:10:12: ODCIINDEXINFODUMP 15-OCT-07
1 I7-10-15:10:12: ODCIOBJECTLIST 15-OCT-07
1 I7-10-15:10:12: REPCAT$_SITES_NEW_FK2_IDX 15-OCT-07
1 I7-10-15:10:12: REPCAT$_SITE_OBJECTS 15-OCT-07
1 I7-10-15:10:29: /5f1659ed_RealType 15-OCT-07
1 I7-10-15:10:29: /60f1dbe_XMLEntityManagerEntit 15-OCT-07
1 I7-10-15:10:29: /61027b31_VoidType 15-OCT-07
1 I7-10-15:10:29: /61734d21_ClassUnloadEvent 15-OCT-07
1 I7-10-15:10:31: /564607d_HTMLAnchorElement 15-OCT-07
1 I7-10-15:10:31: /5aaea7ab_OraCustomizerErrorsT 15-OCT-07
1 I7-10-15:10:31: /5ae161b6_DefaultProxySelector 15-OCT-07
1 I7-10-15:10:31: /5b3f9016_HtmlDoclet 15-OCT-07
1 I7-10-15:10:55: ORDMD_TNPC_LIBS 15-OCT-07
1 I7-10-15:10:55: R_TABLE 15-OCT-07
1 I7-10-15:10:55: SDO_WFS_LOCK 15-OCT-07
1 I7-10-15:10:55: SDO_WS_CONF_PART_PK 15-OCT-07
1 J7-10-15:10:17: DBMS_SUMVDM 15-OCT-07
1 J7-10-15:10:17: WRI$_ADV_OBJSPACE_TREND_T 15-OCT-07
1 J7-10-15:10:20: DBMSOBJG 15-OCT-07
1 J7-10-15:10:20: DBMS_FEATURE_XDB 15-OCT-07
1 J7-10-15:10:29: /b93aef44_JDWPStackFrameSetVal 15-OCT-07
1 J7-10-15:10:29: /b960f153_LdapSchemaCtx 15-OCT-07
1 J7-10-15:10:29: /ba7fed63_POP 15-OCT-07
1 J7-10-15:10:29: /baa7344b_XMLSchemaDescription 15-OCT-07
1 J7-10-15:10:31: /fab81813_MetalComboBoxUI 15-OCT-07
1 J7-10-15:10:31: /fc374dcc_ProviderList3 15-OCT-07
1 J7-10-15:10:31: /fc8d8741_InvalidRoleValueExce 15-OCT-07
1 J7-10-15:10:31: /fc965149_MultiInternalFrameUI 15-OCT-07
1 J7-10-15:10:57: PARAM_VALUES_TAB 15-OCT-07
1 J7-10-15:10:57: PK_MGMT_HTTP_SESS_CBS 15-OCT-07
1 J7-10-15:10:57: PK_MGMT_UPD_PROP_DATA 15-OCT-07
1 J7-10-15:10:57: SMP_EMD_TGT_OBJECT 15-OCT-07
1 K7-10-15:10:27: sun/nio/cs/ext/MS874$Encoder 15-OCT-07
1 K7-10-15:10:27: sun/nio/cs/ext/MS950$Encoder 15-OCT-07
1 K7-10-15:10:27: sun/nio/cs/ext/MacRoman 15-OCT-07
1 K7-10-15:10:27: sun/rmi/rmic/iiop/ArrayType 15-OCT-07
1 K7-10-15:10:29: /fe43c70e_InvalidName 15-OCT-07
1 K7-10-15:10:29: /fec01df_SecretKeyFactorySpi 15-OCT-07
1 K7-10-15:10:29: /fed9699c_ParserTable10 15-OCT-07
1 K7-10-15:10:29: /fed9c0b5_XSParticleDecl 15-OCT-07
1 K7-10-15:10:31: sun/io/CharToByteISO8859_9 15-OCT-07
1 K7-10-15:10:31: sun/io/CharToByteMacCyrillic 15-OCT-07
1 K7-10-15:10:31: sun/io/CharToByteUTF16 15-OCT-07
1 K7-10-15:10:31: sun/misc/UCDecoder 15-OCT-07
1 K7-10-15:11:07: WWV_MIG_ACC_GROUPS 15-OCT-07
1 K7-10-15:11:07: WWV_MIG_ACC_PAGE_IDX2 15-OCT-07
1 K7-10-15:11:07: WWV_MIG_ACC_RPT_PERM_IDX1 15-OCT-07
1 K7-10-15:11:07: WWV_MIG_PROJ_UK1 15-OCT-07
1 L7-10-15:10:28: /66b8ddfe_BasicSpinnerUI1 15-OCT-07
1 L7-10-15:10:28: /67033829_AMD64AbstractMIR2LIR 15-OCT-07
1 L7-10-15:10:28: /698a2ef7_MetalFileChooserUIDi 15-OCT-07
1 L7-10-15:10:28: /69c0cfae_PolicyTableProxyTabl 15-OCT-07
1 L7-10-15:10:30: /6cbfe1d2_ServiceDialogCopiesP 15-OCT-07
1 L7-10-15:10:30: /6ce8cb5e_CertPathBuilder1 15-OCT-07
1 L7-10-15:10:30: /70bd389c_SwingUtilities21 15-OCT-07
1 L7-10-15:10:30: /715aeac3_X509CRLSelector 15-OCT-07
1 L7-10-15:10:34: /f37bb8f4_IDRef 15-OCT-07
1 L7-10-15:10:34: /f5eb4ddb_XSLTransformerConsta 15-OCT-07
1 L7-10-15:10:34: /f7fede6b_PathDescendantAxisIt 15-OCT-07
1 L7-10-15:10:34: /f8f81f1b_Unordered 15-OCT-07
1 M7-10-15:10:09: GV_$FS_FAILOVER_HISTOGRAM 15-OCT-07
1 M7-10-15:10:09: GV_$HS_AGENT 15-OCT-07
1 M7-10-15:10:09: GV_$METRICGROUP 15-OCT-07
1 M7-10-15:10:09: GV_$PX_SESSTAT 15-OCT-07
1 M7-10-15:10:28: /c632ee28_CEStreamExhausted 15-OCT-07
1 M7-10-15:10:28: /c63add20_JDBCProfile 15-OCT-07
1 M7-10-15:10:28: /c6637ff6_ElemJSClassJSMemberI 15-OCT-07
1 M7-10-15:10:28: /c717867b_PowerPCAbstractMIR2L 15-OCT-07
1 M7-10-15:10:30: /ca585409_NativeMethodAccessor 15-OCT-07
1 M7-10-15:10:30: /cc0866a8_IDLTypeHelper 15-OCT-07
1 M7-10-15:10:30: /cc167f76_CancelRequestMessage 15-OCT-07
1 M7-10-15:10:30: /cdc4a850_DuplicateName 15-OCT-07
1 M7-10-15:10:41: /1849964f_Extended 15-OCT-07
1 M7-10-15:10:41: /1adecf21_MlibWarpGridTableOpI 15-OCT-07
1 M7-10-15:10:41: /1de0ffbd_comsunmediajaicodeci 15-OCT-07
1 M7-10-15:10:41: /2294c052_DivideIntoConstOpIma 15-OCT-07
1 N7-10-15:10:10: EXU8GRS 15-OCT-07
1 N7-10-15:10:10: EXU8ICPLSQL 15-OCT-07
1 N7-10-15:10:10: EXU8LOBU 15-OCT-07
1 N7-10-15:10:10: EXU8PSTU 15-OCT-07
1 N7-10-15:10:28: oracle/aurora/util/Statistics 15-OCT-07
1 N7-10-15:10:28: oracle/i18n/text/OraBoot 15-OCT-07
1 N7-10-15:10:28: oracle/i18n/text/OraLinguistic 15-OCT-07
1 N7-10-15:10:28: oracle/jdbc/driver/BufferCache 15-OCT-07
1 N7-10-15:10:30: javax/imageio/spi/IIORegistry 15-OCT-07
1 N7-10-15:10:30: javax/management/JMException 15-OCT-07
1 N7-10-15:10:30: javax/net/SocketFactory 15-OCT-07
1 N7-10-15:10:30: javax/print/AttributeException 15-OCT-07
1 N7-10-15:10:42: /96aa6169_DICOMImage 15-OCT-07
1 N7-10-15:10:42: /97b6ceb_MlibMinFilterOpImage 15-OCT-07
1 N7-10-15:10:42: /9c76c79f_PICTImageEncoder 15-OCT-07
1 N7-10-15:10:42: /a042a1b6_IIPResolutionOpImage 15-OCT-07
1 O7-10-15:10:12: DBMS_ADDM 15-OCT-07
1 O7-10-15:10:12: DBMS_APPLY_POSITION 15-OCT-07
1 O7-10-15:10:12: DBMS_AQ_EXP_QUEUES 15-OCT-07
1 O7-10-15:10:12: DBMS_ASSERT 15-OCT-07
1 O7-10-15:10:29: /3babcd25_MessageToken_v2Messa 15-OCT-07
1 O7-10-15:10:29: /3be9e866_ThreadDeathRequest 15-OCT-07
1 O7-10-15:10:29: /3c8aecb7_ForwardRequestHelper 15-OCT-07
1 O7-10-15:10:29: /3cbd32f6_AiffFileFormat 15-OCT-07
1 O7-10-15:10:31: /46ee7ad6_AnySeqHelper 15-OCT-07
1 O7-10-15:10:31: /476fa8b6_DefaultLoaderReposit 15-OCT-07
1 O7-10-15:10:31: /48429d3_SerProfileToClassErro 15-OCT-07
1 O7-10-15:10:31: /4a07f57a_Messages_japropertie 15-OCT-07
1 O7-10-15:10:49: CWM2_OLAP_TABLE_MAP 15-OCT-07
1 O7-10-15:10:49: CWM_OLAP_LEVEL 15-OCT-07
1 O7-10-15:10:49: DBA$OLAP2ULEVEL_KEY_COL_USES 15-OCT-07
1 O7-10-15:10:49: DBA$OLAP2_AGGREGATION_USES 15-OCT-07
1 P7-10-15:10:15: USER_ADVISOR_SQLW_TABVOL 15-OCT-07
1 P7-10-15:10:15: USER_FLASHBACK_ARCHIVE_TABLES 15-OCT-07
1 P7-10-15:10:15: USER_SQLSET 15-OCT-07
1 P7-10-15:10:15: V$LOGSTDBY 15-OCT-07
1 P7-10-15:10:29: /8f744aff_ProxyInputStream 15-OCT-07
1 P7-10-15:10:29: /903ee3d1_IDLID 15-OCT-07
1 P7-10-15:10:29: /9053dfe4_ClientRequestInfo 15-OCT-07
1 P7-10-15:10:29: /912c89ad_DynSequenceImpl 15-OCT-07
1 P7-10-15:10:31: /e13e8896_Messages_iw 15-OCT-07
1 P7-10-15:10:31: /e1cee4a_TrustManagerFactory 15-OCT-07
1 P7-10-15:10:31: /e3d5b152_DSAKeyPairGenerator 15-OCT-07
1 P7-10-15:10:31: /e407dde1_ToolEnv 15-OCT-07
1 P7-10-15:10:57: MGMT_JOB_EXEC_SUMM_IDX02 15-OCT-07
1 P7-10-15:10:57: MGMT_JOB_LOCK_INFO 15-OCT-07
1 P7-10-15:10:57: MGMT_JOB_SHORT_STR_ARR_TABLE 15-OCT-07
1 P7-10-15:10:57: MGMT_JOB_TARGET_LIST 15-OCT-07
1 Q7-10-15:10:27: sun/io/CharToByteSJIS 15-OCT-07
1 Q7-10-15:10:27: sun/misc/FIFOQueueEnumerator 15-OCT-07
1 Q7-10-15:10:27: sun/misc/Perf$1 15-OCT-07
1 Q7-10-15:10:27: sun/misc/TimerThread 15-OCT-07
1 Q7-10-15:10:29: /e8ac092_ClassLoaderReference 15-OCT-07
1 Q7-10-15:10:29: /ea01ee55_ResolveResolveError 15-OCT-07
1 Q7-10-15:10:29: /ea146b02_HierMemDirCtxHierCon 15-OCT-07
1 Q7-10-15:10:29: /eab14762_VersionHelper123 15-OCT-07
1 Q7-10-15:10:31: sqlj/mesg/OptionDescText_nl 15-OCT-07
1 Q7-10-15:10:31: sqlj/mesg/SyntaxErrorsText_iw 15-OCT-07
1 Q7-10-15:10:31: sqlj/mesg/SyntaxErrorsText_tr 15-OCT-07
1 Q7-10-15:10:31: sqlj/tools/Timing 15-OCT-07
1 Q7-10-15:11:07: WWV_BIU_STEP_ITEM_HELP 15-OCT-07
1 Q7-10-15:11:07: WWV_FLOW_APP_BUILD_PREF 15-OCT-07
1 Q7-10-15:11:07: WWV_FLOW_CHARSETS 15-OCT-07
1 Q7-10-15:11:07: WWV_FLOW_COMPANIES_IDX1 15-OCT-07
1 R7-10-15:10:28: /4c435694_T2SResultSetAccessor 15-OCT-07
1 R7-10-15:10:28: /4ce5062_RejectedExecutionExce 15-OCT-07
1 R7-10-15:10:28: /4cf8101d_PermissionImpl 15-OCT-07
1 R7-10-15:10:28: /4dab6ffd_PositiveExpression 15-OCT-07
1 R7-10-15:10:30: /3b8ba89b_GLXRemoteOffScreenIm 15-OCT-07
1 R7-10-15:10:30: /3beabd3_MBeanServerDelegateIm 15-OCT-07
1 R7-10-15:10:30: /3c96b43e_SunDragSourceContext 15-OCT-07
1 R7-10-15:10:30: /3cfc2731_ComponentAccessibleA 15-OCT-07
1 R7-10-15:10:34: /1f65c6ab_XSLJDWPEventValue4Co 15-OCT-07
1 R7-10-15:10:34: /20f12cf_OracleXMLSQLException 15-OCT-07
1 R7-10-15:10:34: /39f3398a_InfosetWriter 15-OCT-07
1 R7-10-15:10:34: /3bc6dd8d_ExprSequenceExprSequ 15-OCT-07
1 S7-10-15:10:09: COL_USAGE$ 15-OCT-07
1 S7-10-15:10:09: DBA_LOG_GROUP_COLUMNS 15-OCT-07
1 S7-10-15:10:09: DBA_SEQUENCES 15-OCT-07
1 S7-10-15:10:09: DBA_TABLES 15-OCT-07
1 S7-10-15:10:28: /ad0cf592_BasicInternalFrameUI 15-OCT-07
1 S7-10-15:10:28: /ad614359_MetalFileChooserUIFi 15-OCT-07
1 S7-10-15:10:28: /adf105a6_OracleFailoverWorker 15-OCT-07
1 S7-10-15:10:28: /ae994ed1_SimplerBitPattern 15-OCT-07
1 S7-10-15:10:30: /a062817_HashDocAttributeSet 15-OCT-07
1 S7-10-15:10:30: /a327d933_BasicGraphicsUtils 15-OCT-07
1 S7-10-15:10:30: /a347b0ca_JTextComponentAccess 15-OCT-07
1 S7-10-15:10:30: /a529818c_VMManagementImpl 15-OCT-07
1 S7-10-15:10:37: SYS_C003709 15-OCT-07
1 S7-10-15:10:37: UNDER_PATH 15-OCT-07
1 S7-10-15:10:37: XDB$TTSET 15-OCT-07
1 S7-10-15:10:37: XMLBINARYOUTPUTSTREAM 15-OCT-07
1 T7-10-15:10:10: ALL_EDITIONING_VIEWS_AE 15-OCT-07
1 T7-10-15:10:10: ALL_ERRORS 15-OCT-07
1 T7-10-15:10:10: ALL_INDEXTYPES 15-OCT-07
1 T7-10-15:10:10: ALL_LIBRARIES 15-OCT-07
1 T7-10-15:10:28: /ff170cf8_BasicDesktopPaneUI1 15-OCT-07
1 T7-10-15:10:28: /ff337f46_OraLocaleInfoCachedC 15-OCT-07
1 T7-10-15:10:28: com/sun/jmx/snmp/SnmpIpAddress 15-OCT-07
1 T7-10-15:10:28: com/sun/jmx/snmp/SnmpMessage 15-OCT-07
1 T7-10-15:10:30: java/io/ObjectStreamField 15-OCT-07
1 T7-10-15:10:30: java/lang/Character 15-OCT-07
1 T7-10-15:10:30: java/lang/Class$2 15-OCT-07
1 T7-10-15:10:30: java/lang/InterruptedException 15-OCT-07
1 T7-10-15:10:42: /27772a5f_GIFImageDecoder 15-OCT-07
1 T7-10-15:10:42: /285d736f_RegistryFileParser 15-OCT-07
1 T7-10-15:10:42: /2db36ed3_ScaleGeneralOpImage 15-OCT-07
1 T7-10-15:10:42: /2fdec9e2_ConjugateDescriptor 15-OCT-07
1 U7-10-15:10:11: USER_REFRESH_CHILDREN 15-OCT-07
1 U7-10-15:10:11: USER_REPCONFLICT 15-OCT-07
1 U7-10-15:10:11: USER_REPPRIORITY 15-OCT-07
1 U7-10-15:10:11: USER_REPPROP 15-OCT-07
1 U7-10-15:10:29: /26bc7544_MultiDOMAxisIterator 15-OCT-07
1 U7-10-15:10:29: /27dd1f96_XSLMessages 15-OCT-07
1 U7-10-15:10:29: /2843aa40_SecuritySupport125 15-OCT-07
1 U7-10-15:10:29: /2910abf2_TreeReturn 15-OCT-07
1 U7-10-15:10:31: /110b84a_CharConvRepackage 15-OCT-07
1 U7-10-15:10:31: /131de077_ShellServer 15-OCT-07
1 U7-10-15:10:31: /13862faa_TranslatorErrorsText 15-OCT-07
1 U7-10-15:10:31: /190cf850_NTLoginModule 15-OCT-07
1 U7-10-15:10:49: /85fdcbbc_ErrorClassEnum 15-OCT-07
1 U7-10-15:10:49: /9c15feb7_AttributeProjection 15-OCT-07
1 U7-10-15:10:49: /b4e6393_ExceptionBundle_ca 15-OCT-07
1 U7-10-15:10:49: /c6852306_ExternalSourceExpres 15-OCT-07
1 V7-10-15:10:14: SYS_YOID0000009188$ 15-OCT-07
1 V7-10-15:10:14: SYS_YOID0000009240$ 15-OCT-07
1 V7-10-15:10:15: ALL_CAPTURE 15-OCT-07
1 V7-10-15:10:15: DATAPUMP_DDL_TRANSFORM_PARAMS 15-OCT-07
1 V7-10-15:10:29: /7e8f9ad2_VersionHelper125 15-OCT-07
1 V7-10-15:10:29: /7e90ecfe_ValidationState 15-OCT-07
1 V7-10-15:10:29: /7fdd8144_TABLESWITCH 15-OCT-07
1 V7-10-15:10:29: /802be2f4_XSImplementation 15-OCT-07
1 V7-10-15:10:31: /9f659358_ActivationActivation 15-OCT-07
1 V7-10-15:10:31: /a1a6a544_NumberUpSupported 15-OCT-07
1 V7-10-15:10:31: /a248592e_OpaqueCopyAnyToArgb 15-OCT-07
1 V7-10-15:10:31: /a405256b_MultiTextUI 15-OCT-07
1 V7-10-15:10:57: MGMT_AGGR_MEMBERS 15-OCT-07
1 V7-10-15:10:57: MGMT_ARU_FAM_PRD_PK 15-OCT-07
1 V7-10-15:10:57: MGMT_BCN_ARRAY 15-OCT-07
1 V7-10-15:10:57: MGMT_BCN_STEP_DEFN 15-OCT-07
1 W7-10-15:10:26: WM$UDTRIG_INFO 15-OCT-07
1 W7-10-15:10:26: WM_CONTAINS 15-OCT-07
1 W7-10-15:10:27: /1352fb09_ConfirmRemovePolicyE 15-OCT-07
1 W7-10-15:10:27: /180c0e64_OCSPRequest1 15-OCT-07
1 W7-10-15:10:29: /dc077e2_Constant 15-OCT-07
1 W7-10-15:10:29: /dcbe5a83_ExceptionMapper 15-OCT-07
1 W7-10-15:10:29: /dd0ef30e_Stub 15-OCT-07
1 W7-10-15:10:29: /dd512c97_ORBImpl5 15-OCT-07
1 W7-10-15:10:31: oracle/i18n/data/lx200ab.glb 15-OCT-07
1 W7-10-15:10:31: oracle/i18n/data/lx200c5.glb 15-OCT-07
1 W7-10-15:10:31: oracle/i18n/data/lx200f1.glb 15-OCT-07
1 W7-10-15:10:31: oracle/i18n/data/lx20147.glb 15-OCT-07
1 W7-10-15:11:00: MGMT_OSM_DISK_GROUP_ECM_PK 15-OCT-07
1 W7-10-15:11:00: MGMT_PAF_PROCEDURES_UNIQ 15-OCT-07
1 W7-10-15:11:00: MGMT_RT_METRICS_RAW_TR 15-OCT-07
1 W7-10-15:11:00: MGMT_TARGETS 15-OCT-07
1 X7-10-15:10:28: /38ae8be9_JDMAclItem 15-OCT-07
1 X7-10-15:10:28: /39366e3e_SqljInterfaceTransfo 15-OCT-07
1 X7-10-15:10:28: /39d6de9e_SmartGridLayout 15-OCT-07
1 X7-10-15:10:28: /3a26ce89_DigCode 15-OCT-07
1 X7-10-15:10:30: /1bc18f90_ContainerAccessibleA 15-OCT-07
1 X7-10-15:10:30: /1d493451_X509EncodedKeySpec 15-OCT-07
1 X7-10-15:10:30: /1e1073ec_ObjectAlreadyActive 15-OCT-07
1 X7-10-15:10:30: /1f4a4c54_JSpinnerDefaultEdito 15-OCT-07
1 X7-10-15:10:33: /74fe3eaa_XMLPrintDriver 15-OCT-07
1 X7-10-15:10:33: /7aa29152_XSLOutputCharacter 15-OCT-07
1 X7-10-15:10:33: /861dc517_MessageChangedListen 15-OCT-07
1 X7-10-15:10:33: /8da70af4_StaticBaseURI 15-OCT-07
1 X7-11-08:06:03: XDB_DOM_HELPER 08-NOV-07
1 X7-11-08:23:02: FORMAT_STRING 08-NOV-07
1 X7-12-18:08:57: BUS_STATE_HIST 18-DEC-07
1 X7-12-20:11:19: EMP_DETAILS_OBJ 20-DEC-07
1 Y7-10-15:10:28: /7b391ae2_DBConversionUnicodeS 15-OCT-07
1 Y7-10-15:10:28: /7c271cf6_UTF8ValidationFilter 15-OCT-07
1 Y7-10-15:10:28: /7e5a944a_RawFmt 15-OCT-07
1 Y7-10-15:10:28: /7eec0634_JvmThreadingMetaImpl 15-OCT-07
1 Y7-10-15:10:30: /8e22730_ProxyGeneratorProxyMe 15-OCT-07
1 Y7-10-15:10:30: /8f039248_JSeparatorAccessible 15-OCT-07
1 Y7-10-15:10:30: /8f0f5f28_ServiceDialogAppeara 15-OCT-07
1 Y7-10-15:10:30: /8fd57f4d_ComponentViewInvalid 15-OCT-07
1 Y7-10-15:10:35: DRISGP 15-OCT-07
1 Y7-10-15:10:35: DRX$IXV_KEY 15-OCT-07
1 Y7-10-15:10:35: EXF$ATTRLIST 15-OCT-07
1 Y7-10-15:10:35: EXF$CHECK_PRIVILEGE 15-OCT-07
399 rows selected.
Elapsed: 00:00:00.97
Execution Plan
----------------------------------------------------------
Plan hash value: 2484335325
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68309 | 6937K| 58561 (1)| 00:11:43 |
|* 1 | VIEW | | 68309 | 6937K| 58561 (1)| 00:11:43 |
| 2 | WINDOW NOSORT | | 68309 | 3535K| 58561 (1)| 00:11:43 |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 68309 | 3535K| 58561 (1)| 00:11:43 |
|* 4 | INDEX FULL SCAN | IDX_USE_ME | 68309 | | 530 (1)| 00:00:07 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MOD("RNUM",684)<=3)
4 - filter("FNAME" IS NOT NULL)
SCOTT@orcl_11g>
[edit: I removed the portion of my post that belonged in another thread]
[Updated on: Sun, 30 March 2008 14:42] Report message to a moderator |
|
|
|
| Re: Bucketing Query Performance [message #309940 is a reply to message #309928] |
Sun, 30 March 2008 04:54 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Hey Barbara, you're mixing the threads and making my brain hurt.
But it's good to know the syntax was either right, or close enough so you could make it work. The Explain Plan doesn't tell the full story though because the CURSOR() is not shown. You would need to TKPROF it.
For this thread, I'd be interested to know how @coleing's suggestion of tacking DOB onto the index compares performance-wise.
Ross Leishman
|
|
|
|
| Re: Bucketing Query Performance [message #309952 is a reply to message #309865] |
Sun, 30 March 2008 09:46 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Coleing,
Adding the DOB to the index indeed speeds up the query 2x.
However, I cannot go that route, because I am restricted to indices that already exist. The DBA will only give me permissions to create an index for the TEST orauser.
This is something I neglected to mention in my original problem specification.
Thanks,
George
| "coleing wrote on Sat, 29 March 2008 23:43" | You could add DOB to the index to eliminate the table access. That is all I can think of.
|
[Updated on: Sun, 30 March 2008 10:19] Report message to a moderator |
|
|
|
| Re: Bucketing Query Performance [message #309957 is a reply to message #309928] |
Sun, 30 March 2008 10:38 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Hey Barbara,
Now you are getting the same plan as I am for the query:
SELECT i,FNAME,SNAME,DOB
FROM (
SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
--LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
FNAME,SNAME,DOB
FROM CUSTOMER
WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
ORDER BY FNAME,SNAME,ID
)
WHERE rnum = :X
Please note that the query execution time increases linearly with :X
This is to be expected because each time the query is restarted , the query begins to scan all the records from the beginning and has further and further to scan as :X increases.
So if we did a ridiculous query like this:
SELECT i,FNAME,SNAME,DOB
FROM (
SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
--LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
FNAME,SNAME,DOB
FROM CUSTOMER
WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
ORDER BY FNAME,SNAME,ID
)
WHERE rnum = 1
UNION ALL
SELECT i,FNAME,SNAME,DOB
FROM (
SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
--LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
FNAME,SNAME,DOB
FROM CUSTOMER
WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
ORDER BY FNAME,SNAME,ID
)
WHERE rnum = 10000
UNION ALL
SELECT i,FNAME,SNAME,DOB
FROM (
SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
--LEAD(FNAME, 10000, 0) OVER (ORDER BY FNAME,SNAME,ID) inorderjump,
FNAME,SNAME,DOB
FROM CUSTOMER
WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
ORDER BY FNAME,SNAME,ID
)
WHERE rnum = 20000
-- UNION ALL ...97 more times
The following query eliminates 99 of these UNION ALLs, but suffers from the same arithmetical progression problem:
SELECT i,FNAME,SNAME,DOB
FROM
(
SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
row_number() OVER (ORDER BY FNAME,SNAME,ID) rnum,
FNAME, SNAME, DOB
FROM CUSTOMER
WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
ORDER BY FNAME,SNAME,ID
) a
JOIN
(
SELECT 1+rownum*10000 AS rnum
FROM DUAL
CONNECT BY LEVEL <= 99
) b
ON a.rnum=b.rnum
BUT what if we did not start from the beginning each time, but from the row where the previous query finished ?
This would mean adding a condition on FNAME, SMAME, ID to the inner WHERE clause or maybe CONNECT BY PRIOR...
Thus, in the subsequent query, an index could be used to find that new starting row almost instantly, and WINDOW NOSORT STOPKEY would jump forward from that row.
Do you think it's doable ?
Regards,
George
[Updated on: Sun, 30 March 2008 14:20] Report message to a moderator |
|
|
|
|
|
| Re: Bucketing Query Performance [message #309976 is a reply to message #309967] |
Sun, 30 March 2008 18:13 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It's the "jumping forward" that will get you. If you want to jump forward a predetermined number of rows, you have to count those rows, which means reading them. Damage done!
As for not being able to create an index: Congratulations, your work is done. A DBA that refuses to create an index that demontrably imrpoves performance of a critical (is this critical?) query ASSUMES RESPONSIBILITY FOR THE PROBLEM THEMSELVES.
Ross Leishman
|
|
|
|
| Re: Bucketing Query Performance [message #310575 is a reply to message #309976] |
Tue, 01 April 2008 13:09 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
| Quote: | It's the "jumping forward" that will get you
|
Yes, I was just hoping that it would be faster than doing the 2sec JOIN which starts from the beginning each time.
The most frustrating thing for me as a low-level programmer is that I can see that Oracle has the means of doing such a jump more efficiently than going through all the rows in between.
When one looks at the index dump available at:
http://www.orafaq.com/node/1403]
----- begin tree dump
branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)
leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)
leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)
leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)
leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)
leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)
...
...
leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)
leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)
leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)
leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)
----- end tree dump
.. it can be seen that by knowing the total number of rows and performing a running sum of the nrow values, whole branch/leaf blocks could be skipped without going through their contents. That would be reasonably quick way to "jump ahead".
I am surprised that Oracle developers are not using this block skipping technique in some of the built-in functions, e.g.: LEAD, LAG.
Regards,
George
P.S.
I am far from defending my overzealous DBA, but the sorting order (e.g.: FNAME, SNAME, ID) depends on the users of the system, and if he let a new index be created instead of an existing one being reused, soon the users would create many redundant indices. So I understand his reasons.
[Updated on: Tue, 01 April 2008 13:10] Report message to a moderator |
|
|
|
| Re: Bucketing Query Performance [message #310587 is a reply to message #309777] |
Tue, 01 April 2008 13:57 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
Yes indeed. Index skipping would be great (Even though Oracle can do index skipping for non-leading index keys - see index_skip_scan), so in theory you are right - I think.
However, just a final attempt at a solution for you.
can you create a fast refreshing materialized view, with the first, second and third max value for each FNAME, SNAME, ID.
Then reading from that will just be a constant 250 row table!
Although it would mean you need an mview log on CUSTOMER to maintain the updates.
Just a thought though.
Incidentally, adding DOB to the existing index, rather than creating a new one shouldnt cause any harm to anyone else, its just an extra column.
|
|
|
|
| Re: Bucketing Query Performance [message #310629 is a reply to message #310575] |
Tue, 01 April 2008 17:34 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| verpies wrote on Wed, 02 April 2008 04:09 | When one looks at the index dump available at:
http://www.orafaq.com/node/1403]
----- begin tree dump
branch: 0x68066c8 109078216 (0: nrow: 325, level: 1)
leaf: 0x68066c9 109078217 (-1: nrow: 694 rrow: 694)
leaf: 0x68066ca 109078218 (0: nrow: 693 rrow: 693)
leaf: 0x68066cb 109078219 (1: nrow: 693 rrow: 693)
leaf: 0x68066cc 109078220 (2: nrow: 693 rrow: 693)
leaf: 0x68066cd 109078221 (3: nrow: 693 rrow: 693)
...
...
leaf: 0x68069cf 109078991 (320: nrow: 763 rrow: 763)
leaf: 0x68069d0 109078992 (321: nrow: 761 rrow: 761)
leaf: 0x68069d1 109078993 (322: nrow: 798 rrow: 798)
leaf: 0x68069d2 109078994 (323: nrow: 807 rrow: 807)
----- end tree dump
.. it can be seen that by knowing the total number of rows and performing a running sum of the nrow values, whole branch/leaf blocks could be skipped without going through their contents. That would be reasonably quick way to "jump ahead".
|
This is exactly what I was talking about before. These row counts in the index are not stored in some easily and cheaply accessible location; they are stored in the index leaf blocks themselves.
If you want to jump forward (say) 1000 rows) then you must read the leaf block from disk, load it into the buffer cache, read the header, then discard it if the row count is less than the jump-forward number.
The expensive part of this operation is reading from disk into the buffer cache. Once you have committed to do that, any hope of serious tuning is lost.
Where you ARE losing out is that it appears Oracle is reading the table to get the DOB even for discarded rows. As discussed, this is avoidable.
I hear what you're saying about the DBA, but by the look of your posts here and on AskTom, you have spent at least $20,000 of your client/employer's money trying to make this thing perform to a required benchmark. Now unless you have gone rogue and are acting without the support and knowledge of your employer, I call that a critical business requirement.
These are the things that DBAs are paid to fix. The problem (with apologies to the DBA's out there) is that most DBA's are -in part - PROCESS workers, whereas programmers are PROJECT workers. It is process workers job to maintain the status-quo and protect their company from the negative effects of change. It is the project-workers job to drag the company kicking-and-screaming into change for the greater good despite the inevitable negative effects.
DBAs have a dual-role: they have to maintain the status-quo and keep the database performance and availability high. But they also have to support developers, which means supporting change. This makes them conflicted, and process often wins out over project.
[/rant]
Anyway, you can mitigate the DOB lookup for discarded rows with something that I usually recommend against (see http://www.orafaq.com/node/1981).
SELECT i,FNAME,SNAME,(select DOB from customer where rowid = row_id) AS DOB
FROM (
SELECT /*+ INDEX_ASC(CUSTOMER IDX_USE_ME)*/
row_number() OVER (PARTITION BY FNAME,SNAME ORDER BY FNAME,SNAME,ID) i,
FNAME,
SNAME,
ROWID AS row_id,
rownum rnum
FROM CUSTOMER
WHERE FNAME IS NOT NULL AND SNAME IS NOT NULL
ORDER BY FNAME,SNAME,ID
)
WHERE mod(rnum,:RPB)<3;
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:39:18 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
