Home » RDBMS Server » Performance Tuning » High CPU Consuming Query (Oracle,11.2.0.3, AIX)
High CPU Consuming Query [message #613500] Wed, 07 May 2014 09:40 Go to next message
nmoham
Messages: 8
Registered: July 2011
Junior Member
SELECT SRC.table_name, 
       SRC.partition_name  SRC_PARTITION, 
       DEST.partition_name DEST_PARTITION 
FROM   (SELECT table_name, 
               partition_name 
        FROM   (SELECT PC.table_name, 
                       Max(PC.last_analyzed)                    LAST_ANALYZED, 
                       PC.partition_name, 
                       Row_number() 
                         over ( 
                           PARTITION BY PC.table_name 
                           ORDER BY Max(PC.last_analyzed) DESC) R 
                FROM   user_ind_columns IC 
                       join user_indexes I 
                         ON IC.index_name = I.index_name 
                       join user_part_col_statistics PC 
                         ON IC.table_name = PC.table_name 
                            AND PC.column_name IN ( IC.column_name, 'SAMPLETIME' 
                                                    , 
                                                    'DOWNTIME_STATE' ) 
                WHERE  I.index_type = 'BITMAP' 
                GROUP  BY PC.table_name, 
                          PC.partition_name 
                HAVING SUM(Decode(PC.histogram, 'NONE', 1, 
                                                0)) = 0) 
        WHERE  r = 1) SRC 
       join user_tab_partitions DEST 
         ON SRC.table_name = DEST.table_name 
            AND DEST.sample_size IS NULL 
            AND EXISTS (SELECT 1 
                        FROM   user_indexes DI 
                        WHERE  DI.table_name = DEST.table_name 
                               AND DI.index_type = 'BITMAP') 
ORDER  BY SRC.table_name, 
          DEST.partition_name; 


The SQL almost consumes 100% CPU.
*BlackSwan formatted & used {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/102589/

[Updated on: Wed, 07 May 2014 09:44] by Moderator

Report message to a moderator

Re: High CPU Consuming Query [message #613501 is a reply to message #613500] Wed, 07 May 2014 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ORAFAQ tuning below
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: High CPU Consuming Query [message #613508 is a reply to message #613501] Wed, 07 May 2014 10:39 Go to previous messageGo to next message
nmoham
Messages: 8
Registered: July 2011
Junior Member
EXPLAIN PLAN:

11:33:32 SQL> explain plan for
11:33:33 2 SELECT SRC.TABLE_NAME, SRC.PARTITION_NAME SRC_PARTITION, DEST.PARTITION_NAME DEST_PARTITION
11:33:33 3 FROM ( SELECT TABLE_NAME, PARTITION_NAME
11:33:33 4 FROM ( SELECT PC.TABLE_NAME, MAX(PC.LAST_ANALYZED) LAST_ANALYZED, PC.PARTITION_NAME, ROW_NUMBER()
11:33:33 5 OVER (PARTITION BY PC.TABLE_NAME ORDER BY MAX(PC.LAST_ANALYZED) DESC) R
11:33:33 6 FROM USER_IND_COLUMNS IC JOIN USER_INDEXES I ON IC.INDEX_NAME = I.INDEX_NAME
11:33:33 7 JOIN USER_PART_COL_STATISTICS PC ON IC.TABLE_NAME = PC.TABLE_NAME AND PC.COLUMN_NAME IN (IC.COLUMN_NAME, 'SAMPLETIME', 'DOWNTIME_STATE')
11:33:33 8 WHERE I.INDEX_TYPE = 'BITMAP' GROUP BY PC.TABLE_NAME, PC.PARTITION_NAME HAVING SUM(DECODE(PC.HISTOGRAM, 'NONE', 1, 0)) = 0 )
11:33:33 9 WHERE R = 1 ) SRC JOIN USER_TAB_PARTITIONS DEST ON SRC.TABLE_NAME = DEST.TABLE_NAME AND DEST.SAMPLE_SIZE IS NULL
11:33:33 10 AND EXISTS (SELECT 1 FROM USER_INDEXES DI WHERE DI.TABLE_NAME=DEST.TABLE_NAME AND DI.INDEX_TYPE='BITMAP')
11:33:33 11 ORDER BY SRC.TABLE_NAME, DEST.PARTITION_NAME;

Explained.

Elapsed: 00:00:09.81
11:33:44 SQL> SELECT * FROM table(dbms_xplan.display);
Plan hash value: 1664147760

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 3729 (1)| 00:00:45 |
| 1 | SORT ORDER BY | | 1 | 97 | 3729 (1)| 00:00:45 |
| 2 | NESTED LOOPS SEMI | | 1 | 97 | 3728 (1)| 00:00:45 |
| 3 | NESTED LOOPS | | 1 | 95 | 3650 (1)| 00:00:44 |
|* 4 | VIEW | | 1 | 53 | 3632 (1)| 00:00:44 |
|* 5 | WINDOW SORT PUSHED RANK | | 1 | 4292 | 3632 (1)| 00:00:44 |
|* 6 | FILTER | | | | | |
| 7 | HASH GROUP BY | | 1 | 4292 | 3632 (1)| 00:00:44 |
| 8 | NESTED LOOPS | | 1 | 4292 | 3630 (1)| 00:00:44 |
| 9 | NESTED LOOPS | | 1 | 4288 | 3629 (1)| 00:00:44 |
| 10 | NESTED LOOPS OUTER | | 1 | 4280 | 3627 (1)| 00:00:44 |
| 11 | NESTED LOOPS OUTER | | 1 | 4276 | 3626 (1)| 00:00:44 |
| 12 | NESTED LOOPS OUTER | | 1 | 4268 | 3624 (1)| 00:00:44 |
| 13 | NESTED LOOPS OUTER | | 1 | 4265 | 3623 (1)| 00:00:44 |
| 14 | NESTED LOOPS OUTER | | 1 | 4254 | 3622 (1)| 00:00:44 |
| 15 | NESTED LOOPS OUTER | | 1 | 4249 | 3622 (1)| 00:00:44 |
| 16 | NESTED LOOPS | | 1 | 4219 | 3619 (1)| 00:00:44 |
| 17 | NESTED LOOPS | | 2 | 4404 | 3601 (1)| 00:00:44 |
| 18 | NESTED LOOPS | | 121 | 256K| 3593 (1)| 00:00:44 |
|* 19 | HASH JOIN | | 113 | 235K| 3254 (1)| 00:00:40 |
|* 20 | INDEX RANGE SCAN | I_OBJ2 | 352 | 30272 | 110 (0)| 00:00:02 |
| 21 | VIEW | USER_IND_COLUMNS | 113 | 226K| 3143 (1)| 00:00:38 |
| 22 | UNION-ALL | | | | | |
| 23 | NESTED LOOPS OUTER | | 1 | 70 | 3 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 26 | 2 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS CLUSTER | ATTRCOL$ | 1 | 44 | 1 (0)| 00:00:01 |
| 27 | NESTED LOOPS OUTER | | 85 | 13515 | 1579 (1)| 00:00:19 |
| 28 | NESTED LOOPS | | 85 | 9775 | 1578 (1)| 00:00:19 |
|* 29 | HASH JOIN | | 1385 | 120K| 1507 (1)| 00:00:19 |
|* 30 | HASH JOIN | | 1385 | 76175 | 1396 (1)| 00:00:17 |
|* 31 | HASH JOIN | | 11363 | 432K| 937 (1)| 00:00:12 |
|* 32 | TABLE ACCESS FULL | IND$ | 11363 | 122K| 460 (1)| 00:00:06 |
| 33 | INDEX FAST FULL SCAN | I_OBJ5 | 184K| 5035K| 474 (1)| 00:00:06 |
| 34 | TABLE ACCESS FULL | ICOL$ | 22444 | 350K| 458 (1)| 00:00:06 |
|* 35 | INDEX RANGE SCAN | I_OBJ2 | 4775 | 158K| 110 (0)| 00:00:02 |
| 36 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 26 | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 0 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 44 | 1 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 0 (0)| 00:00:01 |
| 40 | NESTED LOOPS OUTER | | 1 | 70 | 3 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 26 | 2 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | I_COL3 | 1 | | 1 (0)| 00:00:01 |
|* 43 | TABLE ACCESS CLUSTER | ATTRCOL$ | 1 | 44 | 1 (0)| 00:00:01 |
| 44 | NESTED LOOPS OUTER | | 28 | 4536 | 1564 (2)| 00:00:19 |
|* 45 | HASH JOIN | | 28 | 3304 | 1563 (2)| 00:00:19 |
| 46 | NESTED LOOPS | | 4120 | 430K| 1104 (2)| 00:00:14 |
|* 47 | HASH JOIN | | 4265 | 337K| 883 (2)| 00:00:11 |
| 48 | NESTED LOOPS | | | | | |
| 49 | NESTED LOOPS | | 4265 | 208K| 401 (1)| 00:00:05 |
|* 50 | INDEX RANGE SCAN | I_OBJ2 | 4265 | 141K| 110 (0)| 00:00:02 |
|* 51 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 0 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 16 | 1 (0)| 00:00:01 |
|* 53 | INDEX FAST FULL SCAN | I_OBJ5 | 174K| 5296K| 480 (2)| 00:00:06 |
| 54 | TABLE ACCESS CLUSTER | COL$ | 1 | 26 | 1 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 56 | TABLE ACCESS FULL | ICOL$ | 22444 | 241K| 458 (1)| 00:00:06 |
| 57 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 44 | 1 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 0 (0)| 00:00:01 |
|* 59 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 34 | 3 (0)| 00:00:01 |
|* 60 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 2 (0)| 00:00:01 |
|* 61 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 34 | 1 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 0 (0)| 00:00:01 |
|* 63 | VIEW | TP$ | 1 | 2017 | 9 (23)| 00:00:01 |
| 64 | SORT UNIQUE | | 36 | 2880 | 9 (56)| 00:00:01 |
| 65 | UNION ALL PUSHED PREDICATE | | | | | |
| 66 | NESTED LOOPS OUTER | | 18 | 1440 | 4 (0)| 00:00:01 |
| 67 | NESTED LOOPS | | 18 | 648 | 3 (0)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 1 | 10 | 2 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | I_TABPART_OBJ$ | 1 | | 1 (0)| 00:00:01 |
|* 70 | TABLE ACCESS CLUSTER | COL$ | 18 | 468 | 1 (0)| 00:00:01 |
|* 71 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 72 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 44 | 1 (0)| 00:00:01 |
|* 73 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 0 (0)| 00:00:01 |
| 74 | NESTED LOOPS OUTER | | 18 | 1440 | 3 (0)| 00:00:01 |
| 75 | NESTED LOOPS | | 18 | 648 | 2 (0)| 00:00:01 |
| 76 | TABLE ACCESS BY INDEX ROWID | TABCOMPART$ | 1 | 10 | 1 (0)| 00:00:01 |
|* 77 | INDEX UNIQUE SCAN | I_TABCOMPART$ | 1 | | 0 (0)| 00:00:01 |
|* 78 | TABLE ACCESS CLUSTER | COL$ | 18 | 468 | 1 (0)| 00:00:01 |
|* 79 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 80 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 44 | 1 (0)| 00:00:01 |
|* 81 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | 0 (0)| 00:00:01 |
| 82 | TABLE ACCESS BY INDEX ROWID | HIST_HEAD$ | 1 | 30 | 3 (0)| 00:00:01 |
|* 83 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | | 2 (0)| 00:00:01 |
|* 84 | INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | 5 | 0 (0)| 00:00:01 |
| 85 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 |
|* 86 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
| 87 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 88 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 89 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 90 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 |
|* 91 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 92 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 |
| 93 | VIEW | USER_TAB_PARTITIONS | 1 | 42 | 18 (0)| 00:00:01 |
| 94 | UNION ALL PUSHED PREDICATE | | | | | |
| 95 | NESTED LOOPS | | 1 | 143 | 7 (0)| 00:00:01 |
| 96 | NESTED LOOPS | | 1 | 140 | 6 (0)| 00:00:01 |
| 97 | NESTED LOOPS OUTER | | 1 | 123 | 5 (0)| 00:00:01 |
| 98 | NESTED LOOPS OUTER | | 1 | 112 | 4 (0)| 00:00:01 |
| 99 | NESTED LOOPS | | 1 | 107 | 4 (0)| 00:00:01 |
|*100 | INDEX RANGE SCAN | I_OBJ2 | 1 | 86 | 3 (0)| 00:00:01 |
|*101 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 1 | 21 | 1 (0)| 00:00:01 |
|*102 | INDEX UNIQUE SCAN | I_TABPART_OBJ$ | 1 | | 0 (0)| 00:00:01 |
|*103 | INDEX UNIQUE SCAN | I_DEFERRED_STG1 | 1 | 5 | 0 (0)| 00:00:01 |
| 104 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 |
|*105 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
|*106 | TABLE ACCESS CLUSTER | TAB$ | 1 | 17 | 1 (0)| 00:00:01 |
|*107 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 108 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|*109 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 110 | NESTED LOOPS | | 1 | 115 | 5 (0)| 00:00:01 |
| 111 | NESTED LOOPS | | 1 | 98 | 4 (0)| 00:00:01 |
|*112 | INDEX RANGE SCAN | I_OBJ2 | 1 | 86 | 3 (0)| 00:00:01 |
|*113 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 1 | 12 | 1 (0)| 00:00:01 |
|*114 | INDEX UNIQUE SCAN | I_TABPART_OBJ$ | 1 | | 0 (0)| 00:00:01 |
|*115 | TABLE ACCESS CLUSTER | TAB$ | 1 | 17 | 1 (0)| 00:00:01 |
|*116 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 117 | NESTED LOOPS | | 1 | 132 | 6 (0)| 00:00:01 |
| 118 | NESTED LOOPS | | 1 | 115 | 5 (0)| 00:00:01 |
| 119 | NESTED LOOPS | | 1 | 112 | 4 (0)| 00:00:01 |
|*120 | INDEX RANGE SCAN | I_OBJ2 | 1 | 86 | 3 (0)| 00:00:01 |
|*121 | TABLE ACCESS BY INDEX ROWID | TABCOMPART$ | 1 | 26 | 1 (0)| 00:00:01 |
|*122 | INDEX UNIQUE SCAN | I_TABCOMPART$ | 1 | | 0 (0)| 00:00:01 |
| 123 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|*124 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|*125 | TABLE ACCESS CLUSTER | TAB$ | 1 | 17 | 1 (0)| 00:00:01 |
|*126 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
| 127 | VIEW PUSHED PREDICATE | VW_SQ_1 | 2 | 4 | 77 (0)| 00:00:01 |
| 128 | NESTED LOOPS OUTER | | 2 | 212 | 77 (0)| 00:00:01 |
| 129 | NESTED LOOPS OUTER | | 2 | 204 | 75 (0)| 00:00:01 |
| 130 | NESTED LOOPS OUTER | | 2 | 188 | 71 (0)| 00:00:01 |
| 131 | NESTED LOOPS OUTER | | 2 | 182 | 69 (0)| 00:00:01 |
| 132 | NESTED LOOPS | | 2 | 160 | 67 (0)| 00:00:01 |
| 133 | NESTED LOOPS | | 3 | 207 | 58 (0)| 00:00:01 |
| 134 | NESTED LOOPS | | 3 | 105 | 57 (0)| 00:00:01 |
| 135 | INDEX FULL SCAN | I_USER2 | 55 | 220 | 1 (0)| 00:00:01 |
|*136 | INDEX RANGE SCAN | I_OBJ2 | 1 | 31 | 2 (0)| 00:00:01 |
|*137 | TABLE ACCESS CLUSTER | IND$ | 1 | 34 | 1 (0)| 00:00:01 |
|*138 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
|*139 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 11 | 3 (0)| 00:00:01 |
|*140 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
| 141 | TABLE ACCESS CLUSTER | SEG$ | 1 | 11 | 1 (0)| 00:00:01 |
|*142 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| 00:00:01 |
| 143 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|*144 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|*145 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8 | 2 (0)| 00:00:01 |
|*146 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("R"=1)
5 - filter(ROW_NUMBER() OVER ( PARTITION BY "O"."NAME" ORDER BY MAX("H"."TIMESTAMP#") DESC )<=1)
6 - filter(SUM(DECODE(CASE WHEN NVL("H"."ROW_CNT",0)=0 THEN 'NONE' WHEN ("H"."BUCKET_CNT">255 OR
("H"."BUCKET_CNT">"H"."DISTCNT" AND "H"."ROW_CNT"="H"."DISTCNT" AND "H"."DENSITY"*"H"."BUCKET_CNT"<1)) THEN
'FREQUENCY' ELSE 'HEIGHT BALANCED' END ,'NONE',1,0))=0)
19 - access("IC"."TABLE_NAME"="O"."NAME")
20 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND
"O"."LINKNAME" IS NULL AND "O"."TYPE#"=19)
filter("O"."TYPE#"=19 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
25 - access("TC"."OBJ#"=:B1 AND "TC"."INTCOL#"=:B2-1)
26 - filter("AC"."OBJ#"(+)=:B1 AND "AC"."INTCOL#"(+)=:B2-1 AND "TC"."OBJ#"="AC"."OBJ#"(+) AND
"TC"."INTCOL#"="AC"."INTCOL#"(+))
29 - access("IC"."BO#"="BASE"."OBJ#")
30 - access("IC"."OBJ#"="IDX"."OBJ#")
31 - access("IDX"."OBJ#"="I"."OBJ#")
32 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9)
35 - access("BASE"."OWNER#"=USERENV('SCHEMAID'))
filter("BASE"."NAMESPACE"=1 OR "BASE"."NAMESPACE"=5)
37 - access("C"."OBJ#"="BASE"."OBJ#" AND "C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SP
ARE2"))
39 - access("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
42 - access("TC"."OBJ#"=:B1 AND "TC"."INTCOL#"=:B2-1)
43 - filter("AC"."OBJ#"(+)=:B1 AND "AC"."INTCOL#"(+)=:B2-1 AND "TC"."OBJ#"="AC"."OBJ#"(+) AND
"TC"."INTCOL#"="AC"."INTCOL#"(+))
45 - access("IC"."OBJ#"="IDX"."OBJ#")
filter("C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2"))
47 - access("I"."BO#"="BASE"."OBJ#")
50 - access("IDX"."OWNER#"=USERENV('SCHEMAID') AND "IDX"."NAMESPACE"=4)
filter("IDX"."NAMESPACE"=4)
51 - access("IDX"."OBJ#"="I"."OBJ#")
52 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9)
53 - filter("BASE"."OWNER#"<>USERENV('SCHEMAID'))
55 - access("C"."OBJ#"="BASE"."OBJ#")
58 - access("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
59 - filter(BITAND("O"."FLAGS",128)=0)
60 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "IC"."INDEX_NAME"="O"."NAME")
61 - filter(DECODE(BITAND("I"."PROPERTY",16),0,'','FUNCTION-BASED
')||DECODE("I"."TYPE#",1,'NORMAL'||DECODE(BITAND("I"."PROPERTY",4),0,'',4,'/REV'),2,'BITMAP',3,'CLUSTER',4,'IOT -
TOP',5,'IOT - NESTED',6,'SECONDARY',7,'ANSI',8,'LOB',9,'DOMAIN')='BITMAP' AND BITAND("I"."FLAGS",4096)=0 AND
("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8
OR "I"."TYPE#"=9))
62 - access("O"."OBJ#"="I"."OBJ#")
63 - filter("TP"."CNAME"="IC"."COLUMN_NAME" OR "TP"."CNAME"='DOWNTIME_STATE' OR "TP"."CNAME"='SAMPLETIME')
69 - access("TP"."OBJ#"="O"."OBJ#")
70 - filter(BITAND("C"."PROPERTY",32768)<>32768)
71 - access("TP"."BO#"="C"."OBJ#")
73 - access("C"."OBJ#"="A"."OBJ#"(+) AND "C"."INTCOL#"="A"."INTCOL#"(+))
77 - access("TCP"."OBJ#"="O"."OBJ#")
78 - filter(BITAND("C"."PROPERTY",32768)<>32768)
79 - access("TCP"."BO#"="C"."OBJ#")
81 - access("C"."OBJ#"="A"."OBJ#"(+) AND "C"."INTCOL#"="A"."INTCOL#"(+))
83 - access("TP"."OBJ#"="H"."OBJ#"(+) AND "TP"."INTCOL#"="H"."INTCOL#"(+))
84 - access("I"."OBJ#"="DS"."OBJ#"(+))
86 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+))
88 - access("I"."TS#"="TS"."TS#"(+))
89 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
90 - access("ITO"."OWNER#"="ITU"."USER#"(+))
91 - access("I"."BO#"="IO"."OBJ#")
92 - access("IO"."OWNER#"="IU"."USER#")
100 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="TABLE_NAME" AND "O"."NAMESPACE"=1 AND
"O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
filter("O"."LINKNAME" IS NULL)
101 - filter("TP"."SAMPLESIZE" IS NULL)
102 - access("O"."OBJ#"="TP"."OBJ#")
103 - access("TP"."OBJ#"="DS"."OBJ#"(+))
105 - access("TP"."TS#"="S"."TS#"(+) AND "TP"."FILE#"="S"."FILE#"(+) AND "TP"."BLOCK#"="S"."BLOCK#"(+))
106 - filter(BITAND("T"."PROPERTY",64)<>64 AND BITAND("T"."TRIGFLAG",1073741824)<>1073741824)
107 - access("TP"."BO#"="T"."OBJ#")
109 - access("TS"."TS#"="TP"."TS#")
112 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="TABLE_NAME" AND "O"."NAMESPACE"=1 AND
"O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
filter("O"."LINKNAME" IS NULL)
113 - filter("TP"."SAMPLESIZE" IS NULL)
114 - access("O"."OBJ#"="TP"."OBJ#")
115 - filter(BITAND("T"."PROPERTY",64)=64 AND BITAND("T"."TRIGFLAG",1073741824)<>1073741824)
116 - access("TP"."BO#"="T"."OBJ#")
120 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"="TABLE_NAME" AND "O"."NAMESPACE"=1 AND
"O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)
filter("O"."LINKNAME" IS NULL)
121 - filter("TCP"."SAMPLESIZE" IS NULL)
122 - access("O"."OBJ#"="TCP"."OBJ#")
124 - access("TCP"."DEFTS#"="TS"."TS#")
125 - filter(BITAND("T"."PROPERTY",64)<>64 AND BITAND("T"."TRIGFLAG",1073741824)<>1073741824)
126 - access("TCP"."BO#"="T"."OBJ#")
136 - access("IO"."OWNER#"="IU"."USER#" AND "IO"."NAME"="DEST"."TABLE_NAME")
137 - filter(DECODE(BITAND("I"."PROPERTY",16),0,'','FUNCTION-BASED
')||DECODE("I"."TYPE#",1,'NORMAL'||DECODE(BITAND("I"."PROPERTY",4),0,'',4,'/REV'),2,'BITMAP',3,'CLUSTER',4,'IOT -
TOP',5,'IOT - NESTED',6,'SECONDARY',7,'ANSI',8,'LOB',9,'DOMAIN')='BITMAP' AND BITAND("I"."FLAGS",4096)=0 AND
("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8
OR "I"."TYPE#"=9))
138 - access("I"."BO#"="IO"."OBJ#")
139 - filter(BITAND("O"."FLAGS",128)=0)
140 - access("O"."OBJ#"="I"."OBJ#" AND "O"."OWNER#"=USERENV('SCHEMAID'))
142 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+))
144 - access("I"."TS#"="TS"."TS#"(+))
145 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+))
146 - access("ITO"."OWNER#"="ITU"."USER#"(+))

256 rows selected.

Elapsed: 00:00:06.32
Re: High CPU Consuming Query [message #613509 is a reply to message #613508] Wed, 07 May 2014 10:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
you can lead some folks to knowledge, but you can't make them THINK!
http://www.orafaq.com/forum/t/174502/102589/
Re: High CPU Consuming Query [message #613510 is a reply to message #613508] Wed, 07 May 2014 10:48 Go to previous messageGo to next message
nmoham
Messages: 8
Registered: July 2011
Junior Member
Tables:

Contains all Internal Oracle Views :
USER_IND_COLUMNS
USER_PART_COL_STATISTICS
USER_TAB_PARTITIONS
USER_INDEXES
Re: High CPU Consuming Query [message #613512 is a reply to message #613500] Wed, 07 May 2014 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe you can explain us what's the query intends to do and return.

Re: High CPU Consuming Query [message #613528 is a reply to message #613512] Wed, 07 May 2014 13:09 Go to previous messageGo to next message
nmoham
Messages: 8
Registered: July 2011
Junior Member
Query returns something like below:

TABLE_NAME SRC_PARTITION DEST_PARTITION
------------------------------ ------------------------------ ------------------------------
BPM_COMPONENTS_90000 P87 P88
BPM_COMPONENTS_90000 P87 P89
BPM_COMPONENTS_90000 P87 P90
BPM_COMPONENTS_90000 P87 P91
BPM_COMPONENTS_90000 P87 P92
BPM_COMPONENTS_90000 P87 P93
BPM_COMPONENTS_90000 P87 P94
BPM_COMPONENTS_90000 P87 P95
BPM_TRANS_2DAY_90000 P2 P3
BPM_TRANS_2HR_90000 P6 P7
BPM_TRANS_3DAY_90000 P2 P3
BPM_TRANS_3HR_90000 P6 P7
BPM_TRANS_90000 P89 P90
BPM_TRANS_90000 P89 P91
BPM_TRANS_90000 P89 P92
BPM_TRANS_90000 P89 P93
BPM_TRANS_90000 P89 P94
BPM_TRANS_90000 P89 P95
BPM_TRANS_90000 P89 P96
BPM_TRANS_DAY_90000 P1 P2
BPM_TRANS_ERRS_2DAY_90000 P2 P3
BPM_TRANS_ERRS_2HR_90000 P6 P7
BPM_TRANS_ERRS_3DAY_90000 P2 P3
BPM_TRANS_ERRS_3HR_90000 P6 P7
BPM_TRANS_ERRS_90000 P83 P88
BPM_TRANS_ERRS_90000 P83 P89
BPM_TRANS_ERRS_90000 P83 P90
BPM_TRANS_ERRS_90000 P83 P91
BPM_TRANS_ERRS_90000 P83 P92
BPM_TRANS_ERRS_90000 P83 P93
BPM_TRANS_ERRS_90000 P83 P94
BPM_TRANS_ERRS_90000 P83 P95
BPM_TRANS_ERRS_DAY_90000 P2 P3
BPM_TRANS_ERRS_HR_90000 P4 P7
BPM_TRANS_HR_90000 P5 P6
Re: High CPU Consuming Query [message #613534 is a reply to message #613528] Wed, 07 May 2014 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't care what it returns, I want to know what you want it returns.

[Edit: English]

[Updated on: Thu, 08 May 2014 05:47]

Report message to a moderator

Re: High CPU Consuming Query [message #613583 is a reply to message #613534] Thu, 08 May 2014 05:05 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Are you sure it's not spending 100% of IT'S time on CPU, as opposed to consuming 100% of CPU?

Are are there loads of things running it at once?
Re: High CPU Consuming Query [message #613602 is a reply to message #613583] Thu, 08 May 2014 07:47 Go to previous messageGo to next message
nmoham
Messages: 8
Registered: July 2011
Junior Member
Whenever this query runs the CPU spikes to 100% for 5-10 minutes...
Re: High CPU Consuming Query [message #613619 is a reply to message #613602] Thu, 08 May 2014 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe the query is not the correct or best one to do what you want.

Re: High CPU Consuming Query [message #613674 is a reply to message #613602] Fri, 09 May 2014 02:06 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
nmoham wrote on Thu, 08 May 2014 13:47
Whenever this query runs the CPU spikes to 100% for 5-10 minutes...


How many sessions are running it concurrently?

That is a serial plan and unless I've gone mad overnight (not impossible) at worst it'll (effectively) sit on a single CPU unless you've got multiple instances of it running*.

You have more than one CPU/core, right?



*Unless there's a weird and wonderful hole in one of those dictionary views.

[Updated on: Fri, 09 May 2014 02:07]

Report message to a moderator

Re: High CPU Consuming Query [message #613678 is a reply to message #613500] Fri, 09 May 2014 02:19 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What is this query meant to achieve? Can you explain in words?

One possible fix: analyze the data dictionary. When dd you last gather dictionary stats?

(Please read How to use [code] tags and make your code easier to read, your exec plans are unreadable.)
Previous Topic: slow sql
Next Topic: SQL Query too slow
Goto Forum:
  


Current Time: Thu Mar 28 17:11:06 CDT 2024