Solution | data | database | problem | performance | optimization | statement
Problem Description:
October 25 Morning Binzhou Netcom's Engineers report OSS application system running slowly, the specific operation is through the OSS system query, a long time to return results, seriously affecting the normal use of customers.
Problem handling:
1. Login to the database host, with SAR command to see the value of idle continued to 0,cpu resources have been depleted:
You can see that the two process numbers are 27420 and 27418, respectively.
3. Capture SQL statements that consume high CPU utilization:
Here's a summary of my SQL statement:
Sql>set Line 240
Sql>set Verify off
Sql>column SID Format 999
Sql>column PID Format 999
Sql>column s_# Format 999
Sql>column username format A9 heading "ORA User"
Sql>column Program Format A29
Sql>column SQL Format A60
Sql>column osname format A9 Heading "OS User"
Sql>select p.pid pid,s.sid sid,p.spid spid,s.username username,
S.osuser osname,p.serial# S_#,p.terminal,p.program Program,
P.background,s.status,rtrim (SUBSTR (A.sql_text, 1)) SQL
From V$process P, v$session s,v$sqlarea A WHERE p.addr = s.paddr
and s.sql_address = a.address (+) and p.spid like '%&1% ';
Enter value for 1:27,420 (note that the PID for the highest CPU process should be entered here)
Get the following SQL statement:
Select NVL (SUM (localcharge), 0), NVL (sum (usage), 0) from Localusage where To_char (Endtime, ' YYYYMMDD ') =20041016
and localcharge>0 and caller like ' 543,886% ';
The 27418 process corresponds to the following SQL statement:
Select NVL (SUM (localcharge), 0) from Localusage where To_char (Endtime, ' YYYYMMDD ') =20041016 and caller like ' 543,888% ';
4. Use the relevant user to connect to the database and check its execution plan:
Sql>connect Wacos/oss
Connected.
Sql>@?/rdbms/admin/utlxplan.sql
Table created.
Sql>set Autotrace on
Sql>set Timing on
Sql>select NVL (SUM (localcharge), 0), NVL (sum (usage), 0) from Localusage where To_char (Endtime, ' YYYYMMDD ') =20041016
and localcharge>0 and caller like ' 543,886% ';
Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
88588 consistent gets
15615 Physical Reads
0 Redo Size
507 Bytes sent via sql*net to client
651 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
This SQL statement has results returned, and found that the Localusage table did a full table scan, but the speed is very slow, with more than 3 minutes.
Sql> Select COUNT (*) from Localusage;
COUNT (*)
----------
5793776
The table has more than 5.79 million records, a large amount of data, full table scanning is no longer suitable.
5. Check the type of the table:
Sql> SELECT index_name, TABLE_NAME, STATUS, partitioned from user_indexes WHERE table_name= ' localusage ';
INDEX_NAME table_name STATUS PAR
------------------------------ ------------------------------ -------- ---
I_localusage_sid localusage N/a YES
UI_LOCALUSAGE_ST_SEQ localusage N/a YES
Sql> SELECT index_name,table_name,locality from user_part_indexes where table_name= ' localusage ';
INDEX_NAME table_name Locali
------------------------------ ------------------------------ ------
I_localusage_sid Localusage Local
Ui_localusage_st_seq Localusage Local
The table was found to be a partitioned table and a partitioned index was established on the Serviceid,startime and Cdrsequence columns, with the index type being a local index.
6. View index key values for partitioned indexes:
Sql> Select Index_name,column_name,index_owner from dba_ind_columns where table_name= ' localusage ';
It was found that no index was established on both the Endtime and caller columns, which was the ultimate cause of the full table scan of the SQL statement.
7. Decide to create a new partitioned index to eliminate the full table scan:
(1). First View Localusage table partitioning:
Sql> Select Partition_name,tablespace_name from user_tab_partitions where table_name= ' localusage ';
(2). Create a local partition index on the caller column:
Sql>set Timing on
Sql>create index I_localusage_caller on localusage (CALLER)
Local
(
PARTITION localusage_200312,
PARTITION localusage_200401,
PARTITION localusage_200402,
PARTITION localusage_200404,
PARTITION localusage_200405,
PARTITION localusage_200406,
PARTITION localusage_200407,
PARTITION localusage_200409,
PARTITION localusage_200410,
PARTITION localusage_200411,
PARTITION localusage_200403,
PARTITION localusage_200408,
PARTITION localusage_200412
)
Tablespace Wacos
STORAGE (
INITIAL 6553600
NEXT 6553600
Maxextents Unlimited
Pctincrease 0)
PCTFREE 5
nologging;
Index created.
elapsed:00:06:27.90 (6 minutes due to large data volume)
8. View the execution plan again:
Sql>select NVL (SUM (localcharge), 0), NVL (sum (usage), 0) from Localusage where To_char (Endtime, ' YYYYMMDD ') =20041016
and localcharge>0 and caller like ' 543,886% ';
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.