Interpreting 10053 trace output

Advertisement

Hi,
I am trying to analyze one sql performance issue. In 10053 trace of the SQL, one analyzed index(IDX_OCI_INSTRMNT_ID) is reflected as Unanalyzed:
BASE STATISTICAL INFORMATION
Table Stats::
  Table: OCI  Alias: OCI  (Using composite stats)
    #Rows: 119379620  #Blks:  4412959  AvgRowLen:  205.00
Index Stats::
  Index: IDX_OCI_BANK_BR_CODE  Col#: 15 16
    LVLS: 3  #LB: 578655  #DK: 20668  LB/K: 27.00  DB/K: 4191.00  CLUF: 86632520.00
  Index: IDX_OCI_INSTRMNT_AMT_ZDATE  Col#: 12 2
    LVLS: 3  #LB: 601715  #DK: 4917119  LB/K: 1.00  DB/K: 21.00  CLUF: 107389165.00
  Index: IDX_OCI_INSTRMNT_ID  Col#: 9
    PARTITION [6]    LVLS: 3  #LB: 55130  #DK: 24310  LB/K: 2.00  DB/K: 375.00  CLUF: 9132500.00
    (NOT ANALYZED)===========================================> The index is locally partitioned and analyzed.
    LVLS: 3  #LB: 55130  #DK: 24310  LB/K: 2.00  DB/K: 375.00  CLUF: 9132500.00
  Index: IDX_OUT_CLG_INSTRMNT_TABLE  Col#: 1 2 3 4 5 58
    USING COMPOSITE STATS
    LVLS: 3  #LB: 977360  #DK: 118952050  LB/K: 1.00  DB/K: 1.00  CLUF: 95676615.00
SINGLE TABLE ACCESS PATH
  BEGIN Single Table Cardinality Estimation
  Column (#9): INSTRMNT_ID(VARCHAR2)
    AvgLen: 17.00 NDV: 1081566 Nulls: 0 Density: 9.2459e-07
  Column (#19): STATUS_FLG(CHARACTER)
    AvgLen: 2.00 NDV: 5 Nulls: 2459035 Density: 0.2
  Column (#12): INSTRMNT_AMT(NUMBER)
    AvgLen: 5.00 NDV: 1061850 Nulls: 0 Density: 9.4175e-07 Min: 0 Max: 4000020025000
  Table: OCI  Alias: OCI    
    Card: Original: 119379620  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00
  END   Single Table Cardinality Estimation
  Access Path: TableScan
    Cost:  791920.71  Resp: 791920.71  Degree: 0
      Cost_io: 716556.00  Cost_cpu: 128124159092
      Resp_io: 716556.00  Resp_cpu: 128124159092
  Access Path: index (RangeScan)
    Index: IDX_OCI_INSTRMNT_AMT_ZDATE
    resc_io: 106.00  resc_cpu: 868831
    ix_sel: 9.4401e-07  ix_sel_with_filters: 9.4401e-07
    Cost: 21.30  Resp: 21.30  Degree: 1
  Access Path: index (AllEqRange)
    Index: IDX_OCI_INSTRMNT_ID
    resc_io: 114.00  resc_cpu: 934714
    ix_sel: 9.2459e-07  ix_sel_with_filters: 9.2459e-07
    Cost: 22.91  Resp: 22.91  Degree: 1
****** trying bitmap/domain indexes ******
  Access Path: index (AllEqRange)
    Index: IDX_OCI_INSTRMNT_ID
    resc_io: 4.00  resc_cpu: 50686
    ix_sel: 9.2459e-07  ix_sel_with_filters: 9.2459e-07
    Cost: 1.01  Resp: 1.01  Degree: 0
  Access Path: index (IndexOnly)
    Index: IDX_OCI_INSTRMNT_AMT_ZDATE
    resc_io: 4.00  resc_cpu: 51086
    ix_sel: 9.4401e-07  ix_sel_with_filters: 9.4401e-07
    Cost: 1.01  Resp: 1.01  Degree: 0
    SORT resource      Sort statistics
      Sort width:        3070 Area size:     1048576 Max Area size:   536862720
      Degree:               1
      Blocks to Sort:       1 Row size:           21 Total Rows:            112
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 1734406
      Total Temp space used: 0
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange  Index: IDX_OCI_INSTRMNT_AMT_ZDATE
         Cost: 21.30  Degree: 1  Resp: 21.30  Card: 0.00  Bytes: 0The query is using 'IDX_OCI_INSTRMNT_AMT_ZDATE' index which is resulting in 542,193.8 gets/exec, whereas if the query uses index 'IDX_OCI_INSTRMNT_ID' the gets will reduce to 1660 per execution.
Since the index 'IDX_OCI_INSTRMNT_ID' is appearing to be not analyzed to CBO, it's not picked by the plan. What could be the reason that 10053 is reflecting the index as not analyzed.
Regards,
S.K.
Miscellaneous Information:
The query:
SELECT /*+ SANTU */ COUNT(1)
FROM      oci
where      oci.INSTRMNT_ID = LPAD( :b2 , :b3 , :b4 )
AND      OCI.STATUS_FLG in ( :b5 , :b6 )
AND       OCI.INSTRMNT_AMT = LPAD( :b7 , :b8 , :b9 )
-------------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
-------------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                     |                           |       |       |    21 |           |       |       |
| 1   |  SORT AGGREGATE                      |                           |     1 |    24 |       |           |       |       |
| 2   |   TABLE ACCESS BY GLOBAL INDEX ROWID | OUT_CLG_INSTRMNT_TABLE    |     1 |    24 |    21 |  00:00:01 | ROW LOCATION| ROW LOCATION|
| 3   |    INDEX RANGE SCAN                  | IDX_OCI_INSTRMNT_AMT_ZDATE|   112 |       |     1 |  00:00:01 |       |       |
-------------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
2 - filter(("OCI"."INSTRMNT_ID"=LPAD(:B2,:B3,:B4) AND INTERNAL_FUNCTION("OCI"."STATUS_FLG")))
3 - access("OCI"."INSTRMNT_AMT"=TO_NUMBER(LPAD(:B7,:B8,:B9)))
INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
IDX_OCI_INSTRMNT_AMT_ZDATE     INSTRMNT_AMT                                 1
IDX_OCI_INSTRMNT_AMT_ZDATE     CLG_ZONE_DATE                                2
IDX_OCI_INSTRMNT_ID            INSTRMNT_ID                                  1
COLUMN_NAME          DATA_TYPE       NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
CLG_ZONE_DATE        DATE                     822          0 .001216545 NONE
INSTRMNT_AMT         NUMBER               1010184          0 9.8992E-07 NONE
INSTRMNT_ID          VARCHAR2             1077892          0 9.2774E-07 NONE

Advertisement

INDEX_NAME                     COM PARTITION_NAME                 SUBPARTITION_COUNT     BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR LAST_ANALY GLO
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART1                                 0          3      134940          25616770 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART10                                0          2        2047            370408 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART11                                0          1          51              8503 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART2                                 0          2       67630          12927440 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART3                                 0          2       57235          10958505 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART4                                 0          2       51985           9934295 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART5                                 0          2       48395           9260565 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART6                                 0          2       50560           9680680 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART7                                 0          2       44935           8600995 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART8                                 0          2       41030           7858735 29-07-2010 YES
IDX_OCI_INSTRMNT_ID            NO  IDXG1_OCI_PART9                                 0          3       73820          14134540 29-07-2010 YES
COM = Composite
GLO = [email protected],
I have already gone through the article by Wolfgang. The index should not be reflected as 'Not analyzed' in 10053 trace files.
BTW..The database version is 10.2.0.4.
Regards,
S.K.

November 30, -0001

Interpreting 10053 trace output

Hi, I am trying to analyze one sql performance issue. In 10053 trace of the SQL, one analyzed index(IDX_OCI_INSTRMNT_ID) is reflected as Unanalyzed: BASE STATISTICAL INFORMATION Table Stats::   Table: OCI  Alias: OCI  (Using composite stats)     #Row

Optimizer parameters different in 10053 trace

Hello, The optimizer settings and the ones reported in the 10053 trace does not match. Is this a known issue ? Version is printed in the code snippet. Here, optimizer_mode is set to ALL_ROWS, but 10053 trace reports this as first_rows_100. Similarly,

How to interpret RFC trace in ST05: Objects column

Dear all, I recorded trace for a call of RFC enabled function module in ABAP NetWeaver from external JAVA app in TA ST05. Here how does it look like: I'm struggling to interpret it at the moment. My main issue is column called Objects. What is meanin

Cobol trace output

Hello, this is probably quite simple to figure out, but I'm not sure what to search for. I have a Win2008 application/batch server, PeopleTools 8.52. My problem is that COBOL processes create two identical output files, one in the Report Repository a

Predicate push down in 10.2.0.3 but not in 11.1.0.7

In the SQL and PL/SQL forum, there was an interesting question on why a predicate isn't pushed down in an 11.1.0.7 database while 10.2.0.3 did push the predicate, here: Same query works OK on 10.2.0.3 and not OK on 11.1.0.7-please help The last post

10053 - no trace file generated

Hi, no 10053 trace file is generated in the diag directory. sql_trace = true trace_enabled = true i set ALTER SESSION SET TRACEFILE_IDENTIFIER = 'TEST'; ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; but there is no trace file

Core dump: dbx stack trace

Can anyone point to a good doco on how to interpret stack trace from Solaris core file? For example, strcasecmp(0xfedf7cd8, 0x47534b53, 0x4b3ee0, 0x619c50, 0xff327234, 0xff33c000), at 0xff2cf390 what do those 6 addresses in brackets represent? Thanks

Enable Trace in Concurrent Programs

Hi All I have enabled the trace option on an Oracle Reports concurrent program. After running this concurrent program where i can see the trace output. Thanks, BinuBinu, Sort the files by last update date, you should be able to find it then. Also, yo

Air application trace

Hi, When i run my air application i am not able to get the trace output. I tryed "whistler","Vizzy".Still no use. And the Air application didnt update the "Application Data\Macromedia\Flash Player\Logs\flashlog.txt" How to ge

Forms Tracing output file issue

will oracle automatically delete forms trace output files(.trc) after the user session ends?Hey, Can you try to run the command without escaping the > character? Like this: os.system("dir c:\\ > c:\\temp\\out.txt") I think that is the only

Trace on air application runtime

Hi, I want to see trace statement when i run my air .exe files. How to do that? Few of them ask to use the ADL,But I am not much aware of that.On my client place some time my air application get crash. Can i able to log the trace files ? Thanks, Siva

10053 lower cost bad plan

Hi all I am wondering that in Metalink document "Case Study: Analyzing 10053 Trace Files" there is a sql that' considered has bad plan with cost 20762 and same sql with NO_INDEX hint considered has good plan with cost 58201. Diffrence is more th

Kodo Trace information

I__m trying to figure out why my Kodo test (query for 1000 documents) is running FASTER than my JDBC test (query for 1000 documents). I__m looking for information on Kodo__s trace output. Given the following trace, it looks as though Kodo is caching

10g r2 Forms  "View Trace Log" from EM

Group, Just setting up a 10g (r2) environment and wanted to see what the new trace output looked like. So I setup EM (enterprise manager) to collect trace logs. Turned it on for a given forms session. This seemed to work ok, but as soon as I try to v

Nested Loop and Driving Table.

In the documentation I read these (/B19306_01/server.102/b14211/optimops.htm#i82080). The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the i

Why the explain plan incorrent?

I have a sql in oracle10.2.03: select * from (select a.* from t_user a, t_message b where b.user_id = a.row_id and a.user_type = 1 order by b.happen_time desc) where rownum <= 5 there are indexes on column a.user_type,b.user_id,b.happen_time, and has

What is Active CodeSource and Policy CodeSource?

I am having trouble creating a policy file with only the permissions needed by my application. Here is the latest issue: The application fails to launch with this error : Caused by: java.security.AccessControlException: access denied (java.util.Prope

Smartforms symbol not found?

Hi experts, I am facing a problem with smartforms, hopefully anyone of you got a solution on this. The call of my Smartforms FM results in a exception 1 --> formatting error. Smartforms_trace says:   MSGV1: GV_PRINT-AMOUNT        symbol GV_PRINT-AMOU

Adding ORDER BY increases parse time dramatically

I have a SQL query involving 10 tables, 2 of which are accessed via separate database links. Running that query with input that results in 0 rows takes less than a second. However, when I add an ORDER BY clause, returning 0 rows now takes over 7 seco

Flash CS3 getResult

I need to return the result of this SQL query to a table on the stage. As you can see, I can trace the output and see it in the debugger, I just cant get over the hurdle of linking the results to something I can render on the stage. Any help would be