OraFAQ Forum: Performance Tuning » Oracle 12c -Query CPU Cost is very high

Home » RDBMS Server » Performance Tuning » Oracle 12c -Query CPU Cost is very high (Oracle 12c Release 1, Linux(Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle 12c -Query CPU Cost is very high [message #658784]

Tue, 27 December 2016 02:24

anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India

Senior Member

Dear All,

I have created tables with compression mode enabled and index with compression mode enabled.
Please find below code for sample table creation with data insertion

DROP TABLE COMPR_T1;
DROP TABLE COMPR_T2;
CREATE TABLE COMPR_T1(V1 CHAR(50), V2 CHAR(50), ID1 NUMBER) COMPRESS FOR OLTP;
CREATE TABLE COMPR_T2(V21 CHAR(50), V22 CHAR(50), ID21 NUMBER) COMPRESS FOR OLTP;

--drop table COMPR_T12;

INSERT INTO COMPR_T1 SELECT 'TEST1234TEST-'||LEVEL,'TEST5678TEST-'||LEVEL,LEVEL FROM DUAL CONNECT BY LEVEL<=100000;
COMMIT;

INSERT INTO COMPR_T2 SELECT 'TEST1234TEST-'||LEVEL,'TEST5678TEST-'||LEVEL,LEVEL FROM DUAL CONNECT BY LEVEL<=100000;
COMMIT;

INSERT INTO COMPR_T1 SELECT 'TEST1234TEST-'||LEVEL,'TEST5678TEST-  ',LEVEL FROM DUAL CONNECT BY LEVEL<=100000;
COMMIT;

INSERT INTO COMPR_T2 SELECT 'TEST1234TEST-'||LEVEL,'TEST5678TEST-  ',LEVEL FROM DUAL CONNECT BY LEVEL<=100000;
COMMIT;
--DELETE FROM COMPR_T1 WHERE V1 IS NULL;
INSERT INTO COMPR_T1 SELECT NULL,NULL,LEVEL FROM DUAL CONNECT BY LEVEL<=10000;
COMMIT;

INSERT INTO COMPR_T2 SELECT NULL,NULL,LEVEL FROM DUAL CONNECT BY LEVEL<=10000;
COMMIT;

CREATE INDEX COMPR_T1NUNQ ON COMPR_T1(ID1) COMPRESS ADVANCED LOW;
CREATE INDEX COMPR_T2NUNQ ON COMPR_T2(ID21) COMPRESS ADVANCED LOW;
ALTER INDEX COMPR_T1NUNQ REBUILD;
ALTER INDEX COMPR_T2NUNQ REBUILD;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','COMPR_T1');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','COMPR_T2');

After running above coode, I tried running below code and I see query CPU cost is very much high(CPU Cost: 340250067), because of that queries are very much slow
and few queries throwing error (after running for 90 mins) as below
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP


SELECT T1.* FROM COMPR_T1 T1 , COMPR_T2
WHERE ID1=ID21
AND EXISTS (SELECT 1 FROM COMPR_T2 T2 WHERE T1.V1=T2.V21);

Plan
SELECT STATEMENT ALL_ROWS Cost: 4,444 Bytes: 65,401,019 Cardinality: 416,567
5 HASH JOIN Cost: 4,444 Bytes: 65,401,019 Cardinality: 416,567
1 INDEX FAST FULL SCAN INDEX SCOTT.COMPR_T2NUNQ Cost: 84 Bytes: 1,050,000 Cardinality: 210,000
4 HASH JOIN RIGHT SEMI Cost: 2,634 Bytes: 30,400,000 Cardinality: 200,000
2 TABLE ACCESS FULL TABLE SCOTT.COMPR_T2 Cost: 440 Bytes: 10,290,000 Cardinality: 210,000
3 TABLE ACCESS FULL TABLE SCOTT.COMPR_T1 Cost: 441 Bytes: 21,630,000 Cardinality: 210,000

Please help to resolve this issue.

Thanks in advance.

Report message to a moderator

Re: Oracle 12c -Query CPU Cost is very high [message #658785 is a reply to message #658784]

Tue, 27 December 2016 02:35

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

I see nothing wring. After running your code, the query takes under 2 seconds for me:

orclz> set timing on
orclz> set autot trace
orclz> SELECT T1.* FROM COMPR_T1 T1 , COMPR_T2
  2  WHERE ID1=ID21
  3  AND EXISTS (SELECT 1 FROM COMPR_T2 T2 WHERE T1.V1=T2.V21);

420000 rows selected.

Elapsed: 00:00:01.75

Execution Plan
----------------------------------------------------------
Plan hash value: 2964653491

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   416K|    62M|       |  4966   (1)| 00:00:01 |
|*  1 |  HASH JOIN            |              |   416K|    62M|  3488K|  4966   (1)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| COMPR_T2NUNQ |   210K|  1025K|       |   128   (1)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT SEMI|              |   200K|    28M|    12M|  3116   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | COMPR_T2     |   210K|     9M|       |   683   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL  | COMPR_T1     |   210K|    20M|       |   684   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - access("ID1"="ID21")
   3 - access("T1"."V1"="T2"."V21")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      33509  consistent gets
       5486  physical reads
          0  redo size
   23726912  bytes sent via SQL*Net to client
     308541  bytes received via SQL*Net from client
      28001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     420000  rows processed

orclz>

Report message to a moderator

Re: Oracle 12c -Query CPU Cost is very high [message #658787 is a reply to message #658785]

Tue, 27 December 2016 03:46

anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India

Senior Member

Thanks for reply, above one is sample example. I have kind of similar tables and query with multiple table joins and exists clause where query is performing very slow.
DB will be migrated to 12c (12.1 release), some queries are running fast in Oracle 11g but same few queries are taking more time to run (1 hour or more and some queries are failing with Oracle error ORA-01652 ) in 12c and in explain plan I am seeing "MERGE JOIN: CARTESIAN" where I am seeing CPU cost very high. Is there anything to be done to avoid high CPU utilization and to avoid "MERGE JOIN: CARTESIAN"?

Report message to a moderator

Re: Oracle 12c -Query CPU Cost is very high [message #658788 is a reply to message #658787]

Tue, 27 December 2016 04:51

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

I do not see MERGE JOIN CARTESIAN in the plan you posted. Are you you asking for help with a completely different query?

This is as though you take your car into a garage, and then say "Please look at my car, I have a problem with my electric toaster".

Report message to a moderator

Re: Oracle 12c -Query CPU Cost is very high [message #660619 is a reply to message #658784]

Wed, 22 February 2017 16:10

mikek
Messages: 29
Registered: January 2017

Junior Member

Thank You for providing the Sample Tables, it was
very helpful in reviewing the query.

From my experience with EXIST, Tuning the Query
that it uses is the Key.

A possible solution. I used a less frequently used
Techniques to tune the SQL. In the 3rd effort I used a
Function Based Index compr_t2(RTRIM(v21)) on the V21 Column to
remove the trailing spaces from the data column V21. It works
because the V21 Column is Defined as CHAR and each column Value
is padded with extra spaces. The new Function based Index is more
compact then a regular Index on the Column. This also required that
the WHERE portion of the query has to use the RTRIM Function on Both
Sides of the =. i.e. "RTRIM(t2.v21) = RTRIM(t1.v1)"

SQL Sections in Output with Explain Plans

1. Baseline Query To show the Baseline Explain Plan.

2. Attempt to improve query with Only a Index on compr_t2(v21).

3. Used Function Based Index on compr_t2(RTRIM(v21)) with modified query.

With the edition of Oracle I am using at home does not allow
me to fully test a Compressed Index.

You will need to look at the improvement of the Query Performance
over the Overhead of adding an additional index to the Table.


SCOTT>SET AUTOTRACE TRACEONLY;


-- 1. Baseline Query (Slightly Formatted)


SCOTT>SELECT t1.*
  2  FROM   compr_t1 t1
  3        ,compr_t2
  4  WHERE  id1=id21
  5  AND    EXISTS (SELECT 1
  6                 FROM   compr_t2 t2
  7                 WHERE  t2.v21 = t1.v1
  8                );

420000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2964653491

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   416K|    62M|       |  5221   (1)| 00:00:01 |
|*  1 |  HASH JOIN            |              |   416K|    62M|  3488K|  5221   (1)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| COMPR_T2NUNQ |   210K|  1025K|       |   128   (1)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT SEMI|              |   200K|    28M|    12M|  3116   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | COMPR_T2     |   210K|     9M|       |   683   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL  | COMPR_T1     |   210K|    20M|       |   683   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   1 - access("ID1"="ID21")
   3 - access("T2"."V21"="T1"."V1")


Statistics
----------------------------------------------------------
         69  recursive calls
          0  db block gets
       7232  consistent gets
       6025  physical reads
          0  redo size
   23733899  bytes sent via SQL*Net to client
     308533  bytes received via SQL*Net from client
      28001  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     420000  rows processed



-- 2. Attempt to improve query with Only a Index on compr_t2(v21).


SCOTT>CREATE INDEX COMPR_T2NUNQ2 ON compr_t2(v21);

Index created.

SCOTT>ALTER INDEX COMPR_T2NUNQ2 REBUILD;

Index altered.

SCOTT>EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','COMPR_T2');

PL/SQL procedure successfully completed.

SCOTT>SELECT t1.*
  2  FROM   compr_t1 t1
  3        ,compr_t2
  4  WHERE  id1=id21
  5  AND    EXISTS (SELECT 1
  6                 FROM   compr_t2 t2
  7                 WHERE  t2.v21 = t1.v1
  8                );

420000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2408882839

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |   416K|    62M|       |  5011   (1)| 00:00:01 |
|*  1 |  HASH JOIN             |               |   416K|    62M|  3488K|  5011   (1)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN | COMPR_T2NUNQ  |   210K|  1025K|       |   128   (1)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT SEMI |               |   200K|    28M|    12M|  2907   (1)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| COMPR_T2NUNQ2 |   210K|     9M|       |   474   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL   | COMPR_T1      |   210K|    20M|       |   683   (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   1 - access("ID1"="ID21")
   3 - access("T2"."V21"="T1"."V1")


Statistics
----------------------------------------------------------
         97  recursive calls
          0  db block gets
       6513  consistent gets
       6011  physical reads
          0  redo size
   23733899  bytes sent via SQL*Net to client
     308533  bytes received via SQL*Net from client
      28001  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     420000  rows processed


-- 3. Used Function Based Index on compr_t2(RTRIM(v21)) with modified query.


SCOTT>DROP INDEX COMPR_T2NUNQ2;

Index dropped.

SCOTT>CREATE INDEX COMPR_T2NUNQ2 ON compr_t2(RTRIM(v21));

Index created.

SCOTT>ALTER INDEX COMPR_T2NUNQ2 REBUILD;

Index altered.

SCOTT>EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','COMPR_T2');

PL/SQL procedure successfully completed.

SCOTT>
SCOTT>SELECT t1.*
  2  FROM   compr_t1 t1
  3        ,compr_t2
  4  WHERE  id1=id21
  5  AND    EXISTS (SELECT 1
  6                 FROM   compr_t2 t2
  7                 WHERE  RTRIM(t2.v21) = RTRIM(t1.v1)
  8                );

420000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 42407975

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |     4 |   508 |       |  2367   (1)| 00:00:01 |
|   1 |  NESTED LOOPS          |               |     4 |   508 |       |  2367   (1)| 00:00:01 |
|*  2 |   HASH JOIN RIGHT SEMI |               |     2 |   244 |  6360K|  2365   (1)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| COMPR_T2NUNQ2 |   210K|  3896K|       |   229   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | COMPR_T1      |   210K|    20M|       |   683   (1)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN     | COMPR_T2NUNQ  |     2 |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access(RTRIM("V21")=RTRIM("T1"."V1"))
   5 - access("ID1"="ID21")

Note
-----
   - this is an adaptive plan


Statistics
----------------------------------------------------------
         27  recursive calls
          0  db block gets
     170148  consistent gets
       3224  physical reads
          0  redo size
   23727473  bytes sent via SQL*Net to client
     308533  bytes received via SQL*Net from client
      28001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     420000  rows processed

SCOTT>

Report message to a moderator

Re: Oracle 12c -Query CPU Cost is very high [message #660849 is a reply to message #660619]

Mon, 27 February 2017 13:00

Caffeine+
Messages: 14
Registered: February 2017

Junior Member

Like John Watson, I too show no issues with the test case:


SQL> select * from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Solaris: Version 12.1.0.2.0 - Production                                          0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> select /*+ gather_plan_statistcs */ T1.* FROM COMPR_T1 T1 , COMPR_T2
WHERE ID1=ID21 AND EXISTS (SELECT 1 FROM COMPR_T2 T2 WHERE T1.V1=T2.V21);


......
TEST1234TEST-95790                                 TEST5678TEST-                                           95790
TEST1234TEST-95791                                 TEST5678TEST-                                           95791
TEST1234TEST-95791                                 TEST5678TEST-                                           95791

420000 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST OUTLINE +predicate'));
SQL_ID  dpqz47h84a3rf, child number 0
-------------------------------------
select /*+ gather_plan_statistcs */ T1.* FROM COMPR_T1 T1 , COMPR_T2
WHERE ID1=ID21 AND EXISTS (SELECT 1 FROM COMPR_T2 T2 WHERE T1.V1=T2.V21)

Plan hash value: 2179466891

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |    420K|00:00:01.77 |   33074 |       |       |          |
|*  1 |  HASH JOIN                  |              |      1 |    236K|    420K|00:00:01.77 |   33074 |    11M|  6538K|   16M (0)|
|   2 |   INDEX FAST FULL SCAN      | COMPR_T2NUNQ |      1 |    215K|    210K|00:00:00.04 |     470 |  1025K|  1025K|          |
|*  3 |   HASH JOIN RIGHT SEMI      |              |      1 |    184K|    200K|00:00:01.06 |   32604 |    19M|  5722K|   24M (0)|
|   4 |    TABLE ACCESS STORAGE FULL| COMPR_T2     |      1 |    215K|    210K|00:00:00.05 |    2518 |  1025K|  1025K|          |
|   5 |    TABLE ACCESS STORAGE FULL| COMPR_T1     |      1 |    210K|    210K|00:00:00.32 |   30086 |  1025K|  1025K|          |
---------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
      INDEX_FFS(@"SEL$5DA710D3" "COMPR_T2"@"SEL$1" ("COMPR_T2"."ID21"))
      LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2" "COMPR_T2"@"SEL$1")
      USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "COMPR_T2"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "T2"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "COMPR_T2"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - access("ID1"="ID21")
   3 - access("T1"."V1"="T2"."V21")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


52 rows selected.

SQL>

I would suggest looking at the system hosting this database to see if there is contention for resources.

Report message to a moderator

Previous Topic:	Performance issue in oracle 10g database
Next Topic:	IM Column Store ( indexes,lob data)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 06 07:31:18 CST 2025